C Api and Stored Procedures

Hey all,

i've been looking into using stored proecdures, and have got some up and running correctly from the command line,
i need to use them from within a c program using the c api.
i have the c api set up correctly, and connect to the db and can run queries fine.
but if i change my query to calling a stored procedure it doesn't work:

"mysql_real_query( mysql, query, strlen(query) )",
query = "CALL myStoredProc(21);"

can the c api even call a stored proc? i thought this would be okay as running a query is running a mysql query, and i assume this will execute any mysql statement.

however my stored proc ALWAYS returns 0 rows affected.

the help docs have something called prepared statements - though i think these are different from precompiled stored procs.

any help/advice is appreciated, cheers,
Craig

okay,
the stored proc is being called,
but the error returned is:
SELECT statments in stored procedures must have INTO

this seems to mean i can only retreive ONE row at a time.

how can i retreive full result sets?

cursors?(being fairly noobish to mysql, i have never used these - heard they are slow though, is this the case?)

In order to return result sets from a stored procedure you need to make sure that you set CLIENT_MULTI_RESULTS in your mysql_real_connect call. That is because if a stored procedure is returning stuff from a SELECT (rather than INTO a variable) then it might return multiple result sets.

If you do that, and use the multiple result set handling you should be fine. See http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html for more info.

hey harrison,
that worked great - exactly what i wanted.

i would think that returning multiple result sets from a stored procedure using the c api would be a well documented thing, but i've been at this a day or two at this stage.

even the link you posted about multiple statement execution isn't exactly what i was looking for (though the CLIENT_MULTI_RESULTS was, thanks).

Ignoring the c-api thing for a minute,
from reading documentation i think that its not even obvious that you CAN return a result set from a stored procedure - in fact i posted in one or two other forums and was told i couldn't return more than one row at a time from a stored procedure, and then only by explicitly stating a matching param for each field, in the CREATE PROCEDURE name (OUT field1 INT, OUT field2 VARCHAR(100), etc...).

having said that the first post at the bottom of the stored procedures section in the online manual says you can:
http://dev.mysql.com/doc/mysql/en/stored-procedures.html
i just couldn't figure out how to 'select' and use the resultset.

thanks again harrison!
Craig