Odbc|unix| programming
Installation, configuration and programming of ODBC under Linux/unix
Ziliang (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 program ODBC under Linux/unix.
First, the principle of ODBC
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:
Second, Linux/unix under the installation of ODBC:
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.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 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:
Once installed, the required sections are the same as those listed above.
Three, Linux/unix under the 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 Drivers Database driver Setup program TXTlibodbctxt.solibodbctxtS.soNNTPlibnn.solibodbcnnS.soMiniSQLlibodbcmini.solibodbcminiS.soPostgreSQLl Ibodbcpsql.solibodbcpsqlS.soMySQL (note) Libodbcmys.sosybase/ms SQL (note) libtdss.sooracle (note) 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 idpwd user Password options:-b batch, no prompt mode-DX set separator between columns for X-W output query results to HTML format-C first row output column name- -version Output ISQL version number
Four, Linux/unix under the ODBC programming:
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_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;
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 Idintegernamechar (Sizeinteger)
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:
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):
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:
The L_next variable is the category used to specify the DSN we want to obtain:
The Sql_fetch_first set Sqldatasources () function to find the first available data source (either a user DSN or a systerm DSN) Sql_fetch_first_user set Sqldatasources ( ) function to find the first user Dsnsql_fetch_first_system set Sqldatasources () to find the first system Dsnsql_fetch_next to find the next data source, as 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 #include #include
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.
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.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
