MySql 100106：Connector/C （II）
Normal
0
7.8 磅
0
2
false
false
false
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:普通表格;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
table.MsoTableGrid
{mso-style-name:网格型;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
border:solid windowtext 1.0pt;
mso-border-alt:solid windowtext .5pt;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-border-insideh:.5pt solid windowtext;
mso-border-insidev:.5pt solid windowtext;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
mso-pagination:none;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
MySql 100106：Connector/C （II）
@
http://zcatt.cublog.cn
1．Prepared statement
Prepared statement（翻成 预编 ？）是一种不同于直接执行（查询）的方式。直接执行方式中，每次查询都会解析一次查询语句，再执行。而预编模式则可以将解析的结果保存，再执行。这样对于同一查询反复执行的情景，只需执行一次解析，后面反复执行这个解析的结果就成了，只需每次执行时给定语句中的参数。从而实现效率的提高。
注意的是，并不是所有环境下预编都会比直接执行的效率高。
支持预编模式的语句大致有：CALL,
CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE。
2．框架
基本步骤：
a) mysql_stmt_init(), 初始化
b) mysql_stmt_prepare, 预编译语句
loop begin
c) mysql_stmt_bind_param()，给定每次查询的参数
d) mysql_stmt_execute(), 执行
e) mysql_stmt_bind_result()， 绑定结果输出缓冲
f) mysql_stmt_store_result(), 获取全部结果
loop begin
g)mysql_stmt_fetch()，取一行结果
loop end
loop end
h) mysql_stmt_close()，释放
一个查询对应一个prepared
statement，调用mysql_stmt_init后会得到一个handle，使用完后应该close。stmt handle与 数据库连接（connection）的关系是多对一的关系。
3．主要数据结构
1) MYSQL_STMT
stmt handle的机构，代表一个prepared语句。mysql_stmt_init()生成，mysql_stmt_close()注销。
2）MYSQL_BIND
用于给定输入的语句参数，和输出的结果。使用前应当用0初始化。语句中参数使用'?'占位。
其中几个重要的域
field
input（bind param）
output (bind result)
enum enum_field_types buffer_type
值的类型
期望接收的值类型
void *buffer
参数的内容
保存结果的buffer
unsigned long buffer_length
*buffer的长度
*buffer能容纳的最大长度
unsigned long *length
实际长度。数字类型的值，此域忽略。
实际长度。数字类型的值，此域忽略。
my_bool *is_null
是否这个域传入NULL值
域值是否NULL
my_bool is_unsigned
是否unsigned
是否unsigned
my_bool *error
对于truncate有特别的用途。
3）MYSQL_TIME
用于表示DATE,TIME,DATETIME和TIMESTAMP。
4. 主要函数
id
func
Description
1
mysql_stmt_init()
分配stmt
handle结构内存，初始化
2
mysql_stmt_close()
释放stmt
handle
3
mysql_stmt_prepare()
预编查询/语句
4
mysql_stmt_bind_param()
绑定入口参数
5
mysql_stmt_bind_result()
绑定出口参数
6
mysql_stmt_execute()
执行
7
mysql_stmt_store_result()
取回所有结果
8
mysql_stmt_fetch()
取一行结果。
5．例子
预备条件：
server上的账户 root:passwd@localhost:3306, 数据库是world, 其中的表city结构如下
mysql> desc
city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11)| NO
| PRI | NULL | auto_increment |
| Name | char(35) | NO |
| | |
| CountryCode |
char(3)| NO |
| | |
| District | char(20) | NO |
| | |
|
Population| int(11)| NO
| | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set
(0.00 sec)
程序如下
// mysqlTest.cpp : 定义控制台应用程序的入口点。
//
#include "stdafx.h"
#include "mysql.h"
#define MYSQL_PORT 3306
#define MYSQL_HOST "localhost"
#define MYSQL_USER "root"
#define MYSQL_PASSWD "passwd"
#define MYSQL_DB "world"
#define MYSQL_NAME_LEN 35
#define MYSQL_SELECTSTATEMENT "select id, name from city where id=?"
int _tmain(int argc, _TCHAR* argv[])
{
int
result;
MYSQL mySQLHandler;
MYSQL_STMT *mySQLStmt;
MYSQL_BIND mySQLBindInput;
MYSQL_BIND mySQLBindOutput;
int
cityId=0;
my_bool isNullInput = FALSE;
my_bool isNullOutput;
my_bool isUnsigned = TRUE;
unsigned
long length;
my_bool err;
int
id;
char
name;
mysql_library_init(0,NULL,NULL);
mysql_init(&mySQLHandler);
printf("1.
inited ---------------\n");
if
(!mysql_real_connect(&mySQLHandler, MYSQL_HOST, MYSQL_USER, MYSQL_PASSWD,
MYSQL_DB,MYSQL_PORT, NULL, 0))
{
printf( "Error connecting to database: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
printf("2.
connected ---------------\n");
mySQLStmt =
mysql_stmt_init(&mySQLHandler);
if
(!mySQLStmt)
{
printf( "stmt init err\n");
exit(1);
}
printf(".
stmt init ---------------\n");
result =
mysql_stmt_prepare(mySQLStmt, MYSQL_SELECTSTATEMENT,
strlen(MYSQL_SELECTSTATEMENT));
if
(result)
{
printf( "Error stmt prepare: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
printf(".
stmt prepare ---------------\n");
while(1)
{
cityId++;
memset(&mySQLBindInput,
0, sizeof(MYSQL_BIND));
mySQLBindInput.buffer_type =
MYSQL_TYPE_LONG;
mySQLBindInput.buffer =
&cityId;
// mySQLBindInput.buffer_length
= sizeof(cityId); // this is ignored by mysql because the type is numeric.
// mySQLBindInput.length
= 0; // this is ignored by mysql because the type is numeric.
mySQLBindInput.is_null = 0;
result =
mysql_stmt_bind_param(mySQLStmt, &mySQLBindInput) ;
if
(result)
{
printf( "Error bind param: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
// printf(".
bind param %d ---------------\n", cityId);
result = mysql_stmt_execute(mySQLStmt);
if
(result)
{
printf( "Error execute: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
// printf(".
Execute ---------------\n");
memset(&mySQLBindOutput,
0, sizeof(MYSQL_BIND));
memset(&mySQLBindOutput,
0, sizeof(MYSQL_BIND));
mySQLBindOutput.buffer_type
=MYSQL_TYPE_LONG;
mySQLBindOutput.buffer =
&id;
mySQLBindOutput.buffer_length
= sizeof(id); // this
is ignored by mysql because the type is numeric.
mySQLBindOutput.length
=&length;
mySQLBindOutput.is_null =
&isNullOutput;
mySQLBindOutput.error =
&err;
mySQLBindOutput.buffer_type
=MYSQL_TYPE_STRING;
mySQLBindOutput.buffer =
&name;
mySQLBindOutput.buffer_length
= MYSQL_NAME_LEN; // this is ignored by mysql because
the type is numeric.
mySQLBindOutput.length
=&length; // this is ignored by mysql because the type is numeric.
mySQLBindOutput.is_null =
&isNullOutput;
mySQLBindOutput.error =
&err;
result =
mysql_stmt_bind_result(mySQLStmt, mySQLBindOutput);
if
(result)
{
printf( "Error bind result: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
// printf(".
bind result ---------------\n");
result =
mysql_stmt_store_result(mySQLStmt);
if
(result)
{
printf( "Error store result: %s\n",mysql_error(&mySQLHandler));
exit(1);
}
// printf(".
store result ---------------\n");
// printf(".
fetching result ---------------\n");
if(mysql_stmt_num_rows(mySQLStmt)==0)
break;
while(!mysql_stmt_fetch(mySQLStmt))
{
printf("id= %d, name= %s \n",
isNullOutput?0:id, isNullOutput?0:name);
}
}
printf(".
fetch result over ---------------\n");
mysql_stmt_close(mySQLStmt);
printf(".
stmt close ---------------\n");
mysql_close(&mySQLHandler);
printf("6.close
mysql ---------------\n");
mysql_library_end();
return
0;
}
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/8866/showart_2142337.html
页:
[1]