mysql C API 调用存储过程_使用c api创建存储过程-优快云博客

本文详细介绍了如何通过MySQL C API调用存储过程，并讨论了调用过程中的一些关键点，包括结果集的顺序、状态判断以及处理结果的方法。通过示例代码演示了如何正确地获取和解析存储过程的返回结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Subject:	Re: Documentation for calling stored procedures via C API
From:	Paul DuBois (pa...@mysql.com)
Date:	Feb 28, 2008 9:14:09 am
List:	com.mysql.lists.mysqldoc

At 6:01 PM +0100 2/28/08, John Fawcett wrote:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

Paul DuBois wrote:

At 5:04 PM +0100 2/28/08, John Fawcett wrote:

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

There has been some discussion on the postfix mailing list about adding support for mysql stored procedure calls to postfix.

However, one of the stumbling blocks is the lack of documentation on calling stored procedures via the C API.

While it is clear from the docs that the connection has to allow multiple result sets, it is not stated how many result sets may be returned and in which order the result sets are returned. Is the result set which provides overall status of the stored procedure guaranteed to be always the last one? What is the correct way of determining via the C API that the stored procedure terminated correctly or in error.

While all these questions can be answered from empirical evidence, before adding support for called procedures to postfix, there needs to be clarity around the documented API beahviour.

If it's a question of writing some modifications to the official mysql documentation, I'd be happy to contribute a modification, but is there a process which can verify the correctness of a submission?

thanks for any help or advice.

John

Hi John,

Use this page:

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Result sets will be returned in the order that the stored procedure generates them.

I'll pose your other questions to the developers.

The last status will not contain a result *set*, by the way.

Paul thanks for the quick reply. I had used the reference above to write a test program to see what mysql returns on stored procedure call differently to queries (attached here for reference).

If my stored procedure contains a single SELECT statement, after I call mysql_store_result to get the result set for the SELECT statement, then the next call to mysql_next_result returns 0 (indicating futher results), however mysql_store_result does not return more results, mysql_errno() = 0 and affected rows = 0.

I suppose it boils down to understanding whether the result without the result set will always be present and will always be the last result and how to intepret success/failure of the procedure.

thanks again.

Yes, this is why you go through the "did current statement return data?" process for each result. A "result" may or may not include a result set.

John -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4-svn0 (GNU/Linux) Comment: Using GnuPG with SUSE -http://enigmail.mozdev.org

iD8DBQFHxuj3d4I3jTtt9EIRAi5OAKCFoNWxM2GWmoVgUVBFjS3W8e2KqwCgoorP dlEBXsmKeQq0wqt+VlS0Pyo= =C75l -----END PGP SIGNATURE-----

#include <sys/socket.h>
#include <netinet/in.h> 
#include <arpa/inet.h>
#include <netdb.h> 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <syslog.h> 
#include <time.h>
#include <mysql.h> 

int main(int argc, char **argv) 
{
	char *host_name="localhost";
	char *user_name="xxxxxxx"; 
	char *password="xxxxxxxx"; 
	char *db_name="dbmail"; 
	int status = 0; 
	MYSQL *mysql; 
	MYSQL_RES *result = 0; 
	char *query1 = "SELECT alias FROM dbmail_aliases WHERE alias='xx...@example.com'"; 
	char *query2 = "call test('xx...@example.com')"; 
	char *query; 
	if ((mysql = mysql_init(NULL)) == NULL) 
		printf("dict_mysql: insufficient memory\n"); 
	if (mysql_real_connect (mysql, host_name, user_name, password, db_name, 3306, 0, CLIENT_MULTI_STATEMENTS) == NULL) 
	{
		printf("mysql_real_connect() failed\n"); mysql_close(mysql); 
		exit(1);
	} 
	query = query1; 
	if(argc == 2 && strcmp("2",argv[1])==0) 
		query = query2; 
	printf("query %s\n",query); 
	if (mysql_query(mysql, query)) 
	{
		printf("mysql query failed: %s\n", mysql_error(mysql)); 
		exit(1);
	} 
	do
	{ /* did current statement return data? */
		result = mysql_store_result(mysql); 
		if (result)
		{ /* yes; process rows and free the result set */ 
			process_result_set(mysql, result); 
			mysql_free_result(result); 
		} 
		else /* no result set or error */ 
		{	printf("error no [%d] error[%s] info[%s]\n",mysql_errno(mysql),mysql_error(mysql),mysql_info(mysql));
			if (mysql_field_count(mysql) == 0) 
			{
				printf("%lld rows affected\n", mysql_affected_rows(mysql)); 
			} 
			else /* some error occurred */
			{
				printf("Could not retrieve result set\n"); 
				break; 
			}
		 } 
		/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */ 
		if ((status = mysql_next_result(mysql)) > 0) 
			printf("status [%d] error no [%d] error[%s] info[%s]\n",status,mysql_errno(mysql),mysql_error(mysql),mysql_info(mysql));
	} while (status == 0); 
	mysql_close(mysql); return 0; 
} 

int process_result_set(MYSQL * mysql,MYSQL_RES * result) 
{
	int numrows =0;
	int i = 0;
	int j = 0;
	MYSQL_ROW row; 
	numrows = mysql_num_rows(result);
	printf("retrieved %d rows\n", numrows); 
	if (numrows == 0) 
	{
		return 0;
	} 
	for (i = 0; i < numrows; i++) 
	{
		row = mysql_fetch_row(result); 
		for (j = 0; j < mysql_num_fields(result); j++)
		{ 
			printf("row [%d] col [%d] result [%s]\n",i,j,row[j]); 
		}
	} 
	return 0; 
}