> Further to this I've just noticed that the stored procedure worked
> before because the output resultset from it is the first result set and
> the second result set is empty. Not cleaning up the second (empty)
> result set appears to cause problems with subsequent calls on the same
> connection so maybe dbi_result_free needs to account for all of the
> contained result sets.
>
> chris
The following hack to dbd_free_query illustrates that this is indeed the
case. The driver now supports cleanup of the second result set from a
stored procedure call which previously caused instability in any
subsequent behaviour:
int dbd_free_query(dbi_result_t *result) {
if (result->result_handle) mysql_free_result((MYSQL_RES
*)result->result_handle);
+ // clean up any secondary resultsets
+
+ dbi_result_t *nestedresult;
+ MYSQL_RES *res;
+
+ while(mysql_next_result((MYSQL *)result->conn->connection) == 0) {
+ res = mysql_store_result((MYSQL
*)result->conn->connection);
+ nestedresult = _dbd_result_create(result->conn, (void
*)res, (res ? mysql_num_rows(res) : 0),mysql_affected_rows((MYSQL
*)result->conn->connection));
+ if(nestedresult->result_handle)
mysql_free_result((MYSQL_RES*)nestedresult->result_handle);
+ }
return 0;
}
I think modifying the API to support a return code of 2 from
dbi_result_next_row to indicate that the resultset is finished but there
are more resultsets and adding a dbi_result_next_results hook would
cover it given that empty resultsets could be skipped at the beginning
though if the second and fourth resultsets in a sequence are the only
ones populated there would need to be a way to interface to the
mysql_more_results call directly though these too could be skipped. I've
only used multi resultsets in the two instances covered here though -
stored procs and INSERT..SELECT LAST_AUTO_INSERT()
chris
>
> Chris Newman wrote:
>> Hi Markus apologies for the delayed response. I have a problem with my
>> build system with all the autoconf stuff but the CVS src from 20070313
>> looks spot on for the multiple connect opts.
>>
>> I've been writing test harnesses for a project I'm working on and
>> noticed that the classic INSERT... SELECT LAST_AUTO_INSERT() isn't
>> working. I had needed the CLIENT_MULTI_STATEMENTS connect opt for a
>> stored procedure before but to retrieve the multi result set I've just
>> added a quick hack to the original 0.8.2-1 driver. It's not great as it
>> only checks for one secondary result set if the first result it null but
>> I figured that this will cover a large majority of usages so the few
>> lines go a long way. I guess I should be more helpful and try and give
>> you a patch or something but maybe there needs to be a new API hook or
>> different return code from dbi_result_next_row to indicate that the next
>> result set in the multi sequence is being returned.
>>
>> So, referencing
>> http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html :
>>
>> ...this works for the INSERT...; SELECT LAST_AUTO_INSERT(); style call:
>>
>> /* if res is null, first check for a secondary result set */
>> if(!res && mysql_next_result((MYSQL *)conn->connection) == 0) {
>> res = mysql_store_result((MYSQL *)conn->connection);
>> }
>>
>> ...in dbd_query:
>>
>> dbi_result_t *dbd_query(dbi_conn_t *conn, const char *statement) {
>> /* allocate a new dbi_result_t and fill its applicable members:
>> *
>> * result_handle, numrows_matched, and numrows_changed.
>> * everything else will be filled in by DBI */
>>
>> dbi_result_t *result;
>> MYSQL_RES *res;
>>
>> if (mysql_query((MYSQL *)conn->connection, statement)) {
>> return NULL;
>> }
>>
>> res = mysql_store_result((MYSQL *)conn->connection);
>>
>> + /* if res is null, first check for a secondary result set */
>> + if(!res && mysql_next_result((MYSQL *)conn->connection) == 0) {
>> + res = mysql_store_result((MYSQL *)conn->connection);
>> + }
>>
>> /* if res is null, the query was something that doesn't return
>> rows (like an INSERT) */
>> result = _dbd_result_create(conn, (void *)res, (res ?
>> mysql_num_rows(res) : 0),
>>
>> mysql_affected_rows((MYSQL *)conn->connection));
>>
>>
>> From the docs I guess there should be a mysql_more_results() call at
>> the end of any active result set so that a different return code can be
>> sent via dbi_result_next_row.
>>
>> chris
>>
>>
>> Markus Hoenicka wrote:
>>> Markus Hoenicka writes:
>>> > Another option is to check all numeric options against the list of the
>>> > mysql_real_connect client flags and 'OR' all of the available flags.
>>> > That is, instead of assembling the client flags yourself, you could
>>> > e.g. set "CLIENT_COMPRESS" and "CLIENT_MULTI_STATEMENTS" to 1 using
>>> > the libdbi set_option functions. The driver would then assemble the
>>> > proper value for connect_flags. The change to the driver would be
>>> > fairly trivial, and I agree that we should support all available flags
>>> > instead of just the compression flag.
>>> >
>>>
>>> I've checked in an updated version of the driver along with the
>>> documentation. Feel free to check out the driver and see whether it
>>> works for your project.
>>>
>>> You can set basically all of the client flags that
>>> mysql_real_connect() understands by setting the numeric options
>>> "mysql_client_compress" and so on to a value larger than zero. In your
>>> case, just add
>>>
>>> dbi_conn_set_option_numeric(conn, "mysql_client_multi_statements", 1);
>>>
>>> to your code.
>>>
>>> regards,
>>> Markus
>>>
>>
>> -------------------------------------------------------------------------
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to share your
>> opinions on IT & business topics through brief surveys-and earn cash
>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
>> _______________________________________________
>> Libdbi-drivers-devel mailing list
>> Libdbi-drivers-devel@xxxxxxxxxxxxxxxxxxxxx
>> https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
>>
>>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Libdbi-drivers-devel mailing list
> Libdbi-drivers-devel@xxxxxxxxxxxxxxxxxxxxx
> https://lists.sourceforge.net/lists/listinfo/libdbi-drivers-devel
>
>
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
|
