Creating System or User Data Sources with the ODBC Administrator

To use the BBj ODBC Driver from a third party ODBC application, run the Windows ODBC Data Source Administrator to create an ODBC data source. Then, in Windows 2000 and higher, navigate to the following:

Start ->Control Panel ->Administrative Tools ->Data Sources (ODBC)

When running Windows versions 98 through pre-Windows 2000, navigate to the following:

Start ->Settings ->Control Panel ->ODBC Data Sources

Using the ODBC Data Source Administrator

 

 

The following data types are set in a unique tab and provide different features for each data sources:

User DSN – These data sources are available only to the Windows user who creates the data source. Data sources used by Web servers or other types of servers (i.e. reporting servers, etc.) will be affected by this behavior. For example, many services run as user "system" do not have access to a user data source, that user "jdoe" created.

System DSN – System data sources are available to all users on the system. Refer to User DSN if limited user access is required.

File DSN – File data sources store the data source configuration information in a plain text file that is easily copied to other machines. User and System data sources store their information in the Windows registry. File data sources, similar to System data sources, are accessible by anyone with read access to the file. NOTE: not all ODBC applications can use File data sources.

To create a data source, do the following:

Select the tab of the appropriate type to be created.

Click [Add…].

Select the "BBj ODBC Driver" from the list of available drivers.

Click "Finish".

The following dialog will appear (with the fields empty):

 

 

Fill in the appropriate information for the data source, and click the [OK] button to create the data source. The options are as follows:

Name – Case sensitive name of the data source used to open a connection to the data source.

Description – Description for the data source.

Server – Host name of the server running BBjServices to which the data source connects.

Port – Port number to which the server running the SQL Engine server connects. The default port is 2001.

User Name – Case sensitive user name entered during the log in sequence.

Password – Case sensitive password entered during the log in sequence. If it is blank, the user is prompted for a password (assuming that the ODBC application does not specify otherwise).

Database – Case sensitive name of the database.

Prefetch Size – Number of records cached on the client side when the driver presents a fetch request. This is an optimization that reduces the amount of communication necessary between the client and server. Usually this can be left at the default of 20.

Max Connect Retries – Number of times the driver will reattempt a connection if the server is not available. There is a one second delay between attempts. This setting defaults to 5 which is adequate in most cases.

User Config – Name/value pairs separated by commas that allows the user to pass arbitrary GLOBAL variables into the SQL engine. These variables may be used in data dictionary path references ((DATA)mydata(COMPANY_ID), etc.). An example follows:

COMPANY_ID=3,ALT_DIR=foo

Use Connection Cache – Directs the driver to pipe the first five ODBC connections through a single server connection. This consumes a single license, even when running applications that normally require more than one simultaneous connection for each user. Performance is often compromised when five ODBC connections wait to use the same server connection.

Use SSL - Directs the driver to use a Secure Socket Layer (SSL) connection to the server. This ensures that data is encrypted when passed to and from the server. NOTE: In order for SSL to work, SSL must be turned on both in the SQL Engine Server in BBjServices as well as this in the driver setup. Also, make sure that SSL on all ODBC clients match the server setup. For example, if the server is setup to use SSL then all clients must also be set to use SSL. Or if the server is not setup to use SSL, then the clients must also not be set up for SSL. Mismatched settings will cause the connection attempts to fail.

In BBj 5.0 and higher, the BBj ODBC Driver offers Connection Pooling to optimize communications between the ODBC Driver and the BASIS DBMS. After an application closes an ODBC connection, the ODBC Driver retains the unused connection in a pool. If the application attempts to make another ODBC connection with the same connect string during that time, the pooled connection will be reused to instantly connect the application to the BBj Database. This eliminates the overhead of creating another socket connection and reestablishing communications between the two components. Configure Connection Pooling with the following options:

ConnectionsRemainIn Pool(secs) – In BBj 5.0 and higher, specifies the seconds a closed connection remains in the Connection Pool. By default, unused connections remain in the pool for 10 seconds.

Character Set Translation – Sets a different character set or code page when the application requires a translation of the data in the database.

ANSI to ANSI Translation – Used when the driver needs to translate the ANSI data in the database to another ANSI character set. Selecting this option provides a dialog box in which the user can choose a translation code page from a list of those installed on the user's system.

ANSI to UNICODE – Used when an application expects UNICODE data for string values from the driver. This option causes the driver to translate the single byte characters stored in the database into a multi-byte UNICODE character. The driver chooses the UNICODE character that corresponds to a mapping specified in a particular ANSI-UNICODE code page based on a particular language.

For example, an application running on a machine configured for Thai characters stores a single byte value in the database for each character in the Thai language. The Thai code page translates these characters correctly to the appropriate UNICODE value for that Thai character. However, the Thai code page would not translate correctly characters stored in an application running on a machine configured for Russian (this requires a Russian code page).