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

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
  @
[download]

I can call this function with a statement like this:

  -- execute directly
  SELECT DB2INST1.FUNC_JUST_RETURN_IT(42)
    FROM SYSIBM.SYSDUMMY1
[download]

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
[download]

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__
[download]

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.
$
[download]

--
No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

Comment on Does DB2 support bind variables to user defined functions (UDF)? Select or Download Code

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
At http://www.dbforums.com/archive/index.php/t-840449.html a similar issue is discussed. You might be able to use `CAST()` to get around this problem - the parameter marker might need to be typed.	[reply] [d/l]
Re^2: Does DB2 support bind variables to user defined functions (UDF)? by andreas1234567 (Vicar) on May 01, 2008 at 05:32 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` [download] 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]	[reply] [d/l] [select]