Unix Database Universal Interface Function (http:www.fanqiang.com)

Unix under the common interface function article from the database: http: //www2.ccw.com.cn Author: Ding Changrong (2002-04-17 06:02:00)

In UNIX environments, commonly used database development methods have ODBC, call layer interface (CLI), SQL embedd C language or other specific development tools. Since different development methods are provided to the C language access database, the writing method is different, so the database application so-developed is difficult to generally in different development environments, resulting in poor transplantability, such as using SQL embedd C language development When the program is changed to the call layer interface (CLI) to write, all C programs in the program have to be modified. To this end, the author designed a set of C language universal interface functions in the UNIX environment, using this universal interface function, replacing the program in a variety of development environments of the database system, improve the code versatility. Increased development efficiency. This article describes the design idea, function prototype design and application examples of this universal interface function. Design ideas use C language modular programming methods to focus on a function (mainly standard SQL statement) related to database access in a module (DBADM), and the function "package" of all related interfaces is in this module. The module serves as a "middleware" accessed, providing support for universal interface functions. Thus, when accessing the database in other application modules, only these universal interface functions can only be called, avoiding the problem of inconsistency when the database access function provided by the database system is avoided, and the C language source program part and the data of the database application are actually The access section (DBADM) is separated. At the same time, the cache switching technology is applied to apply a memory (global variable) for each open table, and the recording buffer for data exchange is performed as the SQL statement of the database. When using the SQL statement to read the data in the database, store the data first in the recording buffer (its value is converted to a character type). Similarly, when the data is written to the database, it is also possible to update in the recording buffer, and then use the record cache area to assemble the INSERT, UPDATE, etc., thereby avoiding the direct use of the SQL statement. In this way, we can write the corresponding database interface module (DBADM) for the common development environment of the database system. If you want to change the development environment, you only need to change the database interface module of the Makefile file, other modules can be eliminated. The expansion, modification, and improvement of function functions also only need to change the database interface module. In this way, not only the versatility of the program, not only from the repetition of different interfaces, and put energy in the design and development of function applications, thereby improving the development efficiency. Function Design Database System Data Access is mainly made using SQL query language, the relevant data access statement mainly has SELECT (query), INSERT (insert), Update, delete, Rollback, commit (Submit), etc. By comparing the commonality and characteristics of several common development environments, we can define them into a "universal" database access function for additional programs.

Below is the main code of the two interface functions defined by the author (Other interface function code omitted): / * dbadm.ec informix-esql * / #include #include #include #Include $ include sqlca; $ include sqlda; $ include sqltypes; / * The biggest open table * / #define mxfiles 20 / * Currently open table scribes, the length of the table record * / INT IFRX, ISQLRECORDLENGTH [MXFILES]; / * Table Recordable storage buffer, opened table * / char szsqlrecordbuffler [mxfiles], szsqlfhl [mxfiles]; / * Have opened the table all field type area * / char szsqlfield_lx [mxfiles]; / * Table Structure * / Struct Sqlda udesc [mxfiles]; / * SQL statement assembly area * / $ char szsqlbuf [1024]; / ​​* Open table * / dbtableopen (char * szselectsql) {Int IIDX, ipost; / * field variable Pointer * / struct sqlvar_struct ufieldvar; / * There is no Select in parameter, select SELECT statement * / if (SUBSTR (SZSELECTSQL, "SELECT") <0) Sprintf (SZSQLBUF, "SELECT * FROM% S", SZSELECTSQL); Else Strcpy (Szsqlbuf, SzselectSql); / * Find open handles * / for (IFRX = 0; IFRX SQLD 1)) == NULL) EXIT (-1); / *

Processing all fields * / for (ufieldvar = udesc [iFRX] -> SQLVAR, IIDX = 0; IIDX sqld; ufieldvar , IIDX ) {switch (ufieldvar-> sqltype) {Case Sqlmoney: / * Amount type * / case Sqldecimal: / * Decimal number * / / * calculates the offset of the field * / ipostion = rypalign (iPOSTION, CDECIMALTYPE); / * Type Definition * / ufieldvar-> SQLTYPE = CDECIMALTYPE; / * Location After moving a decimal number of bytes * / ipstion = RTYPMSIZE (CDECIMALTYPE); / * field length * / ufieldvar-> SQLLEN = RTYPMSIZE (CDECIMALTYPE); Break; default: / * Calculate this field location * / ipost = RTYPALIGN (IPOSTION, CCHARTYPE); / * Type definition as character type * / ufieldvar-> SQLTYPE = CCHARTYPE; ufieldvar-> sqllen = RTYPWIDTH (ufieldvar-> sqltype, ufieldvar-> sqllen) 3; / * Location Remove * / ipost = ufieldvar-> sqllen;}}}}}; / * Record of the memory area recorded * / if ((SzsqlRecordBuffer [IFRX] = (char *) malloc (ipostion)) == null) EXIT (-1); isqlRecordlength [IFRX ] = ipost; / * Get positioned in the record storage buffer in the table * / for (ufieldvar = uDesc [if) -> sqlvar, IIDX = 0; IIDX sqld; IIDX , ufieldvar ) {/ * Calculate the field offset * / ipost = rypalign (iPOSTION, ufieldvar-> sqltype); / * field in the recording buffer location * / ufieldvar-> sqldata = szsqlrecordbu FFER [IFRX] ipost; ipost = ufieldvar-> SQLLEN;} / * Open the cursor function, the specific implementation of the slight * / if (OpenCuser (IFRX) <0) exit (-1); return (IFRX);} / * Insert function*

/ DbInsertRecord (int iTableNo) {struct sqlvar_struct * uFeildVar; int iIdx; char szTableName [60]; / * obtained from the szSQLfhl [iTableNo] in table * / createtableName (szSQLfhl [iTableNo], szTableName); / * assembled insert statement * / sprintf (Szsqlbuf, "Insert INTO% S VALUES (", SZTABLENAME); for (IIDX = 0; IIDX SQLD; UFIELDVAR , IIDX ) {strcat (szsqlbuf, "?"); if (IIDX ! = udesc [iTableno] -> SQLD-1) STRCAT (SZSQLBUF, ",");} strcat (szsqlbuf, ")"); / * Predefined * / $ prepare InsertID from $ szsqlbuf; / * Failed Exit * / IF (SQLCA. Sqlcode! = 0) RETURN (-1); / * Execute INSERT statement * / $ execute insert; iin]]; * Failed exit * / if (Sqlca.sqlcode! = 0) / return (- 1); RETURN (0);} Application Example When using a universal interface function, first call dbsopen (), connect the database, and use the database interface function after successfulness, and perform database data. When you exit the system, use the dbclosebase () function to turn off the database. Test.c is a simple C language application written by the author. It features the LSZ [Decongs] of the database Qsxt [liquidation system] to display the LB [Category] 02 Tchh [proposing number], TRHH [Remission] Number] Content and change 02 to 05.