|
pryrt has asked for the wisdom of the Perl Monks concerning the following question:
I am playing around with DBD::mysql (MySQL v5.6) and perl, experimenting with stored procedures and functions, which I've never used before. I see that there is the ability to make an OUT parameter to the procedure. How do I access that from perl?
I have it working in MySQL:
DELIMITER //
DROP PROCEDURE IF EXISTS DebugMe //
CREATE PROCEDURE DebugMe(OUT arg char(10))
BEGIN
SET arg = "HELLO";
END //
DELIMITER ;
CALL DebugMe(@blah);
SELECT @blah;
Query OK, 0 rows affected (0.00 sec)
+-------+
| @blah |
+-------+
| HELLO |
+-------+
1 row in set (0.00 sec)
But not in perl:
use warnings;
use strict;
use DBI;
my $dsn = 'DBI:mysql:database=test';
my $dbh = DBI->connect($dsn, @ARGV, {RaiseError => 1});
my $sth = $dbh->prepare('SELECT "Hello World"');
$sth->execute();
printf "SELECT rows = %d\n", scalar $sth->rows;
printf "\t(@$_)\n" for $sth->fetchrow_arrayref;
my $v;
$sth = $dbh->prepare('CALL DebugMe(?)');
$sth->execute($v);
$sth->execute(\$v);
__END__
SELECT rows = 1
(Hello World)
DBD::mysql::st execute failed: OUT or INOUT argument 1 for routine tes
+t.DebugMe is not a variable or NEW pseudo-variable in BEFORE trigger
+at sscce.pl line 14.
DBD::mysql::st execute failed: OUT or INOUT argument 1 for routine tes
+t.DebugMe is not a variable or NEW pseudo-variable in BEFORE trigger
+at sscce.pl line 14.
Is this possible? Or will I have to use other methods (like a stored function return-value, or a procedure with multiple result sets)?
|
Re: How to access MySQL stored procedures OUT parameter(s)
by kcott (Archbishop) on Aug 31, 2017 at 06:16 UTC
|
G'day pryrt,
It's been years since I last did this and I don't have MySQL handy to test;
however, as I see you've received no replies in the eight hours since you posted,
I'll suggest checking
"DBI: bind_param_inout".
That seems to ring a bell.
The text includes: "... The statement is typically a call to a stored procedure. ...".
And there's links to related information, if that's not exactly what you need.
Maybe another monk with more current knowledge, or MySQL to hand,
can provide a better answer.
| [reply] |
|
|
kcott:
I've not tried it with MySQL, but I'm pretty sure (95%) that I used bind_param_inout a good few times with the Oracle and MSSQL drivers.
...roboticus
When your only tool is a hammer, all problems look like your thumb.
| [reply] |
|
|
I said "It's been years"; on reflection, it's probably been over a decade.
However, from subsequent replies, it looks like bind_param_inout was indeed the method I used.
Like you, I've not used it with MySQL: I've never written a MySQL stored procedure nor had to write any DBI code to
interface with one (although, I have used DBD::mysql many times over the years with plain SQL).
I've definitely used it with Oracle; pretty sure I've also used it with one or both of Postgres and Informix;
and, I think Sybase as well (although I'm less certain about that one).
| [reply]
[d/l] |
Re: How to access MySQL stored procedures OUT parameter(s)
by pryrt (Abbot) on Aug 31, 2017 at 13:48 UTC
|
Thanks for the help... Unfortunately, the ->bind_param_inout() that ++kcott's and ++karlgoethebier's posts link to does not work: the SO answer points to MySQL Bug Report, which created DBI.pm RT#83519, which merged with DBI.pm RT#65617, which has been open since Feb 2011. :-( That final bug suggests the workaround that ++thanos1983's links recommend to begin with. Fortunately, using the SQL @variable, and then SELECTing that variable into perl worked.
| [reply]
[d/l]
[select] |
|
|
Hello pryrt,
Thanks for sharing your findings. Based on your sample code I will try to give it a try and experiment a bit possibly later on tonight for fun.
Keep updating the thread if you get further information through experimentation.
BR, Thanos.
Seeking for Perl wisdom...on the process of learning...not there...yet!
| [reply]
[d/l]
[select] |
Re: How to access MySQL stored procedures OUT parameter(s)
by karlgoethebier (Abbot) on Aug 31, 2017 at 06:26 UTC
|
In a hurry...but without any warranty. And i vaguely remember that there is a solution without this OUT construct. Regards, Karl
«The Crux of the Biscuit is the Apostrophe»
perl -MCrypt::CBC -E 'say Crypt::CBC->new(-key=>'kgb',-cipher=>"Blowfish")->decrypt_hex($ENV{KARL});'Help
| [reply]
[d/l]
[select] |
Re: How to access MySQL stored procedures OUT parameter(s)
by thanos1983 (Parson) on Aug 31, 2017 at 08:23 UTC
|
Hello pryrt,
I am not using MySQL any more, rather for fun so I do not really know the OUT parameter to be honest. I saw that you do not have a solid answer to your problem so maybe this similar question Calling MySQL stored procedures with in and out parameters, can guide you more to the solution.
Unfortunately I do not have the time to experiment further to try to assist but maybe later on the day I will find some time. Never the less if you find solution to your problem update us so more people in future can benefit.
Update: From the MySQL forums HOW TO RETRIEVE AN "OUT" PARAMETER OF MYSQL STORED PROCEDURE IN PERL, the show one example and the user says is working, worth to give it a try.
Hope this helps, BR.
Seeking for Perl wisdom...on the process of learning...not there...yet!
| [reply]
[d/l]
[select] |
Re: How to access MySQL stored procedures OUT parameter(s)
by thanos1983 (Parson) on Sep 01, 2017 at 21:55 UTC
|
Hello again pryt,
I hope you still follow our thread. I found some time and decide to look into it a bit more. Regarding the part you said # thanos1983's links suggested a two-step process: can be done in one step also to minimize the resources. You can use the do method. In your case it can be used without the prepare execute step.
From the documentation:
This method is typically most useful for non-SELECT statements that ei
+ther cannot be prepared in advance (due to a limitation of the driver
+) or do not need to be executed repeatedly. It should not be used for
+ SELECT statements because it does not return a statement handle (so
+you can't fetch any data).
Having said that, here is a sample that replicates your test, based on your final post:
The idea came from the MySQL forums HOW TO RETRIEVE AN "OUT" PARAMETER OF MYSQL STORED PROCEDURE IN PERL. I remembered the user mentioned that is working, worth it to give it a try.
Relevant question Multiple data sets in MySQL stored procedures,in case of future reference(s).
Hope this helps, BR.
Seeking for Perl wisdom...on the process of learning...not there...yet!
| [reply]
[d/l]
[select] |
|
|
Ah, yes, by "two step", I meant step#1:CALL DebugMe(@dmvar), step#2:SELECT(@dmvar). But I do appreciate the reminder of $dbh->do(). As you might be able to tell, I'm not actually a database guy. :-)
As far as the Multiple-data-sets, that was actually something I had run across in the documentation, and was probably going to experiment with... but $work got in the way of my coffee-break perl/mysql experimentation. :-)
In the end, I'll probably just switch to a stored function instead of stored procedure, because (so far), I only have one value I want returned. (I had been thinking about a second, but it hasn't materialized yet, and probably won't.)
Thanks for staying interested.
| [reply]
[d/l]
[select] |
|
|
