Let you know in advance. Software Development (20): How to execute SQL statements in C language?

"Article Summary"

In communication software, programs often need to deal with databases. To implement features such as fetching data from a database, updating a field on a database table, inserting or deleting a piece of data, you need to construct some SQL statements in your C program and execute them with functions.

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

Keywords

SQL Statement C language program process Development

First, why do you want to execute the SQL statement in the C language Program?

There are several reasons to execute SQL statements in a C language Program:

(1) The program needs to get the field values of a data table in the database and parse the values of those fields to perform subsequent operations.

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

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

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

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

As can be seen in the C language Program, not only the ability to execute "select", "update", "insert", "delete" and other statements, Also requires the ability to execute stored procedures.

Ii. ways in whichC language programs interact with databases

The C language program deals with databases in the form of direct-connect ( synchronous ) and non-direct ( asynchronous ) Two, which are characterized by the following:

1. Direct Connect mode

In this way, theC language program directly interacts with the database for messages, as shown in1 .

Figure 1 Direct connect mode

The advantage of this approach is that the message interaction is instantaneous, theC Language program sends the message to the database, and the result can be obtained quickly; The disadvantage is that if the database execution is slow, the C language program needs to hang there to wait for the result, which affects the efficiency of the program execution.

2. non-direct connection mode

In this way, theC language program indirectly interacts with the database through a separate third-party module, as shown in2 .

Figure 2 non-direct connection mode

The advantage of this approach is that when a C language program sends a message to a third-party module, it can perform other processes without waiting for the database to return results, and the disadvantage is that if the message sequence number is not defined, it is highly likely that the results returned by the third-party module will be confusing. Therefore, in this way, it is important to define the order in which messages are sent ( that is, set a good sequence number ) .

Because of the need to deal with the database, so in the C language program used in the configuration file to fill in the relevant information related database, such as the database machine IP address, port number, user name, password, module number and the specific database name of the operation. Before running the program, be sure to ensure that the relevant configuration item information is correct.

This paper introduces the process of communication between C language Program and database in direct connect mode.

The process of message interaction under the Direct connect mode

In the direct connect mode, theC language Program and the database for the message interaction of the general Flow 3 is shown.

Figure 3 message interaction flowchart in direct-connect mode

As can be seen from Figure 3 , the process of the C language program's message interaction with the database under the direct connection method generally consists of the following steps:

         (1) gets the corresponding database connection and retries if the connection fails. To execute the sql statement, you first need to connect to the corresponding database, that is, get the corresponding database connection handle. In the case of the first connection failure, you want to reconnect. Each project group specifies the number of times a database is re-connected, typically two to three times. If it is still unsuccessful after multiple re-connection, the program returns an error directly and no longer executes the subsequent process. You will need to find out why the connection failed.

         (2) after a successful connection, construct a specific sql statement. These sql statements include not only " Select "," update "," insert "," delete Statements, such as , also include statements that execute stored procedures. If the execution succeeds, the subsequent process continues, and if execution fails, the corresponding database connection is closed and the program exits. Because the previous connection to the database was successful, this step fails, you need to see if it is sql statement constructs a problem.

(3) after successful execution of the SQL statement, if the SQL statement has a return result, it is necessary to call the function to obtain the result, while parsing the result, and if the SQL statement does not return a result, the subsequent process is executed directly. If the result of the fetch and parse fails, the corresponding database connection is closed and the program exits, and if the resolution succeeds, the subsequent process continues.

(4) Note that if a database connection is created inside a program, be sure to close the database connection before the program finishes or exits, preventing the database connection handle from being used incorrectly.

The framework of C language Program under the direct connection mode

According to the message interaction process in Figure 3 , the C Language Program framework under the Direct connect mode is as follows:

......

......

// get the corresponding database connection

if (hdbconn = = NULL)//hdbconn for database handle

{

hdbconn = Getdbconn (...        ); First time Connection

if (hdbconn = = NULL)// connection failed, try again

{

hdbconn = Getdbconn (...    ); Secondary Connection

if (hdbconn = = NULL)// second connection failed, program returns directly and does not perform subsequent process

{

Return

}

}

}

// construct a specific SQL statement and invoke the function to execute the SQL statement

......

......

Iretvalue = ExecuteSQL (Hdbconn, szSQL, ...  ); szSQL to store specific SQL statements

if (Iretvalue = =-1)// return value is -1 means execution failed

{

if (hdbconn! = NULL)// database connection handle is not empty

{

Closedb (Hdbconn); to close a database connection

hdbconn = NULL; Note that to set the hdbconn pointer to empty

}

return; Direct return after failed execution

}

// if the SQL statement has a return value, you need to obtain the result; no return value is required to perform the following process

Iretvalue = Fetch (Hdbconn, Szdbbuf, sizeof (SZDBBUF));// put the returned results in szdbbuf

if (Iretvalue = =-1)// return value is -1 means execution failed

{

if (hdbconn! = NULL)// database connection handle is not empty

{

Closedb (Hdbconn); to close a database connection

hdbconn = NULL; Note that to set the hdbconn pointer to empty

}

return; Direct return after failed execution

}

// continue to follow-up process

......

......

// to check and close the database handle again before the program returns

if (NULL! = hdbconn)

{

Closedb (Hdbconn);

Hdbconn = NULL;

}

Return

V. Summary

This paper gives a detailed introduction to how C language program interacts with database in the Direct connect mode, and shows the framework of C language program in direct connect mode with the flowchart.

In the process of dealing with the database in C language Program, we should pay attention to the following questions:

(1) after getting the database connection handle, first check whether the handle pointer is empty before using it, or reconnect the database if it is empty.

(2) in the process of executing SQL statements, if you encounter abnormal results, you can check from two aspects: first, check the configuration file in the database configuration is correct, the database itself is running well; second, check C The program code is written correctly, especially if the SQL statement is written correctly.

(3) wherever the code is located, be sure to release the handle of the database connection before the program exits, lest it be misused.

In real software development projects, C language programs and databases are commonplace. This paper introduces the specific process of C language program and database interaction, which provides a useful reference for the development of related software.

(Welcome to South Mail bbs:http://bbs.njupt.edu.cn/)
(Welcome to re-mail Bbs:http://bbs.cqupt.edu.cn/nforum/index)

(This series of articles updated two weekly, please look forward to!) I Weibo: Http://weibo.com/zhouzxi?topnav=1&wvr=5, No.: 245924426, welcome attention! )