Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 12322
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: mbrancaleoni [...] espia.it
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: 2.9006
Fixed in: (no value)



Subject: Additional connection flags support in DBD-mysql
Download (untitled) / with headers
text/plain 619b
Hi, I'm sending you a little patch for DBD-mysql-2.9006, that makes possible to enable the mysql connection flag CLIENT_MULTI_STATEMENTS and CLIENT_MULTI_RESULTS , by specifing (as of your standard) mysql_multi_statements=1 or mysql_multi_results=1 in the dsn. Setting this flag on mysql connection is needed for: * getting multiple results sets back from mysql, for example stored procedures ( CLIENT_MULTI_RESULTS ) * sending multiple queries to the server, separating them with ';' (CLIENT_MULTI_STATEMENTS). Note that per mysql specs, CLIENT_MULTI_STATEMENTS imples CLIENT_MULTI_RESULTS. Hope this can help.
Download dbdimp.patch
text/x-diff 753b
--- dbdimp.c.orig 2005-04-02 08:50:05.000000000 +0200 +++ dbdimp.c 2005-04-15 11:58:46.000000000 +0200 @@ -926,6 +926,20 @@ " default group %s.\n", gr); mysql_options(sock, MYSQL_READ_DEFAULT_GROUP, gr); } + if ((svp = hv_fetch(hv, "mysql_multi_statements", 22, FALSE)) && *svp) { + if (SvTRUE(*svp)) { + client_flag |= CLIENT_MULTI_STATEMENTS; + } else { + client_flag &= ~CLIENT_MULTI_STATEMENTS; + } + } + if ((svp = hv_fetch(hv, "mysql_multi_results", 19, FALSE)) && *svp) { + if (SvTRUE(*svp)) { + client_flag |= CLIENT_MULTI_RESULTS; + } else { + client_flag &= ~CLIENT_MULTI_RESULTS; + } + } if ((svp = hv_fetch(hv, "mysql_client_found_rows", 23, FALSE)) && *svp) { if (SvTRUE(*svp)) {
From: Doug Morris <doug+cpan [...] mhost.com>
Download (untitled) / with headers
text/plain 2.3k
I've made a more complete patch in the hopes this can be included in future versions. Based on the original patch provided here, I've done the following: Updated dbdimp.c so that it won't cause versions < 5.x to fail to compile (added #ifdefs). Updated the POD in DBD/mysql.pm to describe what these options do Created an additional set of tests "70multi_statements.t" to be run with "make test" Test results on my mysql4 system: % make test PERL_DL_NONLAZY=1 /usr/bin/perl5.8.6 "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/00base................ok t/10dsnlist.............ok t/20createdrop..........ok t/30insertfetch.........ok t/35limit...............ok t/40bindparam...........ok t/40blobs...............ok t/40listfields..........ok t/40nulls...............ok t/40numrows.............ok t/41blobs_prepare.......ok t/50chopblanks..........ok t/50commit..............ok t/60leaks...............skipped all skipped: $ENV{SLOW_TESTS} is not set or Proc::ProcessTable not installed t/70multi_statements....ok 1/1 skipped: Skipping all multi_transaction tests - mysql version 4 < 5.x t/ak-dbd................ok t/akmisc................ok t/dbdadmin..............ok t/insertid..............ok t/mysql.................ok t/mysql2................ok All tests successful, 1 test and 1 subtest skipped. Files=21, Tests=904, 27 wallclock secs ( 2.82 cusr + 0.33 csys = 3.15 CPU) And the same on the mysql5 system: % make test PERL_DL_NONLAZY=1 /usr/bin/perl5.8.7 "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t t/00base................ok t/10dsnlist.............ok t/20createdrop..........ok t/30insertfetch.........ok t/35limit...............ok t/40bindparam...........ok t/40blobs...............ok t/40listfields..........ok t/40nulls...............ok t/40numrows.............ok t/41blobs_prepare.......ok t/50chopblanks..........ok t/50commit..............ok t/60leaks...............skipped all skipped: $ENV{SLOW_TESTS} is not set or Proc::ProcessTable not installed t/70multi_statements....ok t/ak-dbd................ok t/akmisc................ok t/dbdadmin..............ok t/insertid..............ok t/mysql.................ok t/mysql2................ok All tests successful, 1 test skipped. Files=21, Tests=917, 26 wallclock secs ( 3.69 cusr + 0.21 csys = 3.90 CPU) The patch and the additional tests are attached. -Doug
#!/usr/local/bin/perl # # $Id$ # # This tests multiquery results # $^W = 1; use Data::Dumper; # # Include lib.pl # use DBI (); $mdriver = ""; foreach $file ("lib.pl", "t/lib.pl") { do $file; if ($@) { print STDERR "Error while executing lib.pl: $@\n"; exit 10; } if ($mdriver ne '') { last; } } if ($mdriver eq 'whatever') { print "1..0\n"; exit 0; } sub Version () { my $dbh = DBI->connect($test_dsn, $test_user, $test_password) or ErrMsgF("Cannot connect: Error %s.\n\n" . "Make sure, your database server is up and running.\n" . "Check that '$test_dsn' references a valid database" . " name.\nDBI error message: %s\n", $DBI::err, $DBI::errstr); my $version = $dbh->{'mysql_serverinfo'}; $version =~ /(\d+)/; return int $1; } # # Main loop; leave this untouched, put tests after creating # the new table. # while (Testing()) { # # Test the version. if < 5.x these can all be skipped # my $version = Version(); if ($version < 5) { Skip("Skipping all multi_transaction tests - mysql version $version < 5.x"); } else { Test($state or ($dbh = DBI->connect($test_dsn, $test_user, $test_password, { mysql_multi_statements => 1 })), undef, undef) or ErrMsgF("Cannot connect: Error %s.\n\n" . "Make sure, your database server is up and running.\n" . "Check that '$test_dsn' references a valid database" . " name.\nDBI error message: %s\n", $DBI::err, $DBI::errstr); # # Find a possible new table name # Test($state or $table = FindNewTable($dbh)) or DbiError($dbh->err, $dbh->errstr); # # Create a new table; EDIT THIS! # Test($state or ($def = TableDefinition($table, ["id", "INTEGER", 4, 0], ["name", "CHAR", 64, 0]), $dbh->do($def))) or DbiError($dbh->err, $dbh->errstr); # # Insert test data into the test table....... # Test($state or $dbh->do("INSERT INTO $table" . " VALUES(1, 'Alligator Descartes');" . "INSERT INTO $table" . " VALUES(2, 'Foo Bar')")) or DbiError($dbh->err, $dbh->errstr); # # Create a new stored procedure # Test($state or $dbh->do(" CREATE PROCEDURE test() " . "begin " . "SELECT * from $table; " . "end ;")) or DbiError($dbh->err, $dbh->errstr); # # Disconnect # Test($state or $dbh->disconnect) or ErrMsgF("Failed to disconnect: err %s, errstr %s.\n", $dbh->err, $dbh->errstr); # # Reconnect with multi_results capability # Test($state or ($dbh = DBI->connect($test_dsn, $test_user, $test_password, { mysql_multi_results => 1 })), undef, undef) or ErrMsgF("Cannot connect: Error %s.\n\n" . "Make sure, your database server is up and running.\n" . "Check that '$test_dsn' references a valid database" . " name.\nDBI error message: %s\n", $DBI::err, $DBI::errstr); # # Prepare a handle for calling the procedure # Test($state or $sth = $dbh->prepare("call test()")) or DbiError($dbh->err, $dbh->errstr); # # Call the procedure # Test($state or $sth->execute()) or DbiError($dbh->err, $dbh->errstr); # # Check that we got two rows back # Test($state or ($rv = $sth->rows()) == 2) or ErrMsgF("Expected 2 rows, got %s.\n", $rv); # # finish the handle # Test($state or $sth->finish()) or DbiError($dbh->err, $dbh->errstr); # # Finally drop the test table. # Test($state or $dbh->do("DROP TABLE $table")) or DbiError($dbh->err, $dbh->errstr); # # Drop the procedure # Test($state or $dbh->do("DROP PROCEDURE test")) or DbiError($dbh->err, $dbh->errstr); # # Disconnect # Test($state or $dbh->disconnect) or ErrMsgF("Failed to disconnect: err %s, errstr %s.\n", $dbh->err, $dbh->errstr); } }
diff -c -r DBD-mysql-3.0002.orig/dbdimp.c DBD-mysql-3.0002/dbdimp.c *** DBD-mysql-3.0002.orig/dbdimp.c 2005-07-11 18:18:40.000000000 +0200 --- DBD-mysql-3.0002/dbdimp.c 2006-02-21 20:46:17.000000000 +0100 *************** *** 1326,1331 **** --- 1326,1354 ---- mysql_options(sock, MYSQL_READ_DEFAULT_GROUP, gr); } + /* + * The following two options only are available in mysql 5 and up. The + * ifdef simply ensures this will still compile on older versions of + * mysql. + */ + #ifdef CLIENT_MULTI_STATEMENTS + if ((svp = hv_fetch(hv, "mysql_multi_statements", 22, FALSE)) && *svp) { + if (SvTRUE(*svp)) { + client_flag |= CLIENT_MULTI_STATEMENTS; + } else { + client_flag &= ~CLIENT_MULTI_STATEMENTS; + } + } + #endif /* CLIENT_MULTI_STATEMENTS */ + #ifdef CLIENT_MULTI_RESULTS + if ((svp = hv_fetch(hv, "mysql_multi_results", 19, FALSE)) && *svp) { + if (SvTRUE(*svp)) { + client_flag |= CLIENT_MULTI_RESULTS; + } else { + client_flag &= ~CLIENT_MULTI_RESULTS; + } + } + #endif /* CLIENT_MULTI_RESULTS */ if ((svp = hv_fetch(hv, "mysql_client_found_rows", 23, FALSE)) && *svp) { if (SvTRUE(*svp)) diff -c -r DBD-mysql-3.0002.orig/lib/DBD/mysql.pm DBD-mysql-3.0002/lib/DBD/mysql.pm *** DBD-mysql-3.0002.orig/lib/DBD/mysql.pm 2005-07-11 18:20:29.000000000 +0200 --- DBD-mysql-3.0002/lib/DBD/mysql.pm 2006-02-21 20:47:19.000000000 +0100 *************** *** 656,661 **** --- 656,673 ---- request to the server will timeout if it has not been successful after the given number of seconds. + =item mysql_multi_statements + + If your DSN contains the option "mysql_multi_statements=1", you will be + able to send multiple queries to the server in a single statement (using + ';' as a separator, for instance. + + =item mysql_multi_results + + If your DSN contains the option "mysql_multi_results=1", you will be able + to return multiple a results from a query. Mainly this is needed to return + a result set from a mysql5 stored procedure. + =item mysql_read_default_file =item mysql_read_default_group Only in DBD-mysql-3.0002/t: 70multi_statements.t
Download (untitled) / with headers
text/plain 275b
Hi and thanks for the patch (long time ago - sorry!), Multiple result sets are on by default so no need for a flag (version 4.00). Just use them per DBI spec (please see eg/ directory for examples, or even look at any test in t/ directory that tests procs) Thanks! Patrick
Download (untitled) / with headers
text/plain 181b
I'm re-opening. Even though we always enable CLIENT_MULTI_RESULTS (and I can't find a good reason to allow disabling them), we should allow a way to enable CLIENT_MULTI_STATEMENTS.
Download (untitled) / with headers
text/plain 457b
Jim, I don't understand - if we enable by default, then why do we need a flag to enable them when they are already enabled? In layman terms, why have a light switch for a light that is hard-wired on all the time? Patrick On Sat Jan 06 12:49:23 2007, JIMW wrote: Show quoted text
> I'm re-opening. Even though we always enable CLIENT_MULTI_RESULTS (and > I can't find a good > reason to allow disabling them), we should allow a way to enable > CLIENT_MULTI_STATEMENTS.
Download (untitled) / with headers
text/plain 652b
On Sat Jan 06 14:27:08 2007, CAPTTOFU wrote: Show quoted text
> I don't understand - if we enable by default, then why do we need a flag > to enable them when they are already enabled? In layman terms, why have > a light switch for a light that is hard-wired on all the time?
There are two settings here -- CLIENT_MULTI_RESULTS and CLIENT_MULTI_STATEMENTS. We always turn on CLIENT_MULTI_RESULTS, and I don't see a reason to ever turn them off, so no reason to provide an option for it. But CLIENT_MULTI_STATEMENTS is off by default (and should be off by default -- it's an easy way to accidently make SQL injection holes worse), so we should add an option for it.
Download (untitled) / with headers
text/plain 843b
RESULTS vs. STATEMENTS - My eyes are blurry and were only focusing on the CLIENT_MULTI part of the word! Ok, agreed. On Sat Jan 06 17:33:46 2007, JIMW wrote: Show quoted text
> On Sat Jan 06 14:27:08 2007, CAPTTOFU wrote:
> > I don't understand - if we enable by default, then why do we need a
> flag
> > to enable them when they are already enabled? In layman terms, why
> have
> > a light switch for a light that is hard-wired on all the time?
> > There are two settings here -- CLIENT_MULTI_RESULTS and > CLIENT_MULTI_STATEMENTS. We > always turn on CLIENT_MULTI_RESULTS, and I don't see a reason to ever > turn them off, so no > reason to provide an option for it. But CLIENT_MULTI_STATEMENTS is off > by default (and should > be off by default -- it's an easy way to accidently make SQL injection > holes worse), so we should > add an option for it.
From: alexey.stroganov [...] gmail.com
On Sat Jan 06 22:13:43 2007, CAPTTOFU wrote: Show quoted text
> RESULTS vs. STATEMENTS - My eyes are blurry and were only focusing on > the CLIENT_MULTI part of the word! Ok, agreed.
Please take into account that for now we should prevent processing of MULTI_STATEMENTS when server side prepare is enabled. Show quoted text
> > On Sat Jan 06 17:33:46 2007, JIMW wrote:
> > On Sat Jan 06 14:27:08 2007, CAPTTOFU wrote:
> > > I don't understand - if we enable by default, then why do we need a
> > flag
> > > to enable them when they are already enabled? In layman terms, why
> > have
> > > a light switch for a light that is hard-wired on all the time?
> > > > There are two settings here -- CLIENT_MULTI_RESULTS and > > CLIENT_MULTI_STATEMENTS. We > > always turn on CLIENT_MULTI_RESULTS, and I don't see a reason to ever > > turn them off, so no > > reason to provide an option for it. But CLIENT_MULTI_STATEMENTS is off > > by default (and should > > be off by default -- it's an easy way to accidently make SQL injection > > holes worse), so we should > > add an option for it.
> >
I have added support for mysql_multi_statements. Thanks for the contribution.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.