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

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.

  • Comment on Re^3: Calling MySQL stored procedures with in and out parameters

Replies are listed 'Best First'.
Re^4: Calling MySQL stored procedures with in and out parameters
by morning_leaf (Initiate) on Apr 06, 2015 at 15:25 UTC
    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