in reply to Re: Calling MySQL stored procedures with in and out parameters
in thread Calling MySQL stored procedures with in and out parameters

The answer is what I'm finding!
So happy, and thinks a lot!

But I haven't another question on MySQL Procedure being call:

If Procedure have a parameter act as inout, the above skill can't work.
I use the follow sample, but no work.

#!/usr/bin/perl

#
# PROCEDURE `TEST_COUNT_PLUS`
#
# CREATE DEFINER=`root`@`localhost` PROCEDURE `TEST_COUNT_PLUS`(INOUT 
+NUMBER1 INTEGER)
# BEGIN
   # DECLARE NUMBER2 INTEGER
   # DEFAULT (SELECT COUNT(*) FROM PLAYERS);
   # SET NUMBER1 = NUMBER2 + NUMBER1;
# END$$

use DBI;

my $dbh = DBI->connect('dbi:mysql:tennis','BOOKSQL','BOOKSQLPW') or di
+e "Connection Error: $DBI::errstr\n"; 
$dbh->do("set @hit_count=10");  # error, don't work!!!

my $sql = 'call TEST_COUNT_PLUS(@hit_count)';
my $sth = $dbh->prepare($sql);
$sth->execute || die $DBI::errstr;
$sth->finish; 

# Now get the output variable
@result = $dbh->selectrow_array('SELECT @hit_count') || die $DBI::errs
+tr;
print "result=", $result[0], "\n";

$dbh->disconnect;

[download]
How can I input the INOUT parameter value to the procedure?
thank very much!

Replies are listed 'Best First'.
Re^3: Calling MySQL stored procedures with in and out parameters
by hippo (Archbishop) on Apr 06, 2015 at 08:41 UTC

    If you had used warnings then the cause of the failure would have been made clear to you. If you had used strict then you could have trapped the problem even earlier - at compile time.

[reply]
      I know the code is error.
      But I don't know how to call the MySQL procedure that have a INOUT parameter. Such as :
      CREATE PROCEDURE `TEST_COUNT_PLUS`(INOUT NUMBER1 INTEGER)

      [download]

      If one INOUT parameter change to one IN parameter and one OUT parameter, such as :
      CREATE PROCEDURE `TEST_COUNT_PLUS_2`(IN NUMBER2 INTEGER, OUT NUMBER3 I
+NTEGER)

      [download]

      The skill of the answer is OK. So, I think if set the parameter NUMBER1 value before call procedure, it may work. But skill is Very poor.
[reply]
[d/l]
[select]
        It's my mistake. change the " to ', then OK. Now the follow code can be run :
        #!/usr/bin/perl

#
# PROCEDURE `TEST_COUNT_PLUS`
#
# CREATE DEFINER=`root`@`localhost` PROCEDURE `TEST_COUNT_PLUS`(INOUT 
+NUMBER1 INTEGER)
# BEGIN
   # DECLARE NUMBER2 INTEGER
      # DEFAULT (SELECT COUNT(*) FROM PLAYERS);
   # SET NUMBER1 = NUMBER2 + NUMBER1;
# END$$

use strict;
use DBI;

my @hit_count;
my $dbh = DBI->connect('dbi:mysql:tennis','BOOKSQL','BOOKSQLPW') or di
+e "Connection Error: $DBI::errstr\n"; 
$dbh->do('set @hit_count=10'); # change " to ', disable interpreter.

my $sql = 'call TEST_COUNT_PLUS(@hit_count)';
my $sth = $dbh->prepare($sql);
$sth->execute || die $DBI::errstr;
$sth->finish; 

# Now get the output variable
my @result = $dbh->selectrow_array('SELECT @hit_count') || die $DBI::e
+rrstr;
print "result=", $result[0], "\n";

$dbh->disconnect;

        [download]
[reply]
[d/l]

In Section Meditations