Re: stored procedures

To save the time, wait until 5.0.21 is fixed

  http://bugs.mysql.com/bug.php?id=19857

--w

On Wed, May 24, 2006 at 08:11:05PM -0500, Richard Fogle wrote:
> 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=gcdmp-msql-mysql-modules@xxxxxxxxxxx