Re: Documentation for calling stored procedures via C API

[Paul DuBois]

At 2:03 PM +0100 3/7/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
>
>just wondering if you had any news back on this. I'd
>like to get the calling of stored procedures via the C
>API documented (glad to make a draft myself if it helps)
>so that then the inclusion of mysql stored procedure
>support can be considered by postfix developers
>based on an officailly documented approach.

Johh,

Okay, I have the information how this works now.  I'll update the
manual with this information.

Results from a stored procedure have these characteristics:

- Statements within the procedure may produce result sets
(for example, if it executes SELECT statements). These
result sets are returned in the order that they are
produced as the procedure executes.

- In general, the caller cannot know how many result sets
a procedure will return. Procedure execution may depend on
loops or conditional statements that cause the execution
path to differ from one call to the next. Therefore,
you must be prepared to retrieve multiple results.

- The final result from the procedure is a status result
that includes no result set. The status indicates whether
the procedure succeeded or an error occurred.

If you have other questions, please let me know.  Thanks.

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com