This dialogue allows you to define an ODBC-JDBC Gateway DSN which connects to a JDBC driver.

Buttons

OK

Creates or modifies an Easysoft ODBC-JDBC Gateway ODBC Data Source setup with the currently displayed settings.

Cancel

Exits the Easysoft ODBC-JDBC Gateway ODBC Data Source setup program without affecting any settings.

Help

Displays this help

Test

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.

Remember to 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.

Fields

DSN

The name of the data source.

Description

A brief description of the data source.

User Name

The name of a user to pass to the JDBC driver in order to access the database (if required).

The value entered into this field is passed to the JDBC driver as part of a Properties object in the connect call.

The equivalent Java code is as follows:

    Connection con;
    Properties prop;
    if ( uid != null ) {
        prop.put( "user", uid );
    }
    con = DriverManager.getConnection ( url, p );

This value can be overridden at the ODBC level, either by passing in a non-NULL user name in the call to SQLConnect(), or by passing a

 UID=<value>

attribute in the connection string passed to SQLDriverConnect().

It may also be specified in the JDBC URL passed to the JDBC driver, but the syntax of this is JDBC driver dependent.

Password

The password for the specified user to pass to the JDBC driver in order to access the database (if required).

The value entered into this field is passed to the JDBC driver as part of a Properties object in the connect call.

The equivalent Java code is as follows:

    Connection con;
    Properties prop;
    if ( pwd != null ) {
    prop.put( "password", pwd );
    }
    con = DriverManager.getConnection ( url, p );

This value can be overridden at the ODBC level by passing either a non-NULL user name in the call to SQLConnect() or a

PWD=<value>

attribute in the connection string passed to SQLDriverConnect().

It may also be specified in the JDBC URL passed to the JDBC driver, but the syntax of this is JDBC driver dependent.

Driver Class

The JDBC driver class name for this data source that will be passed to the forName() method.

The equivalent Java code is as follows:

    Class.forName( driverClass );

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-OBDC Bridge) would be the string:

    easysoft.sql.jobDriver

This value can be supplied at the ODBC level by a

DRIVERCLASS=<value>

attribute in the connection string passed to SQLDriverConnect().

Class Path

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 the documents on the Sun Java web site for further information about the setting of class paths under Windows.

This value can be supplied at the ODBC level by a

CLASSPATH={<valuelist>}

attribute in the connection string passed to SQLDriverConnect().

Add. Use this button to add new items to the CLASSPATH.

URL

The string that will be passed as the first argument to the call:

    Connection con;
    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-OBDC Bridge) would be the string:

    jdbc:easysoft://server:8831/dsn_name

This value can be supplied at the ODBC level by a

URL=<value>

attribute in the connection string passed to SQLDriverConnect().

Strip Quote

Controls whether double quotes around column and table names are removed.

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, the following SQL:

    SELECT \"A\" FROM \"T\"

will, when seen by the JDBC driver, be transformed to:

    SELECT A FROM T

This value can be supplied at the ODBC level by a

STRIP_QUOTE=0 | 1

attribute in the connection string passed to SQLDriverConnect().

With Schema

Controls the return of schema names from ODBC metadata calls.

Some JDBC drivers return schema names from the result set generated by getTables() (for example), but are unable to handle SQL that contains the schema name in table and column specifications.

The attribute is selected by default, but clearing it will result in the schema name being returned to the ODBC application, allowing it to assemble the relevant SQL.

This value can be supplied at the ODBC level by a

 SCHEMA=0 |
1

attribute in the connection string passed to SQLDriverConnect().

Reuse CL Object

When loading a JDBC driver the Gateway creates a java.net.URLClassLoader and this is then used to load the JDBC driver.

If this is selected then the URLClassLoader object is retained between calls to SQLConnect and SQLDriverConnect, enabling faster connection and reducing the system resources needed.

This attribute should be selected under normal use and should only be cleared if problems are encountered connecting to multiple JDBC drivers.

This value can be supplied at the ODBC level by a

 REUSECL=0 |
1

attribute in the connection string passed to SQLDriverConnect().

Bigint Default

The ODBC specification states that if SQL_C_DEFAULT is used in combination with SQL_BIGINT fields the result will be a SQL_C_BIGINT. However some applications (in particular Microsoft Access) do not recognise the SQL_BIGINT type and therefore expect the data to be returned as a string. This flag causes the driver to return a CHAR string under these conditions.

This value can be supplied at the ODBC level by a

BIGINT2CHAR=0 | 1

attribute in the connection string passed to SQLDriverConnect().

WCHAR Default

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 this option is selected, 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 nonstandard 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.

This value can be supplied at the ODBC level by a

WCHARDEFAULTC=0 | 1

attribute in the connection string passed to SQLDriverConnect().

Single Statement

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

Selecting this will force the ODBC driver to return a value of 1 from the SQLGetInfo call to find SQL_MAX_CONCURRENT_ACTIVITIES.

This value can be supplied at the ODBC level by a

SINGLESTMT=0 | 1

attribute in the connection string passed to SQLDriverConnect().

Modify Metadata

Some JDBC drivers only return a partial result set from calls such as DatabaseMetaData.getTypeInfo().

Selecting this will cause the ODBC driver to modify the values in the result set returned from metadata calls such as SQLGetTypeInfo, such that they will conform to the expected ODBC3 result set specification.

This value can be supplied at the ODBC level by a

CLEAN_METADATA=0 | 1

attribute in the connection string passed to SQLDriverConnect().

Strip Escape

Some JDBC drivers do not understand ODBC-type escape sequences such as { ].

When this is selected the ODBC driver is allowed 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'}

will be altered to:

    SELECT * FROM T WHERE DATE_FIELD = '1999-02-01'

when this attribute is selected.

This value can be supplied at the ODBC level by a

STRIP_ESCAPE=0 | 1

attribute in the connection string passed to SQLDriverConnect().

Async Cancel

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 this option is set, the Easysoft ODBC-JDBC Gateway will call the cancel() methods.

This value can be supplied at the ODBC level by a

ASYNCCANCEL=0 | 1

attribute in the connection string passed to SQLDriverConnect().

Example JDBC Driver Configuration

Cloudscape

Cloudscape (Embedded)

User Name:
Password:
Driver Class:     com.ibm.db2j.jdbc.DB2jDriver
Class Path:       ~\db2j.jar;~\license.jar;~\db2j.jar (Download)
URL:              jdbc:db2j:c:\toursDB

Single Statement: MUST be ticked.

Cloudscape (Client/Server)

User Name:
Password:
Driver Class:     com.ibm.db2.jcc.DB2Driver
Class Path:       ~\db2j.jar;~\db2jtools.jar;~\db2jcview.jar;~\jh.jar;~\db2jcc.jar;~\db2jcc_license_c.jar;~\license.jar (Download)
URL:              jdbc:db2j:net://<ip address>:1527/"c:\toursDB"

FirstSQL

User Name:        demo
Password:
Driver Class:     COM.FirstSQL.Dbcp.DbcpDriver
Class Path:       C:\firstsql\lib\fsjclasses.jar
URL:              jdbc:dbcp://local

With Schema: MUST be ticked.

HSQL

User Name:        sa
Password:
Driver Class:     org.hsqldb.jdbcDriver
Class Path:       ~\hsqldb.jar  (Download) 
URL: jdbc:        jdbc:hsqldb:hsql://<ip address>

Note: When connecting to HSQL 1.7.1 from Microsoft Access the 'Save password' option in the Link Tables window must be ticked. Without this attempts to re-open the linked table will fail as the user name and password will not be sent to the HSQL driver.

JDataStore

User Name:        sysdba
Password:         masterkey
Driver Class:     com.borland.datastore.jdbc.DataStoreDriver
Class Path:       ~\jdsserver.jar (Download)
URL:              jdbc:borland:dsremote://<ip address>/C:\JDataStore7\samples\JDataStore\datastores\employee.jds

Note

DSN parameter "BigInt Default" must be ticked when being used in conjunction with Microsoft Access. This fixes what is a bug in Access. Access expects any datatype that it doesn't know about to default to a SQL_CHAR. SQL_BIGINT defaults (as stated in the ODBC spec) to a SQL_C_BIGINT. Without this flag, Access expects a SQL_CHAR and fails. Setting this flag forces the driver to use the "incorrect" default mapping that Access expects.
DSN parameter "Modify MetaData" must be ticked when exporting data from Microsoft Access into JDataStore. This is because the result set that is generated by JDataStore for the SQLGetTypeInfo has some invalid combinations of values. This option will force the driver to modify the result set to match what most ODBC apps expect.

OpenBase

User Name:        
Password:         
Driver Class:     com.openbase.jdbc.ObDriver
Class Path:       ~\OpenBaseJDBC.jar
URL:              jdbc:openbase://localhost/Company

Note DSN parameter "Bigint Default" must be ticked when being used in conjunction with Microsoft Access. This is because of the same problem described with JDataStore.

Pointbase

Note

DSN parameter "Single Statement" MUST be ticked.
DSN parameter "With Schema" must be unticked when linking tables from Microsoft Access. This is because while the metadata returns schema information, this cannot be used when issuing SQL queries.
DSN parameter "With Schema" must be ticked when importing data into Microsoft Excel using Microsoft Query. This seems to be a bug in Query, where it does not provide a list of tables, unless there is a schema from the meta data calls. This can be avoided by going to the options dialog, and selecting "System Tables". On returning the tables will be listed as expected.
DSN parameter "Modify MetaData" must be ticked when exporting data from Microsoft Access into PointBase.

Pointbase Embedded

User Name:
Password:
Driver Class:     com.pointbase.jdbc.jdbcUniversalDriver
Class Path:       ~\pbembedded45GA217.jar  (Download)
URL:              jdbc:pointbase:embedded:sample;database.home=\\localhost\path\to\database

Pointbase Server

User Name:        PBPUBLIC
Password:         pbpublic
Driver Class:     com.pointbase.jdbc.jdbcUniversalDriver
Class Path:       ~\pbclient45GA217.jar  (Download)
URL:              jdbc:pointbase:server://<ip address>/sample

MS SQL Server

Driver Class : com.microsoft.sqlserver.jdbc.SQLServerDriver
Class path : ~\sqljdbc4.jar
URL:  jdbc:microsoft:sqlserver://SERVER_NAME\INSTANCE_NAME

MS SQL Server Version 6

Driver Class : com.microsoft.sqlserver.jdbc.SQLServerDriver
Class path : ~\sqljdbc42.jar
URL:  jdbc:sqlserver://SERVER_NAME\INSTANCE_NAME

Oracle Thin Client

Driver Class: oracle.jdbc.OracleDriver
Class Path:   ~\ojdbc14.jar;~\classes12.jar
URL:  jdbc:oracle:thin:@SERVER_NAME:1521:SID_NAME

Cross Access JDBC Driver

DriverClass     = com.cac.jdbc.Driver
ClassPath       = ~/cacjdbc21.jar
URL             = jdbc:cac:A1PATEST:tcp/SERVER_NAME/5000

Contents

Buttons

OK

Cancel

Help

Test

Fields

DSN

Description

User Name

Password

Driver Class

Class Path

URL

Strip Quote

With Schema

Reuse CL Object

Bigint Default

WCHAR Default

Single Statement

Modify Metadata

Strip Escape

Async Cancel

Example JDBC Driver Configuration

Cloudscape

Cloudscape (Embedded)

Cloudscape (Client/Server)

FirstSQL

HSQL

JDataStore

OpenBase

Pointbase

Pointbase Embedded

Pointbase Server

MS SQL Server

MS SQL Server Version 6

Oracle Thin Client

Cross Access JDBC Driver