Re: stored procedures

Martin,

I've tested this with 3.0004_1 and you're right, I can handle the result
sets now.  Thank you for pointing me in the right direction.  I don't
monitor the DBI list, just this one - perhaps I should monitor both..  Had
no idea this was out there.  Anyone reading this can find the release here:

http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-3.0004_1.tar.
gz

This will handle stored procedures as I said it wouldn't in my previous
statements.  This is great!  My C is a bit rusty, but I certainly wouldn't
be above lending a hand.  I know there are quite a few people typing 'mysql
perl stored procedures' in google and the like, would be nice to be able to
get the word out.

Thanks for the response and thanks to Patrick for the release.

R


On 5/24/06 12:23 PM, "Martin J. Evans" <martin.evans@xxxxxxxxxxxx> wrote:
  
> On 24-May-2006 Richard Fogle wrote:
>> 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?
> 
> perl -e 'use DBI; $dbh = DBI->connect("dbi:mysql:test", "xxx", "yyy"); $sql =
> q/call p_next_sequence()/; $sth = $dbh->prepare($sql); $sth->execute; while
> (@row = $sth->fetchrow_array) {print join(",", @row),"\n";}'
> 
> outputs:
> 
> 7
> 
> 
>> 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.
> 
> DBD::mysql does not do bind_param_inout, I was answering the statement of
> DBD::mysql does not support procedures not DBD::mysql does not support out
> parameters.
> 
> You can immitate them with DBD::mysql by doing a select x,y at the end of the
> procedure and using more results.
>  
>> 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!
> 
> dump_results just dumps the rows in the result-set - it is just a wrapper
> around fetchrow_*.
> 
> Isn't the above example a result-set in you terms? The procedure is:
> 
> create procedure p_next_sequence()
> BEGIN
> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
> select LAST_INSERT_ID();
> END$
> 
> 
>> I must've missed the notice going out on this list that there were new
>> releases, I'll go download and check them out.
> 
> I don't think you did but you need to keep your eye on CPAN.
> 
> OTOH, Richard Sketon posted to this list about 3.0004_1 only a few days ago
> (see
> Problem testing DBD::mysql 3.0004) so 3.0004 is not unknown on this list even
> if
> there was not a formal announcement. Note the _1 denoting a development
> release.
> 
> Personally, I have always viewed dbi-users list as the definitive place for
> DBI
> and DBD but its volume is a lot more than this list.
> 
> I suspect the reason there are new releases of DBD::mysql is that recently a
> few
> people posted patches to fix issues and add features to Patrick who cannot
> reasonably be expected to do everything himself. I know not everyone can work
> out a fix or add features but I see little-to-none patches posted on this list
> and that is generally what makes open source move.
> 
> If you post an example of a short procedure that uses output parameters that
> you'd like to use I may find some time to take a quick look at doing something
> with it but that is not a promise.
> 
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
> 
> 
> 
>> 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=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