如何使用mysql 提供的c api来调用sp呢？这是这几天一直研究的问题，其实本周以来一直研究的就是mysql的sp问题，还好通过今天的加班最终终于把它给搞定了．
5.1放假回来，周一开始网上搜集资料，准备写一个树型查询的sp,由于休假时间可能稍微长了一点，成效不大，昏昏沉沉的过去了；同样第二天也这样过去了．
呵呵，周三开始正式的写sp，幸好找到了一个示例，修修改改的就开始测试了，中间遇到了很多问题，google了半天终于把问题一个一个给解决了，其中就设置recursion的深度值这个问题就耗费了我很长很长的时间；不过最后终于还是完成了．下面就是自己写的针对Agent table执行树型查询的sp:
支持从上向下进行树型查询的sp代码：(当然稍微修改一下就可以实现从下向上的树型查询功能)
==========================================================
CREATE PROCEDURE agent_tree(start_with VARCHAR(16))
proc:
BEGINDECLARE temporary_table_exists BOOLEAN;
BEGINDECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
DROP TABLE IF EXISTS Temporary_Table;
END;
BEGINDECLARE v_account_number VARCHAR(16);
DECLARE v_name VARCHAR(24);
DECLARE v_parent_id VARCHAR(16);
DECLARE done, error BOOLEAN DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONSET error = TRUE;
CREATE TEMPORARY TABLE Temporary_Table(Account_number VARCHAR(16),Name VARCHAR(24), Parent_id VARCHAR(16));
IF error THENSELECT 'CREATE TEMPORARY failed';
LEAVE proc;
END IF;
SET temporary_table_exists=TRUE;
SELECT Account_number, Name, Parent_idINTO v_account_number, v_name, v_parent_id FROM AgentWHERE Account_number = start_with limit 1;
IF error THENSELECT 'First SELECT failed';
LEAVE proc;
END IF;
IF v_account_number IS NOT NULL THENINSERT INTO Temporary_Table VALUES(v_account_number, v_name, v_parent_id);
IF error THENSELECT 'First INSERT failed';
LEAVE proc;
END IF;
CALL agent_tree2(v_account_number);
IF error THENSELECT 'First CALL agent_tree2() failed';
END IF;
END IF;
SELECT Account_number, Name, Parent_idFROM Temporary_Table;
IF error THENSELECT 'Temporary SELECT failed';
LEAVE proc;
END IF;
END;
IF temporary_table_exists THENDROP TEMPORARY TABLE Temporary_Table;
END IF;
END;
CREATE PROCEDURE agent_tree2 (start_with VARCHAR(16))
proc:BEGIN
DECLARE v_account_number VARCHAR(16);
DECLARE v_name VARCHAR(24);
DECLARE v_parent_id VARCHAR(16);
DECLARE done, error BOOLEAN DEFAULT FALSE;
DECLARE c CURSOR FORSELECT Account_number, Name, Parent_idFROM Agent WHERE Parent_id = start_with;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONSET error = TRUE;
OPEN c;
IF error THENSELECT 'OPEN failed';
LEAVE proc;
END IF;
REPEATSET v_account_number=NULL;
FETCH c INTO v_account_number, v_name, v_parent_id;
IF error THENSELECT 'FETCH failed';
LEAVE proc;
END IF;
IF done=FALSE THENINSERT INTO Temporary_Table VALUES(v_account_number, v_name, v_parent_id);
IF error THENSELECT 'INSERT in agent_tree2() failed';
END IF;
CALL agent_tree2(v_account_number);
IF error THENSELECT 'Recursive CALL agent_tree2() failed';
END IF;
END IF;
UNTIL done = TRUEEND REPEAT;
CLOSE c;
IF error THENSELECT 'CLOSE failed';
END IF;
END;
===============================================
下面看结果吧：
mysql> select * from Agent;
+-----------+----------------+--------+-----------+--------+--------------+
Unique_id Account_number Name Parent_id Active Phone_number
+-----------+----------------+--------+-----------+--------+--------------+
1 111111 host 000000 y 10000
2 111112 Agent1 111111 y 10001
3 111113 Agent2 111111 y 10002
4 111114 Agent3 111111 y 10003
5 111115 Agent4 111112 y 10004
6 111116 Agent5 111113 y 10005
7 111117 Agent6 111115 y 10006
+-----------+----------------+--------+-----------+--------+--------------+
7 rows in set (0.00 sec)
mysql> call agent_tree('111111');
+----------------+--------+-----------+
Account_number Name Parent_id
+----------------+--------+-----------+
111111 host 000000
111112 Agent1 111111
111115 Agent4 111112
111117 Agent6 111115
111113 Agent2 111111
111116 Agent5 111113
111114 Agent3 111111
+----------------+--------+-----------+
7 rows in set (0.88 sec)
Query OK, 0 rows affected, 1 warning (0.90 sec)
mysql>
在完成了该部分功能之后，就需要写一段C代码来call 这个sp程序，所以又开始研究这个问题．
刚开始准备实现这个功能的时候，自己是一头的雾水，完全不知道从哪里下手？在google 很久没有找到答案(而且刚开始也不知道问什么东西，可能已经找到答案，却被忽视了),所以开始在CU上发帖子咨询，等了几天没有人回答，这时候突然来到了mysql 的论坛，找到C/C++版开始读帖子，慢慢的发现了解决办法．(所以解决问题还是要找对地方，世界上不可能就我一个人在做这个东西的)．好了，下边直接贴代码吧：
#include
#include
#include
#include
#include
#include
#include
#include
#include
int main(int argc, char* argv[])
{
MYSQL handle;
mysql_init(&handle);
if(&handle == NULL){
printf("mysql_init error\n");
return -1;
}
if(!mysql_real_connect(&handle, "localhost", "root", NULL, "Geopacket", 0, NULL,CLIENT_MULTI_STATEMENTS CLIENT_MULTI_RESULTS)){
printf("mysql_real_connect error\n");
mysql_close(&handle);
return -1;
}
const char* sqlQuery = "SET @@max_sp_recursion_depth=3; SET @msg = '111111'; CALL agent_tree(@msg);";
if(mysql_real_query(&handle, sqlQuery, (unsigned long) strlen(sqlQuery))){
printf("mysql_real_query error\n");
mysql_close(&handle);
return -1;
}
printf("mysql_real_query: %s\n", sqlQuery);
MYSQL_RES* result=NULL;
do{
printf("=================================\n");
/* store the resultset */
result = mysql_store_result(&handle);
if(NULL == result){ /* if the resultset was null, 'continue' and get next resultset. */
printf("Empty resultset retrieved\n");
continue;
}
/* retrieve the first row */
MYSQL_ROW row = NULL;
while(row = mysql_fetch_row(result)) {
MYSQL_FIELD *mysqlFields = mysql_fetch_fields(result);
const char* field_name = mysqlFields[0].name;
/* get the length of the data of the first row in the 1st column of the resultset. */
unsigned long* field_lengths = mysql_fetch_lengths(result);
unsigned long field_length = field_lengths[0];
/* copy the data to a null terminated string buffer. */
char* fieldData = (char *)malloc(field_length + 1);
memset(fieldData, '\0', field_length + 1);
memcpy(fieldData, row[0], field_length);
/* output the results. */
printf("%s: %s.\n", field_name, fieldData);
/* perform memory cleanup. */
free(fieldData);
}
mysql_free_result(result);
}while(mysql_next_result(&handle) == 0);
/* free the resultset. */
mysql_free_result(result);
mysql_close(&handle);
return 0;
}
同样，下边是代码执行结果：
#gcc -I/usr/include/mysql 1.c -L/usr/lib/mysql -lmysqlclient -lpthread -lz -o 1.out
# ./1.out
goodmysql_real_query: SET @@max_sp_recursion_depth=3; SET @msg = '111111'; CALL agent_tree(@msg);
=================================
Empty resultset retrieved
=================================
Empty resultset retrieved
=================================
Account_number: 111111.
Account_number: 111112.
Account_number: 111115.
Account_number: 111117.
Account_number: 111113.
Account_number: 111116.
Account_number: 111114.
=================================
Empty resultset retrieved
#
其中在编写该段代码遇到的一些问题，在另外一个帖子中贴出来吧．
