view plaincopy to clipboardprint?/*需要的表结构如下CREATE TABLE `test_data` (`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Order numbers. Must be unique.',`status` BOOL NOT NULL DEFAULT '0' COMMENT 'Whether data has been used or not. A value of 0 means FALSE.',`date_used` DATETIME NULL COMMENT 'Date/time that the data was used.',UNIQUE ( `order_id`)) ENGINE = innodb COMMENT = 'LoadRunnertestdata';*/Action(){int rc; int db_connection; //数据库连接int query_result; // 查询结果集 MYSQL_RESchar** result_row; // 查询的数据衕char *server = "localhost";char *user = "root";char *password = "123456";char *database = "test";int port = 3306;int unix_socket = NULL; int flags = 0; // 找到libmysql.dll的所在位置.rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQLServer5.1\\bin\\libmysql.dll");if (rc != 0) { lr_error_message("Could not load libmysql.dll"); lr_abort();}// 创建MySQL对象db_connection = mysql_init(NULL);if (db_connection == NULL) { lr_error_message("Insufficient memory"); lr_abort();}// 连接到MySQL数据库rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);if (rc == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 向数据库插入数据// 此处的 {ORDER_ID} 是一个参数，简单测试时可以用一个常数代替lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery"); rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 从数据库读取一个数据并显示rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 如果结果集包含多行数据，需要多次调用 mysql_fetch_row 直到返回NULLresult_row = (char **)mysql_fetch_row(query_result); if (result_row == NULL) { lr_error_message("Did not expect the result set to be empty"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 保存参数，用于删除这行数据lr_save_string(result_row[0], "paramOrderID");lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));mysql_free_result(query_result);// 在事务里更新一行数据，需要用InnoDB引擎rc = mysql_query(db_connection, "BEGIN"); //启动事务if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 使用 "FOR UPDATE" 锁住要更新的数据行rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE"); if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}result_row = (char **)mysql_fetch_row(query_result); if (result_row == NULL) { lr_error_message("没有查询到结果"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}lr_save_string(result_row[0], "paramOrderID");lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));mysql_free_result(query_result);lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id='{paramOrderID}'"),"paramUpdateQuery");rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}rc = mysql_query(db_connection, "COMMIT"); // 提交事务if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 再次查找数据，应该为空了，因为前面的事务更新了标志rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}result_row = (char **)mysql_fetch_row(query_result);if (result_row == NULL) { lr_output_message("Result set is empty as expected"); mysql_free_result(query_result);} else { lr_error_message("Did not expect the result set to contain any rows"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 删除数据lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = '{paramOrderID}'"),"paramDeleteQuery");rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 释放MySQL资源mysql_close(db_connection); return 0;}/*需要的表结构如下CREATE TABLE `test_data` (`order_id` BIGINT UNSIGNED NOT NULL COMMENT 'Order numbers. Must be unique.',`status` BOOL NOT NULL DEFAULT '0' COMMENT 'Whether data has been used or not. A value of 0 means FALSE.',`date_used` DATETIME NULL COMMENT 'Date/time that the data was used.',UNIQUE ( `order_id`)) ENGINE = innodb COMMENT = 'LoadRunner test data';*/Action(){int rc;int db_connection; // 数据库连接int query_result; // 查询结果集 MYSQL_RESchar** result_row; // 查询的数据衕char *server = "localhost";char *user = "root";char *password = "123456";char *database = "test";int port = 3306;int unix_socket = NULL;int flags = 0;// 找到libmysql.dll的所在位置.rc = lr_load_dll("C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\libmysql.dll");if (rc != 0) { lr_error_message("Could not load libmysql.dll"); lr_abort();}// 创建MySQL对象db_connection = mysql_init(NULL);if (db_connection == NULL) { lr_error_message("Insufficient memory"); lr_abort();}// 连接到MySQL数据库rc = mysql_real_connect(db_connection, server, user, password, database, port, unix_socket, flags);if (rc == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 向数据库插入数据// 此处的 {ORDER_ID} 是一个参数，简单测试时可以用一个常数代替lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({ORDER_ID})"),"paramInsertQuery");rc = mysql_query(db_connection, lr_eval_string("{paramInsertQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 从数据库读取一个数据并显示rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 如果结果集包含多行数据，需要多次调用 mysql_fetch_row 直到返回NULLresult_row = (char **)mysql_fetch_row(query_result);if (result_row == NULL) { lr_error_message("Did not expect the result set to be empty"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 保存参数，用于删除这行数据lr_save_string(result_row[0], "paramOrderID");lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));mysql_free_result(query_result);// 在事务里更新一行数据，需要用InnoDB引擎rc = mysql_query(db_connection, "BEGIN"); //启动事务if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 使用 "FOR UPDATE" 锁住要更新的数据行rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1 FOR UPDATE");if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}result_row = (char **)mysql_fetch_row(query_result);if (result_row == NULL) { lr_error_message("没有查询到结果"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}lr_save_string(result_row[0], "paramOrderID");lr_output_message("Order ID is: %s", lr_eval_string("{paramOrderID}"));mysql_free_result(query_result);lr_save_string(lr_eval_string("UPDATE test_data SET status=TRUE, date_used=NOW() WHERE order_id='{paramOrderID}'"),"paramUpdateQuery");rc = mysql_query(db_connection, lr_eval_string("{paramUpdateQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}rc = mysql_query(db_connection, "COMMIT"); // 提交事务if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 再次查找数据，应该为空了，因为前面的事务更新了标志rc = mysql_query(db_connection, "SELECT order_id FROM test_data WHERE status IS FALSE LIMIT 1");if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}query_result = mysql_use_result(db_connection);if (query_result == NULL) { lr_error_message("%s", mysql_error(db_connection)); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}result_row = (char **)mysql_fetch_row(query_result);if (result_row == NULL) { lr_output_message("Result set is empty as expected"); mysql_free_result(query_result);} else { lr_error_message("Did not expect the result set to contain any rows"); mysql_free_result(query_result); mysql_close(db_connection); lr_abort();}// 删除数据lr_save_string(lr_eval_string("DELETE FROM test_data WHERE order_id = '{paramOrderID}'"),"paramDeleteQuery");rc = mysql_query(db_connection, lr_eval_string("{paramDeleteQuery}"));if (rc != 0) { lr_error_message("%s", mysql_error(db_connection)); mysql_close(db_connection); lr_abort();}// 释放MySQL资源mysql_close(db_connection);return 0;}