C语言调用mysql存储过程出错

             http://dev.mysql.com/doc/refman/5.1/en/call.html

              http://sswin0922.blog.163.com/blog/static/95115415201002554341619/

 

 

mysql>select * from first;

+----+--------+------+------+

|id    | name   | age   |  sex  |

------+--------+------+-------+

|  1   | jing     |   21   |    f    |

---------------------------------

|   2  |  gao     |   21   |    m   |

---------------------------------

|   3  |  mou    |   12   |    f   

存储过程语句如下：（区分大小写）

mysql>DELIMITER $$

mysql>CREATE PROCEDURE sp(age_i INT)

       ->BEGIN

       ->SELECT *

       ->FROM * first

       ->WHERE age=age_i;

       ->END $$

mysql>DELIMITER ;

在mysql里调用这个存储函数 mysql>CALL sp(12);

+----+--------+------+------+

|id    | name   | age   |  sex  |

------+--------+------+-------+

3         mou      12       f

 

现在想用C语言调用这个存储过程。代码如下。

#include <windows.h>

   #include <stdio.h>

   #include <string.h>

   #include <mysql.h>

   //#include "winsock2.h"   

void main()

{

       MYSQL my_connection;

       MYSQL_RES *res_ptr;

       MYSQL_ROW sqlrow;

       mysql_init(&my_connection);

       if(mysql_real_connect(&my_connection, "localhost", "root","0","abccs", 0, NULL, 0))

       {

              printf("Connection success/n");

       if(mysql_query(&my_connection, "call sp(12)"))

              {

                     printf("select error: %s/n", mysql_error(&my_connection));

              }

              else

              {

                     res_ptr = mysql_store_result(&my_connection);

                     if(res_ptr)

                     {

                            printf("Retrieved %lu rows/n", (unsigned long)mysql_num_rows (res_ptr));         

                            while (sqlrow = mysql_fetch_row(res_ptr))//每次输出每一行

                            {  

                                   unsigned int field_count;

                                   field_count =0;

                                   while(field_count < mysql_field_count(&my_connection))//列

                                   {

                                          printf("%s ", sqlrow[field_count]);field_count++;

                                   }

                                   printf("/n");//换行

                            }

                            if(mysql_errno(&my_connection))

                            {

                                   printf("Retrive error : %s/n", mysql_error(&my_connection));

                            }

                     }

              }

              mysql_free_result(res_ptr);

       }

     mysql_close(&my_connection);

}

出现错误：select error: PROCEDURE abccs.sp can’t return a result set in the given context

 

有两个错误原因，一是存储过程的参数设置不对，它的默认形式是IN，而我的目的是需要它返回结果给调用它的函数，所以应该是OUT或INOUT。二是mysql_query()语句。 

存储过程的正确定义是：

mysql->DELIMITER $$

mysql->CREATE   PROCEDURE p(IN age_i  INT,OUT  ming VARCHAR(25))

          ->BEGIN

          ->SELECT name  into ming  from  first   where  age=age_i;

          ->END  $$

mysql->DELIMITER ;

C语言代码：

#include <windows.h>

   #include <stdio.h>

   #include <string.h>

   #include <mysql.h>

void main()

{

       MYSQL my_connection;

       MYSQL_RES *res_ptr;

MYSQL_ROW sqlrow;

       mysql_init(&my_connection);//初始化mysql句柄

       if(mysql_real_connect(&my_connection, "localhost", "root","0","abccs", 0, NULL, 0))//建立连接

       {

              printf("Connection success/n");

           if(mysql_query(&my_connection, "call p(12,@ming)"))

              {

                     printf("select error: %s/n", mysql_error(&my_connection));

              }

              else

              {   mysql_query(&my_connection,"SELECT @ming");

                     res_ptr = mysql_store_result(&my_connection);

                     if(res_ptr)

                     {

                            printf("Retrieved %lu rows/n", (unsigned long)mysql_num_rows (res_ptr));         

                            while (sqlrow = mysql_fetch_row(res_ptr))//每次输出每一行

                            {  

                                   unsigned int field_count;

                                   field_count =0;

                                   while(field_count < mysql_field_count(&my_connection))//列

                                   {

                                          printf("%s ", sqlrow[field_count]);field_count++;

                                   }

                                   printf("/n");//换行

                            }

                            if(mysql_errno(&my_connection))

                            {

                                   printf("Retrive error : %s/n", mysql_error(&my_connection));

                            }

                     }

              }

              mysql_free_result(res_ptr);//释放资源

       }

     mysql_close(&my_connection);//关闭连接

}