There does not seem to be anything about that covers exactly what I want to do, so here goes:

The scenario is this: I have several stored procedures which take little input and give little output. Typically they take a parameter as either a number or string, and expect either a number or string back. 

my $var_with_chars = "a string";
my @result = [];
        $sql = 'call register_hit("'.$var_with_chars.'",@hit_count)'; 
+#watch out for the "@"!
        my $sth = $db_handle->prepare($sql);
        $sth->execute() || 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]

The above code assumes that you are using Perl DBI and DBD::Mysql and have created a valid database handle, $db_handle.

You will note the @hit_count is a made up variable, only used with the database connection and does not need to be declared locally. All other variables are local. You will also note the way I have had to construct the $sql statement. This is so that when passed to DBD::Mysql the contents of the variable are passed not the variable name. If you try to reverse the style of the quotes then perl tries to pass something for @hit_counter, if it is not defined you will get an error saying so, and if it is it will be evaluated as an array reference causing DBD::Mysql to crash.

I hope that helps.

Replies are listed 'Best First'.
Re: Calling MySQL stored procedures with in and out parameters
by james2vegas (Chaplain) on Mar 24, 2010 at 15:00 UTC
    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]
[reply]
[d/l]

      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]
      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]
Re: Calling MySQL stored procedures with in and out parameters
by moritz (Cardinal) on Mar 24, 2010 at 18:29 UTC
    FYI, with Firebird or InterBase you can simply do: 
    my $sth = $dbh->prepare('SELECT "out1", "out2" FROM "prodecure_name"(?
+, ?)');
$sth->execute($in1, $in2);

    [download]
    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]

Back to Meditations