[Devel] [ openser-Bugs-1618322 ] avp_db_query cannot call mysql stored procedures

[Kobi Eshun]

Hi,

So it seems there are (at least) three issues here RE avp_db_query()  
script-level API:

	1 - mysql_real_connect() flags required to support stored procedure  
invocations that return one or more result sets may not be defined in  
MySQL 4;
	2 - all available result sets must be retrieved, or else the  
connection will become unusable;
	3 - result sets need to be mapped somehow to AVPs.

- I think that item 1 can be addressed with an 'ifndef' directive  
like this:

	#ifndef CLIENT_MULTI_RESULTS
	#define CLIENT_MULTI_RESULTS 0
	#endif

By the way, I found out by accident that if a stored procedure does  
not return any result set, that the existing API works just fine (at  
least so far).

- Item 2 is addressed by iterating over the result sets with  
mysql_next_result() until it returns NULL. I don't know how this  
would jibe with MySQL 4.

- It's not clear to me how to address item 3. There's an implicit  
assumption in avp_db_query() that the result of the query is an NxM  
set. In general, result sets don't conform to that assumption.  
Perhaps a script-level interface to retrieve the next result set, and  
also to flush any pending sets might be necessary?

Cheers,
--
kobi


On Dec 21, 2006, at 2:52 AM, Adrian Georgescu wrote:
> The flag is CLIENT_MULTI_RESULTS and you must also modify the code  
> that reads the results back because there are multiple result sets  
> returned by a stored procedure.


On Dec 21, 2006, at 4:20 AM, Dan Pascu wrote:

>
> It is not a guarantee that there are multiple result sets after you  
> call a
> stored procedure. In fact the number of result sets may be zero,  
> one or
> more than one, depending on what the stored procedure does.
>
> However the important part is that you retrieve all the result sets  
> that
> were generated, else the server may close the connection. Even if it
> doesn't close the connection effectively, if you do not retrieve  
> all the
> results sets, the following SQL queries will fail and the database
> connection will be basically inoperational.

Comment By: Daniel-Constantin Mierla (miconda)

Date: 2006-12-20 23:57

> Message:
> Logged In: YES
> user_id=1246013
> Originator: NO
>
> If I am not wrong, these new flags are available with MySQL 5.0 or
> greater. Could you adapt the patch so it detects mysql version and  
> sets the
> flags only when the version is appropriate? Otherwise, I guess,  
> will give
> compilation errors for older versions of MySQL.



Submitted By: vabdulla (vabdulla)
>
> Summary: avp_db_query cannot call mysql stored procedures
>
> Initial Comment:
> Hi,
>
> When calling a mysql5 stored procedure which returns a result set  
> like this:
> avp_db_query("CALL db.sp_name('$avp(s:param1)', "param2",'$oU')",  
> "$avp(s:rs1);$avp(s:rs2));
>
> I get the following error:
>
> 0(1245) submit_query: PROCEDURE db.sp_name can't return a result  
> set in the given context
> 0(1245) db_raw_query: Error while submitting query
> 0(1245) avpops:db_query_avp: error - cannot do the query
>
> After some research, I have found that this error come from the  
> client flags passed to mysql_real_connect() function.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://openser.org/pipermail/devel/attachments/20061222/29da9630/attachment.html