How do I call Informix stored procedures from Perl?

Question

How do I call informix stored procedures from Perl? I use DBD::ODBC to connect to informix database, but I don't know how to call procedures. my code like this:

my $dbh = DBI->connect("dbi:".DBDRIVE.":".DBNAME,DBUSER,DBPASS,
                      {RaiseError=>0,PrintError=>0,AutoCommit=>1}) ||
          die $DBI::errstr;
    $dbh->do("execute procedure sp_test('2010-05-01 00:00:00')") ||
             warn "failed\n";
    $dbh->disconnect(); 

When I run it, it didn't get error. But I get nothing when I check the database. The store procedure works fine if I run it in database directly. Anyone can help me out?

Answer 1

Pass 1

I suppose DBDRIVE, DBNAME, DBUSER and DBPASS have been defined via the constant module:

use constant DBDRIVE => "informix";  # ...mitsake...
use constant DBNAME  => "stores";    # Or whatever
use constant DBUSER  => "me";
use constant DBPASS  => "mine";

You should use 'or' rather than '||' after the call to DBI->connect().

From 'perldoc DBI':

    $dbh = DBI−>connect($data_source, $username, $password, \%attr)
               or die $DBI::errstr;

You can can get better help while resolving problems by using 'RaiseError=>1' and/or 'PrintError=>1'; that is what they are there for.

In general terms, the syntax of the EXECUTE PROCEDURE is correct, and should work OK as long as the procedure doesn't return anything...

Pass 2

Oh, you stipulate DBD::ODBC rather than DBD::Informix. Therefore, you are are at the mercy of a lot of code that I don't know so well as I know DBD::Informix.

Can we assume that you can connect to the database successfully using DBD::ODBC? And modify it and so on?

You will probably need to turn on the DBI tracing. You may also need to turn on lower level. Is the ODBC driver you are using provided by Informix, or is it the IBM DB2 CLI (C Common Client) driver, or one provided by someone else? Which version of Informix (IDS) are you using? Which platform are you running on? Which versions of Perl, DBI, DBD::ODBC and the various drivers are you using?

ODBC drivers can go munging SQL sent to it. I'm not clear what the various drivers I mentioned would do with the statement.

Pass 3

Did you consider contacting the [email protected] mailing list? That's where the maintainers of DBI and DBD::ODBC hang out.

Answer 2

As strange as it sounds, you need to fetch() from your statement handle in order to actually execute your stored procedure. Try changing your do() call to this instead:

my $sth = $dbh->prepare("execute procedure sp_test('2010-05-01 00:00:00')")
  or die $dbh->errstr
$sth->execute() or die $dbh->errstr
$sth->fetch(); # SPL actually executed here

(You may also want to consider setting RaiseError => 1 in your connect() options to avoid having to do all the ... or die ... stuff after every call.)