Shulou(Shulou.com)06/01 Report--

This article mainly introduces a way for MySQL C API to execute SQL sentences, which involves things, learned from theoretical knowledge, there are many books and documents for your reference, from a practical point of view, accumulated years of practical experience can be shared with you.

Keep looking.

Preprocessing statement (Prepared Statements)

MySQL C API also provides another way to execute SQL statements, that is, prepare before execution

(execute). For SQL statements executed many times, this method can improve the efficiency of execution. The specific steps are as follows:

Call mysql_stmt_init () to create a statement handle that will be used in subsequent function calls.

MYSQL_STMT mysql_stmt_init (MYSQL mysql)

MYSQL_STMT mysql_stmt_init (MYSQL mysql)

Description

Create a MYSQL_STMT handle. For this handle, you should use mysql_stmt_close (MYSQL_STMT *) to release.

Return value

When successful, returns a pointer to the MYSQL_STMT structure. Returns NULL if memory is overflowed.

Error

CR_OUT_OF_MEMORY

Memory overflow.

Call mysql_stmt_prepare () to preprocess the SQL statement

Int mysql_stmt_prepare (MYSQL_STMT stmt, const char stmt_str, unsigned long length)

Int mysql_stmt_prepare (MYSQL_STMT stmt, const char query, unsigned long length)

Description

Given the statement handle returned by mysql_stmt_init (), prepare the SQL statement pointed to by the string query and return the status value. The string length should be given by the "length" parameter. The string must contain 1 SQL statement. You should not add a terminating semicolon (';') or\ g to the statement.

By changing the question mark character "?" Embedded in the appropriate position of the SQL string, the application can contain one or more parameter markers in the SQL statement.

Tags are legal only if they are at a specific location in the SQL statement. For example, it can be in the VALUES () list of an INSERT statement (specifying a column value for a row) or in the comparison section of a column in the WHERE clause (used to specify a comparison value). However, for ID (such as table or column names), they are not allowed to be used, and it is not allowed to specify the operands of binary operators such as the equal sign "=". The latter restriction is necessary because the parameter type cannot be determined. Generally speaking, parameters are legal only in DML (data manipulation language) statements, but not in DDL (data definition language) statements.

Before executing the statement, you must use mysql_stmt_bind_param () to bind the parameter marker to the application variable.

Return value

If the statement is processed successfully, 0 is returned. If an error occurs, a non-zero value is returned.

If there are parameters in the SQL statement, you need to call mysql_stmt_bind_param () to bind the parameters.

My_bool mysql_stmt_bind_param (MYSQL_STMT stmt, MYSQL_BIND bind)

If the parameter is of type TEXT or BLOB and the amount of data is large, you can call the

Mysql_stmt_send_long_data () to send data to the cloud server.

My_bool mysql_stmt_send_long_data (MYSQL_STMT stmt, unsigned int parameter_number, const char data, unsigned long length)

My_bool mysql_stmt_bind_param (MYSQL_STMT stmt, MYSQL_BIND bind)

Description

Mysql_stmt_bind_param () is used to bind data to the parameter markers in the SQL statement for passing to mysql_stmt_prepare (). It uses the MYSQL_BIND structure to provide data. "bind" is the address of an array of MYSQL_BIND structures. As expected by the client library, for each "?" that appears in the query Parameter marker, which contains 1 element in the array.

Suppose you have prepared the following sentence:

INSERT INTO mytbl VALUES (?)

When binding parameters, the array of the MYSQL_BIND structure contains three elements and can be declared as follows:

MYSQL_BIND bind [3]

Return value

If the binding is successful, 0 is returned. If an error occurs, a non-zero value is returned.

Call mysql_stmt_execute () to execute the query.

Int mysql_stmt_execute (MYSQL_STMT * stmt)

Int mysql_stmt_execute (MYSQL_STMT * stmt)

Description

Mysql_stmt_execute () executes preprocessing queries related to the statement handle. During the call, the value of the currently bound parameter marker is sent to the server, which replaces the marker with the newly provided data.

If the statement is UPDATE, DELETE, or INSERT, you can find the total number of rows changed, deleted, or inserted by calling mysql_stmt_affected_rows (). If this is a statement such as SELECT that generates a result set, you must call mysql_stmt_fetch () to get the data before calling any other function that can cause query processing.

For statements that generate result sets, you can request mysql_stmt_execute () to open the cursor for the statement by calling mysql_stmt_attr_set () before executing the statement. If you execute a statement multiple times, mysql_stmt_execute () closes any open cursors before opening the new cursor.

Return value

If the execution is successful, 0 is returned. If an error occurs, a non-zero value is returned.

If the query does not produce a result set, you can call the

Mysql_stmt_affected_rows () and

My_ulonglong mysql_stmt_insert_id (MYSQL_STMT stmt) to obtain the number of changed records and the generated self-increment.

My_ulonglong mysql_stmt_affected_rows (MYSQL_STMT stmt)

My_ulonglong mysql_stmt_insert_id (MYSQL_STMT stmt)

Otherwise, execute mysql_stmt_bind_result () to bind the fields in the result set.

My_bool mysql_stmt_bind_result (MYSQL_STMT stmt, MYSQL_BIND * bind)

My_ulonglong mysql_stmt_affected_rows (MYSQL_STMT * stmt)

Description

Returns the total number of rows changed, deleted, or inserted last time the statement was executed. For UPDATE, DELETE, or INSERT statements, you can call them immediately after mysql_stmt_execute (). For the SELECT statement, mysql_stmt_affected_rows () works like mysql_num_rows ().

Return value

An integer greater than 0 indicates the number of rows affected or retrieved. For UPDATE statements, "0" indicates that no records have been updated, that there are no rows matching the WHERE clause in the query, or that no query has been executed. "- 1" indicates that an error was returned, or for a SELECT query, mysql_stmt_affected_rows () was called before mysql_stmt_store_result () was called. Since mysql_stmt_affected_rows () returns an unsigned value, you can check "- 1" by comparing the return value with "(my_ulonglong)-1" (or the equivalent "(my_ulonglong) ~ 0").

Call mysql_stmt_fetch () to get the records in the result set line by line.

Int mysql_stmt_fetch (MYSQL_STMT stmt)

Before calling mysql_stmt_fetch (), you can also execute mysql_stmt_store_result () to change the result

The set is pre-cached locally.

Int mysql_stmt_store_result (MYSQL_STMT stmt)

Int mysql_stmt_store_result (MYSQL_STMT * stmt)

Description

For all statements (SELECT, SHOW, DESCRIBE, EXPLAIN) that successfully generated the result set, and only if you intend to buffer all the result sets on the client side, you must call mysql_stmt_store_result () so that subsequent mysql_stmt_fetch () calls can return buffered data.

For other statements, there is no need to call mysql_stmt_store_result (), but if it is called, it will not cause any harm or cause any performance problems. You can detect whether the statement generates a result set by checking whether mysql_stmt_result_metadata () returns NULL.

Return value

If the buffering of the result is completed successfully, 0 is returned. If an error occurs, a non-zero value is returned.

Repeat steps 3-6, using different arguments to execute the query each time. Call mysql_stmt_close () to close the handle and release the resource

My_bool mysql_stmt_close (MYSQL_STMT)

In addition, you can call mysql_stmt_errno () and mysql_stmt_error () to know the most recently executed preprocessing

The error code and error message of the rational statement API function.

Unsigned int mysql_stmt_errno (MYSQL_STMT stmt)

Const char mysql_stmt_error (MYSQL_STMT stmt)

Overall:

The MySQL client / server protocol provides preprocessing statements. This function adopts the data structure of the MYSQL_STMT statement handler returned by the mysql_stmt_init () initialization function. Preprocessing execution is an effective way for statements that have been executed multiple times. First of all, the statement is parsed to prepare for execution. Next, execute it one or more times later using the statement handle returned by the initialization function.

For statements that are executed multiple times, preprocessing is faster than direct execution, mainly because the query is parsed only once. In the case of direct execution, a query is made each time the statement is executed. In addition, the network traffic is reduced because only the data of the parameters need to be sent each time the preprocessing statement is executed.

Another advantage of the preprocessing statement is that it uses the binary protocol, which makes the data transfer between the client and the server more efficient.

Programming steps

 1. Create a preprocessing statement handle with mysql_stmt_init (). To prepare a preprocessing statement on the server, call mysql_stmt_prepare () and pass it a string containing the SQL statement

 2. If the statement generates a result set, call mysql_stmt_result_metadata () to get the result set metadata. Although different from the result set that contains the columns returned by the query, the metadata itself takes the form of the result set. The metadata result set indicates how many columns are contained in the result and contains information for each column.

 3. Use mysql_stmt_bind_param () to set the value of any parameter. All parameters must be set. Otherwise, statement execution will return an error or produce unpredictable results.

 4. Call mysql_stmt_execute () to execute the statement.

 5. If the statement generates a result set, the data buffer is bundled and the row value is retrieved by calling mysql_stmt_bind_result ().

 6. By repeatedly calling mysql_stmt_fetch (), the data is extracted into the buffer by row until no more rows are found.

 7. Repeat steps 3 through 6 by changing the parameter values and executing the statement again.

Implementation code:

# include

# include

# include

# include

# define STRING_SIZE 50

# define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"

# define CREATE_SAMPLE_TABLE "CREATE TABLE test_table (col1 INT,\

Col2 VARCHAR (40),\

Col3 SMALLINT,\

Col4 TIMESTAMP) "

# define INSERT_SAMPLE "INSERT INTO test_table (col1,col2,col3) VALUES"

/ / int main (char * argv, int argc)

Int main (char argv [], int argc)

{

Int ret = 0, I = 0

MYSQL * mysql;MYSQL * connect = NULL;char sqlbuf [2048]; mysql = mysql_init (NULL); if (mysql = = NULL) {ret = mysql_errno (& mysql); printf ("func mysql_init () err\ n"); return ret;} printf ("func mysql_init () ok\ n"); connect = mysql_real_connect (mysql, "localhost", "root", "123456", "mydb2", 0, NULL, 0) If (connect = = NULL) {ret = mysql_errno (& mysql); printf ("func mysql_init () err\ n"); return ret;} printf ("func mysql_real_connect () ok\ n"); MYSQL_STMT * stmt;MYSQL_BIND bind [3]; my_ulonglong affected_rows;int param_count;short small_data;int int_data;char str_ data [string _ SIZE] Unsigned long str_length;my_bool is_null;if (mysql_query (mysql, DROP_SAMPLE_TABLE)) {fprintf (stderr, "DROP TABLE failed\ n"); fprintf (stderr, "% s\ n", mysql_error (mysql)); exit (0);} if (mysql_query (mysql, CREATE_SAMPLE_TABLE)) {fprintf (stderr, "CREATE TABLE failed\ n"); fprintf (stderr, "% s\ n", mysql_error (mysql)) Exit (0);} / * Prepare an INSERT query with 3 parameters * / * (the TIMESTAMP column is not named; the server * / / * sets it to the current date and time) * / stmt = mysql_stmt_init (mysql); / / initialize the preprocessing environment handle MYSQL_STMT * stmtif (! stmt) {fprintf (stderr, "mysql_stmt_init (), out of memory\ n"); exit (0) } if (mysql_stmt_prepare (stmt, INSERT_SAMPLE, strlen (INSERT_SAMPLE)) / / add sql (with placeholder) {fprintf (stderr, "mysql_stmt_prepare (), INSERT failed\ n") to the preprocessing environment handle stmt; fprintf (stderr, "% s\ n", mysql_stmt_error (stmt)); exit (0);} fprintf (stdout, "prepare, INSERT successful\ n") / * Get the parameter count from the statement * / param_count= mysql_stmt_param_count (stmt); / / get the number of placeholders in the sql language fprintf (stdout, "total parameters in INSERT:% d\ n", param_count); if (param_count! = 3) / * validate parameter count * / {fprintf (stderr, "invalid parameter count returned by MySQL\ n"); exit (0);} / * Bind the data for all 3 parameters * / memset (bind, 0, sizeof (bind)) / * INTEGER PARAM * / * This is a number type, so there is no need to specify buffer_length * / bind [0] .buffer _ type= MYSQL_TYPE_LONG; / / set the property of the first placeholder bind [0] .buffer = (char *) & int_data;bind [0] .is _ null= 0 _ bind [0] .length = 0 STRING PARAM * / bind [1] .buffer _ type= MYSQL_TYPE_STRING / / set the attribute bind [1] of the second placeholder. Buffer = (char *) str_data;bind [1] .buffer _ length= STRING_SIZE;bind [1] .is _ null= 0politics bind [1] .length = & str_length;/* SMALLINT PARAM * / bind [2] .buffer _ type= MYSQL_TYPE_SHORT; / / set the attribute bind [2] of the third placeholder. Buffer = (char *) & small_data;bind [2] .is _ null= & is_null;bind [2] .length = 0 / * Bind the buffers * / if (mysql_stmt_bind_param (stmt, bind)) / / add the set attributes to the preprocessing environment stmt {fprintf (stderr, "mysql_stmt_bind_param () failed\ n"); fprintf (stderr, "% s\ n", mysql_stmt_error (stmt)); exit (0);} / * Specify the data values for the first row * / / ready to insert data int_data= 10 / * integer * / strncpy (str_data, "MySQL", STRING_SIZE); / * string * / str_length= strlen (str_data); / * INSERT SMALLINT data as NULL * / is_null= 1 / is_null= * Execute the INSERT statement-1*/if (mysql_stmt_execute (stmt)) / / execute the preprocessing environment to insert the first record {fprintf (stderr, "mysql_stmt_execute (), 1 failed\ n") Fprintf (stderr, "% s\ n", mysql_stmt_error (stmt)); exit (0);} / * Get the total number of affected rows * / affected_rows= mysql_stmt_affected_rows (stmt); / / get affected line information fprintf (stdout, "total affected rows (insert 1):% lu\ n", (unsigned long) affected_rows) If (affected_rows! = 1) / * validate affected rows * / {fprintf (stderr, "invalid affected rows by MySQL\ n"); exit (0);} / * Specify data values for second row, then re-execute the statement * / int_data= 1000 (str_data, "The most popular Open Source database", STRING_SIZE); str_length= strlen (str_data); small_data= 1000; / * smallint * / is_null= 0 / * reset * / * Execute the INSERT statement-2*/if (mysql_stmt_execute (stmt)) / / insert the second record {fprintf (stderr, "mysql_stmt_execute, 2 failed\ n"); fprintf (stderr, "% s\ n", mysql_stmt_error (stmt)); exit (0);} / * Get the total rows affected * / affected_rows= mysql_stmt_affected_rows (stmt) / / get the affected lines fprintf (stdout, "total affected rows (insert 2):% lu\ n", (unsigned long) affected_rows); if (affected_rows! = 1) / * validate affected rows * / {fprintf (stderr, "invalid affected rows by MySQL\ n"); exit (0);} / * Close the statement * / if (mysql_stmt_close (stmt)) {fprintf (stderr, "failed while closing the statement\ n") Fprintf (stderr, "% s\ n", mysql_stmt_error (stmt)); exit (0);} mysql_close (connect); printf ("hello...\ n"); return ret

Read the above MySQL C API implementation of SQL statements in a way to introduce, I hope it can give you some help in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.