Let you know software development in advance (20): how to execute SQL statements in C language?

Abstr]

In communication software, programs often need to deal with databases. To implement functions such as getting data from the database, updating a field in the database table, and inserting or deleting a data entry, You need to construct some SQL statements in the C language program, use functions to execute these SQL statements.

This article introduces how to construct and execute SQL statements in C language programs, which provides reference for the development of related software.

【Key words]

SQL statement C language program process development

 

1. Why should I execute SQL statements in C language programs?

The reasons for executing SQL statements in C language programs are as follows:

(1) The program needs to obtain the field values of a data table in the database and parse these field values for subsequent operations.

(2) The program needs to update the field values of a data table in the database.

(3) The program needs to insert values into a data table.

(4) The program needs to delete some values from a data table.

(5) The program needs to execute a stored procedure to complete specific operations.

It can be seen that in C language programs, not only the execution of "select", "update", "insert", "delete" and other statements, but also the execution of stored procedures are required.

 

Ii. How C Language Programs deal with databases

C language programs can deal with databases in two ways: Direct Connection (synchronous) and non-direct connection (asynchronous). Their features are as follows:

1. Direct Connection

In this method, the C language program Directly Interacts with the database for messages, as shown in 1.

Figure 1 direct connection mode

The advantage of this method is that message interaction is real-time. After a C language program sends a message to the database, it can quickly get the result. The disadvantage is that if the database runs slowly, then the C language program needs to wait for the result, which affects the program execution efficiency.

 

2. Non-Direct Connection

In this method, the C language program indirectly interacts with the database through an independent third-party module, as shown in figure 2.

Figure 2 non-direct connection mode

The advantage of this method is that after a C program sends a message to a third-party module, it can execute other processes without waiting for the database to return the result. The disadvantage is that if the message serial number is not defined, the results returned by third-party modules may be messy. Therefore, it is important to define the order in which messages are sent (that is, set the serial number.

Because it is necessary to deal with the database, you must fill in the relevant database information in the configuration file used by the C language program, for example, the IP address, port number, user name, password, module number, and specific database name of the database machine. Before running the program, make sure that the relevant configuration item information is correct.

 

This article describes the process of message interaction between C language programs and databases in direct connection mode.

 

3. Message interaction process in direct connection mode

In the direct connection mode, the general process of message interaction between the C language program and the database is shown in 3.

Figure 3 message interaction flowchart in direct connection mode

 

As shown in figure 3, the interaction process between C language programs and databases in direct connection generally includes the following steps:

(1) obtain the corresponding database connection and retry if the connection fails. To execute an SQL statement, you must first connect to the corresponding database to obtain the corresponding database connection handle. In the case of the first connection failure, you need to reconnect. Each project team specifies the number of database reconnections, generally two to three times. If the connection fails after multiple reconnections, the program returns an error and does not execute the subsequent process. In this case, you need to find the cause of connection failure.

(2) After the connection is successful, you need to construct a specific SQL statement and call the function to execute the SQL statement. These SQL statements include not only "select", "update", "insert", "delete", but also statements that execute stored procedures. If the execution is successful, the subsequent process is continued. If the execution fails, the corresponding database connection is closed and the program is exited. Because the connection to the database was successful before, so this step fails, you need to check whether the SQL statement is constructed properly.

(3) After the SQL statement is successfully executed, if the SQL statement returns results, you need to call a function to obtain the results and parse the results. If the SQL statement does not return results, then the subsequent process is executed directly. If the retrieval and resolution results fail, close the corresponding database connection and exit the program. If the resolution is successful, continue the subsequent process.

(4) Note: If a database connection has been created in a program, you must close the database connection before the program is executed or exited to prevent the database connection handle from being used incorrectly.

 

IV. C Language Program Framework in direct connection mode

According to the Message interaction process in figure 3, the C language program framework in direct connection mode is as follows:

......

......

// Obtain the corresponding database connection

If (hDbConn = NULL) // hDbConn is the database handle

{

HDbConn = GetDBConn (...); // The first connection

If (hDbConn = NULL) // connection failed, retry again

{

HDbConn = GetDBConn (...); // Second connection

If (hDbConn = NULL) // if the second connection fails, the program returns directly without executing the subsequent process.

{

Return;

}

}

}

 

// Construct a specific SQL statement and call the function to execute the SQL statement

......

......

IRetValue = ExecuteSql (hDbConn, szSQL ,...); // SzSQL stores specific SQL statements

If (iRetValue =-1) // if the returned value is-1, the execution fails.

{

If (hDbConn! = NULL) // The database connection handle is not empty.

{

CloseDb (hDbConn); // closes the database connection

HDbConn = NULL; // Note: Set the hDbConn pointer to NULL.

}

Return; // return directly after execution fails

}

 

// If the SQL statement returns a value, you need to obtain the result. If there is no return value, you do not need to execute the following process.

IRetValue = Fetch (hDbConn, szDBBuf, sizeof (szDBBuf); // put the returned result in szDBBuf

If (iRetValue =-1) // if the returned value is-1, the execution fails.

{

If (hDbConn! = NULL) // The database connection handle is not empty.

{

CloseDb (hDbConn); // closes the database connection

HDbConn = NULL; // Note: Set the hDbConn pointer to NULL.

}

Return; // return directly after execution fails

}

 

// Continue the subsequent process

......

......

// Check and close the database handle again before the program returns

If (NULL! = HDbConn)

{

CloseDb (hDbConn );

HDbConn = NULL;

}

 

Return;

 

V. Summary

This article introduces in detail how the C language program interacts with the database in the direct connection mode, and displays the C language program framework in the direct connection mode based on the flowchart.

When dealing with databases in C language programs, we should pay attention to the following issues:

(1) After obtaining the database connection handle, you must first check whether the pointer to this handle is null. If it is null, reconnect the database.

(2) In the execution of SQL statements, if an exception occurs, you can check from two aspects: first, check whether the database configuration in the configuration file is correct, check whether the database runs well. Second, check whether the C program code is correctly written, especially if the SQL statement is correctly written.

(3) No matter where the code is located, you must release the database connection handle before exiting the program to avoid misuse.

 

In actual software development projects, it is common to deal with C language programs and databases. This article introduces the process of interaction between C language programs and databases, and provides a useful reference for the smooth development of related software.

 

 

(Welcome to China Mail BBS: http://bbs.njupt.edu.cn /)
(Welcome To The Mail BBS: http://bbs.cqupt.edu.cn/nForum/index)

(This series of articles will be updated twice a week, so stay tuned! My microblog: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)