-
-
-
-
-
-
-
-
-
Action()
{
int rc,counts;
int db_connection; //
数据库连接
int query_result; // 查询结果集 MYSQL_RES
char** result_row; // 查询的数据
char *server = "localhost";
char *user = "root";
char *password = "
test";
char *database = "test";
char * ExecutionHost;
int port = 3306;
int unix_socket = NULL;
int flags = 0;
// 找到libmysql.dll的所在位置.
rc = lr_load_dll("E:\\Program Files\\MySQL\\MySQL
Server 5.5\\lib\\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();
}
/*
//返回当前执行脚本主机
ExecutionHost=lr_get_host_name();
lr_output_message("Current Execution Script. Computer Name :%s",ExecutionHost);
*/
//此处使用for循环插入类似与工具中的迭代执行。
/*
for(counts=0;counts<2;counts++)
{
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id) VALUES ({paramOrderID})"),"paramInsertQuery");
lr_log_message("--------order_id is :%s",counts);
}
*/
// 向数据库插入数据,此处的 {ORDER_ID} 是一个参数化,status未参数化插入,简单
测试时可以用一个常数代替。把常量放在变量paramInsertQuery中
lr_save_string (lr_eval_string("INSERT INTO test_data (order_id,status) VALUES ({paramOrderID},2)"),"paramInsertQuery");
lr_log_message("--------order_id is :%s",lr_eval_string("{paramOrderID}"));
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 直到返回NULL
result_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;
}