Easysoft ODBC-ODBC Bridge User Guide - Connection

Connecting via the Easysoft ODBC-ODBC Bridge

An ODBC-compliant application is connected to a remote data source via the Easysoft ODBC-ODBC Bridge (OOB) as follows:

1.  On the server, create a system data source for the target database.

2.  On the client, configure a data source pointing to the server machine and the server data source created in step 1.

3.  From your application on the client machine, connect to the OOB Client data source created in step 2.

If you have an internet connection you can omit the server sections and set up a client connection to the demo.easysoft.com server by using the recommended settings in the relevant client subsection.

Chapter Guide

The connection process

This section explains what happens when an ODBC application connects to a data source, and what happens when connecting through the Easysoft ODBC-ODBC Bridge.

Understanding the connection process will give an insight into why the connection might fail and what is required to connect an ODBC application to remote data sources.

To skip this explanatory section, proceed to Setting up the OOB Server.

In ODBC an application connects to a database by means of a data source description, which depends on the ODBC driver used to access the database and consists of a set of attribute and value pairs.

Usually, the application links with a driver manager that looks at the data source description in the connection string, loads in the required ODBC driver and then passes the connection string to the ODBC driver.

At its simplest the application passes a connection string which defines a data source name (DSN) to the ODBC driver (or driver manager), such as:

DSN=test_datasource;

In this case the driver manager looks at the Driver attribute in the data source to decide which driver to use, loads the driver and then the driver looks up the data source to retrieve all the other required attributes.

This information is found in the registry under Windows and in the user and system odbc.ini files under Unix and MAC OS X.

It therefore follows that before an application can connect to an ODBC driver you have to create a data source containing all the attributes the driver requires to describe the database (or alternatively, the application can pass all the attributes in the connection string).

Two data sources are required for the Easysoft ODBC-ODBC Bridge:

For example (omitting the driver manager differences):

Assume there is a Microsoft Access database and a Microsoft Access ODBC driver on a Windows NT machine called "ntbox.easysoft.com".

There is also an application on a Linux box called "linuxbox.easysoft.com", which wants to read data from the Microsoft Access database.

It would be necessary to install:

Figure 12: Linking a Linux client to an NT server

On the ntbox server machine the data source description for the Microsoft Access database might consist of:

[MyDB]

Description = my Access database

mdb file = c:\databases\msaccess\mydb.mdb

MyDB is the data source name and Microsoft Access needs the location of mdb file to define the database.

On the linuxbox client machine the Easysoft ODBC-ODBC Bridge data source might consist of:

[ntbox_mydb]

Description = Access mydb on ntbox

ServerPort = ntbox.easysoft.com:8888

LogonUser = my_ntusername

LogonAuth = my_ntpassword

TargetDSN = mydb

The application on linuxbox can now call SQLDriverConnect passing a connection string of the form "DSN=ntbox_mydb;" or SQLConnect passing a data source name.

The OOB Client looks up the ntbox_mydb data source and retrieves all the other necessary attributes to connect to the server.

The OOB Client connects to the OOB Server, passing my_ntusername and my_ntpassword which are verified and then the OOB Server becomes the user my_ntusername.

At this stage the OOB Client produces a new connection string of the form "DSN=mydb;" in a call to SQLDriverConnect on the OOB Server and the process starts again in the server ODBC driver.

Note that the application is capable of passing any or all the attributes as part of the connection string (for example, the user name and password attributes can be included to avoid storing them on the server machine.

The full connection string for this example would be:

ServerPort=ntbox:8888;

LogonUser=my_ntusername;

LogonAuth=my_ntpassword;

TargetDSN=MyDB;

This is a simplified example of the connection process, but it does illustrate what actually happens.

When the DBMS requires authentication

When the server database requires authentication the ODBC defined UID and PWD attributes may be included in the connection string.

Alternatively, if the TargetUser and TargetAuth attributes are added to the Easysoft ODBC-ODBC Bridge Client data source, the OOB Client will pass these to the database as UID and PWD.

The OOB Client uses some simple rules to determine what to pass to the DBMS:

1.  If UID and/or PWD exist in the connection string and Override UID/PWD (or UseOOBDBAuth) is not enabled (i.e. is not selected on the Easysoft ODBC-ODBC Bridge DSN dialog box or is set to 0 for that data source in odbc.ini on Unix platforms), then UID and/or PWD are passed to the DBMS unchanged. If Override UID/PWD is enabled, then any UID and/or PWD values in the connection string are ignored.

2.  If not Rule 1, but the DSN contains TargetUser and/or TargetAuth, these are changed to UID and PWD and passed to the DBMS.

3.  If not Rule 1 or 2, no UID or PWD will be in the connection string passed to the DBMS (implying that authentication is not required).

On platforms where the OOB Client has specific support for a GUI environment, the ODBC driver may prompt you for database user name and password details, if UID or PWD are not specified or are incorrect.

Note that the Easysoft ODBC-ODBC Bridge does server authentication first and then database authentication. A Server Logon dialog box will be displayed if you have failed to enter values into the Username and Password fields of the OOB Client DSN. In this case a valid user name and password for the server operating system must be entered:

Figure 13: The Easysoft ODBC-ODBC Bridge Server Logon dialog box

The OOB Client ODBC driver will connect to the server and attempt a database connection, as described in The connection process. However, if the ODBC driver at the server end denies authentication and returns the ODBC state 28000, the OOB Client ODBC driver displays the Database Logon dialog box.

This requires a valid database user name and password to be entered, which will prompt the OOB Client ODBC driver to make one further connection attempt with this new authentication:

Figure 14: The Easysoft ODBC-ODBC Bridge Database Logon dialog box

How ODBC driver managers fit into the connection process

This section explains how ODBC driver managers fit into the connection process when connecting via the Easysoft ODBC-ODBC Bridge.

You should read this section if you intend to integrate the Easysoft ODBC-ODBC Bridge with your own ODBC application.

In general, ODBC applications must be linked with either an ODBC driver or a driver manager.

When a program calls SQLDriverConnect (or SQLConnect), it passes in a connection string, which contains a list of connection attributes, normally including one of the following:

A connection string would look something like:

SQLDriverConnect("DSN=pubs;UID=demo;PWD=easysoft;")

where pubs is the data source name, demo is the database user name with which to connect to the database, and easysoft is the password for the database user name.

The driver manager examines the connection attributes and loads the required driver (the driver is either named in the DRIVER= attribute or is looked up in a database of DSNs). From then on the driver manager relays ODBC calls to the driver and passes the result back to its caller.

A configured Easysoft ODBC-ODBC Bridge driver (on the client side) appears as an ordinary ODBC driver connected through the driver manager like any other.

At the server end, the OOB Server acts like an ordinary ODBC-compliant application:

The Windows platform has a well-established driver manager (odbc32.dll) to which programmers link their code

On Unix, many installations do not have a driver manager, so Easysoft distribute the unixODBC driver manager and recommend that you use it with the Easysoft ODBC-ODBC Bridge on Unix platforms (see unixODBC).


REF

The unixODBC driver manager is an open source project sponsored by Easysoft, rather than a commercial Easysoft product, and is fast becoming a standard across the data access community.

Other driver managers are available, but Easysoft believe unixODBC is demonstrably the most flexible and reliable open source solution. unixODBC driver manager distributions can be found at http://www.unixodbc.org.


Setting up the OOB Server

In terms of the Easysoft ODBC-ODBC Bridge, the server is the machine where the ODBC driver for your database is located. The database itself may also be on this machine, although it can be located elsewhere.

To allow remote machines to access your database, you first need to create a data source on the server machine to make the database available to the OOB Server.

Before setting up a data source on your server machine, you must have successfully installed and licensed the OOB Server on this machine (see Installation).

Once the OOB Server is successfully installed, go to the section appropriate to your server platform:

Windows server setup

The OOB Server for Windows can connect to any system data source configured on your machine, given the necessary information.

When creating the data source on your server, you should use the ODBC driver suitable for your database (for example, to connect to a SQL Server database, use the SQL Server ODBC driver to create the data source).

The instructions in this section describe how to create a data source for the Microsoft Northwind database, which is shipped with Microsoft Access.

You should follow the same procedure to connect to your own database on your server machine.

To follow this example, you should have on your computer:

The first step is to open the Microsoft ODBC Data Source Administrator:

1.  In Control Panel, double-click Administrative Tools and then Data Sources (ODBC)

 The ODBC Data Source Administrator dialog box is displayed.

Figure 15: The ODBC Data Source Administrator User DSN tab

2.  Select the System DSN tab:

Figure 16: The ODBC Data Source Administrator System DSN tab

 It is important to create a system DSN rather than a user DSN, which is only visible to the desktop user who created it.

 Since the OOB Server runs as a service, user DSNs are not available to it.

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 Microsoft Access Driver and click Finish.

 The ODBC driver for Microsoft Access displays a dialog box for configuring the data source (this dialog box and the attributes you need to specify vary depending on the ODBC driver you are using).


NB

The Microsoft Access ODBC driver is NOT thread-safe unless run with Jet version 4. Previous versions require the OOB Server to be configured to run in multi-process mode, rather than the default multi-threaded mode (see the Easysoft ODBC-ODBC Bridge FAQ).


5.  Enter your chosen name for this data source in the Data Source Name box (e.g. "OOB Demo DSN").

6.  In the Description field, enter something that would help a user faced with a choice of data sources (e.g. "For demonstrating the OOB"):

Figure 18: The ODBC Microsoft Access Setup dialog box

7.  Click Select... to browse for the target database, select a database and click OK.

 If the Microsoft Office\Office\Samples\Northwind.mdb example database is not accessible, use any available database ( preferably a small one).


NB

Note the data source name because it will need to be specified when a data source is created on the client machine.


8.  Click OK to return to the ODBC Data Source Administrator window.

 Note:

9.  Click OK.

You have now set up a system data source on your machine to a local database, making it visible to the OOB Server.

Starting the OOB Server in Windows

Before an OOB Client can connect to a data source on the server machine, the OOB Server must be running. In Windows, the installation program configures the OOB Server to start automatically as a Service.


64-bit Windows

By default, only the 32-bit OOB Server is started automatically. (With the default configuration settings, both 32-bit and 64-bit OOB servers listening for incoming client connections on port 8888, it is not possible for both servers to run at the same time.) If you want to use the 64-bit OOB Server, you need to:

Stop the 32-bit OOB Server service (Easysoft ODBC-ODBC Bridge Server).

Start the 64-bit OOB Server service (Easysoft ODBC-ODBC Bridge Server x64).

To configure the 64-bit OOB Server service to start automatically when Windows starts:

1. In the Windows Services dialog box, double-click Easysoft ODBC-ODBC Bridge Server.

2. In the Easysoft ODBC-ODBC Bridge Server Properties dialog box, click Stop. In the Startup type list, choose Manual.

The 32-bit OOB Server service is no longer running and will not start when Windows starts.

3. In the Windows Services dialog box, double-click Easysoft ODBC-ODBC Bridge Server x64.

4. In the Easysoft ODBC-ODBC Bridge Server Properties x64 dialog box, click Start. In the Startup type list, choose Automatic.

The 64-bit OOB Server service is now running and will start when Windows starts.


Checking the OOB Server Service under Windows

This procedure is not normally necessary, but should be followed if you are having problems connecting with the Easysoft ODBC-ODBC Bridge.

1.  In Control Panel, double-click Administrative Tools and then Services.

 A list of your system's registered services is displayed.


NB

The Services dialog box looks different in the various versions of Windows, but the principles and the functionality are the same.


Figure 19: The Easysoft ODBC-ODBC Bridge Server Services entry

2.  Find the entry for Easysoft ODBC-ODBC Bridge Server.

3.  If the Startup Type field says Disabled then double-click Easysoft ODBC-ODBC Bridge Server.

4.  In the resulting dialog box, select Automatic and then click OK.

5.  If the Status field does NOT say Started, right-click Easysoft ODBC-ODBC Bridge Server and click Start to bring the Server on-line.

6.  Click Close.

7.  Close the Control Panel.

Testing the Data Source

You now have the OOB Server running on your Windows machine and a data source connecting to the database on the server.

Before setting up the OOB client, test that this data source is working, so that you can verify that the server side is functioning correctly, by running any other ODBC application on your Windows machine, linking to this data source and accessing its data.

Refer to the documentation supplied with your ODBC application if you are unsure how to link to a data source.


NB

If the database on your server machine is a Microsoft Access database then you cannot test the data source by linking to it from the Microsoft Access application.

You must connect to it via another ODBC application on your Windows machine, because although Microsoft Access is a multi-threaded application, the Microsoft Access ODBC driver is NOT thread-safe unless run with Jet version 4.

When using the Easysoft ODBC-ODBC Bridge and the Microsoft Access ODBC driver with a previous Jet version together, you must ensure that the OOB Server is running in multi-process mode (see MultiProcess).


Once you have established that the data source on your server machine is accessing data correctly, you can proceed to Testing the OOB Server.

Unix server setup

The OOB Server for Unix can connect to any system data source configured on the Unix machine, given the necessary information.

Easysoft recommend that you use the unixODBC driver manager, supplied with the Easysoft ODBC-ODBC Bridge, for setting up data sources on Unix.


NB

This section explains how to set up data sources using unixODBC as installed by the Easysoft ODBC-ODBC Bridge. If you choose to use a different driver manager, you should refer to the documentation with that driver manager for details of setting up data sources on Unix. For further information about using unixODBC, see http://www.unixodbc.org.


With unixODBC, you can create a data source by doing either of the following:

Creating a DSN by editing a configuration file

With unixODBC, data sources are stored in a configuration file called odbc.ini.

System data sources are stored in the file output by <UNIXODBCBINDIR>/odbcinst -j. In the following sample output, odbcinst -j shows that System data sources are stored in /etc/odbc.ini.

unixODBC 2.2.12

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

USER DATA SOURCES..: /home/tim/.odbc.ini


NB

By default, you must be logged in as root to edit a system data source defined in /etc/odbc.ini, but user data sources created in an .odbc.ini file in a home directory are visible to an individual user only (i.e. the Logon User passed from the client to the server).


Each section starts with a data source name in square brackets [ ], followed by a number of attribute=value pairs.

The attributes that you need to specify vary depending on which ODBC driver you are using to connect to the local database.

A sample data source using the PostgreSQL driver is of the format:

[MAIN]

Description = Main data on Admin box

Driver = PostgreSQL

Database = main

Servername = localhost

UserName =

Password =

Port = 5432

Protocol = 6.4

ReadOnly = No

RowVersioning = No

ShowSystemTables = No

ShowOidColumn = No

FakeOidIndex = No

ConnSettings =


NB

unixODBC uses the Driver attribute to look up the driver in the odbcinst.ini file and locate the shared object to use as the ODBC driver.

The location of the odbcinst.ini file is shown by odbcinst -j.


Refer to the documentation with your ODBC driver for full details of the attributes required to define a data source.

Creating a DSN using the ODBC Data Source Administrator

To create a data source using the graphical ODBC Data Source Administrator supplied with unixODBC:

1.  On a machine running X, log in as root.

2.  In a terminal emulator window, change into the <InstallDir>/easysoft/unixODBC/bin directory.

3.  Type ./ODBCConfig <Enter>.

The ODBC Data Source Administrator opens.

4.  Click the System DSN tab to create a data source which is available to any user or service that logs into this machine.

5.  Click Add to create a new data source.

 The Adding a New Data Source dialog box displays the available drivers.

6.  Select the driver required to connect to the database and click OK.

 A configuration dialog box specific to that driver is now displayed, such as the PostgreSQL data source:

 

Figure 20: The Configuration dialog box for a PostgreSQL data source

 Refer to the documentation with your ODBC driver for full details of the attributes you need to specify on this dialog box.

7.  Click OK when you have specified the data source attributes you require and then close the ODBC Data Source Administrator.

Testing the Data Source

Before setting up the OOB Client, the new data source and the OOB Server setup should be verified with either an ODBC application available on your Unix machine or the unixODBC isql utility.

To use isql to test the data source:

1.  Change into the <InstallDir>/easysoft/unixODBC/bin directory or the <unixODBCInstallDir>/bin directory.

2.  Type:

 ./isql -v data_source_name

 For example, to connect to the PostgreSQL data source illustrated earlier in this section, you would type:

 ./isql -v main


NB

The -v option displays ODBC diagnostic messages.



NB

On some platforms, it may be necessary to run isql by using isql.sh. To do this, type ./isql.sh instead of ./isql. isql.sh is a wrapper program located in the <InstallDir>/easysoft/unixODBC/bin directory which sets up the dynamic linker before running isql.


 If your server DBMS requires authentication, you should include the DBMS user name and password arguments in the isql command (see When the DBMS requires authentication).

3.  Once connected, either:

select * from tablename

where tablename is a table in that database

¯ OR ¯

4.  To leave isql and return to the system prompt, press <Enter>.

You can now proceed to Testing the OOB Server.

Testing the OOB Server

oobping

If you believe that there are problems with the connection between the application and the server then you can use oobping to help diagnose and fix any errors before proceeding to Setting up the OOB Client.

oobping is a small program distributed with the Easysoft ODBC-ODBC Bridge, and is a valuable tool for checking Easysoft ODBC-ODBC Bridge connectivity and diagnosing connection problems or connection timing issues:

In Windows distributions the oobping.exe program is located in the <installdir>\Easysoft\Easysoft ODBC-ODBC Bridge directory.

In Unix and Mac OS X distributions there are two versions of oobping located in the /usr/local/easysoft/bin directory.

oobping has the following command line:

oobping [-h host | -d ODBC_connection_string] {-t port} {-u osuser -p ospassword} {-e}

where:

-h host

The name or IP address of the machine where the OOB Server is located.

-d ODBC_connection_string

An ODBC connection string consisting of a list of semi-colon separated attribute=value pairs, as defined by ODBC.

e.g. DSN=test;UID=john;PWD=smith;

If the -u and/or -p attributes are also specified as well as -d then "LogonUser=xxx;LogonAuth=yyy;" (where xxx and yyy are the values specified for -u and -p) will be added to the end of the connection string.


NB

When running oobping from a Unix shell, you may have to enclose the ODBC connection string, user name and password with single quotes. Do this to protect any spaces or special characters that the shell might interpret these settings contain.

When running oobping from an MS-DOS window, use double quotes to protect spaces.


-t port

The port on which the OOB Server is listening.

-u osuser

A valid user name on the "host" operating system.

-p ospassword

A password for the user specified with the -u attribute.

-e

Show the amount of time (in seconds) to complete the requested operation.

Worked examples

The following examples illustrate the ways in which oobping can be used to investigate connection issues:

Example 1: Check the OOB Server is running on the correct machine and listening on the correct port

oobping connects to port 8888 on the machine myserver, where OOB Server version 1.1.0.00 is reported as running.

oobping -h myserver -t 8888

Host: myserver, Port: 8888

Attempting connection...OK

Examining Server...

OOB Server Version: 1.1.0.00

OOB Server Name: OOB


NB

oobping defaults to port 8888, so the -t attribute can be omitted.


If the wrong port is specified or some other service is listening on the specified OOB Server port, then you will get a variety of errors, such as when pointing oobping at an SMTP server on port 25:

oobping -h myserver -t 25

Host: myserver, Port: 25

Failed to receive data

Packet (size=842149920) too big for buffer (size=256)

If there is nothing listening on the specified port then you will get a connection refused error and you should check that the OOB Server is running and is configured to use the specified port.

e.g.

oobping -h myserver -t 8889

Host: myserver, Port: 8889

Connection refused, connect(), after 5 attempts

If you have specified access control rules in the OOB Server then you might see an error such as this:

oobping -h myserver -t 8888

Host: myserver, Port: 8888

Client denied access due to access control rule.

Here the machine you are running oobping on has been denied access to the OOB Server and you should check the access control rules on the security page of the OOB Web Administrator.

Example 2: Check OOB Server authentication

Once you are sure a connection can be made to the OOB Server with oobping (as in example 1) you can check OOB Server authentication.

The -u and -p arguments to oobping allow you to specify a valid operating system user name and password.


NB

If you have disabled authentication in the OOB Server then any user name and password will work no matter what you enter.

The values specified with -u and -p are equivalent to the OOB Client DSN attributes LogonUser and LogonAuth.


e.g.

oobpings -h myserver -t 8888 -u 'A User' -p 'mypassword'

Host: myserver, Port: 8888

Attempting connection...OK

Examining Server...

OOB Server Version: 1.1.0.00

OOB Server Name: OOB

Trying to authenticate...OK

If there is something wrong with the user name or password then the output will look something like this:

oobpings -h myserver -t 8888 -u 'A User' -p 'mypassword'

Host: myserver, Port: 8888

Attempting connection...OK

Examining Server...

OOB Server Version: 1.1.0.00

OOB Server Name: OOB

Trying to authenticate...Fail

Authentication failure (error number 1326)

In this case the error returned by the remote user name/password authentication service is 1326 (as the server was on Windows, this is a Windows error code).

All the common Windows error codes can be found in the Easysoft ODBC-ODBC Bridge FAQ.

Example 3: Check connectivity to a specified remote data source

Once you have completed examples 1 and 2 you should have:

You can now check connectivity to a specified remote data source (DSN), which can be done in two ways with oobping.

The simplest way is to add a local DSN to your odbc.ini file and then specify the DSN in the -d argument as "DSN=dsnname". This method tests you have defined the local OOB Client DSN correctly and put the definition in the right file.

An alternative method is to specify all the connection attributes in the -d argument, not just the DSN.

e.g.

Suppose as a result of examples 1 and 2 you have the following information:

Server = myserver

Port = 8888

LogonUser = me

LogonAuth = mypassword

Now enter the name of the target DSN on myserver in the TargetDSN attribute.


NB

Note that the target DSN MUST be a remote SYSTEM data source, as you cannot access USER data sources.


If the database also needs login information then the database user name and password are specified using the TargetUser and TargetAuth attributes.

If you want to test a DSN in your odbc.ini file then it would look something like:

[mydsn]

ServerPort = myserver:8888

LogonUser = me

LogonAuth = mypassword

TargetDSN = mysystemdsn

TargetUser = dbusername

TargetAuth = dnpassword


NB

"Driver=OOB" must be included in the DSN in order to use the unixODBC driver manager, but this is not needed when using oobping.


You can run oobping as follows:

oobping -d "DSN=mydsn;"

Using Connection string :

DSN=mydsn;

Connected OK

01000:1:5701:[NetConn: 032bc620][Microsoft][ODBC SQL Server Driver]

[SQL Server]Changed database context to 'pubs'.

01000:2:5703:[NetConn: 032bc620][Microsoft][ODBC SQL Server Driver]

[SQL Server]Changed language setting to us_english.

OutConnectionString:

DSN=mydsn;UID=dbusername;PWD=dbpassword;SERVERPORT=myserver:8888;

TARGETDSN=mysystemdsn;LOGONUSER=me;LOGONAUTH=mypassword;

Connected to database: pubs

DBMS Name: Microsoft SQL Server

Driver Name: esoobclient

Driver Version: 01.00.0043

Disconnecting

The ODBC connection string "DSN=mydsn" was passed via oobping to the OOB Client, but the OOB Client did not receive sufficient attributes in the connection string.

In order to define what it should do, the OOB Client looked up the DSN 'mydsn' in the odbc.ini file, where it obtained the additional attributes ServerPort, LogonUser, LogonAuth, TargetDSN, TargetUser and TargetAuth.

The OOB Client then connected to the OOB Server on myserver via port 8888, logged you in with LogonUser/LogonAuth values and finally connected via ODBC to the remote data source 'mysystemdsn'.


NB

Although in this example the DSN pointed to Microsoft SQL Server and informational diagnostics reported the language as "us_english" and the database as "pubs", not all databases will return informational messages on the connection.


The OutConnectionString is a string returned by the OOB Client which you can use to connect to this data source again and the final messages show the database name, DBMS name, driver name and driver version.

Instead of specifying just the name of a DSN to oobping and defining the other connection attributes in the odbc.ini file you can specify all the connection attributes in one go and not use an odbc.ini file.

The OutConnectionString shows you what connection string you could have passed to the OOB Client to connect without a DSN (if you remove the "DSN=mydsn;") to produce the same result.

e.g.

oobping -d "UID=dbusername;PWD=dbpassword;SERVERPORT=myserver:8888;

TARGETDSN=mysystemdsn;LOGONUSER=me;LOGONAUTH=mypassword;"

You might be slightly confused as to why TargetUser/TargetAuth is specified in the odbc.ini file, but UID/PWD in the connection string.

Strictly speaking, the ODBC defined attributes are UID/PWD and they are passed to the database for authentication.

As far as ODBC connection strings are concerned, UID/PWD and TargetUser/TargetAuth are synonymous in the Easysoft ODBC-ODBC Bridge, but if specified in the odbc.ini file you should always use TargetUser/TargetAuth.

Now you know how to use oobping with connection strings, here are some examples of common problems you might see:

oobping -d

"UID=dbuser;PWD=dbauth;TargetDSN=test;LogonUser=me;LogonAuth=mypassword"

Using Connection string :

UID=dbuser;PWD=dbauth;TargetDSN=test;LogonUser=me;LogonAuth=mypassword

IM002:1:0:[Easysoft ODBC (Client)]

Data source not found and no default driver specified

HY000:2:0:[Easysoft ODBC (Client)]

general error: Missing attribute(s): SERVERPORT

The initial diagnostic, IM002 is defined by ODBC, but as it is rather vague, the OOB Client added a secondary more helpful diagnostic.

Here the Easysoft ODBC-ODBC Bridge connection attribute 'ServerPort' was omitted and so the OOB Client did not know which server to connect to.

oobping -d

"ServerPort=myserver:8888;UID=dbuser;PWD=dbauth;TargetDSN=test;LogonUser=me;Log

onAuth=mypassword"

Using Connection string :

ServerPort=myserver:8888;UID=dbuser;PWD=dbauth;TargetDSN=test;LogonUser=me;LogonAuth=mypassword

28000:1:18456:[][Microsoft][ODBC SQL Server Driver][SQL Server]

Login failed for user 'dbuser'.

This Microsoft SQL Server error suggests the database password 'dbauth' is not correct for the database user 'dbuser' or that 'dbuser' is not a valid user, and illustrates the importance of identifying which component is reporting an ODBC error.

You should examine the components displayed in [], where the one furthest to the right is the reporting component and as you move left through the components you are moving closer to the OOB Client.

For example, if you specified a TargetDSN which did not exist on the server machine then the driver manager on the remote machine would be the component reporting the error and so the diagnostic error message (for Windows) would be:

IM002:1:0:[][Microsoft][ODBC Driver Manager]

Data source name not found and no default driver specified

The other important thing to note here is that oobping outputs any ODBC diagnostics as:

ODBC State : diagnostic sequence : ODBC native error: text

The native error code is specific to the component reporting the error, and can be referenced in the Microsoft SQL Server documentation, which should tell you more accurately in which circumstances this error is reported.

Example 4: Checking connection times

oobping includes the -e option, which times the requested operation and can be invaluable in diagnosing a slow connection and determining which phase the problem is occurring in.

These operations may also use the -e switch.

e.g.

oobping -e -h myserver -t 8888

Host: myserver, Port: 8888

Attempting connection...OK

Examining Server...

OOB Server Version: 1.1.0.00

OOB Server Name: OOB

Time for execution: 0.16s

If this is repeated with the -u and -p attributes you can work out the extra time required to perform the operating system logon:

oobping -e -h myserver -u myosuser -p mypassword

Host: myserver, Port: 8888

Attempting connection...OK

Examining Server...

OOB Server Version: 1.1.0.00

OOB Server Name: OOB

Trying to authenticate...OK

Time for execution: 0.52s

This example clearly demonstrates the extra time required to authenticate a user.


NB

When connecting to a server in part of a Windows domain, the server may need to contact the Primary Domain Controller (PDC). This can take a significant amount of time.

In addition, if you install the OOB Server on the same machine as Microsoft SQL Server, the OOB Server and SQL Server compete for CPU time. It is often quicker to put the OOB Server on a different Windows machine.


Setting up the OOB Client

The Easysoft ODBC-ODBC Bridge Client is the machine running the ODBC application with which you want to access the data on the server.

To allow an ODBC application on the client machine to access data on the remote server, you need to create a data source on the client.

This data source uses the Easysoft ODBC-ODBC Bridge Client driver and specifies the attributes required to connect to the data source on the remote server.

Before setting up a data source on your client machine, you must have successfully installed the OOB Client on this machine, and set up the server side of the Easysoft ODBC-ODBC Bridge.

Instructions for installing the OOB Client on Windows, Unix, and Mac OS X platforms are provided in Installation.

If you have not already set up the server side of the Easysoft ODBC-ODBC Bridge, go to Setting up the OOB Server.

Once the OOB Client is successfully installed and your server is set up correctly, go to the section appropriate to your appropriate client platform:

Windows client setup

This section explains the steps you should take to connect an ODBC application on a Windows machine (where the OOB Client is installed) to the data source on your remote server (where the OOB Server is installed).

The instructions in this section show you how to connect to an example data source on a server at Easysoft, but you should follow the same procedure to connect to the data source on your own server.

The OOB Client is an ODBC driver. You configure a data source using the OOB Client in the same way that you configure a data source using any other ODBC driver, so many of the steps in this section are similar to those in Windows server setup.


64-bit Windows

64-bit Windows machines support both 32-bit and 64-bit ODBC drivers.

If you want to use a 32-bit ODBC application, you need to use the 32-bit OOB Client. If you want to use a 64-bit ODBC application, you need to use the 64-bit OOB Client.

There is both a 32-bit and a 64-bit version of the ODBC Data Source Administrator, which is used to configure ODBC data sources. To access the 32-bit ODBC Data Source Administrator, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

The 64-bit ODBC Data Source Administrator is located in Control Panel under Administrative tools.

OOB Client data sources created in the 32-bit ODBC Data Source Administrator will specify the 32-bit version of the OOB Client. OOB Client data sources created in the 64-bit ODBC Data Source Administrator will specify the 64-bit version of the OOB Client.

System data sources created in the 32-bit ODBC Data Source Administrator are only visible to 32-bit applications. If you want to create an OOB Client System data source for use with a 32-bit application, use the 32-bit ODBC Data Source Administrator therefore. Likewise, System data sources created in the 64-bit ODBC Data Source Administrator are only visible to 64-bit applications.



64-bit Windows

(The reason for this is that System data sources created in the 64-bit ODBC Data Source Administrator are stored in a registry key called \HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. System data sources created in the 32-bit ODBC Data Source Administrator are stored in a registry key called \HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI. The WOW64 layer intercepts registry calls to HKEY_LOCAL_MACHINE\Software that are made by 32-bit applications, and then redirects them to the HKEY_LOCAL_MACHINE\Software\WOW6432node key.)

User data sources are visible to both 32-bit and 64-bit applications, irrespective of the version of ODBC Data Source Administrator they were created in. If a 64-bit application connects to an OOB Client User data source created in the 32-bit ODBC Data Source Administrator, the 64-bit version of the OOB Client will be used. Likewise, a 32-bit application that connects to a 64-bit OOB Client User data source will use the 32-bit version of the OOB Client.


Firstly, open the Microsoft Data Source Administrator:

1.  In Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).

 The ODBC Data Source Administrator opens.

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 is displayed, containing a list of drivers:

Figure 21: The Create New Data Source dialog box

4.  Select Easysoft ODBC-ODBC Bridge and click Finish.

 A a DSN configuration dialog box is displayed:

Figure 22: A blank Easysoft ODBC-ODBC Bridge DSN dialog box

The Easysoft ODBC-ODBC Bridge DSN dialog box

The attributes on this dialog box are split into five tabs, arranged by functionality, from left to right:

 The Test button allows you to check that the client is able to connect to the specified server data source.


NB

If you click Test before specifying a server and port (the Servers setting) or a remote System DSN (the TargetDSN setting), you'll be prompted to enter these required settings.


DSN

5.  In the Name box, enter a name for this data source.

 Choose carefully because you will not be able to change this after pressing OK.


NB

A demo data source will probably already exist because it is set up during the OOB Client installation. It is required if you intend to use the demo.exe client application.


6.  In the Description box, enter something that would help a user faced with a choice of data sources.

Server

Figure 23: The Easysoft ODBC-ODBC Bridge DSN dialog box - Server tab

7.  In the Servers box, type the name of the machine on which the OOB Server is running and the port that it is listening on.

 Use the following format:

 server:port

 The default port is 8888. Specify this port unless you know that the OOB Server is listening on another port.

 For example, if you are connecting to the Easysoft demo server, type:

  demo.easysoft.com:8888

 If the remote System DSN is available on more than one OOB Server, you can define a primary OOB Server for the DSN and additional fallback OOB Servers. For more information about defining multiple OOB Servers, see Client support for Fallback OOB Servers.

8.  In the Username and Password boxes, enter a valid logon account and password for the machine on which the OOB Server is running (if required).

 The OOB Server carries out all activities as this user.


NB

If your server is part of a Windows domain, you may need to include the domain name with the user name, using the format domain/user name. For example: admin/John Smith.

If you have defined multiple OOB Servers for this data source, the username and password must be valid for each OOB Server that you specify.


¯ OR ¯

 If you are connecting to the Easysoft demo server, enter demo and easysoft respectively in these boxes.

9.  To test the connection to the OOB server and make sure the authentication details you entered are valid, click the ... button.

10.  In the Servers dialog box, click the Test.

 If the test succeeds, in the Servers list, a green tick displays next to the server.

¯ OR ¯

 If a connection can't be made, in the Servers list, a red cross displays next to the server. The Test Log output shows the reason why the test failed in red text. For help on how to resolve the connection problem, click Help.

Figure 24: The Servers dialog box dialog box showing output from a successful connection to an OOB server.

TargetDSN

Figure 25: The Easysoft ODBC-ODBC Bridge DSN dialog box - Target DSN tab

11.  In TargetDSN, enter the data source name on your remote machine.

¯ OR ¯

 Click the ... button to retrieve the data sources that are available on the remote machine, then choose one from the list. Note that the AllowDSNBrowse OOB Server configuration parameter controls whether you can retrieve the list of remote data sources. If this facility has been disabled, you'll need to enter the data source name instead.

¯ OR ¯

If connecting to the Easysoft demo server, enter pubs.


64-bit Windows

If you are connecting to a 32-bit Windows OOB Server, you must specify a 32-bit ODBC driver data source. If you are connecting to a 64-bit Windows OOB Server, you must specify a 64-bit ODBC driver data source.

If you attempt to use a 32-bit OOB Server with a 64-bit ODBC driver data source, or a 64-bit OOB Server with a 32-bit ODBC driver data source, the connection will fail with the error:

The specified DSN contains an architecture mismatch between the Driver and Application


 

12.  If your remote data source (i.e. the database itself) requires a user name and password apart from the user logon account for the machine, then enter these in TargetUser and TargetAuth.

¯ OR ¯

 If you are connecting to the Easysoft demo data source, enter demo and easysoft in TargetUser and TargetAuth.

¯ OR ¯

 If your data source does not need separate authentication details then leave these fields blank.

 

Figure 26: The DSN set up for the Easysoft demo data source

Optimisation

Figure 27: The Easysoft ODBC-ODBC Bridge DSN dialog box - Target DSN tab

For more information about the options on the Optimisation tab, see Attribute Fields. If you are connecting to the Easysoft demo data source, leave the options set to their default values.

Settings

Figure 28: The Easysoft ODBC-ODBC Bridge DSN dialog box - Settings tab

For more information about the options on the Settings tab, see Attribute Fields. If you are connecting to the Easysoft demo data source, leave the options set to their default values.

Check your values

13.  Now click Test.

 The OOB Test dialog box displays, showing the current dialog box settings.

14.  In the OOB Test dialog box, click Test.

Using the current dialog box dialog box settings, the OOB Client tests that:

For a comprehensive description of the test process, click the Help button. Note that you can test the server connection and authentication details separately. To test just your server settings, see step 10.

15.  If a stage in the connection process cannot be successfully completed, the OOB client stops the test and displays diagnostic output. The output identifies the test that failed and provides the reason for the failure. For information on how to solve connection problems, click Help.

¯ OR ¯

 If you see "Test Successful" the connection process has been successfully completed.

Figure 29: The OOB Test dialog box showing test results generated from valid DSN settings

16.  Click OK in the test box and OK in the DSN dialog box.

The connection has been made.

Connecting a Client Application in Windows

You should now have a data source on your Windows machine that connects through the Easysoft ODBC-ODBC Bridge to a second data source on the remote server machine (either your own server or demo.easysoft.com).

To demonstrate that the Easysoft ODBC-ODBC Bridge is functioning correctly you can connect an example ODBC application to the local data source.

You will need experience of Microsoft Access to complete this section.


Caution!

If you are using the Easysoft ODBC-ODBC Bridge across the internet (e.g. to contact the Easysoft demo server) then Microsoft Access is not recommended, as its very heavy use of ODBC calls generates significant network traffic when using the Easysoft ODBC-ODBC Bridge. The Demo.exe Client describes a more efficient client program.


1.  Start Microsoft Access (for example) and create a blank database.

2.  Do one of the following:

The Link dialog box displays the existing databases on your system.

3.  Do one of the following:

 The Microsoft ODBC driver manager displays the Select Data Source dialog box:

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

4.  Click the Machine Data Source tab.

 Find the local data source you created, somewhere 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 to the OOB data source and the OOB client relays the ODBC API calls to the OOB Server. Microsoft Access will retrieve a list of tables in the remote data source and display them in a window.

6.  Click on a table and then click OK.

 After a short wait, you are returned to the Database window.

7.  Double-click any table to open and browse it.

The Demo.exe Client

An application called demo.exe is included with the Easysoft ODBC-ODBC Bridge that can be used to verify that a connection has been made to an ODBC data source.

The demo.exe program lets you test your OOB Client installation by connecting to the demo data source.

Note that you can also use demo.exe to connect to any Easysoft ODBC-ODBC Bridge data sources you have created yourself.

The source code is included in the demonstration program to give developers an insight into creating simple ODBC client applications.


NB

The demo.exe client application will not work without a local data source having been configured.

Refer to the section Windows client setup and create the demo data source. Note that the data source must be named demo for some parts of demo.exe to work.


1.  Start an MS-DOS window.

2.  Change directory to the Examples directory within the easysoft install directory.

 e.g.

 Program Files\Easysoft\Easysoft ODBC ODBC Bridge

 \Examples

3.  Execute the demo.exe program:

Figure 31: The demo.exe program

 The demonstration program is self-explanatory: select an option, press <Enter> and then follow the information displayed on screen.

 [1] displays the initial notice.

 [2] displays the requirements for the demo.

 [3] this option is not supported on Windows.

 [4] tests the connection by contacting demo.easysoft.com through the Easysoft ODBC-ODBC Bridge.

 [5] retrieves a table from the database on demo.easysoft.com.


NB

Option 5 will only retrieve data when your demo data source is configured to connect to the demo.easysoft.com server. You will probably receive errors if you configure your demo data source to connect to another database.


 [6] attempts to connect to all user data sources one at a time. Whether the connection fails or succeeds is displayed in a Status column. If the connection fails, diagnostics are provided.


NB

If a data source points to a machine that cannot currently be contacted, the connection attempt may take a couple of minutes to time out.


 [7] builds up a connection string to connect to the OOB Client ODBC driver but with values you specify to demo.exe (you do not need to create a data source). You are prompted for each value in turn (the default values build up a connection string to the demo data source on demo.easysoft.com). The connection string is displayed in full before demo.exe attempts to connect using that connection string.

 [8] displays suggestions as to what may be the problem with your connection.

 [9] exits from the demo.exe program.

Unix client setup

This section explains the steps you should take to connect an ODBC application on a Unix machine (where the OOB Client is installed) to the data source on your remote server (where the OOB Server is installed).

To connect the OOB Client in Unix to a remote data source, you need to define the data source on the remote machine by specifying its attributes in a data source on the local (client) machine.

You can create a data source for the OOB Client on the local Unix machine either by:

Driver manager functionality in the Easysoft ODBC-ODBC Bridge

The most important driver manager functionality provided by the Easysoft ODBC-ODBC Bridge is the storing of data source attributes in order to be able to connect given a minimal connection string.

When an application connects through the OOB Client, it can pass in as little information as just a data source name (DSN).

The OOB Client will search for a configuration file in this sequence:

For the last location, the OOB client will attempt to load the unixODBC libodbcinst shared object and use SQLGetPrivateProfileString(). In this way, if you built unixODBC yourself with a different --sysconfdir option value, OOB can still find your DSNs.

When unixODBC is installed with the Easysoft ODBC-ODBC Bridge, --sysconfdir is set to /etc, but if you have built and configured your own unixODBC then it will be whatever you specified with the --sysconfdir configuration option, or /usr/local/etc if omitted.


NB

If you are running Apache/PHP or ColdFusion with the Easysoft ODBC-ODBC Bridge, Easysoft recommend you use /etc/odbc.ini. Putting the file in the web server document tree risks making it publicly accessible, and putting it in the `current directory' may be meaningless as the web server can be started from any directory.


Creating a DSN by editing a configuration file

With unixODBC, data sources are stored in a configuration file called odbc.ini.

Use the <UNIXODBCBINDIR>/odbcinst -j command to locate the odbc.ini file where System data sources are defined and the odbc.ini file where User data sources are defined. The following lines show some sample odbcinst -j output:

unixODBC 2.2.12

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

USER DATA SOURCES..: /home/tim/.odbc.ini

You usually have to be logged in as root to edit the system odbc.ini.

To add a data source, open the relevant odbc.ini file in a text editor, then add a new section.

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.ini are not case sensitive.


The Driver attribute names the ODBC driver in the odbcinst.ini file to use for this data source. When the Easysoft ODBC-ODBC Bridge is installed into unixODBC it places an OOB entry into the odbcinst.ini file so you should always have Driver = OOB in your Easysoft ODBC-ODBC Bridge data sources. On some platforms, the OOB Client is distributed as two separate drivers, a thread-safe one requiring pthreads (OOB_r) and one which is not thread-safe (OOB) and does not require pthreads. If this distribution contains the thread-safe driver, an additional driver will be added to your odbcinst.ini file. To use the thread-safe driver, specify Driver = OOB_r in the data source definition.

To configure your DSN in your odbc.ini file, you will need to specify:

Other optional attribute values may be set in the odbc.ini file. For more information about the available attributes, see Attribute Fields.


NB

Don't forget that any attributes not specific to the Easysoft ODBC-ODBC Bridge are passed through to the remote data source, so you can effectively set up the remote data source from the local machine.


An example odbc.ini file

If you have a Linux box called linus.mydomain and Microsoft SQLServer and the OOB Server running on a remote Windows NT machine called ntbox.mydomain with a Windows NT user name of myname and your password of mypassword.

Imagine you have set up a system data source on ntbox (see Windows server setup) called myNTdsn which requires database authentication dbuser and dbpassword.

You want to access data in Microsoft SQLServer on ntbox from linus using your Perl or PHP script.

Figure 32: A Linux client connected to an NT server

The Linux box odbc.ini file would contain a section of the format:

[localdsn]

Driver=OOB

ServerPort=ntbox.mydomain.com:8888

LogonUser=myname

LogonAuth=mypassword

TargetDSN=myNTdsn

TargetUser=dbuser

TargetAuth=dbpassword


NB

If using a driver manager at the client end, unixODBC looks up the Driver= entry in the odbcinst.ini file to locate the shared object to use as the ODBC driver.


When your application connects through the OOB Client, it needs to pass in the DSN localdsn, which the OOB Client then uses to access the correct section in the odbc.ini file.

Creating a DSN using the ODBC Data Source Administrator

To create a data source using the graphical ODBC Data Source Administrator:

1.  On a machine running X, log in as root.

2.  In a terminal emulator window, change into the <InstallDir>/easysoft/unixODBC/bin directory.

3.  Type ./ODBCConfig and press <Enter>.

The ODBC Data Source Administrator opens.

4.  Click the System DSN tab to create a data source which is available to any user or service that logs into this machine.

 If you create a user DSN, only the root user will be able to access it because you logged in as root when you started your session. User DSNs will be placed in the .odbc.ini file in the home directory of the current user (i.e. $HOME/.odbc.ini).

5.  Click Add to create a new data source.

 The Adding a New Data Source dialog box lists the available drivers.

6.  Select the Easysoft ODBC-ODBC Bridge driver and then click OK.

 On some platforms, the ODBC-ODBC Bridge DSN dialog box is displayed:

 

Figure 33: A blank Easysoft ODBC-ODBC Bridge DSN dialog box

For information about adding and testing a data source by using this dialog box, see The Easysoft ODBC-ODBC Bridge DSN dialog box.

On other platforms, the Data Source Properties dialog box is displayed.

 

Figure 34: The Data Source Properties dialog box

 The options that you can set on this dialog box are the same as the attributes that you can specify when defining a data source in the odbc.ini file.

 Refer to Creating a DSN by editing a configuration file for information about each setting and details of how they are handled by the Easysoft ODBC-ODBC Bridge.

7.  Click OK when you have specified all the data source attributes and then close the ODBC Data Source Administrator.

Once you have a data source on your Unix client connecting to a data source on your server, you can run an application on your client machine and connect to the data source on the server machine.

The Demo Application

The distribution includes a simple ODBC program for connecting to any ODBC data source in order to test that it is working.

This program can be used in the event that your own remote ODBC data source is not yet configured.

Note that unlike other ODBC applications, demo is not linked to an ODBC Driver Manager. demo is linked directly with the OOB Client. You can therefore only use it to test data sources defined in the sample ./odbc.ini file or those where the OOB attributes are entered directly into demo. oobping is a more flexible and more recent program to use to test the Easysoft ODBC-ODBC Bridge. For more information about oobping, see oobping.

To run the demo program:

1.  Change into the <InstallDir>/easysoft/oob/examples directory.

2.  Type:

 ./demo

 The source code for this demo program is in the examples subdirectory.

Figure 35: The demo.exe program on Unix

The demonstration program is self-explanatory: select an option, press <Enter> and then follow the information displayed on screen.

 [1] displays the initial notice.

 [2] displays the requirements for the demo.

 [3] displays the contents of ./odbc.ini.

 [4] tests the connection by contacting demo.easysoft.com through the Easysoft ODBC-ODBC Bridge.

 [5] retrieves a table from the database on demo.easysoft.com.


NB

Option 5 will only retrieve data when your demo data source is configured to connect to the demo.easysoft.com server. You will probably receive errors if you configure your demo data source to connect to another database.


 [6] attempts to connect to each data source in the local odbc.ini file. Whether the connection fails or succeeds is displayed in a Status column. If the connection fails, diagnostics are provided.


NB

If a data source points to a machine that cannot currently be contacted, the connection attempt may take a couple of minutes to time out.


 [7] builds up a connection string for the OOB Client ODBC driver but with values you specify to the demo program. You are prompted for each value in turn (the default values build up a connection string to the demo data source on demo.easysoft.com). The connection string is displayed in full before the demo program attempts to connect using that connection string.

 [8] displays suggestions as to what may be the problem with your connection.

 [9] exits from the demo program.

Mac OS X client setup

This section shows you how to connect an ODBC application on a Mac OS X computer (where the OOB Client is installed) to a data source on a remote server (where the OOB Server is installed).

To access remote data from an ODBC application on the client computer, you need to create an ODBC data source on the client. This data source uses the OOB Client ODBC driver and specifies the attributes required to connect to the remote data source.

To create an OOB Client data source on Mac OS X, use ODBC Administrator. ODBC Administrator is a component of iODBC that provides a GUI with which you can add, modify, delete and examine data sources.

Data sources are stored in the odbc.ini file. User data sources are stored in ~/Library/ODBC. To find out the directory where System data sources are stored, in a Terminal window, type the following command:

iodbc-config --odbcini

iodbc-config is a script that outputs iODBC configuration information. The --odbcini option prints the system wide odbc.ini file path. The following line shows some example output from the previous command:

 /Library/ODBC/odbc.ini

 


Note

To add, remove or edit System data sources, you need to be logged in as an administrator user.

Depending on the permissions on the system wide odbc.ini file, you may have to log in as the root user to add, remove or edit System data sources. Otherwise, the changes that you make will not be saved in the odbc.ini file.

By default, the root user account is not active. For information about enabling the root user and the implications of using the root account, in the Mac OS X Help, search for "root user."


To add an OOB Client data source

1.  Open ODBC Administrator in the /Applications/Utilities folder.

 ODBC Administrator is displayed.

Figure 36: The Mac OS X ODBC Administrator

2.  Do one of the following:

 If the System DSN tab is locked, the Add button will be unavailable. To unlock the System DSN tab, click the lock icon, and then type an administrator user name and password when prompted.

The Choose A Driver dialog prompts you to choose the driver for which you want to set up the data source.

Figure 37: The Mac OS X ODBC Administrator Choose A Driver dialog box

3.  From the list of ODBC drivers, choose Easysoft ODBC-ODBC Bridge, then click OK.

The OOB Client for Mac OS X DSN dialog box is displayed.

 

Figure 38: The OOB Client for Mac OS X DSN dialog box

 The dialog box uses the following tabs to organise data source attributes:

4.  In the DSN tab, in the Name box, type the data source name.

 Note that the OOB Installer program creates a sample System data source named "demo". If you want to create your own test System data source, use a different name. Otherwise, the demo data source will be overwritten.

5.  In the Description box, type some text to describe the data source.

 Some applications display the description text to help users differentiate between different data sources.

Figure 39: The OOB Client for Mac OS X DSN dialog box--Servers tab

6.  In the Servers tab, in the Servers box, type server:port.

 server is the fully qualified domain name or IP address of the server on which the OOB Server is running. port is the port that the OOB Server is listening on. The default port is 8888.

 For example, to connect to the Easysoft demo server, type:

 demo.easysoft.com:8888

 If the remote System DSN is available on more than one OOB Server, you can define a primary OOB Server for the DSN and additional fallback OOB Servers. For more information about defining multiple OOB Servers, see Client support for Fallback OOB Servers.

7.  In the Username and Password boxes, type the username and password to use to log in to the server on which the OOB Server is running (if required).

 If the username and password are valid, the OOB Server process changes to become the specified user.

 For example, to connect to the Easysoft demo server, type demo in the Username box and easysoft in the Password box.


Note

If your server is part of a Windows domain, you may need to include the domain name with the user name, using the format domain/user name. For example: admin/John Smith.

If you have defined multiple OOB Servers for this data source, the username and password must be valid for each OOB Server that you specify.


Figure 40: The OOB Client for Mac OS X DSN dialog box--TargetDSN tab

8.  In the TargetDSN tab, in the TargetDSN box, type the name of a System data source on the remote server.

 For example, if you are connecting to the Easysoft demo server, type pubs.

9.  If the database requires a username and password, type these in the TargetUser and TargetAuth boxes. Otherwise, leave these boxes blank.

 For example, if you are connecting to the Easysoft demo data source, in the TargetUser box, type demo. In the TargetAuth box type easysoft.

10.  For more information about the options on the Optimisation tab and the Settings tab, see Attribute Fields. If you are connecting to the Easysoft demo data source, leave the options set to their default values.

11.  Click Finish.

12.  In ODBC Administrator, click Apply to save your new data source.

Testing Data Sources

iodbctest is a command line ODBC application that is supplied with iODBC. Use it to test your OOB data sources. If a connection can successfully be made, you can query the remote database by executing SQL statements in iodbctest.

To test an OOB Client data source

1.  Open Terminal in the /Applications/Utilities folder.

2.  At the shell prompt, type:

 iodbctest

3.  Type DSN=name

 where name is the name of an OOB data source that you want to test.

 If the connection succeeds, iodbctest prompts you to type some SQL.

 If the connection fails, iodbctest displays an error to help you identify what the problem is. For help on troubleshooting the problem, see FAQ.txt in the /usr/local/easysoft/oob/doc directory.

4.   To exit iodbctest, type quit.

Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-ODBC Bridge in a table showing:

Attributes which are text fields are displayed as "value".

Attributes which are binary fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

DSN

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

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

Description

A single line of descriptive text which may be retrieved by certain applications to describe the data source..

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

Servers

The name or IP address of the remote host on which the OOB Server is running.

The port on this machine where the OOB Server (or an agent on its behalf such as inetd, for more information see The mechanics of inetd and the server) is listening for incoming connections. The default port is 8888. Specify this port unless you know that the OOB Server is listening on another port. Separate the host name and port with a colon (:).

If the remote System DSN is available on more than one OOB Server, you can define a primary OOB Server for the DSN and additional fallback OOB Servers. For more information about defining multiple OOB Servers, see Client support for Fallback OOB Servers..

Interface Value
DSN Dialogue Box Servers
odbc.ini file (Unix) ServerPort=server:port [, server:port...]
Connect String SERVERPORT=server:port

Username

The name of a user on the machine on which the OOB Server is running (if required). The OOB Server changes to this user when an incoming connection is made.

On Windows, you may need to include the domain name in this attribute. If so, use the format domain/user name. For example: admin/John Smith

If you have defined multiple OOB Servers for this data source, the user name and password must be valid for each OOB Server that you specify

Interface Value
DSN Dialogue Box Username
odbc.ini file (Unix) LogonUser=value
Connect String LOGONUSER=value
.

Password

The password for the user specified by Username.

Interface Value
DSN Dialogue Box Password
odbc.ini file (Unix) LogonAuth=value
Connect String LOGONAUTH=value

TargetDSN

The System DSN on the remote machine.

Interface Value
DSN Dialogue Box TargetDSN
odbc.ini file (Unix) TargetDSN=value
Connect String DSN=value

TargetUser

The user name that will be supplied to the database to authenticate the connection. This may not be required by your database.

Interface Value
DSN Dialogue Box TargetUser
odbc.ini file (Unix) TargetUser=value
Connect String UID=value

TargetAuth

The password for the user specified by TargetUser.

Interface Value
DSN Dialogue Box TargetAuth
odbc.ini file (Unix) TargetAuth=value
Connect String PWD=value

BlockFetchSize

If you set BlockFetchSize to a value greater than 0, the Easysoft ODBC-ODBC Bridge determines whether to perform an optimization which retrieves multiple rows of data instead of one row at a time. The value you specify with BlockFetchSize is the number of rows to retrieve in one go. The maximum value is 100.

This optimization is not performed if the application itself binds columns in the result set. If your ODBC application uses cursors or positioned updates/deletes, you should not set this to greater than 1. Refer to question "I only want to retrieve data from the server, are there any tricks to speed it up in read-only mode" in the Easysoft ODBC-ODBC Bridge KB for more information about this value.

The default value 0 means that block fetches should not be done by the OOB client.

Interface Value
DSN Dialogue Box BlockFetchSize
odbc.ini file (Unix) BlockFetchSize=value
Connect String BLOCKFETCHSIZE=value

MetaDataBlockFetch

When ON (set to 1 or checked), the MetaDataBlockFetch option enables blockfetching for meta data without affecting other result sets.

This will increase the speed of retrieving metadata, such as lists of tables or columns in a data source. This option is enabled by default, but needs to be disabled for a few ODBC drivers that do not support it (see "Why do I not get a list of all the tables and columns in a database?" the Easysoft ODBC-ODBC Bridge KB for a list of these drivers).

Interface Value
DSN Dialogue Box MetaDataBlockFetch
odbc.ini file (Unix) MetaDataBlockFetch=0|1
Connect String METADATABLOCKFETCH=0|1

Override UID/PWD

When ON (set to 1 or checked), the Override UID/PWD option causes the Easysoft ODBC-ODBC Bridge to ignore the UID and PWD values passed from the application in the connection string and use the TargetUser and TargetAuth values for UID/PWD.

This option may need to be selected for some software (e.g. Crystal Info which passes an empty PWD field and expects the driver to issue a prompt dialog box), but this is not always possible when the driver is on a remote machine.

The default for Override UID/PWD is off (0).

Interface Value
DSN Dialogue Box Override UID/PWD
odbc.ini file (Unix) UseOOBDBAuth=0|1
Connect String USEOOBDBAUTH=0|1

Pass all SQLGetInfo requests on

When ON (set to 1 or checked), SQLGetInfo requests for the ODBC driver name, version and ODBC version are sent to the remote ODBC driver instead of returning the OOB Client name, version and ODBC version. This may be useful in situations when you want to know the remote ODBC driver so the application can take alternative actions.

The default for Pass all SQLGetInfo requests on is off (0)

Interface Value
DSN Dialogue Box Pass all SQLGetInfo requests on
odbc.ini file (Unix) GetInfoPassThru=0|1
Connect String GETINFOPASSTHRU=0|1
.

Disguise wide characters

When ON (set to 1 or checked), column types described by the ODBC driver as SQL_Wxyz are disguised for applications that do not understand wide characters. For example, some versions of StarOffice need this enabled when connecting to Microsoft SQLServer.

The default for Disguise wide characters is off (0).

Interface Value
DSN Dialogue Box Disguise wide characters
odbc.ini file (Unix) DisguiseWide=0|1
Connect String DISGUISEWIDE=0|1

Map SQLExecDirect

When ON (set to 1 or checked), the Easysoft ODBC-ODBC Bridge maps calls to SQLExecDirect to SQLPrepare/SQLExecute for ODBC 2.0 applications.

Between ODBC 2.0 and 3.0 a change in the allowable state transitions occurred. In ODBC 2.0 an application could call SQLDescribeParam even in the executed state (i.e. after the call to SQLPrepare and after the call to SQLExecute). In ODBC 3.0, this is no longer possible. The Easysoft ODBC-ODBC Bridge works around this limitation for ODBC 2.0 applications by mapping SQLExecDirect to SQLPrepare/SQLExecute and caching the parameter descriptions in between).

The Easysoft ODBC-ODBC Bridge has always performed this mapping for ODBC 2.0 applications but Map SQLExecDirect allows you to turn it off.

The default for Map SQLExecDirect is on (1).

Interface Value
DSN Dialogue Box Map SQLExecDirect
odbc.ini file (Unix) MapExecDirect=0|1
Connect String MapExecDirect=0|1

Override Length in SQLGetxxx

When ON (set to 1 or checked), the OOB Client overrides the BufferLength argument value in SQLGetInfo, SQLGetConnectAttr, SQLGetStmtAttr and SQLGetDescField requests for integer information types. The OOB Client replaces the BufferLength value supplied by the application with the size of either an SQLINTEGER or an SQLSMALLINT.

The ODBC specification says that ODBC drivers should ignore the BufferLength argument supplied by the application for integer information types. However, some ODBC drivers incorrectly expect to receive a buffer length for integer information types from the application. If an application passes a buffer length smaller than the size of an SQLINTEGER or an SQLSMALLINT, these ODBC drivers may return SQL_SUCCESS_WITH_INFO with SQLSTATE 01004 (Data truncated), instead of the integer attribute value. To work around this problem, enable Override Length in SQLGetxxx .

The default value OFF (0) means that the Easysoft ODBC-ODBC Bridge passes the BufferLength value to the remote ODBC driver unchanged.



Interface Value
DSN Dialogue Box Override Length in SQLGetxxx
odbc.ini file (Unix) OverrideLength=0|1
Connect String OVERRIDELENGTH=0|1

Connection Attempts

The number of times to attempt a connection before failing. Each time a connection attempt fails the OOB client waits (0.1 * connect attempt) seconds before trying again.

If you're using multiple server definitions in a single DSN you may find lowering this setting from the default of 5 an advantage as it reduces the time from the client failing to connect to the first server and starting a connection attempt to the second and subsequent servers.



Interface Value
DSN Dialogue Box Connection Attempts
odbc.ini file (Unix) ConnectAttempts=value
Connect String CONNECTATTEMPTS=value

Block Statement Attributes

A comma separated list of statement attributes that the OOB Client will block in SQLSetStmtAttr calls. Any statement attribute you specify with Block Statement Attributes will not be passed to the remote ODBC driver. Instead, the OOB Client blocks them and returns SQL_SUCCESS.

To block a statement attribute, specify the ODBC assigned statement attribute value. Look up these values in the ODBC specification and the SQL header files. For example, the values to use for the SQL_ATTR_MAX_ROWS and SQL_ATTR_METADATA_ID attributes are 1 and 10014. To block both these attributes, specify the following Block Statement Attributes value:

1,10014

Usually, you should not block any statement attributes as this may prevent your application from working correctly. The Block Statement Attributes setting was introduced to work around a problem with the Allbase ODBC driver which was setting SQL_ATTR_MAX_ROWS on all statements when it was set on one particular statement.

By default, the OOB Client does not block statement attributes.



Interface Value
DSN Dialogue Box Block Statement Attributes
odbc.ini file (Unix) IgnoreStmtAttrs=statement_attribute [, statement_attribute...]
Connect String IGNORESTMTATTRS =statement_attribute [, statement_attribute...]

Encrypt Communication

Whether to use encryption to protect data passed between the OOB client and server.

When set to No Encryption (0), the channel between OOB client and server is not encrypted.

When set to Encrypt If Available (1), the channel between OOB client and server is encrypted if the OOB server is capable of this (i.e. is a version that supports encryption and has been configured with a valid key and certificate file.) If the OOB server is not capable of encryption, the connection between OOB client and server will still succeed, but the channel between client and server will be unencrypted.

When set to Encrypt Required (2), the channel between OOB client and server is encrypted if the OOB server is capable of this. Otherwise, the connection will fail. The OOB client will not attempt to validate the certificate used by the OOB server.

When set to Encrypt Required + Validate (3), the channel between OOB client and server is encrypted if the OOB server is capable of this. Otherwise, the connection will fail. In addition, you need to specify the certificate used by the OOB server with the Certificate File attribute. If the OOB client is unable to verify the certificate, the connection will fail.

Note that the Test button on the Windows OOB client data source configuration dialog box cannot be used to check whether encryption has been successfully set up, you need to use to connect to the OOB client data source in your application to do this.



Interface Value
DSN Dialogue Box Encrypt Communication
odbc.ini file (Unix) Encrypt= 0 | 1 | 2 | 3
Connect String ENCRYPT = 0 | 1 | 2 | 3

Certificate File

The path to a file containing the certificate used by the OOB server to encrypt the connection between it and the OOB client. For example, C:\Cert.pem. Use this attribute if Encrypt Communication is set to Encrypt Required + Validate (3).



Interface Value
DSN Dialogue Box Certificate File
odbc.ini file (Unix) CertFile=path
Connect String CERTFILE=path

Unquote_Catalog_Fns

When ON (set to 1 or checked), Unquote_Catalog_Fns removes the double quotes from around names in the calls to catalog functions such as SQLColumns. This is a workaround for a problem in Applixware which can incorrectly quote arguments to the catalog functions.

The default for Unquote_Catalog_Fns is off (0).

Interface Value
DSN Dialogue Box Not available
odbc.ini file (Unix) Unquote_Catalog_Fns=0|1
Connect String UNQUOTE_CATALOG_FNS=0|1

MetaData_ID_Identifier

When ON (set to 1), MetaData_ID_Identfier causes the OOB Client to call SQLSetStmtAttr with the SQL_ATTR_METADATA_ID attribute to set it to SQL_TRUE. This causes ODBC 3.0 drivers to treat strings in metadata functions as literals. This attribute is useful if you have an application you cannot change which assumes all strings in metadata calls are treated as literal but are going to an ODBC driver containing tables etc with special characters in them (e.g. _). Applixware's AXData sometimes needs this attribute defining.

The default for MetaData_ID_Identifier is OFF (0)

Interface Value
DSN Dialogue Box Not available
odbc.ini file (Unix) MetaData_ID_Identifier=0|1
Connect String METADATA_ID_IDENTIFIER=0|1

DecAsNumeric

When ON (set to 1), DecAsNumeric causes the OOB Client to map an SQL_DECIMAL type in a SQLBindParameter call to an SQL_NUMERIC type. The DecAsNumeric attribute was introduced to work around a problem with Oracle Heterogeneous Services and Microsoft Access. When Oracle attempted to bind a type as a SQL_DECIMAL, the Microsoft Access ODBC driver returned the error "Optional feature not implemented".

The default for DecAsNumeric is OFF (0)

Interface Value
DSN Dialogue Box Not available
odbc.ini file (Unix) DecAsNumeric=0|1
Connect String DECASNUMERIC=0|1

Client support for Fallback OOB Servers

By default, the OOB Client is passed a data source name (DSN) which defines a single remote server with which to connect.

If the client fails to connect to the specified server then it tries 4 more times by default, pausing (0.1 * connection attempts) seconds between each attempt (the number of additional attempts can be configured with the parameter ConnectAttempts).

Each client DSN can also define multiple servers and ports. If the client fails to connect to the first server in the list it retries with each server in the list until either a connection is made or the list is exhausted.

You specify the servers on which the remote System DSN is available when configuring the client DSN. On Windows, Mac OS X and some Unix platforms, you configure client DSNs in the Easysoft ODBC-ODBC Bridge DSN dialog box. For more information about configuring client DSNs, see pages Windows client setup, Mac OS X client setup and Creating a DSN using the ODBC Data Source Administrator.

To add multiple servers by using the Easysoft ODBC-ODBC Bridge DSN dialog box on Windows

1.  In the Easysoft ODBC-ODBC Bridge DSN dialog box, click the Server tab.

2.  Click the ... button.

3.  In the Server box, type the host name or IP address of the primary OOB Server.

4.  In the Port box, type the port on which the primary OOB Server is listening.

 The default port is 8888. Specify this port unless you know that the OOB Server is listening on another port.

5.  Click Add.

6.  In the Server box, type the host name or IP address of a fallback OOB Server on which the remote System DSN is also defined.

7.  If the fallback OOB Server is listening on a different port to the default, in the Port box, type the port. Otherwise skip this step.

8.  Do one of the following:

If a connection can't be made to a particular server, in the Servers list, a red cross displays next to the server. The Test Log output shows the reason why the test failed in red text. For help on how to resolve the connection problem, click Help.


NB

You must enter values in the Username and Password boxes in the Server tab before testing, unless authentication is disabled in the OOB Server.


9.  Click OK.

Figure 41: Multiple OOB Servers defined in the Servers dialog box

The OOB Client tries to connect to OOB Servers in the order they are defined in the list. To reorder the OOB Servers, in the Servers list, click the OOB Server whose position you want to change. Then use the Up or Down buttons to move the OOB Server up or down the list.

When you add a new OOB Server, its added to the bottom of the list. If you want to add a server in a different position in the list, click the server above which you want the new server to appear, then click Insert.

To add multiple servers by using the Easysoft ODBC-ODBC Bridge DSN dialog box on Mac OS X

1.  In the Easysoft ODBC-ODBC Bridge DSN dialog box, click the Servers tab.

2.  In the Servers tab, in the Servers box, type primaryserver:port,fallbackserver:port.

where primaryserver is the fully qualified domain name or IP address of the primary OOB Server on which the remote System DSN is defined, port is the port on which the OOB Server is listening (the default is 8888), and fallbackserver is the name or IP address of another OOB Server on which the remote system DSN is defined.

 Separate each fallbackserver:port you specify with a comma

.

Figure 42: Multiple OOB Servers defined in the Easysoft ODBC-ODBC Bridge DSN dialog box on Mac OS X

3.  Click Finish.

On Unix, you configure client data sources by editing an odbc.ini file. (For more information about configuring client data sources on Unix, see page Creating a DSN by editing a configuration file.)

To add multiple servers by editing odbc.ini

In the section for the remote System DSN, specify the primary OOB server and each fallback OOB server with the ServerPort attribute. Use the following format for the ServerPort entry:

ServerPort = primaryserver:port,fallbackserver:port [,fallbackserver:port...]

where primaryserver is the name or IP address of the primary OOB Server on which the remote System DSN is defined, port is the port on which the OOB Server is listening (the default is 8888), and fallbackserver is the name or IP address of another OOB Server on which the remote system DSN is defined.

For example:

[data_source_name]

Driver = OOB

TargetDSN = demo

LogonUser = user name

LogonAuth = password

ServerPort = demo.easysoft.com:8888,fallback.easysoft.com:8888

Assuming an example primary OOB Server named demo.easysoft.com and a fallback OOB Server named fallback.easysoft.com, the OOB Client will attempt a connection to port 8888 on demo.easysoft.com. If this fails and after Connection Attempts additional attempts a connection has still not been made then the client will try to connect to the backup server fallback.easysoft.com.

There may be an advantage in lowering Connection Attempts from the default of 5 if multiple server definitions are being used in a single DSN, as this will reduce the time taken from the client failing to connect to the first server and starting a new connection attempt to the second and subsequent servers. For more information about the Connection Attempts setting, see Connection Attempts.

 


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