in reply to Calling MySQL stored procedures with in and out parameters

If you are using passing variables into a database call you should use bound parameters, which will take care of any escaping of special characters and stuff, preventing SQL injection attacks.

my $var_with_chars = "a string";
my @result = [];
        $sql = 'call register_hit(?, @hit_count)';
        #watch out for the "@"!
        my $sth = $db_handle->prepare($sql);
        $sth->execute($var_with_chars) || die $DBI::errstr;
        $sth->finish; 
        # Now get the output variable
        @result = $db_handle->selectrow_array('SELECT @hit_count'); 
        # || die $DBI::errstr;
        print "result=", $result[0], "\n";

[download]

Replies are listed 'Best First'.
Re^2: Calling MySQL stored procedures with in and out parameters
by Benedict White (Novice) on Mar 24, 2010 at 15:12 UTC

    Many thanks James, for some reason when I tried that before it did not work, but then that may have been because of the precise way I set it out.

    That said, there is now a hopefully clear and easy way for folks who do not know how, to get parameters in and out of MySQL procedures.

[reply]
Re^2: Calling MySQL stored procedures with in and out parameters
by morning_leaf (Initiate) on Apr 06, 2015 at 01:26 UTC
    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!
[reply]
[d/l]

      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]

In Section Meditations