Re: stored procedures

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