iguanodon has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Getting return value of DB2 stored procedures
by Melly (Chaplain) on Oct 20, 2006 at 12:06 UTC

    Is this any help?

    Tom Melly, tom@tomandlu.co.uk
[reply]
      No, the code in that article doesn't bind the return value. So far all of the examples I've seen work that way, so I have to assume the driver just doesn't support it. But it's interesting that binding the return value doesn't cause an error, it just silently fails to populate the bound variable.

[reply]
Re: Getting return value of DB2 stored procedures
by neilwatson (Priest) on Oct 20, 2006 at 13:14 UTC
[reply]
      That example also doesn't bind a return value, but then the procedure doesn't return anything. In my example the return value is the first place holder before the =call. Where I work the convention is for stored procedures to return a value indicating success or an error code to indicate what went wrong, so I'd like my Perl code to stick to that if possible.

[reply]
        Did you ever figure this out? I've been trying my heart out to figure out why a stored procedure that I'm calling is not returning a value.... Here is what my code looks like: http://pastebin.com/Sih7L2mU
[reply]
Re: Getting return value of DB2 stored procedures
by Fletch (Bishop) on Oct 20, 2006 at 13:08 UTC

    Never used DB2, but just thought I'd toss an idea out: could you possibly call your procedure such that the results go into a temporary table? Then you could follow up with a SELECT to retrieve them from there. Not the most efficient way I'm sure, but might at least work.

    You might also search IBM's developer forums. They've got a pretty good collection of articles and I wouldn't be surprised if they didn't have an article on DBI and DB2 there.

[reply]

Back to Seekers of Perl Wisdom