> # Connection

> ## Connecting with the Easysoft ODBC-ODBC Bridge

> This chapter helps you connect an ODBC-compliant client application through the bridge to a remote datasource.

> Since the client and server ends of the OOB will normally be running on different platforms, the chapter is arranged into self-contained sections for easy reference. The approach is the same no matter what platforms are involved:

> - Make your ODBC data source visible to the bridge.
> - Configure a DSN for the client end of the bridge.
> - Connect the client application to the bridge DSN.

> If you have an internet connection and want a speedy demonstration, you can skip the server sections and set up a client connection to our server `demo.easysoft.com`. To do this, use the recommended settings in the relevant client subsection.

> ### Chapter Guide

> - Setting up the OOB Server in Windows
> - Connecting to the Bridge Client in Windows
> - Setting up the OOB Server in Unix
> - Connecting to the Bridge Client in Unix

> ### Setting up the OOB Server in Windows

> The OOB server for Windows can connect to any System DSN (Data Source Name) configured on your machine, given the necessary information. If you do not already have a DSN on your machine then now is the time to create one. You should use the ODBC driver suitable for your datasource. This section presents a worked example creating a DSN for Microsoft's Northwind database, which is shipped with MS-Access.

> You can follow the example as an exercise on your own computer, providing:

> - You have Microsoft Access on your machine,
> - You have Microsoft's ODBC driver for Access (Almost all Access installations have this)
> - You have an Access database to connect to, for example, Microsoft's `northwind.mdb`.

> The first step is to open Microsoft's Data Source Administrator.

> 1. Select Start > Settings > Control Panel and open the `ODBC` icon.

> 2000 | To find the ODBC icon in Windows 2000, open `Administrative Tools` in Control Panel. The ODBC icon is called `Datasources (ODBC)`.

> The ODBC Data Source Administrator Opens.

> 2. Click the System DSN tab.
> Figure 3.1: ODBC Data Source Administrator -- System DSN Tab.

> It is important to create a System DSN, as opposed to a User DSN. User DSNs are visible only to the desktop user who created them. Since the bridge server runs as a service, User DSNs are not available to it.

> 3. Click the Add... button to add a new DSN.

> The Create New Data Source window appears, containing a list of drivers.

> Figure 3.2: The Create New Data Source Window
> 4. Select `Microsoft Access Driver` and click Finish.

> The ODBC driver for Microsoft Access presents a dialog box for configuring the Data Source. (This step differs from database to database)

> Enter your chosen name for this data source in the Data Source Name box, for example ```OOB Demo DSN`''.

> In the Description field, enter something that would help a user faced with a choice of DSNs, for example, "The Bridge's Target DSN".

> Figure 3.3: The MS Access ODBC driver configured
> 5. Click Select... to browse for the target database. Select your chosen database and click Ok. For our example, we used the database in `Microsoft Office\Office\Samples\Northwind.mdb`, but this database may not exist or may be in a different location on your system. Im this case, use any database you have to hand, preferably a small one.

> You should now have a dialog looking much like that of Figure 3.3.

> NB | Note the Data Source Name, as it will be required when you come to connect through the bridge.

> 6. Click OK. You are returned to the ODBC Data Source Administrator window. Note:
> - The window now contains a line with your new DSN in it, and
> - The SystemDSN tab should be selected. If it is not, then you must remove the DSN, select the SystemDSN tab, and return to step 3.
> 7. Click OK.

> You have set up a System-wide Data Source Name on your machine to a local database, making it visible to the OOB server.

> #### Starting the Server in Windows

> For a bridge client to connect to the local DSN, the OOB Server must be running. In Windows NT, the installation program configures the OOB Server to start automatically as an NT Service. For Windows 95, the server must be run manually.

> 95/98 | Choose Start > Programs > Easysoft > ODBC-ODBC Bridge > Server.

> #### Checking the Bridge Server Service under Windows NT

> This procedure is not normally necessary, but if you are having problems connecting with the bridge, it is a good idea to check through it.

> 1. Choose Start > Settings > Control Panel and open the `Services` icon.

> You are presented with a dialog box containing all your NT system's registered services. Find the entry for the `Easysoft ODBC-ODBC Bridge Server`

> (see Figure 3.4).
> Figure 3.4: The Bridge Server entry in the Services dialog
> 2. If the entry's `Startup` field says `Disabled`, then you should click Startup.... In the resulting dialog box, check the radiobutton marked Automatic, and click OK.
> 3. If the entry's `Status` field does not say `Started` then you should click Start to bring it on-line.
> 4. Click Close.
> 5. Close Control Panel.

> #### Conclusion

> You now have an ODBC-ODBC Bridge Server running on the Windows machine. You should now log in to the client machine and refer to the relevant section -- either Connecting to the Bridge Client in Windows below, or Connecting to the Bridge Client in Unix.

> ### Connecting to the Bridge Client in Windows

> This section explains the steps you should take on the client machine for the Windows platforms. It applies to you whether you want to

> - connect to the demo.easysoft.com demo server,
> - connect to the ODBC-ODBC Bridge server created in the Setting up the OOB Server in Windows or Setting up the OOB Server in Unix sections, or
> - connect to your own OBDC-ODBC Bridge server.

> You should be sure which of the above you are attempting before you begin this section.

> The Bridge Client Driver appears to the Client Application just as any other ODBC driver. For this reason many of the steps in this section are similar to those of the section Setting up the OOB Server in Windows above. The first step is to open Microsoft's Data Source Administrator.

> 1. Select Start > Settings > Control Panel and open the `ODBC` icon.

> 2000 | To find the ODBC icon in Windows 2000, open `Administrative Tools` in Control Panel. The ODBC icon is called `Datasources (ODBC)`.

> The ODBC Data Source Administrator Opens.

> NB | Do not click the System DSN tab for the client DSN. DSNs created for the server end must be system DSNs, but client-end DSNs should normally be user DSNs.

> 2. If the User DSN tab is not selected, select it now.
> 3. Click the Add... button to add a new DSN.

> The Create New Data Source window appears, containing a list of drivers.

> Figure 3.5: The Create New Data Source Window, with the Bridge Driver selected
> 4. Select `Easysoft ODBC-ODBC Bridge` and click Finish.

> The bridge driver pops up a configuration dialog box.

> Figure 3.6: The ODBC-ODBC Bridge Driver Configuration Dialog

> The text boxes on the dialog are split into four sections, arranged by functionality, from top to bottom:

> - How the DSN appears to the driver manager and ODBC client,
> - How to attach to the bridge server on the remote machine,
> - The DSN to attach to on the remote machine through the server, and
> - Special settings for tuning.
> 5. Enter your chosen Data Source Name for this data source, for example `demo`. Choose carefully, as you will not be able to change this after hitting OK.

> NB | If you intend to use the `demo.exe` client application then the DSN must be named `demo`.

> 6. In the Description field, enter something that would help a user faced with a choice of DSNs.

> #### Specifying the remote machine

> 7. In the Server box, enter the name of the machine on which the Bridge Server is running, or `demo.easysoft.com`.
> 8. The Transport box allows you to specifiy the network transport protocol to use. Currently, only TCP/IP is supported.
> 9. You should also use the default value for the Port box -- unless you happen to know that the server is listening at some other port.

> The next two boxes are for the logon account and password for the machine on which the server is running. The bridge server carries out all activities as that user.

> 10. Enter the user name and password of a valid logon account on the server host in the User and Password boxes, if required.
> – OR –

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

> #### Specifying the DSN on the remote machine

> 11. In TargetDSN, enter the name of the DSN you created on the remote machine.
> – OR –

> If connecting to the Easysoft `demo` server, enter `pubs`.

> 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 datasource does not need separate authentication details then leave these fields blank.

> Figure 3.7: The Driver set up for `demo.easysoft.com`

> #### Check your values

> 13. Ignore the special attributes section and click Test.

> This attempts to connect using the information on the form and send an ODBC request, displaying the results in a window.

> 14. If you see an error message, then you need to correct some of the fields in the configuration dialog. The messages are fairly self explanatory, but note the following:
> - If you get "Authentication Failure" on its own, then it is User and Password that are causing the problem. If you get "Failed to connect to remote driver" and some other text in which a login failure is noted then it is TargetUser and TargetAuth.
> - Any reference to data sources or DSNs refers to the Target DSN.
> - Messages refering to RPC mean that the client cannot find the bridge server. Check Server and Port. Transport has no effect at this stage since only TCP/IP is supported.
> - DSN and Description have no effect on the test procedure.
> – OR –

> If you see a lot of lines starting with SQL then you have connected to the remote machine. Click OK in the test box and OK in the configure dialog.

> The connection has been made.

> #### Connecting a Client Application in Windows

> By this stage, you should have a DSN on your Windows machine that connects through the bridge to a second DSN on the remote machine -- either your own server or our `demo.easysoft.com`. To demonstrate this to yourself, you can connect an ODBC application to the local DSN. Microsoft Access is used for this exercise, as most Windows users have a copy. You must be comfortable with basic use of MS Access to proceed with this section.

> Caution! |
> If you are using the bridge across the internet -- for example to contact the Easysoft `demo` server -- then Microsoft Access is not recommended. MS Access is very heavy in its use of ODBC calls, and when using the bridge each of those calls generates network traffic. Skip to the next subsection, The Demo.exe Client for a more efficient client program.

> 1. Start Microsoft Access 97 (for example) and create a blank database.
> 2. Select File > Get External Data > Link Tables to connect to the sample database.
> Figure 3.8: The Link Tables Dialog in MS Access

> Access presents the Link window, showing existing databases on your system.

> 3. From the Files of type drop-down list, choose ODBC Databases.

> Microsoft's ODBC driver manager presents the Select Data Source window.

> Figure 3.9: Machine Data Source tab in the Select Data Source dialog
> 4. Click the Machine Data Source Tab.

> Find the local DSN you created, somewhere in the list. Note that the description you gave it appears beside it.

> 5. Select your DSN and click OK.

> Access 97 interrogates the bridge, which relays the questions to your remote data source. Access presents the Link Tables window, showing a list of available datasets.

> 6. Click on a table, then click OK.

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

> 7. Double-click on the table to open and browse it.

> #### The Demo.exe Client

> In the distribution archive, we include a lightweight client for connecting to a datasource at `demo.easysoft.com`. This program can be used in the event that your own remote ODBC datasource is not yet configured. The source code is also included in the demonstration program to give developers a kickstart into creating simple ODBC client applications.

> NB | The `demo.exe` client application will not work without the local DSN configured. Refer to the section Connecting to the Bridge Client in Windows and create the demo DSN. Note that the DSN must be named `demo` for `demo.exe` to work.

> 1. Start an MS-DOS window.
> 2. Change directory to the `Examples` directory within the easysoft install directory, for example:

> `C:> cd Program Files\Easysoft\ Easysoft ODBC ODBC Bridge\Examples`

> 3. Execute the `demo.exe` program.
> Figure 3.10: The demo.exe Progam in Action

> The demonstration program is self-explanatory and extremely simple. Options 3 and 4 contact demo.easysoft.com through the bridge; option 4 actually retrieves a table from our database.

> ### Setting up the OOB Server in Unix

> At the time of writing, there is little demand for users to be able to configure ODBC data sources for the OOB Server on the Unix platform. Forthcoming Easysoft products will configure the OOB automatically where required without user intervention. If you are interested in linking the OOB server to your own ODBC drivers, please contact us and we will be glad to provide information and support.

> ### Connecting to the Bridge Client in Unix

> Since Unix does not yet have a standard driver manager, the OOB has been designed with some driver manager functionality in place. This does not mean that the OOB client will load third-party ODBC drivers on the local machine, but it does support driver manager calls such as `SQLDrivers()` and `SQLDataSources()`, and will perform some mappings if for instance an ODBC 2.0 client connects to an ODBC 3.0 datasource.

> The most important driver manager functionality the OOB provides, however, is support for storing DSN attributes in order to be able to connect given a minimal connection string. When an application connects through the client, it can pass in as little information as just a DSN. The OOB client will search for a configuration file in the following places, in order:

> - `$ODBCINI` (i.e. it looks for the file whose name is stored in the environment variable `ODBCINI`)
> - current-dir`/odbc.ini`
> - current-dir`/.odbc.ini`
> - home-dir`/odbc.ini`
> - home-dir`/.odbc.ini`
> - `/etc/odbc.ini` -- for system DSNs only

>   NB | If you are running Apache/PHP with the OOB, Easysoft recommend you use `/etc/odbc.ini`. Putting the file in the Apache home directory risks making it publicly accessible, and putting it in the `current directory' may cause problems when starting Apache in a different directory.

> #### The Format of the odbc.ini File

> Each section of the odbc.ini file starts with a DSN in brackets (a.k.a. "Square Brackets"). The following lines are attribute=value pairs, which are passed to the datasource on the remote machine, with some important exceptions:

> Name | Description
> `Description` | A single line of text to describe the datasource to users.
> `Server` | The name or IP address of the remote host where the OOB server is running.
> `Port` | The port on which the OOB server is listening.
> Transport | The network transport used across the bridge. Must be set to tcpip
> LogonUser | The name of a user on the remote machine. The OOB server changes to this user when an incoming connection is made.
> LogonAuth | The password for LogonUser
> TargetDSN | The DSN on the remote machine
> TargetUser | If present, this is passed to the database engine as the `UID` attribute. This is only required if you have user access control on your database.
> TargetAuth | The password for TargetUser
> BlockFetchSize | A value of 0 means `do not perform block fetches'. Values from 1 to 100 specify the number of rows that should be fetched at a time. You should read FAQ section on block fetch mode before using this.
> Unquote_Catalog_Fns | If present, fixes a bug in Applixware. See the FAQ
> MetaData_ID_Identifier | If present, fixes a bug with some older applications which accidentally use wildcards in catalog functions.

> Note that the Attribute names are not case-sensitive. There are four classes of attribute here:

> - `Description` is effectively a comment, though it may be displayed to users by a GUI-enabled driver manager should you have one installed.
> - `Server`, `Port`, `Transport`, `LogonUser` and `LogonAuth` are concerned with finding the remote machine and connecting to the OOB server. Imagine a telnet session:

> `$ telnet server port`

> `login logonuser`

> `password: logonauth`

> - `TargetDSN`, `TargetUser` and `TargetAuth` are renamed `DSN`, `UID` and `PWD` respectively, and repeated by the server to its driver or driver manager. Imagine a database session once you are logged in to the remote machine.

> `$ isql targetdsn targetuser targetauth`

> - BlockFetchSize, Unquote_Catalog_Fns and MetaData_ID_Identifier are all flags to change the bridge's behaviour according to the specific application.

>   NB | You must provide the bridge with `Server`, `Port`, `Transport`, `LogonUser`, `LogonAuth` and `TargetDSN`. This is enough information to allow the server end to present the DSN to its driver or driver manager. Don't forget, any attributes not specific to the bridge are passed through to the remote data source, so you can effectively set up the remote datasource from the local machine.

> #### Example odbc.ini file

> As an example, let us say you are on a linux box called `linus.mydomain`. You have MS SQLServer and the OOB server running on a remote Windows NT machine called `ntbox.mydomain`. Your Windows NT user is `myname` and your password is `mypassword`.

> Imagine you have set up a datasource on `ntbox` (refer to Setting up the OOB Server in Windows) called `myNTdsn` which requires database authentication `dbuser` and `dbpassword`. You want to access data in MS SQLServer on `ntbox` from `linus` using your Perl program or some PHP.

> Your `odbc.ini` file would contain a section like this:

> ``[localdsn]``

> ``Server=ntbox.mydomain.com``

> ``Port=8888``

> ``Transport=tcpip``

> ``LogonUser=myname``

> ``LogonAuth=mypassword``

> ``TargetDSN=myNTdsn``

> `TargetUser=dbuser`

> `TargetAuth=dbpassword`

> When your application connects through the OOB Client, it needs to pass in the DSN `localdsn`. The OOB client uses this DSN to access the correct section in the `odbc.ini` file.

> #### The Demo Client

> In the distribution archive, we include a lightweight client for connecting to a datasource at `demo.easysoft.com`. This program can be used in the event that your own remote ODBC datasource is not yet configured. The source code is in the `examples` subdirectory. To build it you need to find a suitable makefile in the `examples` directory:

> `$ cd InstallPath/easysoft/oob/examples`

> `$ ls Make*`

> `Makefile.linux`

> You must then edit the chosen makefile to remove the comment from the second line, and make sure that the path pointed to is the correct InstallPath, for instance, `#INSTALLPATH=/usr/local` might become `INSTALLPATH=/opt/` -- note that the `#` is deleted whether or not you change the directory.

> You can then `make` the examples:

> `$ make -f Makefile.linux`

> `...`

> ...and run the demo program:

> `$ ./demo`

> The demonstration program is self-explanatory and extremely simple. Options 3 and 4 contact demo.easysoft.com through the bridge. Option 4 actually retrieves a table from our database.

> [Top] | [Contents] | [Previous] | [Next] | [Index]

| Easysoft - The Home of Data Access |