MySQL C language interface-preprocessing statement

For statements executed multiple times, preprocessing is faster than direct execution, mainly because only one parsing operation is performed on the query. In the case of direct execution, the query is performed each time the statement is executed. In addition, since only the parameter data needs to be sent each time a pre-processing statement is executed, network traffic is reduced.

 

A:Select*From tablename

B:Select*From tablename

General server processing method:

A--->S--->A B--->S--->B

 

The server adopts a preprocessing mechanism.

A--->S--->A s--->B. Reduce the execution of interpretation once.

 

A:Select*From tablename where ID=?

B:Select*From tablename where name=?

 

Features of the preprocessing mechanism:

1. Reduce server load

2. Improve Server Response Speed

3. The parameter mechanism can be provided to give customers more query methods

 

Data Type of preprocessing Mechanism

Mysql_stmt this structure indicates the preprocessing statement

Mysql_bind this structure is used for statement input (data value sent to the server) and output (result value returned from the server)

 

1. Transmit data from the customer to the server

2. Transmit data from the server to the customer

 

Function:

Mysql_stmt*Mysql_stmt_init(MySQL*MySQL)

Create a mysql_stmt handle. For this handle, use mysql_stmt_close(Mysql_stmt*)Release

Int mysql_stmt_prepare(Mysql_stmt*Stmt,Const char*Query,Unsigned long length)

Given mysql_stmt_init()The Return Statement handle. 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.

My_bool mysql_stmt_bind_param(Mysql_stmt*Stmt,Mysql_bind*Bind)

Used to bind data to parameter tags in SQL statements

My_bool mysql_stmt_bind_result(Mysql_stmt*Stmt,Mysql_bind*Bind)

Mysql_stmt_bind_result()Associate (BIND) the columns in the result set with the data buffer and length buffer.

Int mysql_stmt_execute(Mysql_stmt*Stmt)

Mysql_stmt_execute()Execute the pre-processing query related to the statement handle

Int mysql_stmt_store_result(Mysql_stmt*Stmt)

For subsequent mysql_stmt_fetch()The call can return buffered data.

Int mysql_stmt_fetch(Mysql_stmt*Stmt)

Mysql_stmt_fetch()Next row in the returned result set

My_bool mysql_stmt_close(Mysql_stmt*)

Disable preprocessing statements

 

 

 

Preprocessing process:

1. mysql_stmt*St;

2. initialize mysql_stmt for the data type to be processed*Mysql_stmt_init(MySQL*)St=Mysql_stmt_init(MySQL*);

 

3. Bind the pre-processing handle to the specific SQL statement int mysql_stmt_prepare(Mysql_stmt*St,Char*SQL,Intlength);

 

Mysql_stmt_prepare(St,SQL,Strlen(Str));

 

4. MySQL statement Parameters

Select*From tablename where ID=?And name=?

Assign Parameters

Mysql_bind para[N]// 1. N is determined based on the parameters in the statement (customer --> Service) 2. N is determined based on the number of fields in the statement (Service --> customer)

Memset(Para,0, Sizeof (Para));

Parameter operations

Para[0].Buffer_type=Mysql_type_long // set the Data Type of the Parameter

Int ID;

Para[0].Buffer= &ID;// Pass the parameter value

 

Para[1].Buffer_type=Mysql_type_string

Char Str[20];

Para[1].Buffer_length= Sizeof (Str);

Para[1].Buffer=Str;

 

Pre-processing and parameter binding mysql_stmt_bind_param(St,Para);

Execute mysql_stmt_execute(St);

Space occupied by the release preprocessing mechanism mysql_stmt_close(Mysql_stmt*)Mysql_stmt_close(St);

 

 

Example 1:

/** Client to server */# include <stdio. h> # include <MySQL. h> # include <string. h> int main (void) {MySQL * conn = mysql_init (null); // initialize the server handle/* log on to the server */If (! Mysql_real_connect (Conn, "localhost", "root", "", "test", 0, null, 0) {fprintf (stderr, "mysql_real_connect: % s \ n ", mysql_error (conn); Return-1;} mysql_stmt * stmt = mysql_stmt_init (conn); // create a mysql_stmt handle char * query = "insert into Stu values (?, ?); "; If (mysql_stmt_prepare (stmt, query, strlen (query) {fprintf (stderr," mysql_stmt_prepare: % s \ n ", mysql_error (conn )); return-1 ;}int ID; char name [20]; printf ("ID name:"); scanf ("% d % s", & ID, name ); mysql_bind Params [2]; memset (Params, 0, sizeof (Params); Params [0]. buffer_type = mysql_type_long; Params [0]. buffer = & ID; Params [1]. buffer_type = mysql_type_string; Params [1]. buffer = Name; Params [1]. buffer_length = strlen (name); Explain (stmt, Params); mysql_stmt_execute (stmt); // execute the pre-processing mysql_stmt_close (stmt) related to the statement handle; mysql_close (conn ); return 0 ;}

 

Example 2:

/** Log on to the client */# include <stdio. h> # include <MySQL. h> # include <string. h> int main (void) {MySQL * conn = mysql_init (null); // initialize the server handle/* log on to the server */If (! Mysql_real_connect (Conn, "localhost", "root", "", "test", 0, null, 0) {fprintf (stderr, "mysql_real_connect: % s \ n ", mysql_error (conn); Return-1;} mysql_stmt * stmt = mysql_stmt_init (conn); // create a mysql_stmt handle char * query = "select * from Stu ;"; if (mysql_stmt_prepare (stmt, query, strlen (query) {fprintf (stderr, "mysql_stmt_prepare: % s \ n", mysql_error (conn); Return-1 ;} int ID; char name [20]; // printf ("ID name:"); // scanf ("% d % s", & ID, name ); mysql_bind Params [2]; memset (Params, 0, sizeof (Params); Params [0]. buffer_type = mysql_type_long; Params [0]. buffer = & ID; Params [1]. buffer_type = mysql_type_string; Params [1]. buffer = Name; Params [1]. buffer_length = sizeof (name); // mysql_stmt_bind_param (stmt, Params); mysql_stmt_bind_result (stmt, Params ); // associate the columns in the result set with the data buffer and length buffer (BIND) mysql_stmt_execute (stmt); // execute the pre-processing mysql_stmt_store_result (stmt) related to the statement handle ); // so that subsequent mysql_stmt_fetch () calls can return buffered data while (mysql_stmt_fetch (stmt) = 0) // printf ("% d \ t % s \ n", ID, name); mysql_stmt_close (stmt); mysql_close (conn); Return 0 ;}

 

Example 3:

/** Client to the server, and then to the client */# include <stdio. h> # include <MySQL. h> # include <string. h> int main (void) {MySQL * conn = mysql_init (null); // initialize the server handle/* log on to the server */If (! Mysql_real_connect (Conn, "localhost", "root", "", "test", 0, null, 0) {fprintf (stderr, "mysql_real_connect: % s \ n ", mysql_error (conn); Return-1;} mysql_stmt * stmt = mysql_stmt_init (conn); // create a mysql_stmt handle char * query = "select * from Stu where id = ?; "; If (mysql_stmt_prepare (stmt, query, strlen (query) {fprintf (stderr," mysql_stmt_prepare: % s \ n ", mysql_error (conn )); return-1 ;}int ID; char name [20]; printf ("ID:"); scanf ("% d", & ID); mysql_bind Params [2]; memset (Params, 0, sizeof (Params); Params [0]. buffer_type = mysql_type_long; Params [0]. buffer = & ID; Params [1]. buffer_type = mysql_type_string; Params [1]. buffer = Name; Params [1]. buffer_length = sizeof (name); mysql_stmt_bind_param (stmt, Params); mysql_stmt_bind_result (stmt, Params); // associate the columns in the result set with the data buffer and length buffer (BIND) mysql_stmt_execute (stmt); // execute the statement handle-related pre-processing handler (stmt); // so that subsequent mysql_stmt_fetch () calls can return buffered data while (mysql_stmt_fetch (stmt) = 0) // printf ("% d \ t % s \ n", ID, name); mysql_stmt_close (stmt); mysql_close (conn ); return 0 ;}

Address: http://blog.chinaunix.net/uid-24219701-id-1745030.html