Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Does DB2 support bind variables to user defined functions (UDF)?

by andreas1234567 (Vicar)
on Apr 30, 2008 at 12:48 UTC ( #683670=perlquestion: print w/replies, xml ) Need Help??

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

I'm running DB2/LINUX 9.5.0 on i386, with perl v5.8.5, DBI 1.604, DBD::DB2 1.1.

DB2 supports both stored procedures (SP) and user-defined functions (UDF). The former allows in, out and inout parameters that can be bound (see below) as can be expected. For the latter I cannot get bind variables to work.

I have a DB2 UDF (function) defined as follows (the simplest I could think of)

CREATE FUNCTION FUNC_JUST_RETURN_IT(i int) RETURNS int NO EXTERNAL ACTION DETERMINISTIC RETURN i @
I can call this function with a statement like this:
-- execute directly SELECT DB2INST1.FUNC_JUST_RETURN_IT(42) FROM SYSIBM.SYSDUMMY1
I would also like to prepare the statement with the input variable as a bind variable in order to re-user the statement, e.g.
-- prepare, then execute later SELECT DB2INST1.FUNC_JUST_RETURN_IT(?) FROM SYSIBM.SYSDUMMY1
Does the DB2, DBD::DB2 and DBI trio support bind variables to user defined function (UDF) as they do with stored procedures?
use strict; use warnings; use DBD::DB2; use DBD::DB2::Constants; use Test::More; plan q/no_plan/; # I'm the man with no plan .. sub DBSETUP_PARAMS { return ('DBI:DB2:viper', 'db2inst1', '*****'); } # Call function with unbound value sub call_func_nobind { my $cardno = shift; my $rv = undef; my $dbh = DBI->connect(DBSETUP_PARAMS()); die(q{Connect failed!}) if (!defined($dbh)); my $sth = $dbh->prepare( qq{SELECT DB2INST1.FUNC_JUST_RETURN_IT($cardno) FROM SYSIBM.SYSDUM +MY1}); die(q{Prepare failed!}) if (!defined($sth)); $sth->execute() or die (q{execute failed}); die(q{Execute failed!}) if (!defined($sth)); my $row = $sth->fetchrow_arrayref(); ($row) ? $row->[0] : undef; } # Failing attempt to call function with bound value sub call_func_bind { my $cardno = shift; my $rv = undef; my $dbh = DBI->connect(DBSETUP_PARAMS()); die(q{Connect failed!}) if (!defined($dbh)); my $sth = $dbh->prepare(qq{SELECT DB2INST1.FUNC_JUST_RETURN_IT(?) FROM SYSIB +M.SYSDUMMY1}); die(q{Prepare failed!}) if (!defined($sth)); $sth->bind_param(1, $cardno); die(q{bind_param failed!}) if (!defined($sth)); $sth->execute() or die (q{execute failed}); die(q{Execute failed!}) if (!defined($sth)); my $row = $sth->fetchrow_arrayref(); ($row) ? $row->[0] : undef; } # ------ main ------ cmp_ok(call_func_nobind(q{123456}), q{==}, 123456, q{Expect 123456 ret +urned}); cmp_ok(call_func_bind(q{123456}), q{==}, 123456, q{Expect 123456 ret +urned}); 1; __END__
Run:
$ /usr/bin/perl -w call.function.pl ok 1 - Expect 123456 returned DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N A +statement contains a use of a parameter marker that is not valid. SQ +LSTATE=42610 DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL0418N A +statement contains a use of a parameter marker that is not valid. SQ +LSTATE=42610 execute failed at call.function.pl line 48. 1..1 # Looks like your test died just after 1. $
--
No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

Replies are listed 'Best First'.
Re: Does DB2 support bind variables to user defined functions (UDF)?
by pc88mxer (Vicar) on Apr 30, 2008 at 18:20 UTC
      Yes it works when using the CAST function:
      $ diff call.function.pl~ call.function.pl 76c76 < $dbh->prepare(qq{SELECT DB2INST1.FUNC_JUST_RETURN_IT(?) FROM SYS +IBM.SYSDUMMY1}); --- > $dbh->prepare(qq{SELECT DB2INST1.FUNC_JUST_RETURN_IT(CAST(? AS I +NT)) FROM SYSIBM.SYSDUMMY1}); $ /usr/bin/perl -w call.function.pl ok 1 - Expect 123456 returned ok 2 - Expect 123456 returned 1..2
      Thank you very much.
      --
      No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://683670]
Approved by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2021-10-26 11:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My first memorable Perl project was:







    Results (90 votes). Check out past polls.

    Notices?