Installation, configuration and programming of ODBC under Linux / UNIX
content:
ODBC Principle Linux / Unix ODBC installation Linux / UNIX ODBC Configuration Linux / Unix ODBC programming Reference Reference About the author
In the Linux area:
Tutorial Tools & Product Codes & Component Articles
Gazing (Cavendish@eyou.com) July 2002
The main content of this article is to introduce the simple principle of ODBC, and how to install, configure and programming in Linux / UNIX.
1. ODBC principle ODBC is an Open Database Connect, an open database interconnect, which is a unified interface standard for accessing the database in 1991, which is an intermediate part between the application and database system. It implements the operation of the database by using the driver corresponding to the application platform and the required database to interact with the application, avoiding the operation of directly calling with the database directly in the application, providing the independence of the database. ODBC is mainly composed of drivers and driver managers. The driver is a module for supporting an ODBC function call. Each driver corresponds to the corresponding database. When the application is transplanted from a database system to the other, simply change the ODBC management program setting. Unless otherwise corresponding to the corresponding database system. The driver manager can be linked to all ODBC applications, which is responsible for managing the binding of the ODBC functions in the application and the DLL. ODBC uses a hierarchical approach to manage the database, in each layer of the database communication structure, the ODBC introduces a public interface to solve the potential inconsistency, which is well solved based on the database. The relative independence of the system application is one of the important reasons for ODBC to achieve great success. From the structure, ODBC is divided into single-type and multi-type.
Single-type driver Single-type drivers are between applications and databases, as a mediation driver provides a unified data access method. When the user performs a database operation, the application passes an ODBC function call to the ODBC driver manager, and it is determined by the ODBC API to process it directly and the result is returned or the driver is executed and the result is returned. As can be seen, the homogeneous driver itself is a database engine, which can be done directly to the database, although the database may be located anywhere in the network. The multi-type driver multi-type driver is responsible for transmitting commands and data between database engines and client applications, which do not perform data processing operations and an interface of network communication protocols for remote operations. The front-end application proposes a request for database processing. The request forwards to the ODBC driver manager, the driver manager is done or transmits the multi-beam driver in accordance with the request, and the multi-beam driver will be translated into specific The manufacturer's database communication interface (such as Oracle's SQLNET) can understand and handle the interface to process, the interface transmits the request via the network to the server, then send the result back to the database communication interface, database The interface passes the result to the multi-style ODBC driver, and then transmits the result to the application. Many programmers have experienced the benefits of ODBC under the Windows platform, but when database programming under Linux / UNIX, you have to select a unique API to program, once the database changes, all of these API-related procedures must be modified. In fact, in Linux / Unix, there is also its own ODBC, allowing our database programming as simple as in the Windows platform. Below we start introducing ODBC: Second, Linux / UNIX: Method 1: First download the latest UnixodBC source package (http://www.unixodbc.org/unixodbc-2.2.1.tar. GZ) Under / USR / LOCAL, 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 = / ETC
Make
Make Install
After the installation is successful, the header files required by UNIXODBC are installed under / usr / inLuCDE, and the compiled library file is installed under / usr / lib, and the executable related to UNIXODBC is installed under / usr / bin, the configuration file Put it under / etc. Method 2: Download the RPM package for installation, we use Red Hat 7.3 as an example: UNIXODBC-2.2.0-5 RPM for i386 (installation package and source package) (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://speakesy.rpmfind.net/linux/redhat/7.3/EN/OS/i386/redhat/rpms/unixodbc -Devel-2.2.0-5.i386.rpm) Directly load UNIXODBC-2.2.0-5.i386.0-5.i386.rpm, the command is as follows: RPM-IVH UNIXODBC-2.2.0-5.i386.rpmrpm -ivh unixodbc-devel-2.2.0-5.i386.rpm
After installation, the desired portions are the same as those listed above. Third, Linux / UNIX ODBC configuration: run the Odbcconfig program (under / usr / bin), as shown below:
Figure 1: ODBCCONFIG main window
Is it very similar to the ODBC setting window under Windows? I want everyone to understand. Step 1: Install the database of ODBC drivers Drivers in this column to set up the database, click the Add button, will appear below:
Figure 2: ODBCCONFIG DRIVER Properties window
The name column fills in the database driver, Description is a database-driven description. DRIVER is used to select the database driver, and the setup is used to select the database driver installer. If you are installed according to the above installation method, these programs All are put under / usr / lib, below is a list of database drivers:
Database database database driver driver installer TXTlibodbctxt.solibodbctxtS.soNNTPlibnn.solibodbcnnS.soMiniSQLlibodbcmini.solibodbcminiS.soPostgreSQLlibodbcpsql.solibodbcpsqlS.soMySQL (Note) libodbcmyS.soSybase / MS SQL (Note) libtdsS.soOracle (Note) liboraodbcS.so Note: MySQL, Sybase / MS SQL and Oracle's database driver can be found at the following URL: mysql http://www.unixodbc.org/myodbc.htmlsybase/ms sql http: //www.freetds.orgoracle 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.rpmftp: //ftp.redhat.com/pub/redhat/linux/7.3/EN/OS/i386/srpms/myodbc-2.50.39-4.Src.rpm After selecting the driver, click "√ "Save exit. Step 2: Setting DSNDSN three kinds in User DSN, System DSN, and File DSN, we take SYSTEM DSN as an example. After selecting the System DSN column, click the Add ... button to see the figure below: Figure 3: Create a DSN Select Database Drive
The list of database drivers you already have installed, I only have mysql and postgreSQL here, then select the driver you want to use, then click OK.
Figure 4: DSN setting
I am using MySQL database drive, different databases, and this window will be different. Name is the name of the data source, Description is a description, Server can select the server. If the unit starts Mysql, you can select LocalHost. If Port and Socket have special requirements, modify according to the actual situation, Database is used to select the database, The drop-down menu does not necessarily contain all the databases, you can fill in the name you have created here. After all are configured, click "√" to save exit. The ODBC data source under Linux / UNIX is already set, you can also view the ODBC usage in the status bar of the ODBCCONFIG program, set whether to do logs or start the connection pool in the Advanced column, in the About column, there is A schematic of a Linux / UNIX ODBC, you can see a list of all developers in the Credits button. All information about the database driver in the ODBCCONFIG program is placed in ODBCINST.INI (under / etc) file, information about DSN is placed in ODBC.INI (under / ETC) file, everyone is interested, you can Go to observe yourself. Step 3: Use the DataManager program to view the database to run the DataManager program, you can view the contents of Drivers, System DSN, and User DSNs. When browsing the database, you can enter the SQL statement in the SQL column on the right, then click the humanoid button. You can run the SQL statement, run the result, will be displayed in the Results column, you can see the following figure below: Figure 5: Use the DataManager to browse the database
Step 4: Use the ISQL program to view the database UNIXODBC to view the program under the command stage, this is ISQL, usage as follows: Isql DSN [UID]] [options]
DSN data source name
UID user ID
PWD user password
Options:
-b batch, no mode of prompt
-DX Settings the separator between the columns to X
-w Exports the query result to HTML format
-C first line output column name
--Version outputs ISQL version number
Fourth, Linux / Unix ODBC programming: 1. Use the UNIXODBC ODBC API to program: Before you program, let's take a look at the common data types in the ODBC API and the corresponding relationship between the data type used in the C language. :
ODBC data type identifier Type C data type SQL_C_CHARSQLCHAR * unsigned char * SQL_C_SSHORTSQLSMALLINTshort intSQL_C_USHORTSQLUSMALLINTunsigned short intSQL_C_SLONGSQLINTEGERlong intSQL_C_FLOATSQLREALfloatSQL_C_DOUBLESQLDOUBLE, SQLFLOATdoubleSQL_C_BINARYSQLCHAR * unsigned char * SQL_C_TYPE_DATESQL_DATE_STRUCTstruct tagDATE_STRUCT {SQLSMALLINT year; SQLUSMALLINT month; SQLUSMALLINT day;} DATE_STRUCT; SQL_C_TYPE_TIMESQL_TIME_STRUCTstruct tagTIME_STRUCT {SQLUSMALLINT hour; SQLUSMALLINT minute; SQLUSMALLINT Second;} Time_struct; We use the database name used here as Test (DSN), the username used by this DSN is root, the password is empty, the name of the table is the web, the field is as follows:>
Field name data type idintegerNameChar (40) Sizeinteger first: Set the ODBC environment handle and set parameters First we need to declare an ODBC environment handle (SQLhenv), which can be used to get the odbc environment information, we need to call SQLAlLoChandle (SQL_HANDE_ENV, SQL_NULL_HANDLE, & V_OD_ENV) to get this handle, V_OD_ENV is the environment handle of the SQLhenv type to be assigned. After allocating the handle, you need to set the ODBC version used, you can call SQLSETENVATTR (v_od_env, sql_attr_odbc_vent_version, (void *) SQL_OV_ODBC3, 0), SQL_ATTR_ODBC_VERSION is the variable that stores the ODBC version number of your defined ODBC, SQL_OV_ODBC3 description Your program is used by ODBC 3.0. Second: Set the connection handle and set the timeout parameter We need to declare a connection handle (SQLHDBC), to store the database connection information, call SQLAllocHandle (SQL_HANDBC, V_OD_ENV, & V_OD_HDBC) to get the connection handle, v_od_hdbc is the SQLHDBC type to be assigned Connect the handle. After allocation, we can call SQLSETCONNECTATTR (V_OD_HDBC, SQL_Login_Timeout, (SQLPointer *) 5, 0) to set the connection timeout parameter. Third: Connecting the database calls SQLConnect (v_od_hdbc, (sqlchar *) "TEST", SQL_NTS, (SQLCHAR *) "root", sql_nts, (sqlchar *) ", sql_nts) Connect the database mentioned earlier, need to set Three parameters, that is, the database name, username, and password (because my database password is empty, the password here is empty), the position of the SQL_NTS behind these parameters should be written, if written is SQL_NTS to make SqlConnect To determine the length of the parameters. Fourth: Assign the handle of the SQL statement and query: SQLHSTMT needs to be declared, used to store SQL statement information, call SQLAllochandle (SQL_HANDLE_STMT, V_OD_HDBC, & V_OD_HSTMT) to get this handle, v_od_hstmt is what we want to assign SQLHSTMT type SQL statement handle. Our query statement is: SELECT NAME, ID from Web Order BY ID After performing this query statement, the results of the query may have a lot of lines, but only two columns per line, respectively correspond to Name and ID, and their data types are Integer and Char. *, The data type identifier in the ODBC is SQL_C_ULONG and SQL_C_CHAR. We need to declare such two variables to store query results: SQLINTEGER V_OD_ID; char v_od_buffer [200];
Then we need to use the sqlbindcol function to bind the query results and 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 used to store the error message number, and the type is also SQLINTEGER. Next, we call the 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 in the result, it can also be used SQLRowCount ( V_od_hstmt, & v_od_rowanz to get the number of results, V_OD_COLANZ, and V_OD_ROWANZ store the corresponding result, and the types are SQLSMallint and SQLinteger. Before reading the results, we need to call the SQLFETCH (V_OD_HSTMT) statement, which can be used to obtain the first result can also be used in the next, a bit like Next. Then we can get the results of each record in V_OD_ID and V_OD_Buffer. Fifth: About closing the connection and release the handle to turn off the database, call SqlDisconnect (v_od_hdbc), but you need to release the handle of the SQL statement before shutting down the database, and the connection handle and ODBC environment handle should be released after turning off the database. The statement is as follows (according to 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: About the error message in the above case We need to define two variables: long v_od_erg;
SQLINTEGER V_OD_ERR; SQLALLOCHANDLE, SQLSETENVATTR, SQLSETCONNECTATTENVATTR, SQLSETCONNECTATTR, SQLCONNECT, SQLEXECDIRECT, SQLNUMRESULTCOLS and SQLROWCOUNT call results can be stored with v_od_erg, V_OD_ERR can get error messages in SQLBINDCOL. Seventh: Getting the DSN information of this unit we can use the SqlDataSources function to obtain the DSN information of this unit after declaring the SQLhenv handle. 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;
}
}
l_next variable is used to specify the category of the DSN we have to get:
SQL_FETCH_FIRST setting the SQLDataSources () function finds the first available data source (may be a User DSN, it may be Systerm DSN) SQL_FETCH_FIRST_USER setting the SQLDataSources () function to find the first set the SQLDataSources User DSNSQL_FETCH_FIRST_SYSTEM () function to find the first System DSNSQL_FETCH_NEXT finds the next data source, as for the data source type, according to the previous definition here, we do ODBC programming under UNIX's C language has been finished. The ODBC API needs to reference the following header files (these files have been installed. / usr / incrude under: # include
#include
Testing UNIXODBC
* /
#include
#include
#include
#include
#include
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_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 STENV / 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, & 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_HANDE_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_HANDE_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 Column% 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. Qt ODBC programming QT 3.0 provides three controls related to databases with Data Table, Data Browser, and Data View. You can set the database you want to connect in QT, you can connect the QODBC3 in the Driver column, and other options you understand. The method of using the three database controls described above can see the corresponding document in QT, which is also very useful. Reference:
