Install, configure, and program ODBC in Linux/Unix

This article describes the simple principle of ODBC and how to install, configure, and program ODBC in Linux/Unix.

ODBC principles

 

ODBC is the abbreviation of open database connect, which is a unified interface standard proposed by Microsoft in 1991 for accessing databases. It is the middleware between applications and database systems. It performs database operations by interacting with the driver and application of the required database on the corresponding application platform, avoiding the direct call of database-related operations in the application, this provides database independence.

ODBC mainly consists of the driver and driver manager. A driver is a module used to support ODBC function calls. Each driver corresponds to the corresponding database. When an application is transplanted from one database system to another, you only need to change the alias of the corresponding database system set by the ODBC Administrator in the application. The driver manager can be linked to all ODBC applications and is responsible for managing the binding of ODBC functions to functions in the DLL in the application.

ODBC uses hierarchical methods to manage databases. At each layer of the database communication structure, it may depend on the characteristics of database products, ODBC introduces a public interface to solve potential inconsistency, thus solving the relative independence of database system applications, this is also one of the important reasons for the great success of ODBC since its launch.

In terms of structure, ODBC is divided into single-bundle type and multi-bundle type.

1. Single-bundle driver
   A single-bundle driver is between an application and a database, providing a unified data access method like an intermediary driver. When a user performs database operations, the application passes an ODBC function call to the ODBC driver manager, the odbc api determines whether the call is directly processed and whether the result is returned or sent to the driver for execution and the result is returned. As can be seen from the above, a single-bundle driver itself is a database engine that can directly perform database operations, although the database may be located anywhere on the network.
2. Multi-bundle driver
   A multi-bundle driver transfers commands and data between the database engine and the customer application. It does not execute data processing but is used for remote operations on a network communication protocol interface. The front-end application sends a request to the database, which is forwarded to the ODBC driver Manager. The driver manager completes or passes the request to multiple bundles of drivers, the multi-bundle driver translates requests into forms that can be understood by the database communication interfaces of specific manufacturers (such as the sqlnet of Oracle) and submits them to interfaces for processing, the interface sends the request to the data engine on the server over the network. After the server completes processing, it sends the result back to the database communication interface. The database interface sends the result to multiple ODBC drivers, then, the driver sends the result to the application.

Many programmers have realized the benefits of ODBC on the Windows platform. However, when programming a database in Linux/Unix, they have to select a Unique API for programming based on different databases, once the database changes, all programs related to these APIs must be modified. In fact, in Linux/Unix, we now have our own ODBC, which can make our database programming as simple as on the Windows platform.

Next we will introduce ODBC in Linux/Unix:

Back to Top

Install ODBC in Linux/Unix

 

Method 1:
First download the latest unixodbc source package (http://www.unixodbc.org/unixODBC-2.2.1.tar.gz) put under/usr/local, and then run the following command:

tar zxvf unixODBC-2.2.1.tar.gzcd unixODBC-2.2.1 ./configure --prefix=/usr/local/unixODBC-2.2.1 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etcmakemake install

After the installation is successful, all header files required by unixodbc are installed under/usr/inlucde, And the compiled library files are installed under/usr/lib, the executable files related to unixodbc are installed in/usr/bin, and the configuration files are stored in/etc.

Method 2:
Download the RPM package for installation. Here we use red hat 7.3 as an example:
UnixODBC-2.2.0-5 rpm for i386 (installation package and source package)
(Butler ftp://ftp.rpmfind.net/linux/redhat/7.3/en/ OS /i386/SRPMS/unixODBC-2.2.0-5.src.rpm)
UnixODBC-devel-2.2.0-5 rpm for i386
Ftp://speakeasy.rpmfind.net/linux/redhat/7.3/en/ OS /i386/RedHat/RPMS/unixODBC-devel-2.2.0-5.i386.rpm)
Simply mount the unixODBC-2.2.0-5.i386.rpm and unixODBC-devel-2.2.0-5.i386.rpm to the system, the command is as follows:

rpm -ivh unixODBC-2.2.0-5.i386.rpmrpm -ivh unixODBC-devel-2.2.0-5.i386.rpm

After the installation, the required parts are located at the same location as listed above.

Back to Top

ODBC configuration in Linux/Unix

 

Run the odbcconfig Program (under/usr/bin), for example:

Figure 1: odbcconfig Main Window

Is it similar to the ODBC setting window in windows? I think everyone can understand it.

Step 1: Install the ODBC driver for the database
In the drivers column, set the driver for the database. Click the Add button and the following will appear:

Figure 2: odbcconfig driver Properties window

Enter the name of the database driver in the name column. description is the description of the Database driver, driver is used to select the database driver, and setup is used to select the database Driver Installer, if you install these programs in the/usr/lib directory according to the above installation method, the following is a list of database drivers:

Database	Database driver	Database Driver Installer
Txt	Libodbctxt. So	Libodbctxts. So
NNTP	Libnn. So	Libodbcnns. So
Minisql	Libodbcmini. So	Libodbcminis. So
PostgreSQL	Libodbcpsql. So	Libodbcpsqls. So
MySQL	(Note)	Libodbcmys. So
Sybase/MS SQL	(Note)	Libtdss. So
Oracle	(Note)	Liboraodbcs. So

Note:
MySQL, Sybase/ms SQL, and Oracle database drivers can be found:
MySQL http://www.unixodbc.org/myodbc.html
Sybase/ms SQL http://www.freetds.org/
Oracle http://www.easysoft.org/

MySQL driver MyODBC-2.50.39-4 rpm for i386 and source package:
Ftp://speakeasy.rpmfind.net/linux/redhat/7.3/en/ OS /i386/RedHat/RPMS/MyODBC-2.50.39-4.i386.rpm
Ftp://ftp.redhat.com/pub/redhat/linux/7.3/en/ OS /i386/SRPMS/MyODBC-2.50.39-4.src.rpm

After selecting the driver, click "√" to save and exit.

Step 2: Set DSN
DSN can be divided into user DSN, system DSN, and file DSN. We use system DSN as an example. Select the system DSN column and click Add... Button to see:

Figure 3: Create a DSN and select a database driver

The list lists the database drivers you have installed. I only installed MySQL and PostgreSQL here, select the driver you want to use, and click OK:

Figure 4: DSN settings

Here I am using the MySQL database driver. The content of this window varies with different databases. The name is the name of the data source, the description is the description, and the server can select the server. If the local machine starts MySQL, you can select localhost. If the port and socket have special requirements, modify them according to the actual situation, database is used to select a database. The drop-down menu does not necessarily contain all databases. You can enter the name of the database you have created here. After the configuration is complete, click "√" to save and exit.

In this way, the ODBC data source in Linux/Unix has been set. You can also view ODBC usage in the Status column of the odbcconfig program, in the advanced column, set whether to log or start the connection pool. In the about column, there is a Linux/Unix ODBC, and the list of developers' names can be seen in the credits button. All information about the database driver in the odbcconfig program is stored in odbcinst. in the INI (under/etc) file, information about DSN is stored in ODBC. if you are interested in the INI (under/etc) file, you can observe it by yourself.

Step 3: Use the datamanager program to browse the database
After running the datamanager program, you can view drivers, system DSN, and user DSN. When browsing the database, you can enter SQL statements in the SQL column on the right, click the humanoid button to run the SQL statement. The running result is displayed in the Results column. For details, see:

Figure 5: Use datamanager to browse Databases

Step 4: Use the iSQL program to view the database
Unixodbc also provides the program for viewing the database in the console, Which is iSQL. The usage is as follows:

ISQL DSN [uid [PWD] [Options] DSN data source name uid user idpwd User Password options:-B batch processing, no prompt mode-DX sets the delimiter between columns to X-W to output the query result to the HTML format-C first line output column name-version output iSQL version number

Back to Top

ODBC programming in Linux/Unix

 

1. Use the odbc api provided by unixodbc for programming:
Before programming, let's take a look at the relationship between common data types in ODBC APIs and the data types we use in C:

Type identifier	ODBC Data Type	C Data Type
SQL _c_char	Sqlchar *	Unsigned char *
SQL _c_sshort	Sqlsmallint	Short int
SQL _c_ushort	Sqlusmallint	Unsigned short int
SQL _c_slong	Sqlinteger	Long int
SQL _c_float	Sqlreal	Float
SQL _c_double	Sqldouble, sqlfloat	Double
SQL _c_binary	Sqlchar *	Unsigned char *
SQL _c_type_date	SQL _date_struct	Struct tagdate_struct {sqlsmallint year; sqlusmallint month; sqlusmallint day;} date_struct;
SQL _c_type_time	SQL _time_struct	Struct tagtime_struct {sqlusmallint hour; sqlusmallint minute; sqlusmallint second;} time_struct;

The database name we use here is test (DSN). The user name used by this DSN is root, the password is blank, and the table name is web. The fields are as follows:

Field name	Data Type
ID	Integer
Name	Char (40)
Size	Integer

1. Set ODBC environment handle and Parameters
First, we need to declare an ODBC environment handle (sqlhenv), which can be used to obtain relevant ODBC environment information. We need to call sqlallochandle (SQL _handle_env, SQL _null_handle, & v_od_env) to obtain this handle, v_od_env is the environment handle of the sqlhenv type to be allocated. After the handle is allocated, you need to set the ODBC version used for it. You can call sqlsetenvattr (v_od_env, SQL _attr_odbc_version, (void *) SQL _ov_odbc3, 0 ), SQL _attr_odbc_version is a variable that stores your defined ODBC version number. SQL _ov_odbc3 indicates that your program uses ODBC 3.0.

Second, set the connection handle and set the timeout parameter.
We need to declare a connection handle (sqlhdbc) to store database connection information. Call sqlallochandle (SQL _handle_dbc, v_od_env, & v_od_hdbc) to obtain the connection handle, v_od_hdbc is the connection handle of the sqlhdbc type to be allocated. After the allocation, we can call sqlsetconnectattr (v_od_hdbc, SQL _login_timeout, (sqlpointer *) 5, 0) to set the connection timeout parameter.

Third: connect to the database
Call sqlconnect (v_od_hdbc, (sqlchar *) "test", SQL _cnt, (sqlchar *) "root", SQL _cnt, (sqlchar *) "", SQL _nt) to connect to the database I mentioned earlier, you need to set three parameters: Database Name, user name, and password (because my Database Password is empty, the password here is also empty ), the length of these parameters should be written at the position of SQL _cnt. If the length of these parameters is written, sqlconnect is used to determine the length of the parameter.

4. Allocate and query the SQL statement handle:
You need to declare the handle (sqlhstmt) of an SQL statement to store the SQL statement information. Call sqlallochandle (SQL _handle_stmt, v_od_hdbc, & v_od_hstmt) to obtain the handle, v_od_hstmt is the SQL statement handle of the sqlhstmt type to be allocated.

Our query statement is:

SELECT name, id FROM web ORDER BY id

After executing this query statement, the query results may have many rows, but each row has only two columns, corresponding to the name and ID respectively. Their data types are integer and char *, the data type identifiers in ODBC are SQL _c_ulong and SQL _c_char. We need to declare two variables to store the query results:

SQLINTEGERV_OD_id;charV_OD_buffer[200];

Then we need to use the sqlbindcol function to bind the query result to the defined variable:

SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);

In this example, v_od_err is a variable used to store the error message number. The type is also sqlinteger.
Next, we call sqlexecdirect for query:

SQLExecDirect ( V_OD_hstmt, "SELECT dtname,iduser FROM web order by iduser", SQL_NTS );

We can use sqlnumresultcols (v_od_hstmt, & v_od_colanz) to obtain the number of columns of results, or use sqlrowcount (v_od_hstmt, & v_od_rowanz) to obtain the number of results, v_od_colanz and v_od_rowanz respectively store the corresponding results. The types are sqlsmallint and sqlinteger.
Before reading the results, we need to call the sqlfetch (v_od_hstmt) statement. This statement can be used to obtain the first result or to get the next one, which is a bit like next. Then we can get the results of each record in v_od_id and v_od_buffer.

Fifth: Close the connection and release the handle
Close the database connection and call sqldisconnect (v_od_hdbc). However, you must release the SQL statement handle before closing the database, the connection handle and ODBC environment handle should be released after the database is closed. The statement is as follows (in normal order ):

SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);SQLDisconnect(V_OD_hdbc);SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);

Sixth: handling error information in the above cases
We need to define two variables:

longV_OD_erg;SQLINTEGERV_OD_err;

Call results of sqlallochandle, sqlsetenvattr, sqlsetconnectattr, sqlconnect, sqlexecdirect, sqlnumresultcols, and sqlrowcount can be stored using v_od_erg. v_od_err can obtain error information in sqlbindcol.

7. Obtain the DSN information of the Local Machine
After declaring the sqlhenv handle, we can use the sqldatasources function to obtain the local DSN information. The procedure is as follows:

void OD_ListDSN(void){char       l_dsn[100],l_desc[100];short int  l_len1,l_len2,l_next;l_next=SQL_FETCH_FIRST;while( SQLDataSources(V_OD_Env,l_next,l_dsn, sizeof(l_dsn),&l_len1, l_desc, sizeof(l_desc), &l_len2) == SQL_SUCCESS){printf("Server=(%s) Beschreibung=(%s)/n",l_dsn,l_desc);l_next=SQL_FETCH_NEXT;}}

The l_next variable is used to specify the type of The DSN we want to obtain:

SQL _fetch_first	Set the sqldatasources () function to find the first available data source (either user DSN or systerm DSN)
SQL _fetch_first_user	Set sqldatasources () function to find the first user DSN
SQL _fetch_first_system	Set sqldatasources () function to find the first system DSN
SQL _fetch_next	Find the next data source. For the data source type

Here, we have already completed ODBC programming in the C language of Unix. The aforementioned odbc api must reference the following header files (these files have been installed under/usr/include ):

#include <sql.h>#include <sqlext.h>#include <sqltypes.h>

In addition, if you use GTK for programming, you can call the aforementioned odbc api in GTK because GTK has not yet been added to the database processing components.

Examples are attached here for your reference:

/* odbc.c    testing unixODBC*/#include <stdlib.h>#include <stdio.h>#include <odbc/sql.h>#include <odbc/sqlext.h>#include <odbc/sqltypes.h>SQLHENV V_OD_Env;// Handle ODBC environmentlong V_OD_erg;// result of functionsSQLHDBC V_OD_hdbc;// Handle connectionchar V_OD_stat[10];// Status SQLSQLINTEGER V_OD_err,V_OD_rowanz,V_OD_id;SQLSMALLINT V_OD_mlen,V_OD_colanz;char             V_OD_msg[200],V_OD_buffer[200];int main(int argc,char *argv[]){// 1. allocate Environment handle and register version V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)){printf("Error AllocHandle/n");exit(0);}V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)){printf("Error SetEnv/n");SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);exit(0);}// 2. allocate connection handle, set timeoutV_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)){printf("Error AllocHDB %d/n",V_OD_erg);SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);exit(0);}SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);// 3. Connect to the datasource "web" V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "Test", SQL_NTS,                                     (SQLCHAR*) "root", SQL_NTS,                                     (SQLCHAR*) "", SQL_NTS);if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)){printf("Error SQLConnect %d/n",V_OD_erg);SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1,               V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);printf("%s (%d)/n",V_OD_msg,V_OD_err);SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);exit(0);}printf("Connected !/n");V_OD_erg=SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &V_OD_hstmt);if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO)){printf("Fehler im AllocStatement %d/n",V_OD_erg);SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);printf("%s (%d)/n",V_OD_msg,V_OD_err);SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);exit(0);}    SQLBindCol(V_OD_hstmt,1,SQL_C_CHAR, &V_OD_buffer,150,&V_OD_err);    SQLBindCol(V_OD_hstmt,2,SQL_C_ULONG,&V_OD_id,150,&V_OD_err);    V_OD_erg=SQLExecDirect(V_OD_hstmt,"SELECT dtname,iduser FROM web order by iduser",SQL_NTS);       if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))    {       printf("Error in Select %d/n",V_OD_erg);       SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);       printf("%s (%d)/n",V_OD_msg,V_OD_err);       SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);       SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);       SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);       exit(0);    }    V_OD_erg=SQLNumResultCols(V_OD_hstmt,&V_OD_colanz);    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))    {        SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);        SQLDisconnect(V_OD_hdbc);        SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);        SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);        exit(0);    }    printf("Number of Columns %d/n",V_OD_colanz);    V_OD_erg=SQLRowCount(V_OD_hstmt,&V_OD_rowanz);    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))    {      printf("Number of RowCount %d/n",V_OD_erg);      SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);      SQLDisconnect(V_OD_hdbc);      SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);      exit(0);    }    printf("Number of Rows %d/n",V_OD_rowanz);    V_OD_erg=SQLFetch(V_OD_hstmt);      while(V_OD_erg != SQL_NO_DATA)    {     printf("Result: %d %s/n",V_OD_id,V_OD_buffer);     V_OD_erg=SQLFetch(V_OD_hstmt);      }  ;    SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);    SQLDisconnect(V_OD_hdbc);    SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);    SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);    return(0);}

Remember to add the connection option [-lodbc] when compiling the Connection Program.

2. ODBC programming in QT
Qt 3.0 provides three database-related controls: data table, data browser, and data view. You can set the database you want to connect to in the Project of QT, and select qodbc3 in the driver column. You will understand other options at a glance. For the usage of the preceding three database controls, see the corresponding documents in QT.

References

1. Microsoft ODBC homepage: http://www.microsoft.com/data/odbc /;
2. Unixodbc home page: http://www.unixodbc.org /;
3. Home page of freeodbc: http://www.jepstone.net/freeodbc /;
4. Easysoft homepage: http://www.easysoft.com /;
5. Trolltech's QT 3.0 documentation homepage: http://doc.trolltech.com/3.0 /.