The Easysoft ODBC-Oracle WP Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load, which database to connect to and how to connect to it. This chapter describes how to create data sources, use DSN-less connections and configure the Easysoft ODBC-Oracle WP Driver.
Before setting up a data source, you must have successfully installed the Easysoft ODBC-Oracle WP Driver.
For Easysoft ODBC-Oracle WP Driver installation instructions, see Installation.
There are two ways to set up a data source to your Oracle® data:
By default, the Easysoft ODBC-Oracle WP Driver installation creates a SYSTEM data source named [ORACLE_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-Oracle WP Driver distribution, the SYSTEM odbc.ini file is in /etc.
If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.
If you accepted the default choices when installing the Easysoft ODBC-Oracle WP Driver, USER data sources must be created and edited in $HOME/.odbc.ini.
| To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j. By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini. |
You can either edit the sample data source or create new data sources.
Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.
| Attribute names in odbc.ini are not case sensitive. |
The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source.
When the Easysoft ODBC-Oracle WP Driver is installed into unixODBC, it places an Easysoft ODBC-Oracle WP entry in odbcinst.ini. For Easysoft ODBC-Oracle WP Driver data sources therefore, you need to include a Driver = Easysoft ODBC-Oracle WP entry.
To configure a Oracle® data source, in your odbc.ini file, you need to specify:
Driver = Easysoft ODBC-Oracle WP
Server = my_oracle_database_machine
SID = my_oracle_database_service_name
User = my_oracle_database_user
Password = my_oracle_database_user
Other optional attribute values may be set in the odbc.ini file, and are described in Attribute Fields.
The Easysoft ODBC-Oracle WP Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-Oracle WP Driver installation:
By default, this is located in /usr/local/easysoft/unixODBC/lib.
By default, this is located in /usr/local/easysoft/lib.
By default, this is located in /usr/local/easysoft/lib.
You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.
| The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl). |
The isql query tool lets you test your Easysoft ODBC-Oracle WP Driver data sources.
To test the Easysoft ODBC-Oracle WP Driver connection
1. Change directory into /usr/local/easysoft/unixODBC/bin.
2. Type ./isql -vdata_source, where data_source is the name of the target data source.
3. At the prompt, type an SQL query. For example:
Type help to return a list of tables:
This section lists some common connection problems and their solutions.
Check the Server attribute in your data source specifies a valid machine name or IP address. Check that the machine name can be looked up by using DNS or is present in /etc/hosts. Check that you are on the same network as the target host by pinging the machine:
If ping times out or fails, then either the DNS lookup is not working properly or there is some other networking or routing issue that needs to be resolved. Contact your network administrator.
Check with your database administrator that the Oracle® listener is running.
If the listener is running, is the Easysoft ODBC-Oracle WP Driver connecting to the correct listener port?
By default, the Easysoft ODBC-Oracle WP Driver tries to connect to the default listener port, 1521. However, when connecting to machines where multiple Oracle® instances or versions are running, you may need to specify a different port. Check with your database administrator to find out the correct listener port. Specify this port with the Port attribute in your ODBC data source. For example:
You can use telnet to check that you can connect to the listener port from you client machine. For example:
If the listener is listening on the port you specify, you will see output similar to:
To exit from telnet, type CTRL-] and then quit.
If you do not see this output or a "Connection refused" error is displayed, the Oracle® listener is not listening on the specified port.
Check that you have specified a valid service name with the SID attribute in odbc.ini.
If the service name is valid, it may be that the database service is not running or has not registered with the listener.
Failure to register with the listener may be a temporary condition. For example, the database may not have registered yet because the listener has only recently been started. It is therefore worth waiting a few moments and then reconnecting to your ODBC data source, before contacting your database administrator.
Check that the User and Password attributes for the data source in the odbc.ini specify a valid Oracle® user name and password.
If you are connecting to an Oracle® 11g database or later, check that the password specified in odbc.ini is the correct case. By default, passwords are case sensitive for new or modified accounts in Oracle® 11g and later.
The Easysoft ODBC-Oracle WP Driver supports the following attributes. The attributes may be set in the odbc.ini file or the ODBC connection string in a SQLDriverConnect call.
Attributes whose value is a text string have this format:
Values for logical attributes can contain either 0 (set to off) or 1 (set to on) and have this format:
| A single line of descriptive text that may be retrieved by some applications to describe the data source. | |
| The host name or IP address of the Oracle® database server machine. | |
| The TCP port on which the listener is listening for database connection requests. The listener runs on your database server machine and handles incoming client requests. If your listener is listening on the default port for database connection requests, 1521, you can omit the Port setting. | |
| The service name that identifies the database you want to connect to. For example, sales. For Oracle® Database XE, the service name is XE. | |
| The name of the user that the driver supplies to Oracle® to authenticate the connection. If you specify a user in the connection string, any entry in the data source will be ignored. For more information about specifying Easysoft ODBC-Oracle WP Driver attributes in the connection string, see DSN-less Connections. | |
| The password for the user name specified by User. Note that passwords are case sensitive for new or modified accounts in Oracle® 11g and later. If you specify a password in the connection string, any entry in the data source will be ignored. | |
| When ON (set to 1), the Easysoft ODBC-Oracle WP Driver returns metadata for the current Oracle® user only. Setting Enable_User_Catalog to ON reduces the number of rows returned by SQLTables calls. When OFF (set to 0), the driver returns metadata for all users. Note that many ODBC applications will never need this amount of catalog data. | |
| When ON (set to 1), the Easysoft ODBC-Oracle WP Driver returns private synonyms for table names in metadata result sets. By default, the driver return synonyms. If you do not need to see synonyms, set Enable_Synonyms to OFF (0). Including synonyms in metadata calls may greatly increase the size of metadata result sets for ODBC API calls such as SQLTables. | |
| When ON (set to 1), the default value of the Connection Attribute SQL_ATTR_METADATA_ID is set to SQL_TRUE (see StarOffice 5.2). If SQL_TRUE, the string arguments of catalog functions are treated as identifiers. The case is not significant. For non-delimited strings, the driver removes any trailing spaces, and the string is folded to uppercase. For delimited strings, the driver removes leading and trailing spaces, and takes literally whatever is between the delimiters. Note Setting this attribute can cause failures in applications that expect the default for SQL_ATTR_METADATA_ID to be SQL_FALSE. | |
| When ON (set to 1), the Easysoft ODBC-Oracle WP Driver preserves the case of parameter values passed to metadata calls. The default for Metadata_Dont_Change_Case is OFF (set to 0). | |
| When ON (set to 1), schema names are not returned by metadata calls. This is a workaround for some applications that do not handle SCHEMA names properly (see OpenOffice.org 1.0). | |
| The maximum size in bytes that the Easysoft ODBC-Oracle WP Driver returns for LONG, LONG RAW, BLOB, CLOB and NCLOB columns. Specify a non-zero value for LimitLong to restrict the size returned by the driver when describing these data types. | |
| Specifies the local encoding of data being passed to or from the Easysoft ODBC-Oracle WP Driver when that data is bound as a SQL wide type (SQL_WCHAR, SQL_WVARCHAR, SQL_WLONGVARCHAR) and stored in Oracle® as NCHAR, NVARCHAR2 and NCLOB columns. For example: SQLPrepare( hstmt, "INSERT INTO MYNCHARTABLE VALUES (?)", SQL_NTS ); SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,SQL_WCHAR, 100, 0, &cval, sizeof( cval ), &len1 ); As an example, if this Client_CSet entry is present in the data source, the Easysoft ODBC-Oracle WP Driver will try to convert character data to and from the UTF-8 encoding: # Convert from UTF-8 when submitting data to Oracle® # Convert to UTF-8 when retrieving data from Oracle®. Use Client_CSet if you experience data loss/corruption when working with character data and your application cannot convert data to and from the encoding scheme it expects. Note The version of the Easysoft ODBC-Oracle WP Driver driver that supports the Client_CSet attribute is not currently available on the Easysoft web site, but can be obtained by contacting the Easysoft support team (support@easysoft.com). | |
| The Easysoft ODBC-Oracle WP Driver dynamically links to iconv on your machine to do the conversion at run-time. For a list of available encodings for Client_CSet, run this command on the machine where the Easysoft ODBC-Oracle WP Driver is installed: Set Client_CSet to the encoding that corresponds with the LANG environment variable value on the client machine. For example, if LANG was set to en_US.UTF-8 on the client machine, you would set Client_CSet to UTF-8. If iconv cannot convert a character, the Easysoft ODBC-Oracle WP Driver will omit the character and write this entry to the unixODBC or driver log file (assuming logging is enabled): | |
| Whether to use TCP keepalive probes to verify that an idle connection is still intact. When ON (set to 1), keepalive probes are sent, after a period of inactivity, to verify that the connection to the SQL Server machine is still valid. To do this, the Easysoft ODBC-Oracle WP Driver sets the SO_KEEPALIVE socket option by using setsockopt(). If no response to the probes is received, the socket is closed. The duration of the period of inactivity is a system default, and is typically two hours. |
In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:
SQLDriverConnect(..."DRIVER={Easysoft ODBC-Oracle WP};
where database_machine is the host name or IP address of the Oracle® database server machine, service_name is the database service name, user and password are a valid Oracle® login and password. You need to use the Easysoft ODBC-Oracle WP DRIVER keyword to connect to the Easysoft ODBC-Oracle WP Driver.
Other Easysoft ODBC-Oracle WP Driver attribute settings (as described in Setting Up Data Sources) can be added to the connection string using the same PARAMETER=value; format. For example, the following connection string uses the PORT attribute to specify a non-default listener port number:
"DRIVER={Easysoft ODBC-Oracle WP};SERVER=my_xe_machine;PORT=1522;SID=XE;USER=hr;PWD=hr_password"
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
