loadrunner12：常用函数汇总说明之Database Functions参数函数，操作mysql数据库
环境准备：
1、准备好测试用的Mysql数据库。
2、下载并安装mysql odbc x32：https://dev.mysql.com/downloads/connector/odbc/
3、配置数据源（应该配置32位数据源）
遇到的问题：
1、“DB Connection failed {"ERROR [IM002] [Microsoft] [ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序”，主要原因时LR12为32位的，无法读取64位的数据源，将64位数据源改为32位即可。
解决办法：参考https://jingyan.baidu.com/article/6d704a13407c4128db51ca2d.html
安装32位的mysql-connector-odbc-8.0.25-win32，并重启电脑，查看之前配置的数据源显示支持了32位的。
根据使用手册内容可见，LR支持的关于Database Functions包括：
| lr_db_connect | Connects to a database. |
| lr_db_dataset_action | Performs an action on a dataset. |
| lr_db_disconnect | Disconnects from a database. |
| lr_db_executeSQLStatement | Submits an SQL statement to a database. |
| lr_db_getValue | Retrieves a value from a dataset. |
以下举例使用的数据为本地MySql数据库testdb中的student表：
脚本展示：
1、查询student表中数据
ConnMysql()
{
//定义两个变量
int i=1,NumRows;
//创建数据库连接
lr_db_connect("StepName=conMySql",
"ConnectionString=Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=testdb;Trusted_Connection=False;User=root;Password=mysql",
"ConnectionName=mysqlData",
"ConnectionType=ODBC",
LAST );
//查询结果保存在Students数据集中，并返回查询记录数据总条数，存入NumRows中
NumRows=lr_db_executeSQLStatement("StepName=findStudent",
"ConnectionName=mysqlData",
"SQLStatement=select * from student;",
"DatasetName=StudentsSet",
LAST );
lr_output_message("The query returned %d rows.", NumRows);
//循环打印所有记录的id、name、birthday,score。
while(i
2、更新student表中的数据
UpdateMysql()
{
int NumRows=0;
//创建数据库连接
lr_db_connect("StepName=conMySql",
"ConnectionString=Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=testdb;Trusted_Connection=False;User=root;Password=mysql",
"ConnectionName=mysqlData",
"ConnectionType=ODBC",
LAST );
//更新student表中学生“张三”的分数
lr_db_executeSQLStatement("StepName=updateScore",
"ConnectionName=mysqlData",
"SQLStatement=UPDATE student SET score=33 WHERE NAME=\"张三\";",
"DatasetName=StudentsSet",
LAST );
//查询结果保存在Students数据集中，并返回查询记录数据总条数，存入NumRows中
NumRows=lr_db_executeSQLStatement("StepName=findStudent",
"ConnectionName=mysqlData",
"SQLStatement=select * from student where NAME=\"张三\";",
"DatasetName=StudentsSet",
LAST );
lr_output_message("The query returned %d rows.", NumRows);
//获取查询到的数据集StudentsSet中的分数
lr_db_getvalue("StepName=showScore",
"DatasetName=StudentsSet",
"Column=score",
"Row=next",
"OutParam=studentScore",
LAST);
lr_output_message("学生“张三”的分数为：%s", lr_eval_string("{studentScore}"));
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
3、删除一条学生记录
DeleteMysql()
{
int NumRows=0;
//创建数据库连接
lr_db_connect("StepName=conMySql",
"ConnectionString=Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=testdb;Trusted_Connection=False;User=root;Password=mysql",
"ConnectionName=mysqlData",
"ConnectionType=ODBC",
LAST );
//删除student表中学生“七七”的记录
lr_db_executeSQLStatement("StepName=DeleteScore",
"ConnectionName=mysqlData",
"SQLStatement=DELETE FROM student WHERE NAME=\"七七\";",
"DatasetName=StudentsSet",
LAST );
//查询结果保存在Students数据集中，并返回查询记录数据总条数，存入NumRows中
NumRows=lr_db_executeSQLStatement("StepName=findStudent",
"ConnectionName=mysqlData",
"SQLStatement=select * from student where NAME=\"七七\";",
"DatasetName=StudentsSet",
LAST );
lr_output_message("The query returned %d rows.", NumRows);
if(NumRows==0){
lr_output_message("删除成功！");
}else{
lr_error_message("删除失败");
}
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
4、插入一条学生记录
InsertMysql()
{
//创建数据库连接
lr_db_connect("StepName=conMySql",
"ConnectionString=Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=testdb;Trusted_Connection=False;User=root;Password=mysql",
"ConnectionName=mysqlData",
"ConnectionType=ODBC",
LAST );
//插入一条记录，并保存在Students数据集中
lr_db_executeSQLStatement("StepName=updateScore",
"ConnectionName=mysqlData",
"SQLStatement=INSERT INTO student VALUES(9,\"小酒\",NOW(),99);",
"DatasetName=StudentsSet",
LAST );
//查询插入的记录，并保存在Students数据集中
lr_db_executeSQLStatement("StepName=findStudent",
"ConnectionName=mysqlData",
"SQLStatement=select * from student where NAME=\"小酒\";",
"DatasetName=StudentsSet",
LAST );
lr_db_getvalue("StepName=showID",
"DatasetName=StudentsSet",
"Column=id",
"Row=next",
"OutParam=studentID",
LAST);
lr_db_getvalue("StepName=showName",
"DatasetName=StudentsSet",
"Column=name",
"Row=current",
"OutParam=studentName",
LAST);
lr_db_getvalue("StepName=showBirth",
"DatasetName=StudentsSet",
"Column=birthday",
"Row=current",
"OutParam=studentBirth",
LAST);
lr_db_getvalue("StepName=showScore",
"DatasetName=StudentsSet",
"Column=score",
"Row=current",
"OutParam=studentScore",
LAST);
lr_output_message(lr_eval_string("{studentId}"));
lr_output_message(lr_eval_string("{studentName}"));
lr_output_message(lr_eval_string("{studentBirth}"));
lr_output_message(lr_eval_string("{studentScore}"));
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
5、查询所有记录、重置游标、释放数据集内存：（ lr_db_dataset_action()）
QueryDataSet()
{
//创建数据库连接
lr_db_connect("StepName=conMySql",
"ConnectionString=Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=testdb;Trusted_Connection=False;User=root;Password=mysql",
"ConnectionName=mysqlData",
"ConnectionType=ODBC",
LAST );
//查询结果保存在Students数据集中，并返回查询记录数据总条数，存入NumRows中
lr_db_executeSQLStatement("StepName=findStudent",
"ConnectionName=mysqlData",
"SQLStatement=select * from student;",
"DatasetName=StudentsSet",
LAST );
/*
RESET: Set the cursor to the first record of the dataset.
REMOVE: Releases the memory allocated for the dataset.
PRINT: Prints the contents of the entire dataset to the Replay Log and other test report summaries.
*/
lr_db_dataset_action("StepName=PrintDataset",
"DatasetName=StudentsSet",
"Action=PRINT", //RESET、REMOVE、PRINT
LAST );
lr_db_getvalue("StepName=showName",
"DatasetName=StudentsSet",
"Column=name",
"Row=next",
"OutParam=studentName",
LAST);
lr_output_message("第一条记录的学生名称为：%s",lr_eval_string("{studentName}"));
lr_db_getvalue("StepName=showName",
"DatasetName=StudentsSet",
"Column=name",
"Row=next",
"OutParam=studentName",
LAST);
lr_output_message("第二条记录的学生名称为：%s",lr_eval_string("{studentName}"));
//将数据库游标重置到数据集的第一条记录
lr_db_dataset_action("StepName=PrintDataset",
"DatasetName=StudentsSet",
"Action=RESET", //RESET、REMOVE、PRINT
LAST );
lr_db_getvalue("StepName=showName",
"DatasetName=StudentsSet",
"Column=name",
"Row=next", //若无以上RESET操作，此时查出的记录应为第三条记录的学生名称
"OutParam=studentName",
LAST);
lr_output_message("游标重置后查询的记录为第一条记录的学生名称为：%s",lr_eval_string("{studentName}"));
//释放分配给数据集的内存。再次查询数据集的数据是将会报错“数据集未定义”，即{"Undefined dataset [Dataset name=StudentsSet]"}
lr_db_dataset_action("StepName=PrintDataset",
"DatasetName=StudentsSet",
"Action=REMOVE", //RESET、REMOVE、PRINT
LAST );
lr_db_getvalue("StepName=showName",
"DatasetName=StudentsSet",
"Column=name",
"Row=next",
"OutParam=studentName",
LAST);
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
遗留问题：
lr_db_getvalue()读取单个学生的中文姓名时，打印出来中文名字只打印一半，中文被截断了，但是读取英文不存在该问题（如下图所示），目前尚未定位出原因及解决办法，欢迎评论去讨论~~~