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.
_______________________________________________
Devel mailing list
[email protected]
http://openser.org/cgi-bin/mailman/listinfo/devel
