Bug #37068 use the c api call the procedure Error!!
Submitted: 29 May 2008 12:12 Modified: 31 May 2008 23:19
Reporter: zmcsdh zmcsdh Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.1.23 5.1.24 OS:Other (OPENBSD)
Assigned to: CPU Architecture:Any
Tags: call api

[29 May 2008 12:12] zmcsdh zmcsdh
Description:
/*c program start */
#include<stdlib.h>
#include<mysql.h>
#include<string.h>
#include<stdio.h>
main()
{
 MYSQL mysql;
 MYSQL_RES *res;
 MYSQL_ROW row;
 int i;
 int j;
 char inp[100];
 mysql_init(&mysql);
 mysql_real_connect(&mysql,"192.168.0.104","root","","test",NULL,NULL,0);
while(1)
{
 printf("\n\nPlease Enter The SQL:");
  gets(&inp);
  strcpy(inp,"call look_the_main('000060',null)");
 if(mysql_query(&mysql,&inp))
 {
    printf("Error!!\n");
       return;
 }
 res=mysql_store_result(&mysql);
 j=mysql_num_fields(res);
 while(row=mysql_fetch_row(res))
 {
       for(i=0;i<j;i++)
         printf("%s\t",row[i]);
       printf("\n");
 }
 mysql_free_result(res);
}
   mysql_close(&mysql);
}

/*end the c program*/

/*create the table/*
drop table day_list;

create table day_list ( day_id int primary key auto_increment , day_code char(6),day_day date,day_time time, day_price double(7,3),day_quantity int,day_flag char(1));
/*end */

/*prodedure def*/
delimiter //
drop procedure look_the_main;//

create procedure look_the_main(num char(6),quan int)

begin 
declare qu int;

if quan is null
then
select 10000/(sum(day_price)/count(day_price)) into quan from day_list where day_code=num;

end if ;

select day_code,day_day,sum(day_price)/count(day_price) as ad_price ,sum(day_quantity) as quantity ,count(*) as count ,quan ,day_flag from day_list where day_flag='B' and day_quantity >= quan and day_code=num group by day_day;
select day_code,day_day,sum(day_price)/count(day_price) as ad_price ,sum(day_quantity) as quantity ,count(*) as count ,quan ,day_flag from day_list where day_flag='S' and day_quantity >= quan and day_code=num group by day_day;

end ;
//

/* end def*/

insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.03,273,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.16,433,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.16,3,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.25,10,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.25,2,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:30',25.4,1,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,4,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,26,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,16,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.43,45,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.57,14,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.5,185,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.52,21,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.56,18,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.58,52,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:31',25.5,113,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.57,41,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,44,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.52,60,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,24,'B');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.55,23,'S');
insert into day_list(day_code,day_day,day_time,day_price,day_quantity,day_flag) values ('000060','20080527','09:32',25.56,20,'B');

How to repeat:
!1

Suggested fix:
!!
[29 May 2008 12:55] Paul DuBois
You might need to modify mysql_real_connect().

http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-syntax.html:

"
MySQL supports the very useful extension that allows the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1. The client should also specify the CLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with the mysql_real_connect() C API function. See Section 24.2.3.52, “mysql_real_connect()”, and Section 24.2.9, “C API Handling of Multiple Statement Execution”.
"

Additional information:
http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
[31 May 2008 18:25] Sveta Smirnova
Thank you for the report.

Set as "Not a Bug", because reason Paul DuBois described in the comment above.
[31 May 2008 23:19] zmcsdh zmcsdh
thx