Re: stored procedures

On 24-May-2006 Richard Fogle wrote:
> Hey Mark,
> 
> Stored procedures still isn't supported. 

Don't agree. Here is one working:

perl -e 'use DBI; $dbh = DBI->connect("dbi:mysql:test", "xxx", "yyy"); $sql =
q/call p_next_sequence()/; $sth = $dbh->prepare($sql); $sth->execute;
$sth->dump_results;'

'2'
1 rows

MySQL client 5.0.15
DBD::Mysql 3.0004

They also work in DBD::ODBC with myodbc - I use them all the time.

> I asked this question some time ago.
>  At this point, I'm not sure if DBD::mysql is still under active
> development,

It is.

> it's so far behind PHP/Java.

How is it behind PHP and Java?

To my knowledge all of the DBI interface is supported (there may be a few minor
exceptions I've not found yet) and some additional DBD::mysql methods also
exist.

>  Couldn't get much of an answer on
> the mysql developer boards either, except that someone at MySQL maintains
> the code (I think part time).  If anyone has more information or if I'm
> wrong I'd really like to hear it, this is preventing me from using perl for
> a large project.

DBD::mysql is actively maintained by Patrick Galbraith at mysql. There have
been 2 development releases in the last 3 weeks:

http://search.cpan.org/~capttofu/DBD-mysql-3.0004/

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


> R
> 
> 
> On 5/23/06 11:27 PM, "Mark Strong" <[email protected]> wrote:
> 
>> Can't get em to work correctly
>> 
>> See example.
>> 
>> Which works fine as it is, but comment out
>> 
>> $dbh->do('drop procedure if exists someproc') or die $DBI::errstr;
>> 
>> and
>> 
>> $dbh->do("$sql") or die $DBI::errstr;
>> 
>> After all we already created it on the first run, but it doesn't work
>> correctly with those lines commented out.
>> 
>> This is with DBD-mysql-3.0004_1.tar.gz, DBI 1.50, mysql
>> libs from v 5.0.20a
>> 
>> What am I doing wrong?  And a more complex example that actually selects
>> some data from a table (using a stored procedure) and returns it, only
>> returns the column headings, but for both (using ethereal) I can see the
>> data is returned from the server to the client (perl DBD), but I can't
>> seem to successfully retrieve it
>> 
>> 
>> Mark.
>> 
>> 
>> example
>> #!/usr/bin/perl -w
>> 
>> use DBI;
>> 
>> $db = 'db5';
>> $host = 'mas-data01';
>> $user = 'root';
>> $password = 'frednirk';
>> 
>> my ($rowset, $i);
>> 
>> $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
>>                              $user, $password, {RaiseError => 1});
>> 
>> $dbh->do('drop procedure if exists someproc') or die $DBI::errstr;
>> 
>> $sql = q{create procedure someproc() deterministic
>>         begin
>>         declare a,b,c,d int;
>>         set a=1;
>>         set b=2;
>>         set c=3;
>>         set d=4;
>>         select a, b, c, d;
>>         select d, c, b, a;
>>         select b, a, c, d;
>>         select c, b, d, a;
>>         end};
>> 
>> $dbh->do("$sql") or die $DBI::errstr;
>> 
>> $sth=$dbh->prepare('call someproc()') or die $DBI::err.":
>> ".$DBI::errstr;
>> $sth->execute() or die DBI::err.": ".$DBI::errstr; $rowset=0;
>> do {
>>         print "\nRowset
>> ".++$i."\n---------------------------------------\n\n";
>>         foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
>>                 print $sth->{NAME}[$colno]."\t";
>>         }
>>         print "\n";
>>         while (@row= $sth->fetchrow_array())  {
>>                 foreach $field (0..$#row) {
>>                         print $row[$field]."\t";
>>                 }
>>                 print "\n";
>>         }
>> } until (!$sth->more_results);
>> 
>> $sth->finish();
>> $dbh->disconnect();
>> exit(0);
>>  
>>  
>> This e-mail message is for the sole use of the intended recipient(s) and may
>> contain confidential and privileged information of Transaction Network
>> Services.
>> Any unauthorized review, use, disclosure or distribution is prohibited.  If
>> you 
>> are not the intended recipient, please contact the sender by reply e-mail
>> and
>> destroy all copies of the original message.
> 
> 
> 
> -- 
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:   
> http://lists.mysql.com/[email protected]

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]