 |  |  |  |
The Easysoft ODBC-Oracle Driver is usually installed on the machines where your applications are running. ODBC applications access ODBC drivers through the ODBC driver manager and a data source which tells the driver manager which ODBC driver to load, which database to connect to and how to connect to it. This chapter describes how you create data sources, use DSN-less connections and configure the Easysoft ODBC-Oracle Driver.
Before setting up a data source, the Easysoft ODBC-Oracle Driver must have been successfully installed on the machines where your ODBC application is running.
Instructions for installing the Easysoft ODBC-Oracle Driver on Windows and Unix platforms are provided in Installation.
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={ORACLE};DB=pubs;UID=demo;PWD=easysoft;"...)
where pubs is the database name, demo is the user name with which to connect to the database, and easysoft is the password for the demo user.
Further Easysoft ODBC-Oracle Driver attribute settings, as described in this section, can also be added to the connection string using the same "PARAMETER=value;" format.
Refer to the section relevant to your platform to continue:
The Easysoft ODBC-Oracle driver can be configured to connect to an Oracle® database using OS Authentication. To do this, you must first ensure that the database and client are configured and working correctly. More details on the use of OS Authentication and its implications can be found in your Oracle® documentation. To establish that the system is configured correctly, we recomend using a tool such as sqlplus, before attemping to configure your driver. Once the Oracle® software is configured, the driver can be set up by leaving the username and password fields empty.
To connect an ODBC application on a Windows machine to an Oracle® database on a remote server:
1. Select Start > Settings > Control Panel, double-click Administrative Tools and then Data Sources (ODBC).
| Select Start > Settings > Control Paneland double-click ODBC Data Sources (32bit). |
| Select Start > Settings > Control Paneland double-click Data Sources (ODBC). |
The ODBC Data Source Administrator dialog box is displayed:
2. To create a data source that is only available to the user currently logged into this machine, select the User DSN tab.
To create a data source that is available to any user who logs into this machine, select the System DSN tab.
3. Click Add... to add a new data source.
The Create New Data Source dialog box displays a list of drivers:
4. Select Easysoft ODBC-Oracle Driver and click Finish.
The Easysoft ODBC-Oracle Driver DSN configuration dialog box is displayed.
The attributes on this dialog box are split into four tabs, arranged by functionality, from left to right:
Test allows you to check that the client is able to connect to the specified server data source.
5. In the DSN box enter a name for this data source.
Choose carefully because you will not be able to change this after pressing OK.
6. In the Description box enter something that would help a user faced with a choice of data sources.
7. In the Database box enter the logical name used to identify the Oracle® target database. This is the local net service name defined in your tnsnames.ora file.
8. In the Username and Password boxes enter the user name and password of a valid account in the Oracle® target database.
For details of the the attributes that can be set on this dialog box, see Attribute Fields
For details of the the attributes that can be set on this dialog box, see Attribute Fields
For details of the the attributes that can be set on this dialog box, see Attribute Fields
The Easysoft ODBC-Oracle Driver attempts to connect to the database and send an ODBC request, displaying the results in a window.
10. If an error message is displayed then check the entries in the DSN setup fields.
If the target database can be successfully accessed by running SQL Plus from Start > Programs > Oracle® > Application Development > SQL Plus then the problem can be assumed to be on the client machine, rather than on the server machine.
An information message will be displayed if you have successfully connected to the server:
11. Click OK in the Easysoft Setup Test dialog box and OK in the Easysoft ODBC-Oracle Driver DSN Setup dialog box.
There is now a data source on the Windows machine that connects via the Easysoft ODBC-Oracle Driver to an Oracle® database on a remote server machine.
An example ODBC application can now be connected to the local data source to demonstrate that the Easysoft ODBC-Oracle Driver is functioning correctly.
| This section requires access to Microsoft Access. |
1. Start Microsoft Access (for example) and create a blank database.
2. Select File > Get External Data > Link Tables.
The Link dialog box displays the existing databases on your system:
3. From the Files of type drop-down list, choose ODBC Databases.
The Select Data Source dialog box displays the existing data sources on your system:
4. Click the Machine Data Source tab and find the local data source you created in the list (note that your description of the data source is displayed beside it).
5. Select your data source and click OK.
Microsoft Access connects (through the ODBC Driver Manager) to the Easysoft ODBC-Oracle driver and retrieves a list of available tables .
The Link Tables window is displayed, showing a list of available datasets:
6. Click on a table and then click OK.
After a short wait, you are returned to the Database window.
7. Double-click on one of the tables to open and browse it.
There are two options when setting up a data source to your Oracle® data:
This is exactly the same mechanism as is used on the ODBC Data Source Administrator on Windows platforms (see Windows Setup).
| If you have user and system data sources with the same name the driver manager will use the user data source. |
The default Easysoft ODBC-Oracle Driver installation will create a system DSN named [ORACLE]. If you are using unixODBC that came with this distribution the system odbc.ini file will be /etc.
If you have built unixODBC yourself, or installed it from some other source then 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).
User data sources are stored in the file .odbc.ini in the current user's home directory (e.g. $HOME/.odbc.ini). .
| By default, you must be logged in as root to edit a system data source defined in /etc/odbc.ini. |
| odbcinst -j will show you where the driver manager stores system and user data sources. |
You may either change some of the initial configuration parameter values for the sample data source or add extra 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.iniare not case sensitive. |
For the addition of a data source, all the settings in the following example must be appended to the relevant odbc.ini file. The Driver attribute names the ODBC driver in the odbcinst.ini file to use for this data source. When the Easysoft ODBC-Oracle Driver is istalled into unixODBC it places an Oracle® entry into the odbcinst.ini file so you should always have Driver = Oracle® in your Easysoft ODBC-Oracle Driver data sources.
| More details of configuring data sources with unixODBC can be found at www.unixodbc.org. |
To configure your Oracle® DSN in your odbc.ini file, you will need to edit:
Driver = Oracle®
Other optional attribute values may be set in the odbc.ini file, and are described in Attribute Fields.
There are a number of driver attributes that are stored in the odbcinst.ini file. By default this file is installed in /etc. If you have built unixODBC yourself, then it will be whatever path you specified in the sysconfdir=directory configure option (if sysconfdir has not been specified then the path will default to /usr/local/etc).
| By default, you must be logged in as root to edit a system data source defined in /etc/odbcinst.ini,. |
If the applications using the driver does not use threads, you can set Threads to be 0. This will provide a performance enhancement, by disabling thread safety in the driver.
For the Easysoft ODBC-Oracle Driver to function correctly the following environment variables must be created and given values specific to the directory structure on the target machine:
This must be the Oracle® Client software directory, except when using Oracle® Instant Client.
ORACLE_HOME=/home/oracle/OraHome1
This must always be set to the directory $ORACLE_HOME/lib:, except when using Oracle® Instant client, when it is the location where the Instant Client is installed.
LD_LIBRARY_PATH=$LD_LIBRARY_PATH/home/oracle/OraHome1/lib
The Easysoft ODBC-Oracle Driver must also be able to find the following shared objects which it installs:
By default this will always be located in /usr/local/easysoft/unixODBC/lib/:
By default this will always be located in /usr/local/easysoft/lib/
By default this will always be located in /usr/local/easysoft/lib
It may be necessary to set up and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on the operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libeslicshr.so are located.
| The shared object file extension (.so) varies across operating systems ( .so, .a, or .sl). |
Run the isql query tool to prove that the Easysoft ODBC-Oracle Driver is working:
/usr/local/easysoft/unixODBC/bin
where [DSN] is the name of the target data source
3. Enter SQL commands to query the database, such as:
SQL> select table_name from user_tables
4. or enter 'help' to return a list of tables
Some Easysoft ODBC-Oracle distributions contain our checksys diagnostic tool which can report configuration and environment problems and suggest corrective action. It can be found in the /usr/local/easysoft/oracle directory.
This section lists the attributes which can be set for the Easysoft ODBC-Oracle Driver in a table showing:
Attributes which are text fields are displayed as "value".
Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".
If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.
For example, in the statement:
the value entered may be "0", "1" or "2".
The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.
| DSN Dialog Box (Windows) | DSN |
| odbc.ini file (Unix) | [value] |
| Connect String | DSN=value |
Descriptive text which may be retrieved by certain applications to describe the data source.
| DSN Dialog Box (Windows) | Description |
| odbc.ini file (Unix) | Description=value |
| Connect String | Not Used |
The logical name used to identify the Oracle® target database. This is the local net service name defined in your tnsnames.ora file.
| DSN Dialog Box (Windows) | Database |
| odbc.ini file (Unix) | Database=value |
| Connect String | DB=value |
The name of the user that will be supplied to Oracle® to authenticate the connection.
If a value is specified in the connect string then any entry in the DSN will be ignored.
| DSN Dialog Box (Windows) | Username |
| odbc.ini file (Unix) | User=value |
| Connect String | UID=value |
The password supplied to Oracle® to authenticate the connection
| DSN Dialog Box (Windows) | Password |
| odbc.ini file (Unix) | Password=value |
| Connect String | PWD=value |
If a value is specified in the connect string then any entry in the DSN will be ignored.
When ON (i.e. set to 1 or checked), 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 can cause failures in applications which expect the default to be SQL_FALSE and do not check the attribute.
The default for METADATA_ID is off (0)
| DSN Dialog Box (Windows) | MetaData ID |
| odbc.ini file (Unix) | MetaData_ID=0|1 |
| Connect String | METADATA_ID=0|1 |
When ON (i.e. set to 1 or checked), the case of the parameter values passed to metadata calls will not change.
The default for METADATA_DONT_CHANGE_CASE is off (0).
| DSN Dialog Box (Windows) | Preserve MetaData Case |
| odbc.ini file (Unix) | MetaData_Dont_Change_Case=0|1 |
| Connect String | METADATA_DONT_CHANGE_CASE=0|1 |
When ON (i.e. set to 1 or checked), the driver trims trailing spaces from varchar types when passed as bound parameters. If VarcharTrimTrailingSpaces is set to 1 then trailing spaces are removed from the end of the data.
The default behaviour is to not trim spaces.
| DSN Dialog Box (Windows) | Trim Trailing Varchar Spaces |
| odbc.ini file (Unix) | VarcharTrimTrailingSpaces=0|1 |
| Connect String | VARCHARTRIMTRAILINGSPACES=0|1 |
When ON (i.e. set to 1 or checked), schema names are not returned by metadata calls. This works around some problem applications which do not handle SCHEMA names properly (see OpenOffice 1.0)..
| DSN Dialog Box (Windows) | No Metadata Schema |
| odbc.ini file (Unix) | Metadata_Dont_Do_Schema=0|1 |
| Connect String | METADATA_DONT_DO_SCHEMA=0|1 |
When ON (i.e. set to 1 or checked), information on LONG data types will be returned in the result set from the SQLGetTypeInfo function call.
Restrictions with LONG data types in Oracle® databases (such as only permitting one column per table to be defined) often cause errors to occur, and this attribute can be used to include LONG within the list of valid data types which can be used by an application.
The default for USE_LONGS is off (0).
| DSN Dialog Box (Windows) | Use Longs |
| odbc.ini file (Unix) | Use_Longs=0|1 |
| Connect String | USE_LONGS=0|1 |
When ON (i.e. set to 1 or checked), table name synonyms are returned in metadata result sets.
By default synonyms are not returned.
| DSN Dialog Box (Windows) | Enable Synonyms |
| odbc.ini file (Unix) | Enable_Synonyms=0|1 |
| Connect String | ENABLE_SYNONYMS=0|1 |
When ON (i.e. set to 1 or checked), this limits the driver to returning metadata (e.g. tables) for the current Oracle® user.
The default is to see metadata for all users.
| DSN Dialog Box (Windows) | Enable User Catalog |
| odbc.ini file (Unix) | Enable_User_Catalog=0|1 |
| Connect String | ENABLE_USER_CATALOG=0|1 |
Oracle® does not support the describing of parameters, so the driver does not support the SQLDescribeParam ODBC call. However, if this ini setting is set to 1 (or checked) then the driver will describe any parameters as varchar.
The default for DESCRIBE_PARAM_AS_STRINGS is off (0).
| DSN Dialog Box (Windows) | Enable User Catalog |
| odbc.ini file (Unix) | Describe_Param_As_Strings=0|1 |
| Connect String | DESCRIBE_PARAM_AS_STRINGS=0|1 |
The following values can be set in order to map Oracle® data types onto ODBC data types:
| 0 | ||
| | ||
| | ||
| | ||
| 1 | ||
| | ||
| | ||
| | ||
| 2 | ||
| | Note: this mapping can cause problems fetching large numbers | |
| | SQL_DOUBLE |
| DSN Dialog Box (Windows) | Datatype Map |
| odbc.ini file (Unix) | Datatype_Map=0|1|2 |
| Connect String | DATATYPE_MAP=0|1|2 |
Any value specified will override the maximum size of a LONG column (in bytes).
Perl DBI tries to allocate a buffer the size of a LONG column and, as this is rather large, it can cause problems which setting DBI_LONG_SIZE can resolve (see Perl DBI).
| DSN Dialog Box (Windows) | DBI Long Size |
| odbc.ini file (Unix) | DBI_Long_Size=value |
| Connect String | DBI_LONG_SIZE=value |
This setting defines an Oracle® SQL statement that will be run immediately after the driver has established a connection to the database.
| DSN Dialog Box (Windows) | Connect SQL |
| odbc.ini file (Unix) | Connect_SQL=value |
| Connect String | CONNECT_SQL=value |
When ON (i.e. set to 1 or checked), increases the performance of the Easysoft ODBC-Oracle Driver if there are no CLOB or BLOB data types in use. This is only applicable to Oracle® verison 8.1.7.
| DSN Dialog Box (Windows) | No Lobs |
| odbc.ini file (Unix) | No_LOBS=0|1 |
| Connect String | NO_LOBS=0|1 |
When ON (i.e. set to 1 or checked) this stops the Easysoft ODBC-Oracle Driver from preparsing the SQL (passed to SQLPrepare and SQLExecDirect) to convert ODBC escapes and parameter markers. Setting this option will provide a small speed increase but will prevent your application from using ODBC escapes sequences and parameter markers.
| DSN Dialog Box (Windows) | No Parse |
| odbc.ini file (Unix) | No_Parse=0|1 |
| Connect String | NO_PARSE=0|1 |
Indicates the number of rows returned from a single "fetch" call made to the server.
For example, if the value of OCI_ATTR_PREFETCH_ROWS attribute is set to ten, then ten rows will be fetched from the database server and the next call to SQLFetch will not need to make a call to the server as the required row will be held by the client already. The default value is 10.
| DSN Dialog Box (Windows) | Prefetch Rows |
| odbc.ini file (Unix) | OCI_Attr_Prefetch_Rows=value |
| Connect String | OCI_ATTR_PREFETCH_ROWS=value |
Indicates the number of bytes of memory used on the client to store records returned from a single SQLFetch call made to a driver.
This controls the number of records returned, which will be the total required in order to fill the allocated memory area.
For example, if the available memory can store two rows then the next call to SQLFetch will not need to make a call to the server, as the required row will be held by the client already.
| DSN Dialog Box (Windows) | Prefetch Memory |
| odbc.ini file (Unix) | OCI_Attr_Prefetch_Memory=value |
| Connect String | OCI_ATTR_PREFETCH_MEMORY=value |
OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY update the Oracle® Statement attributes of the same name:
OCI_ATTR_PREFETCH_MEMORY = 100
By default, the Easysoft ODBC-Oracle Driver makes a call to the database server for every ten records returned.
These two settings allow a user to specify as an alternative either the number of rows to be fetched or the amount of memory to be used when fetching data.
| These attributes are different methods of specifying the quantity of data returned in one fetch. You should only set one but if you set both attributes OCI_ATTR_PREFETCH_ROWS will take precedence. |
This attribute enables Oracle® Statement caching. To enable caching set this attribute to the size of the required cache. Setting the attribute to 0 will switch statement caching off. For more details on Oracle® Statement Caching please consult you Oracle® documentation.
The default is no statement caching.
| DSN Dialog Box (Windows) | Statement Caching |
| odbc.ini file (Unix) | Statement_Caching=value |
| Connect String | STATEMENT_CACHING=value |
This is used to define the type of pooling required. This can be SESSION or CONNECTION.
| DSN Dialog Box (Windows) | Pool Type |
| odbc.ini file (Unix) | Pool_Type=Session|Connection |
| Connect String | POOL_TYPE=SESSION|CONNECTION |
This can be GLOBAL or ENV. This is used to associate the pool with the ODBC environment or make it a global resource.
| DSN Dialog Box (Windows) | Pool Scope |
| odbc.ini file (Unix) | Pool_Scope=Global|Env |
| Connect String | POOL_SCOPE=GLOBAL|ENV |
This is used to define the number of sessions or connections that are created when the pool is created.
| DSN Dialog Box (Windows) | Pool Initial |
| odbc.ini file (Unix) | Pool_Initial=value |
| Connect String | POOL_INITIAL=value |
This is used to specify the maximum number of sessions or connections that the pool can contain.
| DSN Dialog Box (Windows) | Pool Max |
| odbc.ini file (Unix) | Pool_Max=value |
| Connect String | POOL_MAX=value |
This is the number that the session or connection count is incremented by when required.
| DSN Dialog Box (Windows) | Pool Increment |
| odbc.ini file (Unix) | Pool_Increment=value |
| Connect String | POOL_INCREMENT=value |
This is the database username that is to be used to authenticate the sessions or connections.
| DSN Dialog Box (Windows) | Pool Username |
| odbc.ini file (Unix) | Pool_Username=value |
| Connect String | POOL_USERNAME=value |
This is the database password that is to be used to authenticate the sessions or connections.
| DSN Dialog Box (Windows) | Pool Password |
| odbc.ini file (Unix) | Pool_Password=value |
| Connect String | POOL_PASSWORD=value |
This is the name of the database against which the pools are to be created.
| DSN Dialog Box (Windows) | Pool Database |
| odbc.ini file (Unix) | Pool_DB=value |
| Connect String | POOL_DB=value |
When ON (i.e. set to 1), the Easysoft ODBC-Oracle Driver uses the Oracle® XA library to access Oracle®. This makes the XA connection available for use by the Easysoft ODBC-Oracle Driver and any work done by the driver is under the control of the Transaction Manager. (Your ODBC application also needs to turn off the ODBC auto-commit mode by using SQLSetConnectAttr with the SQL_ATTR_AUTOCOMMIT attribute.)
If you want to use the Easysoft ODBC-Oracle Driver to connect to Oracle® in the context of a distributed XA transaction, enable the XA_Enlist option. Otherwise, leave the option set to its default value OFF (0).
| DSN Dialog Box (Windows) | Not available |
| odbc.ini file (Unix) | XA_Enlist=0|1 |
| Connect String | XA_ENLIST=0|1 |
The name of the database specified with the DB field in the xa_open string. For example, you specify a database named "payroll" with the following xa_open string clause:
You also need to specify "payroll" as the value for the XA_Connection_String attribute field:
XA_Connection_String is only necessary if you are using the Easysoft ODBC-Oracle Driver to connect to Oracle® in the context of an XA transaction and the Transaction Manager specifies a named database in the xa_open string.
| DSN Dialog Box (Windows) | Not available |
| odbc.ini file (Unix) | XA_Connection_String=value |
| Connect String | XA_CONNECTION_STRING=value |
| | | |
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© 1993 - 2023 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.
