I just inherited a project where I need to call DB2 stored procedures from Perl. Is it possible to get the return value of DB2 stored procedures with DBD::DB2? What I'm seeing is that I can bind an in/out parameter to the return value, but it never gets populated. For example the procedure call 

...
my $sth = $dbh->prepare("{?=call some_procedure(?, ?, ?)}");
$sth->bind_param_inout(1, \$status, 20);
$sth->bind_param(2, $foo);
$sth->bind_param_inout(3, \$bar, 20);
$sth->bind_param_inout(4, \$baz, 20);
...

[download]

will run with no error, and the two out parameters (3 and 4) will be populated. But the return value of the procedure (parameter 1) is never set, it will contain whatever $status was initialized with.

The procedure definitely returns a value, I can see it in the DB2 procedure debugger, and I'm able to get the return value if I call the procedure from JDBC, so it must be the DBD::DB2 driver that isn't returning the value. This is a bummer because our Java programs rely on the return value to tell whether the procedure call was successful. I'd like the Perl code to work the same way. I could work around this in Perl by returning the the status in another out parameter or by just verifying that all of the out parameters contain valid data but I'd rather be able to just get the procedure return value if possible.

I'm using DBD::DB2 version 0.78 which I realize is rather old but upgrading will require a lot of regression testing, and I don't see anything in the docs for the latest DBD::DB2 version to suggest that it would behave differently with respect to handling of the return value. I'll try the latest DBD::DB2 on a different box but I thought it would be worth asking here first.

In reply to Getting return value of DB2 stored procedures by iguanodon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.