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
lr_db_dataset_action
lr_db_disconnect
lr_db_executeSQLStatement
lr_db_getValue

 

以下举例使用的数据为本地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<NumRows){
        lr_db_getvalue("StepName=showStudentID", 
            "DatasetName=StudentsSet", 
            "Column=id", 
            "Row=next", 
            "OutParam=studentId", 
            LAST);
        lr_db_getvalue("StepName=showStudentName", 
            "DatasetName=StudentsSet", 
            "Column=name", 
            "Row=current", 
            "OutParam=studentName", 
            LAST);
            
        lr_db_getvalue("StepName=showStudentBirthday", 
            "DatasetName=StudentsSet", 
            "Column=birthday", 
            "Row=current", 
            "OutParam=studentBirthday", 
            LAST);
    
        lr_db_getvalue("StepName=showStudentScore", 
            "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("{studentBirthday}"));
        lr_output_message(lr_eval_string("{studentScore}"));
        
        i=i+1;

    }
    
    //关闭数据库的链接
    lr_db_disconnect("StepName=disconnectMysql", 
        "ConnectionName=mysqlData", 
        LAST);
    
    
    return 0;
}

　　

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()读取单个学生的中文姓名时，打印出来中文名字只打印一半，中文被截断了，但是读取英文不存在该问题（如下图所示），目前尚未定位出原因及解决办法，欢迎评论去讨论~~~