Raised This Month: $ Target: $400
 0% 

[SQL] Calling stored procedures which return a result set


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Eiver
New Member
Join Date: May 2007
Old 05-05-2007 , 16:47   [SQL] Calling stored procedures which return a result set
Reply With Quote #1

Executing queries like the one below will always fail:

call MyStoredProc('Some input','Some more input')

The query above works only if the stored procedure does not return anything. If it returns something, it will always fail.

I tried using both DBI and SQLX - both have the same problem.
SQLX returns an error: PROCEDURE MyStoredProc can't return a result set in the given context

I know that other APIs (like for example PHP and its mysql_query()) also had this problem. However in PHP it was possible to set client options, so that it would accept result sets from a stored procedure.

Is there a way to get around this problem in AMX MOD X?

Last edited by Eiver; 05-05-2007 at 16:53.
Eiver is offline
Podunk
Senior Member
Join Date: Nov 2005
Location: Florida
Old 09-22-2007 , 19:39   Re: [SQL] Calling stored procedures which return a result set
Reply With Quote #2

I'm having the same problem

Quote:
[22 Nov 2006 12:44] Tonci Grgin
Hi Frank and thanks for helping us on MySQL connector/ODBC v5.

This is not a bug but correct and documented behavior:
If you write C programs that execute stored procedures with the CALL SQL statement, you
must set the CLIENT_MULTI_RESULTS flag when you call mysql_real_connect(), either
explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS. This is because each CALL
returns a result to indicate the call status, in addition to any results sets that might
be returned by statements executed within the procedure. To process the result of a CALL
statement, use a loop that calls mysql_next_result() to determine whether there are more
results. For an example, see Section 22.2.9, “C API Handling of Multiple Statement
Execution”.


ahh: http://bugs.alliedmods.net/index.php...ls&task_id=281
__________________
Check out my website at http://matthewmiller.info

Last edited by Podunk; 09-22-2007 at 19:47.
Podunk is offline
Send a message via MSN to Podunk
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:33.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode