stored procedures

Can't get em to work correctly

See example.

Which works fine as it is, but comment out=20

$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 =3D 'db5';
$host =3D 'mas-data01';
$user =3D 'root';
$password =3D 'frednirk';

my ($rowset, $i);

$dbh =3D DBI->connect("DBI:mysql:database=3D$db;host=3D$host",
$user, $password, {RaiseError =3D> 1});

$dbh->do('drop procedure if exists someproc') or die $DBI::errstr;

$sql =3D q{create procedure someproc() deterministic
begin
declare a,b,c,d int;
set a=3D1;
set b=3D2;
set c=3D3;
set d=3D4;
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=3D$dbh->prepare('call someproc()') or die $DBI::err.":
".$DBI::errstr;
$sth->execute() or die DBI::err.": ".$DBI::errstr; $rowset=3D0;
do {
print "\nRowset
".++$i."\n---------------------------------------\n\n";
foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
print $sth->{NAME}[$colno]."\t";
}
print "\n";
while (@row=3D $sth->fetchrow_array()) {
foreach $field (0..$#row) {
print $row[$field]."\t";
}
print "\n";
}
} until (!$sth->more_results);

$sth->finish();
$dbh->disconnect();
exit(0);
=20
=20
This e-mail message is for the sole use of the intended recipient(s) and =
may=20
contain confidential and privileged information of Transaction Network =
Services.
Any unauthorized review, use, disclosure or distribution is prohibited. =
If you=20
are not the intended recipient, please contact the sender by reply =
e-mail and=20
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=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: stored procedures

Hey Mark,

Stored procedures still isn't supported. I asked this question some time
ago. At this point, I'm not sure if DBD::mysql is still under active
development, it's so far behind PHP/Java. 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.

R


On 5/23/06 11:27 PM, "Mark Strong" 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@m .gmane.org

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" 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@easysoft.com

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

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" 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" 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@easysoft.com



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

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" 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" 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@easysoft.com
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=martin.evans@easysoft.com

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

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-mys ql-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" 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" 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" 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@easysoft.com
>>
>>
>>
>> --
>> MySQL Perl Mailing List
>> For list archives: http://lists.mysql.com/perl
>> To unsubscribe:
>> http://lists.mysql.com/perl?unsub=martin.evans@easysoft.com



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

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-mys ql-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" 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" 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" 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@m .gmane.org