Hi, all. I missed Patrick's request for comments almost one month ago, and I hope I can still contribute some points.

Migrating Perl applications from 4.1 to 5.0 brings several problems ranging from broken compatibility to the unavailability of new MySQL 5 features due to lack of suitable interface.

(1) The first one is related to the prepared statement protocol, which is the default behavior in the latest development releases. This breaks code with statements like "USE db_name", because this command is not supported by the prepared statement protocol. I reported this problem (bug #15665) and it was fixed in release 3.0002_5 Unfortunately, I was not thorough enough in my report, since I stopped at the first problem I had (with USE, that is). But the same problem arises when using any replication related commands, such as CHANGE MASTER TO, SLAVE START|STOP, SHOW MASTER/SLAVE STATUS, and actually most of the SHOW commands. Either all these commands are filtered (which should slow down query execution speed), or the default behavior should be brought back to the emulated prepared statements. Moreover, I learned recently, during a presentation by a MySQL developer, that the only benefit of prepared statements in MySQL is to have the query on the server side, i.e. we only save network bandwidth, but the prepared query is evaluated through the query optimizer every time. There is no real "prepared" query mechanism that saves optimizer time. With this fact in mind, I believe that the balance between this little benefit and the risk of breaking existing
code is in favor of keeping emulated prepared statements as the default.

(2) The second problem deals with stored procedures that return one or more record
sets. In C, it is enough to use mysql_next_result to get all record sets in order,
with their possibly different metadata correctly read. With DBD::mysql, only the first record set is recognized and reported. Example:

delimiter // drop procedure if exists several// create procedure several() begin select user() as u,now() as t; select database() as d,now() as t , "hello"; end // delimiter ;

Using a C routine that reads all recordsets (the same as the regular mysql
client does), I get $ ./multi_client query> use test 0 rows affected query> call several() +================+=====================+ | u | t | +================+=====================+ | user@localhost | 2006-02-25 11:03:05 | +================+=====================+ 1 rows returned +======+=====================+=======+ | d | t | hello | +======+=====================+=======+ | test | 2006-02-25 11:03:05 | hello | +======+=====================+=======+ 1 rows returned 0 rows affected query> exit

There is no equivalent Perl routine to achieve the same result. Here is my try:

# --- CUT ---- #!/usr/bin/perl use strict; use warnings; use DBI; use Data::Dumper;

my $HOST = '127.0.0.1';

my $dbh = DBI->connect("DBI:mysql:test;host=$HOST" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf;mysql_multi_results=1", undef, undef, {RaiseError => 1}) or die q{can't connect\n};

print $DBD::mysql::VERSION, $/; print Dumper $dbh->selectall_arrayref('call several()', {Slice => {}}); # --- CUT ---- From this snippet, I get:

3.0002_5 $VAR1 = [ { 'u' => 'gmax@localhost', 't' => '2006-02-25 11:05:39' } ];

Perhaps I am missing some obvious thing, but I wasn't able to find any help in
the docs so far.

(3) The third obstacle to have full development with stored procedures is a
mechanism to pass parameters to procedures accepting an OUT or INOUT argument. There is no
standard DBI method for this, so I believe that it is time to make a specific extension
to DBD::mysql. example:

create procedure xyx (IN param_w, IN param_x, OUT param_y, INOUT param_z ) ....

I could not find any way of passing a variable to param_y and param_z.

All comments on these points will be greatly appreciated.

Ciao Giuseppe