TOC PREV NEXT INDEX



Easysoft ODBC-Oracle Driver User Guide - Configuration

Configuring the Easysoft ODBC-Oracle Driver

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.

Chapter Guide

DSN-less connections

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:

OS Authentication

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.

Windows Setup

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).


9x

Select Start > Settings > Control Paneland double-click ODBC Data Sources (32bit).



NT

Select Start > Settings > Control Paneland double-click Data Sources (ODBC).


 The ODBC Data Source Administrator dialog box is displayed:

Figure 16: The ODBC Data Source Administrator

2.  To create a data source that is only available to the user currently logged into this machine, select the User DSN tab.

¯ OR ¯

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:

Figure 17: The Create New Data Source dialog box

4.  Select Easysoft ODBC-Oracle Driver and click Finish.

The Easysoft ODBC-Oracle Driver DSN configuration dialog box is displayed.

Figure 18: The DSN Setup dialog box - Connection tab

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.

Connection

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.

Settings

Figure 19: The DSN Setup dialog box - Settings tab

For details of the the attributes that can be set on this dialog box, see Attribute Fields

Optimization

Figure 20: The DSN Setup dialog box - Optimization tab

For details of the the attributes that can be set on this dialog box, see Attribute Fields

OCI Pooling

Figure 21: The DSN Setup dialog box - OCI Pooling tab

For details of the the attributes that can be set on this dialog box, see Attribute Fields

9.  Now click Test.

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.

¯ OR ¯

An information message will be displayed if you have successfully connected to the server:

Figure 22: The Easysoft Setup Test DSN dialog box

11.  Click OK in the Easysoft Setup Test dialog box and OK in the Easysoft ODBC-Oracle Driver DSN Setup dialog box.

 The connection has been made.

Connecting a Client Application

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.


NB

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:

Figure 23: The Link dialog box in Microsoft Access

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:

Figure 24: The Select Data Source dialog box Machine Data Source tab

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:

Figure 25: The Link Tables dialog box Tables tab

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.

Unix Setup

Data Source Attributes

There are two options when setting up a data source to your Oracle® data:

¯ OR ¯

This is exactly the same mechanism as is used on the ODBC Data Source Administrator on Windows platforms (see Windows Setup).


NB

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). .


NB

By default, you must be logged in as root to edit a system data source defined in /etc/odbc.ini.



NB

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.


NB

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.


NB

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:

e.g.

[ORACLE]

Driver = Oracle®

Description =

Database = test.server

User = system

Password = manager

Other optional attribute values may be set in the odbc.ini file, and are described in Attribute Fields.

Driver Attributes

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).


NB

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.

Environment

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.

e.g.

ORACLE_HOME=/home/oracle/OraHome1

export ORACLE_HOME

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.

e.g.

LD_LIBRARY_PATH=$LD_LIBRARY_PATH/home/oracle/OraHome1/lib

export LD_LIBRARY_PATH

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.


NB

The shared object file extension (.so) varies across operating systems ( .so, .a, or .sl).


Establishing a Test Connection

Run the isql query tool to prove that the Easysoft ODBC-Oracle Driver is working:

1.  Change directory into

 /usr/local/easysoft/unixODBC/bin

2.  Type ./isql -vDSN

 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

 SQL>help

Trouble-shooting a test connection

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.

To run the tool

cd /usr/local/easysoft/oracle

./checksys -dDSN

Attribute Fields

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:

Data_Type_Map=0|1|2

the value entered may be "0", "1" or "2".

DSN

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value
DSN Dialog Box (Windows) DSN
odbc.ini file (Unix) [value]
Connect String DSN=value

Description

Descriptive text which may be retrieved by certain applications to describe the data source.

Interface Value
DSN Dialog Box (Windows) Description
odbc.ini file (Unix) Description=value
Connect String Not Used

Database

The logical name used to identify the Oracle® target database. This is the local net service name defined in your tnsnames.ora file.



Interface Value
DSN Dialog Box (Windows) Database
odbc.ini file (Unix) Database=value
Connect String DATABASE=value

Username

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.

Interface Value
DSN Dialog Box (Windows) Username
odbc.ini file (Unix) User=value
Connect String UID=value

Password

The password supplied to Oracle® to authenticate the connection

Interface Value
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.

MetaData ID

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)



Interface Value
DSN Dialog Box (Windows) MetaData ID
odbc.ini file (Unix) MetaData_ID=0|1
Connect String METADATA_ID=0|1

Preserve Metadata Case

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).



Interface Value
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

Trim Trailing Varchar Spaces

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.

Interface Value
DSN Dialog Box (Windows) Trim Trailing Varchar Spaces
odbc.ini file (Unix) VarcharTrimTrailingSpaces=0|1
Connect String VARCHARTRIMTRAILINGSPACES=0|1

No Metatdata Schema

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)..

Interface Value
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

Use Longs

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).



Interface Value
DSN Dialog Box (Windows) Use Longs
odbc.ini file (Unix) Use_Longs=0|1
Connect String USE_LONGS=0|1

Enable Synonyms

When ON (i.e. set to 1 or checked), table name synonyms are returned in metadata result sets.

By default synonyms are not returned.



Interface Value
DSN Dialog Box (Windows) Enable Synonyms
odbc.ini file (Unix) Enable_Synonyms=0|1
Connect String ENABLE_SYNONYMS=0|1

Enable User Catalog

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.



Interface Value
DSN Dialog Box (Windows) Enable User Catalog
odbc.ini file (Unix) Enable_User_Catalog=0|1
Connect String ENABLE_USER_CATALOG=0|1

Describe Parameters

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).



Interface Value
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

DataType Map

The following values can be set in order to map Oracle® data types onto ODBC data types:

Value Oracle® Data Type ODBC Data Type
0

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER <= 9 digits

SQL_INTEGER


NUMBER = n digits

SQL_NUMERIC


NUMBER = n,m digits

SQL_NUMERIC

1

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER <= 9 digits

SQL_INTEGER


NUMBER = n digits

SQL_DOUBLE


NUMBER = n,m digits

SQL_DOUBLE

2

NUMBER <= 4 digits

SQL_SMALLINT


NUMBER = n digits

SQL_INTEGER

Note: this mapping can cause problems fetching large numbers


NUMBER n,m digits

SQL_DOUBLE



Interface Value
DSN Dialog Box (Windows) Datatype Map
odbc.ini file (Unix) Datatype_Map=0|1|2
Connect String DATATYPE_MAP=0|1|2

DBI Long Size

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).



Interface Value
DSN Dialog Box (Windows) DBI Long Size
odbc.ini file (Unix) DBI_Long_Size=value
Connect String DBI_LONG_SIZE=value

Connect SQL

This setting defines an Oracle® SQL statement that will be run immediately after the driver has established a connection to the database.



Interface Value
DSN Dialog Box (Windows) Connect SQL
odbc.ini file (Unix) Connect_SQL=value
Connect String CONNECT_SQL=value

No Lobs

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.

The default is off (0).



Interface Value
DSN Dialog Box (Windows) No Lobs
odbc.ini file (Unix) No_LOBS=0|1
Connect String NO_LOBS=0|1

No Parse

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.



Interface Value
DSN Dialog Box (Windows) No Parse
odbc.ini file (Unix) No_Parse=0|1
Connect String NO_PARSE=0|1

Prefetch Rows

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.



Interface Value
DSN Dialog Box (Windows) Prefetch Rows
odbc.ini file (Unix) OCI_Attr_Prefetch_Rows=value
Connect String OCI_ATTR_PREFETCH_ROWS=value

Prefetch Memory

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.



Interface Value
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:

e.g.

OCI_ATTR_PREFETCH_ROWS = 10

¯ OR ¯

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.


NB

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.


Statement Caching

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.



Interface Value
DSN Dialog Box (Windows) Statement Caching
odbc.ini file (Unix) Statement_Caching=value
Connect String STATEMENT_CACHING=value

Pool Type

This is used to define the type of pooling required. This can be SESSION or CONNECTION.



Interface Value
DSN Dialog Box (Windows) Pool Type
odbc.ini file (Unix) Pool_Type=Session|Connection
Connect String POOL_TYPE=SESSION|CONNECTION

Pool Scope

This can be GLOBAL or ENV. This is used to associate the pool with the ODBC environment or make it a global resource.



Interface Value
DSN Dialog Box (Windows) Pool Scope
odbc.ini file (Unix) Pool_Scope=Global|Env
Connect String POOL_SCOPE=GLOBAL|ENV

Pool Initial

This is used to define the number of sessions or connections that are created when the pool is created.



Interface Value
DSN Dialog Box (Windows) Pool Initial
odbc.ini file (Unix) Pool_Initial=value
Connect String POOL_INITIAL=value

Pool Max

This is used to specify the maximum number of sessions or connections that the pool can contain.



Interface Value
DSN Dialog Box (Windows) Pool Max
odbc.ini file (Unix) Pool_Max=value
Connect String POOL_MAX=value

Pool Increment

This is the number that the session or connection count is incremented by when required.



Interface Value
DSN Dialog Box (Windows) Pool Increment
odbc.ini file (Unix) Pool_Increment=value
Connect String POOL_INCREMENT=value

Pool Username

This is the database username that is to be used to authenticate the sessions or connections.



Interface Value
DSN Dialog Box (Windows) Pool Username
odbc.ini file (Unix) Pool_Username=value
Connect String POOL_USERNAME=value

Pool Password

This is the database password that is to be used to authenticate the sessions or connections.



Interface Value
DSN Dialog Box (Windows) Pool Password
odbc.ini file (Unix) Pool_Password=value
Connect String POOL_PASSWORD=value

Pool Database

This is the name of the database against which the pools are to be created.



Interface Value
DSN Dialog Box (Windows) Pool Database
odbc.ini file (Unix) Pool_DB=value
Connect String POOL_DB=value

XA_Enlist

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).



Interface Value
DSN Dialog Box (Windows) Not available
odbc.ini file (Unix) XA_Enlist=0|1
Connect String XA_ENLIST=0|1

XA_Connection_String

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:

DB=payroll

You also need to specify "payroll" as the value for the XA_Connection_String attribute field:

XA_Connection_String=payroll

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.



Interface Value
DSN Dialog Box (Windows) Not available
odbc.ini file (Unix) XA_Connection_String=value
Connect String XA_CONNECTION_STRING=value


Copyright Notice
TOC PREV NEXT INDEX


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.