ODBC – Other Databases


Windows ODBC Overview. HydraStudio

Once a database has been installed that uses ODBC, a connection setup can usually be created using the Querix Connect tool within ‘HydraStudio’.

  1. In ‘HydraStudio’, at the bottom of the Project tree space, select the database that you want to setup a connection for from the dropdown database list.
  2. From the menu, choose Tools and select Database Connections.
    The Querix Connect window is displayed.
  3. To add a new connection, click the ODBC Administrator button.
    The ODBC Data Source Administrator window is displayed.
  4. In the ODBC Data Source Administrator window, choose the System DSN tab and click the Add button.
    The Create New Database Source window is displayed.
  5. From the list of database drivers displayed, highlight the one for which you want to add a new connection setup, and click the Finish button.
    The ODBC DSN Setup window is displayed for the database and engine type that you selected.
  6. In the Client region, Data Source Name field, type a name to identify this connection setup.
  7. In the Server region, Address field, type the host name or IP address.
  8. In the Transport Hint field, select the appropriate protocol options.
  9. In the Data Source Name field, click the Get DSN List button and select the appropriate DSN engine; OR click the Create button and, in the Logon dialog displayed, type the Username and Password in the appropriate fields.
  10. Click the OK button.

Unix/Linux ODBC Connections

For a Unix/Linux connection, there are three main stages needed. These are:

  1. Install the iODBC driver manager
  2. Install the ODBC driver for your chosen database
  3. Set up the DSN. The iODBC driver manager for Unix/Linux platforms can be downloaded from http://www.iodbc.org by following the “downloads, cvs” link.
    The appropriate ODBC driver for your chosen database is available on the supplier’s Web site.

Installing the iODBC Driver Manager

The iODBC Driver Manager can be downloaded as an rpm file, or for older platform versions, a tar.gz file. You will need to build the driver manager. Download the appropriate file and copy it to your /usr directory. If you have a tar.gz file follow steps 1 to 3 and then continue at step 5, for an rpm file start at step 4.

  1. With the downloaded file in the /usr directory, unzip the file with the command: gunzip libiodbc-3.51.1.tar.gz
  2. Extract the files with the command: tar xvf libiodbc-3.51.1..tar
  3. Change directory to the newly created installation directory with the command: cd iodbc-3.51.1.
  4. For an rpm file, run the command: rpm –i file_name
  5. In the new directory run the command: ./configure The system will carry out a series of configuration functions.
  6. Run the command: make
  7. Run the command: make install

Creating a DSN

The database and the DSN must have the same name, otherwise serious system error may be encountered. You will also need to find the location of the psqlodbc.so file before starting to create the DSN. This will probably be /usr/lib/psqlodbc.so.

  1. Search for the file /etc/odbc.ini. This may have been created during the iODBC installation. If it has not, you will need to create the file manually. If you have to create the /etc/odbc.ini file manually it should look something like the example shown in Step 2.
  2. Add the following details to the odbc.ini file: In the following code ‘cms’ is the name of the database application. Database_name is the name of the database type being used, such as Oracle, MySQL or PostgreSQL.
    [ODBC Data Sources]
    cms = Database_name cms
    [cms]
    Description = Database_name cms
    Driver = /usr/lib/ Database_nameodbc.so
    Trace = No
    TraceFile = /tmp/odbc.log
    Databse = cms
    UserName =
    Password =
    Port = 5432
    Protocol = 6.4
    ReadOnly = Yes
    RowVersioning = No
    ShowSystemTables = No
    SowOidColumn = No
    FakeOidIndex = No
    ConnSettings =
  3. Edit the following settings in your environment:

    HYDRA_DB_DRIVER=odbc
    SQLSERVER=cms
    ODBCINI=/etc/odbc.ini

    Each user can have their own odbc.ini file if needed. If you want to test the connection to the database, you can use the ‘odbctest' tool, provided by iODBC, as well as the ‘testconn' application in 'HydraStudio'.