Installation, configuration and programming of ODBC under Linux/unix __HTML5

The main content of this article is to introduce the simple principle of ODBC and how to install, configure and program ODBC under Linux/unix.


ODBC principle










ODBC is the abbreviation for open database Connect, which is the Open-source interconnect, a unified interface standard for accessing databases, presented by Microsoft in 1991, and middleware between applications and database systems. It provides database independence by using the interaction of the driver and the application on the corresponding application platform and the corresponding database to avoid the direct call to database operation in the application.





ODBC consists primarily of drivers and driver managers. The driver is a module that supports ODBC function calls, each of which corresponds to the corresponding database, and when the application is ported from one database system to another, you simply change the alias that is set by the ODBC Administrator program in the application that corresponds to the corresponding database system. The driver Manager can link to all ODBC applications and is responsible for managing the binding of the ODBC functions in the application to functions in the DLL.





ODBC uses a hierarchical approach to managing databases, and at every level of the database communication structure, ODBC introduces a common interface to address potential inconsistencies in the areas where the database product itself may be dependent, which is a good solution to the relative independence of the database system application, which is also an ODBC One of the important reasons for great success once it is launched.





From the structural point of view, ODBC is divided into two types: single beam and multiple beam type. Tan-Driver
The Tan driver is between the application and the database and provides a uniform way of accessing data, like a mediation driver. When a user performs a database operation, the application passes an ODBC function call to the ODBC Driver Manager, which is judged by the ODBC API to handle directly by it and return the result to the driver and return the result. Visible from the top, the single beam driver itself is a database engine that directly completes operations on the database, although the database may be located anywhere on the network. Multi-beam driver
A multiple-beam driver is responsible for transferring commands and data between the database engine and the client application, which itself does not perform data processing operations and is used as an interface to the network communication protocol for remote operations. The front-end application presents a request to the database processing, which is forwarded to the ODBC Driver manager, where the driver manager completes or passes to the multiple drivers in place, depending on the request, and the multi-beam driver translates the request to a specific manufacturer's database communication interface, such as Oracle Sqlnet) can be understood in a form that is delivered to the interface to deal with, the interface sends the request over the network to the data engine on the server, the server processes the result back to the database communication interface, the database interface passes the result to the multi-beam ODBC driver, and the driver passes the result to the application.





Many programmers have realized the benefits of ODBC under the Windows platform, while the database programming under the Linux/unix has to choose the specific API according to different databases to program, once the database has changed, All programs associated with these APIs must be modified. In fact, under the Linux/unix now also have their own ODBC, you can make our database programming as simple as under the Windows platform.





Let's start with an introduction to ODBC under Linux/unix:

Back to the top of the page

Installation of ODBC under Linux/unix










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

Tar zxvf unixodbc-2.2.1.tar.gz cd unixODBC-2.2.1 ./configure--prefix=/usr/local/unixodbc-2.2.1-- Includedir=/usr/include--libdir=/usr/lib-bindir=/usr/bin--sysconfdir=/etc make make install

After the successful installation, UNIXODBC required headers are installed to the/USR/INLUCDE, the compiled library files installed to the/usr/lib under the UNIXODBC related executable files installed to the/usr/bin, the configuration file into the/etc.

Method Two:
Download the RPM package for installation, here we take red Hat 7.3 For example:
Unixodbc-2.2.0-5 RPM for I386 (installation package and source pack)
(FTP://SPEAKEASY.RPMFIND.NET/LINUX/REDHAT/7.3/EN/OS/I386/REDHAT/RPMS/UNIXODBC-2.2.0-5.I386.RPM, 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)
It is OK to load the unixodbc-2.2.0-5.i386.rpm and unixodbc-devel-2.2.0-5.i386.rpm directly into the system, the command is as follows:

RPM-IVH unixodbc-2.2.0-5.i386.rpm RPM-IVH unixodbc-devel-2.2.0-5.i386.rpm

Once installed, the required sections are the same as those listed above.

Back to the top of the page

Linux/unix Configuration of ODBC

Run the Odbcconfig program (under/usr/bin), as shown in the following figure:

Figure I: odbcconfig main window

and Windows under the ODBC Settings window is not very similar. I think we all can read it.

First step: Install the ODBC Driver for the database
Drivers this column to set the database driver, click the Add button, the following image appears:

Figure II: Odbcconfig Driver Properties Window

The Name column is populated with database-driven names, description is a database-driven description, driver is used to select the database driver, and setup is used to select the database-driven installer, which is installed if you follow the installation method described above/usr/ Under Lib, here is a list of database drivers:

Database	Database driver	Database-driven Setup program
Txt	Libodbctxt.so	Libodbctxts.so
NNTP	Libnn.so	Libodbcnns.so
Minisql	Libodbcmini.so	Libodbcminis.so
PostgreSQL	Libodbcpsql.so	Libodbcpsqls.so
Mysql	Comments	Libodbcmys.so
Sybase/ms SQL	Comments	Libtdss.so
Oracle	Comments	Liboraodbcs.so

Comments:
MySQL, Sybase/ms SQL, and Oracle database drivers can be found at the following URLs:
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 pack:
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 a good driver, click "√" to save the exit.

Step two: Set up DSN
DSN is divided into three kinds of User DSN, System DSN and File DSN, and we take System DSN as an example. After selecting the System DSN column, click Add ... The button will see the following figure:

Figure three: Creating a DSN Selection database driver

The list lists the database drivers you have installed, I only installed MySQL and PostgreSQL, then select the driver you want to use, and then click OK will appear in the following figure:

Figure IV: DSN settings

I use MySQL database driver here, different database, the content of this window will be different. Name is the names of the data sources, description is a description, the server can select servers, if the computer started MySQL can choose localhost, if the port and socket have special requirements, and then according to the actual situation to modify, Database is used to select databases, the Pull-down menu does not necessarily contain all the databases, you can fill in the name of the database you have created. All configured, click "√" Save exit.

So Linux/unix ODBC data source has been set up, you can also in the status bar Odbcconfig program to see the use of ODBC, in the Advanced column to set whether to do log or start connection pool, in the About column, There is a schematic diagram of Linux/unix ODBC where you can see a list of all the developers ' names in the credits button. All information about the database driver in the Odbcconfig program is placed in the Odbcinst.ini (under/etc) file, the information about DSN is placed in Odbc.ini (under/etc) file, if you are interested, you can see for yourself.

Step three: Use the DataManager program to browse the database
After running the DataManager program, you can view the drivers, System DSN, and User DSN, and when browsing the database, you can enter the SQL statement on the right side of the SQL bar, and then click the Human button to run the SQL statement. The results will be displayed in the results column, which can be seen in the following figure:

Figure five: Using DataManager to browse the database

Step Fourth: Use the ISQL program to view the database
UNIXODBC also provides a procedure for viewing the database under the command table, which is isql, as follows:

isql DSN [UID [PWD]] [options]
DSN		data source name
UID		User ID
PWD		user password
options:-
b		batch, Pattern without prompt
-dx		set the delimiter between columns to X-
w		to output the query results as HTML format-
C		first row output column names
--version	           The version number of the output isql

Back to the top of the page

The programming of ODBC under Linux/unix










1, the use of UNIXODBC provided by the ODBC API for programming:
Before programming, let's take a look at the corresponding relationship between the common data types in the ODBC API 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), this DSN uses the username root, the password is blank, the table name is the Web, the field is as follows:



Field name Data Type
Id Integer
Name CHAR (40)
Size Integer


First: Set the ODBC environment handle and set the parameters
First we need to declare an ODBC environment handle (SQLHENV) that can be used to get information about the ODBC environment we need to call Sqlallochandle (Sql_handle_env, Sql_null_handle, &v_od_ ENV) to get this handle, v_od_env is the sqlhenv type of environment handle to assign. After assigning the handle, you give it a need to set the ODBC version used, you can call SQLSetEnvAttr (V_od_env, Sql_attr_odbc_version, (void*) SQL_OV_ODBC3, 0), Sql_attr_ Odbc_version is a variable that holds your definition of the ODBC version number, and SQL_OV_ODBC3 indicates that your program is using ODBC 3.0.





Second: Set the connection handle and set the timeout parameter
We need to declare a connection handle (SQLHDBC) that holds the database connection information, calls Sqlallochandle (Sql_handle_dbc, v_od_env, &V_OD_HDBC) to get the connection handle, V_od_ HDBC is the connection handle for the type of SQLHDBC you want to assign. Once allocated, we can call SQLSetConnectAttr (V_od_hdbc, Sql_login_timeout, (Sqlpointer *) 5, 0) to set the connection timeout parameter.





Third: Connecting to the database
Call SQLConnect (V_OD_HDBC, (sqlchar*) "Test", Sql_nts, (sqlchar*) "root", Sql_nts, (sqlchar*) "", sql_nts) connect to the database I mentioned earlier and need to set Three parameters, is the database name, username and password (because my database password is empty, so the password is also empty), the next sql_nts position should be written to the length of these parameters, if the written sql_nts is to let SQLConnect determine the length of the parameters.

Four: Assign a handle to the SQL statement and query:
You need to declare a handle to an SQL statement (SQLHSTMT) that holds the SQL statement information, call Sqlallochandle (sql_handle_stmt, V_OD_HDBC, &v_od_hstmt) to obtain this handle, v_od_ HSTMT is the sqlhstmt type of SQL statement handle that we want to assign.

Our query statement is:

SELECT name, ID from Web order by ID

After executing this query, the query results may have many rows, but only two columns per row, corresponding to the name and ID, their data type is integer and char*, and the data type identifier in ODBC is Sql_c_ulong and Sql_c_char. We need to declare this two variables to store the results of the query:

Sqlinteger v_od_id; Char v_od_buffer[200];

Then we need to use the SQLBindCol function to bind the query results to our defined variables:

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);

The v_od_err here is the variable that holds the error message number, and the type is Sqlinteger.
Next, we call SQLExecDirect to 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 get the number of columns for the result, or we can use SQLRowCount (v_od_hstmt, &v_od_rowanz) To obtain the results of the number of bars, V_od_colanz and V_od_rowanz respectively store the corresponding results, the types are Sqlsmallint and Sqlinteger.
Before we read the results, we need to invoke the SQLFetch (V_OD_HSTMT) statement, which can be used to get the first result, and it can be used to make the next one, a bit like next. Then we can get the results of each record in v_od_id and V_od_buffer.

V: About closing connections and releasing handles
Close the database connection, call SQLDisconnect (V_OD_HDBC), but you need to release the handle to the SQL statement before you close the database, and you should release the connection handle and ODBC environment handle after you close the database, 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: Error information processing in the above situation
We need to define two variables:

Long V_od_erg; Sqlinteger V_od_err;

Call results for Sqlallochandle, SQLSetEnvAttr, SQLSetConnectAttr, SQLConnect, SQLExecDirect, Sqlnumresultcols, and SQLRowCount can be used with V _od_erg to store, V_od_err can get the error message in SQLBindCol.

Seventh: Obtain DSN information for this machine
After declaring the sqlhenv handle, we can use the Sqldatasources function to obtain the DSN information for this machine. 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 the category used to specify the DSN we want to obtain:

Sql_fetch_first Set the Sqldatasources () function to find the first available data source (either a user DSN or a systerm DSN)
Sql_fetch_first_user Set the Sqldatasources () function to find the first user DSN
Sql_fetch_first_system Set the Sqldatasources () function to find the first system DSN
Sql_fetch_next The next data source is found, and the data source type is based on the previous definition

Here, we have finished the ODBC programming under the UNIX C language, the above ODBC API needs to refer to the following several header files (these files are already installed under/usr/include):

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

In addition, if you use GTK to program, because so far GTK has not joined the special processing database parts, so you can invoke the above ODBC API in GTK.

Here is a routine for your reference study:

/* 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 Environment Long V_od_erg; Result of functions Sqlhdbc v_od_hdbc; Handle connection char v_od_stat[10]; Status SQL Sqlinteger 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_ha NDLE_ENV,SQL_NULL_HANDLE,&AMP;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 timeout V_od_erg = Sqlallochandle (Sql_handle_dbc, v_od_env, &AMP;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); }

2. ODBC programming under QT
QT 3.0 provides data Table, data Browser, and Data View three database-related controls. You can set the database you want to connect to in Qt's project, choose QODBC3 in the Driver column, and you'll see the other options. The above three database controls can be used in the appropriate documentation in QT and are well used.

Reference to Microsoft's ODBC home page: http://www.microsoft.com/data/odbc/Unixodbc's home page: http://www.unixodbc.org freeodbc: http:// www.jepstone.net/FreeODBC/Easysoft's homepage: http://www.easysoft.com trolltech QT 3.0 Document home: http://doc.trolltech.com/ 3.0/.