This section describes how to create an ODBC data source for the Easysoft ODBC-JDBC Gateway.
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-JDBC Gateway};DRIVERCLASS=com.borland.datastore.jdbc.DataStoreDriver;UID=dbuser;PWD=dbpass;CLASSPATH=/tmp/jdsserver6.jar;URL=jdbc:borland:dsremote://server/C:\JDatastore6\bin\TEST.jds"...)
Other Easysoft ODBC-JDBC Gateway attribute settings can be added to this connection string, using the "PARAMETER=value;" format. These attributes are described in Attribute Fields.
This section describes how to create ODBC data sources for the Easysoft ODBC-JDBC Gateway on Windows:
1. In Control Panel, double-click Administrative Tools and then Data Sources (ODBC)
The ODBC Data Source Administrator dialog box is displayed:
2. Select the User DSN tab to set up a data source that only you can access.
Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.
3. Click Add to add a new DSN.
The Create New Data Source dialog box displays a list of the drivers which are available:
4. Select Easysoft ODBC-JDBC Gateway and click Finish.
The Easysoft ODBC-JDBC Gateway DSN Setup dialog box prompts the user to create an ODBC data source for a specific JDBC driver using the appropriate connection information:
The following fields are defined on the Easysoft ODBC-JDBC Gateway DSN Setup dialog box:
A brief description of the data source.
The name of a user to pass to the JDBC driver in order to access the database (if required).
The password for the specified user to pass to the JDBC driver in order to access the database (if required).
The JDBC driver class name for this data source that will be passed to the forName()function.
|
The syntax and contents of this field are unique to the JDBC driver being used and can be obtained from the individual driver documentation. An example value (in this case for the Easysoft JDBC-ODBC Bridge) would be the string: |
A list of any .jar files required to enable Java to load the JDBC driver class.
This list is specified in the same way as the Java CLASSPATH environment variable, with the separator between entries depending on the operating system used (Windows uses a semi-colon ";" and other platforms use a colon ":").
Click Add to launch a file browser to help you to locate the required .jar file on your system.
If the target driver requires multiple .jar files then click Add again to add a further entry.
|
The order in which the .jar files are specified can be significant. |
|
Please refer to http://java.sun.com/j2se/1.4/docs/tooldocs/windows/classpath.html on the Sun Java web site for further information about the setting of class paths under Windows. |
The URL required to connect to your JDBC database
|
The syntax and contents of this field are unique to the JDBC driver being used and can be obtained from the individual driver documentation. An example value (in this case for the Easysoft JDBC-ODBC Bridge) would be the string: jdbc:easysoft://server:8831/dsn_name To display some examples for different JDBC drivers, click Help. |
.
For details of the other attributes that can be set in this dialog box, see Attribute Fields
Displays a dialog box that allows a jar file to be added to the Class Path field.
Takes the values for all Easysoft ODBC-JDBC Gateway Data Source settings and attempts to establish a connection to the specified JDBC data source.
It will report either the first error encountered or a successful completion of the test, and will time out after about 15 seconds.
Click OK to exit the Easysoft ODBC-JDBC Gateway Data Source Setup program after a successful connection has been made to ensure that those connection settings are saved.
Creates or modifies an Easysoft ODBC-JDBC Gateway ODBC Data Source Setup with the currently displayed settings.
Exits the Easysoft ODBC-JDBC Gateway ODBC Data Source Setup program without affecting any settings.
There are two options when setting up a data source to your 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-JDBC Gateway installation will create a system DSN named ODBC_JDBC_SAMPLE. If you are using unixODBC included with this distribution, the system odbc.ini file will be /etc.
If you have 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).
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.
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-JDBC Gateway is installed into unixODBC it places an Easysoft ODBC-JDBC Gateway entry into the odbcinst.ini file so you should always have Driver = Easysoft ODBC-JDBC Gateway in your Easysoft ODBC-JDBC Gateway data sources.
|
More details of configuring data sources with unixODBC can be found at www.unixodbc.org. |
To configure your Easysoft ODBC-JDBC Gateway DSN in your odbc.ini file, you will need to edit:
This list is specified in the same way as the Java CLASSPATH environment variable. If the target driver requires multiple .jar, separate each .jar file in the list with a colon (":")
|
The order in which the .jar files are specified can be significant. |
.
For more information about setting the class path on Unix, see http://java.sun.com/j2se/1.4.2/docs/tooldocs/solaris/classpath.html.
You can find some examples for different JDBC drivers in /usr/local/easysoft/ojg/doc/dlg_help.html.
For example, this sample data source has been modified to connect to the hsqldb JDBC driver:
Driver = Easysoft ODBC-JDBC Gateway
DriverClass = org.hsqldb.jdbcDriver
URL = jdbc:hsqldb:hsql://my_hsql_database_server
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,. |
You need to set JvmPath to point at a valid JVM on your system. For more information, see Defining which JVM to use.
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.
The Easysoft ODBC-JDBC Gateway must also be able to find the following shared objects that 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/LIBPATH etc.(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). |
You may also need to ensure LD_LIBRARY_PATH/LIBPATH etc. are pointing to any directories containing the JVM shared objects and JVM required shared objects or the dynamic linker knows where to find dependent shared objects. For more information, see Defining which JVM to use.
Run the isql query tool to prove that the Easysoft ODBC-JDBC Gateway 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
This section lists the attributes which can be set for the Easysoft ODBC-JDBC Gateway 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".
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 name of a user to pass to the JDBC driver in order to access the database (if required).
The attribute value is passed to the JDBC driver as part of a Properties object in the connect call.
The equivalent Java code is as follows:
con = DriverManager.getConnection( url, p );
This value can be overriden at the ODBC level by passing either a non-NULL user name in the call to SQLConnect() or a UID=value attribute in the connection string passed to SQLDriverConnect().
It can also be supplied in the JDBC URL supplied to the JDBC driver, but the syntax of this is JDBC driver dependent.
| DSN Dialog Box (Windows) | User Name |
| odbc.ini file (Unix) | User=value |
| Connect String | UID=value |
The password for the specified user to pass to the JDBC driver in order to access the database (if required).
The attribute value is passed to the JDBC driver as part of a Properties object in the connect call.
The equivalent Java code is as follows:
con = DriverManager.getConnection ( url, p );
This value can be overriden at the ODBC level by passing either a non-NULL password in the call to SQLConnect() or a PWD=value attribute in the connection string passed to SQLDriverConnect().
It can also be supplied in the JDBC URL supplied to the JDBC driver, but the syntax of this is JDBC driver dependent.
| DSN Dialog Box (Windows) | Password |
| odbc.ini file (Unix) | Password=value |
| Connect String | PWD=value |
The JDBC driver class name for this data source that will be passed to the forName()function.
The equivalent Java code is as follows:
|
The syntax and contents of this field are unique to the JDBC driver being used and can be obtained from the individual driver documentation. An example value (in this case for the Easysoft JDBC-ODBC Bridge) would be the string: |
| DSN Dialog Box (Windows) | Driver Class |
| odbc.ini file (Unix) | DriverClass=value |
| Connect String | DRIVERCLASS=value |
A list of any .jar files required to enable Java to load the JDBC driver class.
This list is specified in the same way as the Java CLASSPATH environment variable, with the separator between entries depending on the operating system used (Windows uses a semi-colon ";" and other platforms use a colon ":").
|
Please refer to http://java.sun.com/j2se/1.4/docs/tooldocs/windows/classpath.html on the Sun Java web site for further information about the setting of class paths under Windows. For more information about setting the class path on Unix, see http://java.sun.com/j2se/1.4.2/docs/tooldocs/solaris/classpath.html. |
| DSN Dialog Box (Windows) | Class Path |
| odbc.ini file (Unix) | ClassPath=value |
| Connect String | CLASSPATH=value |
The URL required to connect to your JDBC database (URL).
The URL string will be passed as the first argument to the call:
con = DriverManager.getConnection( url, p );
|
The syntax and contents of this field are unique to the JDBC driver being used and can be obtained from the individual driver documentation. An example value (in this case for the Easysoft JDBC-ODBC Bridge) would be the string: |
| DSN Dialog Box (Windows) | URL |
| odbc.ini file (Unix) | URL=value |
| Connect String | URL=value |
Controls how any SQL passed through the Easysoft ODBC-JDBC Gateway is scanned and modified.
Some JDBC drivers can not accept double quotes around column and table names, and in some cases the quotes will trigger failure, as it may invoke unwanted case-sensitive behaviour.
As an example, when Strip Quote is ON (i.e. set to 1 or checked), the following SQL:
will, when seen by the JDBC driver, be transformed to:
| DSN Dialog Box (Windows) | Strip Quote |
| odbc.ini file (Unix) | Strip_Quote=0|1 |
| Connect String | STRIP_QUOTE=0|1 |
The default for Strip Quote is OFF (0).
Some JDBC drivers are only capable of using a single active result set, but may return a value of either zero or greater than one from DatabaseMetaData.getMaxStatements().
When ON (i.e. set to 1 or checked), the Single Statement attribute forces the ODBC driver to return a value of 1 from the SQLGetInfo call to find SQL_MAX_CONCURRENT_ACTIVITIES.
| DSN Dialog Box (Windows) | Single Statement |
| odbc.ini file (Unix) | Single_Statement=0|1 |
| Connect String | SINGLESTMT=0|1 |
The default for Single Statement is OFF (0).
Controls the return of schema information from ODBC metadata calls.
Some JDBC drivers return schema information from the result set generated by getTables() (for example), but are unable to handle SQL that contains the schema information in table and column specifications.
The default for With Schema is ON (1). When OFF (i.e. set to 0 or unchecked), schema information is returned to the ODBC application, allowing it to assemble the relevant SQL.
| DSN Dialog Box (Windows) | With Schema |
| odbc.ini file (Unix) | With_Schema=0|1 |
| Connect String | SCHEMA=0|1 |
Some JDBC drivers only return a partial result set from calls such as DatabaseMetaData.getTypeInfo().
When ON (i.e. set to 1 or checked), this attribute causes the ODBC driver to modify the values in the result set returned from metadata calls (such as SQLGetTypeInfo) so that they conform to the expected ODBC3 result set specification.
| DSN Dialog Box (Windows) | Modify Metadata |
| odbc.ini file (Unix) | Clean_Metadata=0|1 |
| Connect String | CLEAN_METADATA=0|1 |
The default for Modify Metadata is OFF (0).
When loading a JDBC driver, the Gateway creates a java.net.URLClassLoader and this is then used to load the JDBC driver.
When ON (i.e. set to 1 or checked), the URLClassLoader object is retained between calls to SQLConnect and SQLDriverConnect, thus enabling a faster connection and reducing the system resources required.
This attribute should only be disabled (i.e. set to 0 or unchecked) if problems are encountered connecting to multiple different JDBC drivers.
| DSN Dialog Box (Windows) | Reuse CL Object |
| odbc.ini file (Unix) | ReuseCL=0|1 |
| Connect String | REUSECL=0|1 |
The default for Reuse CL Object is ON (1).
While some JDBC drivers can accept ODBC-type escape sequences, some JDBC drivers are unable to understand them.
When ON (i.e. set to 1 or checked), this attribute allows the ODBC driver to modify the SQL passed to the JDBC driver, removing the ODBC sequences.
For example, the following SQL when passed to the ODBC driver:
SELECT * FROM T WHERE DATE_FIELD = {d '1999-02-01'}
SELECT * FROM T WHERE DATE_FIELD = '1999-02-01'
when this attribute is enabled.
| DSN Dialog Box (Windows) | Strip Escape |
| odbc.ini file (Unix) | Strip_Escape=0|1 |
| Connect String | STRIP_ESCAPE=0|1 |
The default for Strip Escape is OFF (0).
The ODBC specification states that if SQL_C_DEFAULT is used in combination with SQL_BIGINT fields then the result returned will be in SQL_C_BIGINT format.
When ON (i.e. set to 1 or checked), a CHAR string is returned under these conditions, as some applications (Microsoft Access in particular) do not know of the SQL_BIGINT data type and therefore expect the data to be returned as a string.
| DSN Dialog Box (Windows) | Bigint Default |
| odbc.ini file (Unix) | Bigint2Char=0|1 |
| Connect String | BIGINT2CHAR=0|1 |
The default for Bigint Default is OFF (0).
By default, the Easysoft ODBC-JDBC Gateway will not call PreparedStatement.cancel() and ResultSet.Cancel() when the ODBC function SQLCancel is called. Several JDBC Drivers will fail if this is done in separate threads, which the ODBC specification allows.
When ON (i.e. set to 1 or checked), the Easysoft ODBC-JDBC Gateway will call the cancel() methods.
| DSN Dialog Box (Windows) | Async Cancel |
| odbc.ini file (Unix) | Async_Cancel=0|1 |
| Connect String | ASYNCCANCEL=0|1 |
The default for Async Cancel is OFF (0).
The ODBC specification allows applications to request the format in which data is returned, and for each SQL datatype there is a default type. The specification says that for a wide character field, the default return type is a SQL_WCHAR, a Unicode data type.
By default, the Easysoft ODBC-JDBC Gateway will map a SQL_WCHAR column when requested as a SQL_DEFAULT to a SQL_WCHAR, in accordance with the ODBC specification.
However, for tables with one or more WCHAR fields that are part of the primary key of that table, Microsoft Access expects that conversion to result in SQL_CHAR data. The symptom for this is that the table opens, but displays #deleted in all the fields.
When ON (i.e. set to 1 or checked), the Easysoft ODBC-JDBC Gateway uses the SQL_CHAR conversion for SQL_DEFAULT requests on a SQL_WCHAR type. This is the conversion that Access expects.
This option is a workaround for Access. Enabling WCHAR Default causes the Easysoft ODBC-JDBC Gateway to behave in an non-standard way and may cause problems with other applications. If this is the case, create a separate data source for use with Access and only enable WCHAR Default in that data source
| DSN Dialog Box (Windows) | WCHAR Default |
| odbc.ini file (Unix) | WcharDefaultC=0|1 |
| Connect String | WCHARDEFAULTC=0|1 |
The default for WCHAR Default is OFF (0).
By default, the Easysoft ODBC-JDBC Gateway maps a SQLMoreResults call to a statement.getMoreResults call. However, some JDBC drivers do not properly implement getMoreResults. Setting Disable MoreResults allows the JDBC driver to always return SQL_NO_DATA from a SQLMoreResults call.
When ON (i.e. set to 1 or checked), the Easysoft ODBC-JDBC Gateway does not call getMoreResults in the JDBC driver
| DSN Dialog Box (Windows) | Disable MoreResults |
| odbc.ini file (Unix) | DisableMoreresults=0|1 |
| Connect String | DISABLEMORERESULTS=0|1 |
The default for Disable MoreResults is OFF (0).
When ON (i.e. set to 1 or checked), the Easysoft ODBC-JDBC Gateway provides support for describing parameters if the target JDBC driver supports PreparedStatement.getParameterMetaData
| DSN Dialog Box (Windows) | Not Available |
| odbc.ini file (Unix) | CanDescribeParam=0|1 |
| Connect String | CANDESCRIBEPARAM=0|1 |
The default for CanDescribeParam is OFF (0).
When ON (i.e. set to 1), the Easysoft ODBC-JDBC Gateway uses the XA interface to access the JDBC driver. This makes the XA connection available for use by the Easysoft ODBC-JDBC Gateway and any work done by the gateway 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-JDBC Gateway 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 DB field value 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 setting:
| DSN Dialog Box (Windows) | Not available |
| odbc.ini file (Unix) | XA_Connection_String=value |
| Connect String | XA_CONNECTION_STRING=value |
The class the JDBC driver implements the XADataSource interface with.
| DSN Dialog Box (Windows) | Not available |
| odbc.ini file (Unix) | XAClass=value |
| Connect String | XCLASS=value |
For the Oracle JDBC driver, use:
XAClass = oracle.jdbc.xa.client.OracleXADataSource
For the Microsoft SQL Server JDBC driver, use:
XAClass = com.microsoft.sqlserver.jdbc.SQLServerXADataSource
The method the JDBC driver provides to create XA transaction ids (Xid). The Transaction Manager uses Xids to coordinate the branches of a distributed transaction.
| DSN Dialog Box (Windows) | Not available |
| odbc.ini file (Unix) | XIDClass=value |
| Connect String | XIDCLASS=value |
For the Oracle JDBC driver, use:
XIDClass = oracle.jdbc.xa.OracleXid
For the SQL Server JDBC driver, use:
© 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.
