Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: How to access MySQL stored procedures OUT parameter(s)

by pryrt (Abbot)
on Aug 31, 2017 at 13:48 UTC ( [id://1198406] : note . print w/replies, xml ) Need Help??


in reply to How to access MySQL stored procedures OUT parameter(s)

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.

Including experimental results, for posterity

use warnings; use strict; use DBI qw/:sql_types/; use Test::More; my $dsn = 'DBI:mysql:database=pryrtcom_signs'; my $dbh = DBI->connect($dsn, @ARGV, {RaiseError => 0}); 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); is( $dbh->errstr , undef , 'DebugMe(?) with ->execute($v)' ); $sth->execute(\$v); is( $dbh->errstr , undef , 'DebugMe(?) with ->execute(\$v)' ); # [kcott] and [karlgoethebier]'s links both suggest bind_parama_inout( +): $v = undef; $sth = $dbh->prepare('CALL DebugMe(?)'); $sth->bind_param_inout(1, \$v, 10, SQL_CHAR); is( $dbh->errstr , undef , 'bind_param_inout() without error' ); # SO points to [http://bugs.mysql.com/bug.php?id=23554|MySQL Bug Repor +t], # which created [https://rt.cpan.org/Public/Bug/Display.html?id=83519| +DBI.pm RT#83519], # which merged with [https://rt.cpan.org/Public/Bug/Display.html?id=65 +617|DBI.pm RT#65617], # which has been open since Feb 2011, but suggests the workaround that + [thanos1983]'s links suggest. # [thanos1983]'s links suggested a two-step process: $v = undef; $sth = $dbh->prepare('CALL DebugMe(@dmvar)'); $sth->execute() or warn sprintf "execute scalar => %s\n", $ +dbh->errstr; #$sth->finish; ($v) = $dbh->selectrow_array('SELECT @dmvar'); is( $dbh->errstr , undef , 'SELECT(@dmvar) without error' ); is( $v , 'HELLO' , 'DebugMe(@dmvar); SELECT @dmvar' ); done_testing(); __END__ SELECT rows = 1 (Hello World) DBD::mysql::st execute failed: OUT or INOUT argument 1 for routine pry +rtcom_signs.DebugMe is not a variable or NEW pseudo-variable in BEFOR +E trigger at sscce.pl line 15. not ok 1 - DebugMe(?) with ->execute($v) # Failed test 'DebugMe(?) with ->execute($v)' # at sscce.pl line 16. # got: 'OUT or INOUT argument 1 for routine pryrtcom_signs.De +bugMe is not a variable or NEW pseudo-variable in BEFORE trigger' # expected: undef DBD::mysql::st execute failed: OUT or INOUT argument 1 for routine pry +rtcom_signs.DebugMe is not a variable or NEW pseudo-variable in BEFOR +E trigger at sscce.pl line 17. not ok 2 - DebugMe(?) with ->execute(\$v) # Failed test 'DebugMe(?) with ->execute(\$v)' # at sscce.pl line 18. # got: 'OUT or INOUT argument 1 for routine pryrtcom_signs.De +bugMe is not a variable or NEW pseudo-variable in BEFORE trigger' # expected: undef DBD::mysql::st bind_param_inout failed: Output parameters not implemen +ted at sscce.pl line 23. not ok 3 - bind_param_inout() without error # Failed test 'bind_param_inout() without error' # at sscce.pl line 24. # got: 'Output parameters not implemented' # expected: undef ok 4 - SELECT(@dmvar) without error ok 5 - DebugMe(@dmvar); SELECT @dmvar 1..5 # Looks like you failed 3 tests of 5.

Replies are listed 'Best First'.
Re^2: How to access MySQL stored procedures OUT parameter(s)
by thanos1983 (Parson) on Aug 31, 2017 at 14:23 UTC

    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!