Guide to Seamlessly Connecting ODBC with GBase 8s Database on Linux

In this article, we will explore the configuration process of ODBC on Linux. From setting up environment variables to editing ODBC configuration files and performing the final connection test, each step is crucial. Before we begin, make sure your Linux environment is properly set up. This includes configuring database environment variables and ODBC configuration files. We will use the bash shell to execute the necessary commands and scripts.

1. Configuring Database Environment

The first step in connecting to the database is configuring environment variables. We need to set GBASEDBTDIR, PATH, GBASEDBTSQLHOSTS, ODBCINI, and LD_LIBRARY_PATH. These variables will guide the system on how to locate the database and ODBC drivers. The settings include:

• GBASEDBTDIR
• PATH — should include ${GBASEDBTDIR}/bin
• GBASEDBTSQLHOSTS — (Optional, if not set, the default ${GBASEDBTDIR}/etc/sqlhosts will be used)
• ODBCINI — odbc.ini configuration file
• LD_LIBRARY_PATH — library file path

You can configure the ODBC environment variables together with the database environment variables, which are generally located in /home/gbasedbt/profile.* or /home/gbasedbt/.bash_profile.

Example:

export GBASEDBTDIR=/opt/GBASE/gbase
export PATH=$PATH:${GBASEDBTDIR}/bin 
export GBASEDBTSQLHOSTS=${GBASEDBTDIR}/etc/sqlhosts 
export ODBCINI=$GBASEDBTDIR/etc/odbc.ini
export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql

2. Configuring odbcinst.ini File

The odbcinst.ini file is the configuration file for the ODBC driver. We will guide you on how to modify this file to ensure that the system can recognize and use the ODBC driver for the GBase 8s database.

Update the Driver and Setup paths in the odbcinst.ini file to point to the respective directories under ${GBASEDBTDIR}:

Driver=/opt/GBASEDBT/lib/cli/iclit09b.so 
Setup=/opt/GBASEDBT/lib/cli/iclit09b.so

Example:

[ODBC Drivers]
GBase ODBC DRIVER=Installed

[GBase ODBC DRIVER]
Driver=/opt/GBASE/gbase/lib/cli/iclit09b.so
Setup=/opt/GBASE/gbase/lib/cli/iclit09b.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=03.51
FileUsage=0
SQLLevel=1
smProcessPerConnect=Y

3. Configuring odbc.ini File

The odbc.ini file defines the Data Source Names (DSNs) for the database. We will explain in detail how to configure this file, including the driver path, database name, login ID, password, and other key information.

[ODBC Data Sources]
Infdrv1=GBase ODBC DRIVER
Infdrv2=GBase ODBC DRIVER

[Infdrv1]
Driver=/opt/GBASE/gbase/lib/cli/iclit09b.so
Description=GBase ODBC DRIVER
Database=gbasedb
LogonID=gbasedbt
pwd=GBase8s
Servername=instance_name

[Infdrv2]
Driver=/extra/gbasedbt/lib/cli/iclis09b.so
Description=GBase ODBC DRIVER
Database=stores_demo
LogonID=odbc
pwd=odbc
Servername=ids_server2
CursorBehavior=0
CLIENT_LOCALE=en_us.8859-1
DB_LOCALE=en_us.8859-1
TRANSLATIONDLL=/extra/gbasedbt/lib/esql/igo4a304.so

[ODBC]
; Uncomment the below line for UNICODE connection
; UNICODE=UCS-4
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/opt/GBASE/gbase
TRACEDLL=idmrs09a.so

4. Configuring the SQLHOSTS File

The SQLHOSTS file specifies the address and port of the database server. Here is an example configuration to help you quickly set up and connect to the GBase database.

Example sqlhosts configuration:

gbaseserver  onsoctcp  rhel53  9105

At this point, the ODBC configuration is complete.

5. Testing the ODBC Connection

We will demonstrate how to use the catalog.c tool provided by GBASEDBT to generate the catalog executable and use it to test if the ODBC connection is successful. Apart from the catalog.c tool, we can also use the isql command to verify the ODBC connection. We will provide specific commands and expected outputs to help you confirm whether the configuration is correct.

Generate the catalog executable in the ${GBASEDBTDIR}/demo/cli directory using GBASEDBT:

make catalog 
./catalog Infdrv1  # 'Infdrv1' is the DSN configured above

If you see an output similar to the following, the ODBC connection is successful:

Using specified DSN :
Infdrv1 STEP 1 done...connected to database 
Table Name tt         Column: a         Column: b         Column: c         Column: d 
STEP 2 done...catalog information obtained from the database

You can also use the isql command to test the connection (if isql is not available, please install unixodbc):

isql Infdrv1 -v  # 'Infdrv1' is the DSN configured above

By following this guide, you should be able to complete the ODBC configuration for the GBase 8s database on Linux smoothly. This will not only improve your efficiency in handling database tasks but also ensure data security and reliability. Thank you for reading!