LR通过ODBC连接Mysql数据库
1. 准备好测试用的Mysql数据库。
2. 下载ODBC驱动,“mysql-connector-odbc-win32.zip”并安装驱动。
3. 配置ODBC数据源。(安的是32位就打开32位的,如果是64位的,就打开64位的)
打开“控制面析”à“管理工具”à“ODBC数据源(32位)”à点击“添加按钮”打开如下图所示:
点击“完成”,进行数据库连接配置页面。如下图所示:
Data Source Name:可以随便定义
TCP/IP Server:请输入Mysql数据库的地址和端口,端口号默认是3306。
User:数据库的用户名
Password:数据库的密码
Database:要连接的数据库
配置完成之后请,点击“Test”按钮进行测试,如果提示“Connection successful”表明连接成功。最后点击“OK”就可以了。
4. 打开LR,创建一个Web HTTP/HTML脚本。创建一个新Action名字为:ConnMysql,内容如下:
ConnMysql()
{
//定义两个变量
int i=1,NumRows;
//创建数据库连接
lr_db_connect("StepName=conMysql",
"ConnectionName=mysqlData",
"ConnectionString=Driver={MySQL ODBC 5.2 Unicode Driver};Server=192.168.16.34;Database=mysql_jforum;Trusted_Connection=False;User=root;Password=root",
"ConnectionType=ODBC",
LAST);
lr_start_transaction("sql");
//查询结果保存在Users数据集中,并返回查询记录数据总条数,存入NumRows中
NumRows=lr_db_executeSQLStatement("StepName=findUsers",
"ConnectionName=mysqlData",
"SQLStatement=SELECT * FROM jforum_users;",
"DatasetName=Users",
LAST);
lr_output_message("The query returned %d rows.", NumRows);
//循环打印所有记录的id、userName、password三个字段。
while(i<NumRows){
lr_db_getvalue("StepName=showUsersID",
"DatasetName=Users",
"Column=user_id",
"Row=next",
"OutParam=userId",
LAST);
lr_db_getvalue("StepName=showUsers",
"DatasetName=Users",
"Column=username",
"Row=next",
"OutParam=userName",
LAST);
lr_db_getvalue("StepName=showPassword",
"DatasetName=Users",
"Column=user_password",
"Row=next",
"OutParam=userPassword",
LAST);
lr_output_message(lr_eval_string("{userId}"));
lr_output_message(lr_eval_string("{userName}"));
lr_output_message(lr_eval_string("{userPassword}"));
i+=1;
}
lr_end_transaction("sql",LR_AUTO);
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
5. 运行脚本,我们可以看到们查询出数据库中的用户名和密码。如下所示:
6. 更新数据库jforum_user表:
UpdateUsers()
{
//创建数据库连接
int i=0,NumRows=0;
lr_db_connect("StepName=conMysql",
"ConnectionName=mysqlData",
"ConnectionString=Driver={MySQL ODBC 5.2 Unicode Driver};Server=192.168.16.34;Database=mysql_jforum;Trusted_Connection=False;User=root;Password=root",
"ConnectionType=ODBC",
LAST);
//更新用户hp_user321011的密码为123456
lr_db_executeSQLStatement("StepName=UpdateUsers",
"ConnectionName=mysqlData",
"SQLStatement=UPDATE jforum_users SET user_password=\"tester123456\" WHERE username=\"hp_user321011\"",
"DatasetName=Users",
LAST);
NumRows=lr_db_executeSQLStatement("StepName=findUsers",
"ConnectionName=mysqlData",
"SQLStatement=SELECT * FROM jforum_users WHERE username='hp_user321011'",
"DatasetName=Users",
LAST);
lr_output_message("The query returned %d rows.", NumRows);
lr_db_getvalue("StepName=findUserPassword",
"DatasetName=Users",
"Column=user_password",
"Row=next",
"OutParam=passWord",
LAST);
lr_output_message("用户hp_user321011的密码为: %s",lr_eval_string("{passWord}"));
lr_db_disconnect("StepName=disconectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
运行结果如下:
7. 增加用户表的记录:
AddUsers()
{
lr_db_connect("StepName=MysqlConn",
"ConnectionName=MysqlData",
"ConnectionString=Driver={MySQL ODBC 5.2 Unicode Driver};Server=192.168.16.34;Database=test;Trusted_Connection=False;User=root;Password=root",
"ConnectionType=ODBC",
LAST);
lr_db_executeSQLStatement("StepName=addUser",
"ConnectionName=MysqlData",
"SQLStatement=INSERT INTO user VALUES(4,'zhaoyun','123',1)",
"DatasetName=addUsers",
LAST);
lr_db_disconnect("StepName=disconectMysql",
"ConnectionName=MysqlData",
LAST);
return 0;
}
运行结果如下:
8. 删除用户脚本如下:
DeleteUsers()
{
lr_db_connect("StepName=ConnMysql",
"ConnectionName=mysqlData",
"ConnectionString=Driver={MySQL ODBC 5.2 Unicode Driver};Server=192.168.16.34;Database=test;Trusted_Connection=False;User=root;Password=root",
"ConnectionType=ODBC",
LAST);
lr_db_executeSQLStatement("StepName=deleteUsers",
"ConnectionName=mysqlData",
"SQLStatement=DELETE FROM user WHERE username='zhaoyun'",
"DatasetName=delUsers",
LAST);
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
运行结果如下:
9. 查询所有记录:( lr_db_dataset_action())
SearchUsers()
{
int i=1,NumRows;
//创建数据库连接
lr_db_connect("StepName=conMysql",
"ConnectionName=mysqlData",
"ConnectionString=Driver={MySQL ODBC 5.2 Unicode Driver};Server=192.168.16.34;Database=mysql_jforum;Trusted_Connection=False;User=root;Password=root",
"ConnectionType=ODBC",
LAST);
lr_start_transaction("sql");
//查询结果保存在Users数据集中,并返回查询记录数据总条数,存入NumRows中
NumRows=lr_db_executeSQLStatement("StepName=findUsers",
"ConnectionName=mysqlData",
"SQLStatement=SELECT * FROM jforum_users;",
"DatasetName=Users",
LAST);
lr_output_message("The query returned %d rows.", NumRows);
/*
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=SearchUsers",
"DatasetName=Users",
"Action=PRINT", //RESET、REMOVE、PRINT
LAST);
lr_end_transaction("sql",LR_AUTO);
//关闭数据库的链接
lr_db_disconnect("StepName=disconnectMysql",
"ConnectionName=mysqlData",
LAST);
return 0;
}
运行结果如下: