Re: stored procedures

Martin - thanks for responding, would you please point to an example where a
stored procedure will return a result set that DBD::mysql enables us loop
through and/or assign to an array/ref?  Also, please post an example of how
to handle OUT vars in the procedure.  No problems in RTFM, just can't find
any and I'm not alone.

We've been able to call stored procedures via $dbh->do("CALL foo(bar)") and
the like, and see if any rows changed, but nothing that would handle the
result sets which is about 75% of the functionality needed.  Thus stored
procedures not being supported.  $sth->dump_results, while useful to a
point, isn't a very good example.  Again, if I'm wrong please let me know,
I'd really like to use this.  Thanks!

I must've missed the notice going out on this list that there were new
releases, I'll go download and check them out.

R


On 5/24/06 10:35 AM, "Martin J. Evans" <martin.evans@xxxxxxxxxxxx> wrote:

> 
> 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" <mstrong@xxxxxxxx> 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/perl?unsub=martin.evans@xxxxxxxxxxxx



-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    
http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@xxxxxxxxxxx