MYSQL mysql;mysql_init(&mysql);
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"call pr_add()"); //执行无返回存储过程mysql_query(&mysql,"delete from DB_ACCUNT where acc_plat='address'"); //这是再执行查询或删除,修改无效??
mysql_close(&mysql);/////////////////////////////////////////////////
如果是这样:
MYSQL mysql;mysql_init(&mysql);
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"call pr_add()"); //执行无返回存储过程
mysql_close(&mysql); //执行操作存储过程后关闭一次数据库mysql_init(&mysql); //再重新链接打开数据库
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"delete from DB_ACCUNT where acc_plat='address'"); //这是再执行查询或删除,修改没问题
mysql_close(&mysql);
.....问题是:如果不执行存储操作,,,所有的查询或删除,修改没问题,,,
如果不存储操作,后不关闭数据库,重新链接 所有的查询或删除,修改不执行,,,
问题在哪里?
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"call pr_add()"); //执行无返回存储过程mysql_query(&mysql,"delete from DB_ACCUNT where acc_plat='address'"); //这是再执行查询或删除,修改无效??
mysql_close(&mysql);/////////////////////////////////////////////////
如果是这样:
MYSQL mysql;mysql_init(&mysql);
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"call pr_add()"); //执行无返回存储过程
mysql_close(&mysql); //执行操作存储过程后关闭一次数据库mysql_init(&mysql); //再重新链接打开数据库
mysql_real_connect(&mysql,"localhost","root","","billingdb",0,NULL,CLIENT_MULTI_STATEMENTS);
mysql_query(&mysql,"delete from DB_ACCUNT where acc_plat='address'"); //这是再执行查询或删除,修改没问题
mysql_close(&mysql);
.....问题是:如果不执行存储操作,,,所有的查询或删除,修改没问题,,,
如果不存储操作,后不关闭数据库,重新链接 所有的查询或删除,修改不执行,,,
问题在哪里?
#include <iostream>
#include <windows.h>
#include <mysql.h>
#include <string>
static const char host[32] = "localhost";
static const char user[32] = "test";
static const char passwd[32] = "passwd";
static const char db[32] = "test";
/**
mysql> select * from t;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> delimiter //
mysql> create procedure get_t(in t1 int)
-> begin
-> select id from t where id=t1;
-> end
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> call get_t(1);
-> //
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
*/
void test_more_results(MYSQL* h)
{
char str[512] = "insert into test_num values(101);insert into test_num values(122);commit;";
int r = mysql_real_query(h, str, strlen(str));
if (r)
{
const char * error = mysql_error(h);
std::cout<<"*** Connection Error " << error << std::endl;
}
do
{
MYSQL_RES* res = mysql_store_result(h);
mysql_free_result(res);
}
while ( (0 == mysql_next_result(h)) );
}
void test_proc_stmt(MYSQL* h)
{
MYSQL* mysql_ = h;
MYSQL_BIND bind;
MYSQL_BIND obind[1];
// test_more_results(mysql_);
MYSQL_STMT *hStmt = mysql_stmt_init(mysql_);
my_bool true_value= 1;
mysql_stmt_attr_set(hStmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &true_value);
char sql[] = "call get_t(?)";
//char sql[] = "select id from t where id=?";
if (mysql_stmt_prepare(hStmt, sql, strlen(sql)))
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_stmt_reset(hStmt);
if (mysql_stmt_prepare(hStmt, sql, strlen(sql)))
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_close(mysql_);
exit( -1);
}
}
int id = 1;
unsigned long id_len = 0;
memset(&bind, 0, sizeof(bind));
bind.buffer_type = FIELD_TYPE_LONG;
bind.buffer = (void*)&id;
bind.is_unsigned = true;
bind.length = &id_len;
// bind[0].buffer_length = sizeof(id);
// bind[0].is_null = 0;
if (mysql_stmt_bind_param(hStmt,(MYSQL_BIND*)(&bind)) != 0)
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_close(mysql_);
exit( -1);
}
if (mysql_stmt_execute(hStmt) != 0)
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_close(mysql_);
exit( -1);
}
int t2;
memset(obind, 0, sizeof(obind));
obind[0].buffer_type= MYSQL_TYPE_LONG;
obind[0].buffer= (char *)&t2;
obind[0].buffer_length = sizeof(t2);
if (mysql_stmt_bind_result(hStmt, (MYSQL_BIND*)&obind[0]) != 0)
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_close(mysql_);
exit( -1);
}
if ( mysql_stmt_store_result(hStmt) != 0 )
{
std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl;
mysql_close(mysql_);
exit( -1);
}
int rows = mysql_stmt_num_rows(hStmt);
for (int i=0; i<rows; i++)
{
if (mysql_stmt_fetch(hStmt) == 0)
{
std::cout<<"id = "<<t2<<std::endl;
}
}
mysql_stmt_free_result(hStmt);
mysql_stmt_close(hStmt);
}
//
// Just for demo only.
//
int main()
{
MYSQL* mysql_ = NULL;
MYSQL_RES* result_ = NULL;
MYSQL_ROW row_;
mysql_ = mysql_init(mysql_);
// if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
{
const char * error = mysql_error(mysql_);
std::cout<<"*** Connection Error " << error << std::endl;
return -1;
}
mysql_autocommit(mysql_, false);
std::string encodeStr = "set names 'gbk'";
mysql_real_query(mysql_, encodeStr.c_str(), encodeStr.size());
/*
const char* tmpTableName = "t"; // assume you are querying the table 't'
char str[512];
int cnt = 0;
sprintf(str,"select count(*) as cnt from %s", tmpTableName);
mysql_real_query(mysql_, str, strlen(str));
result_ = mysql_store_result(mysql_);
while (row_ = mysql_fetch_row(result_))
{
// get the field value
if (row_[0])
{
std::cout<<"count = "<<row_[0]<<std::endl;
// convert it into int
cnt = atoi(row_[0]);
std::cout<<"cnt value = "<<row_[0]<<std::endl;
}
}
mysql_free_result(result_);
test_more_results();
*/
test_proc_stmt(mysql_);
do
{
MYSQL_RES* res = mysql_store_result(mysql_);
mysql_free_result(res);
}
while ( (0 == mysql_next_result(mysql_)) );
test_proc_stmt(mysql_);
mysql_close(mysql_);
return 0;
}