DBI Placeholders and DB2 Integers

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

VERSIONS: AIX=v5.3.7.0, DB2=v9.1.0.6, Perl=v5.8.8, DBD::DB2.pm v1.78

I am trying to update a DB2 table using a PREPARED statement with placeholders that has a where clause containing 1xCHAR column and 4xINTEGER columns. I cannot get past the fatal error msg "CLI0112E Error in assignment".

By process of elimination I have determined that is it definitely the integer values that are causing the problem. If I hard-code some integer values into the execute() statement the update works. If I build a new SQL statement for each iteration and use $dbh->do($upd_sql_stmt) that works too.

However with hundreds, and potentially thousands, of updates to do per file processed it would be far more efficient to be able to execute a pre-prepared statement with place holders than do an implicit prepare/execute for each loop.

The integer values to be plugged into the update sql are extracted from a Base36 encoded numeric string. However when used in placeholders the DBI/DB2 module just refuses to accept them as integer values instead of strings and thus the update aborts.

So far I have tried, individually and collectively, the following strategies to "force" the interpretation of the numeric variable values are integers:

my $update_idx1 = "update root.$current_table set UUID = ?" .
                  "where DOC_NAME = ? " .
                  "and DOC_OFF = ? and DOC_LEN = ? " .
                  "and COMP_OFF = ? and COMP_LEN = ?";
my $upd_idx1 = $dbh->prepare($update_idx1) 
   or die "Cannot prepare upd_idx1: $dbh->errstr";

my ($comp_off, $comp_len, $doc_off, $doc_len) = (0, 0, 0, 0);
my @array = map { Base36::decode_base36($_) + 0 }
   ($enc_comp_off, $enc_comp_len, $enc_doc_off, $enc_doc_len);

$comp_off=($array[0] / 1);
$comp_len=($array[1] / 1);
$doc_off =($array[2] / 1);
$doc_len =($array[3] / 1);

$upd_idx1->bind_param(1, $uuid)           
   or die "Bind failed for UUID: $dbh->errstr";
$upd_idx1->bind_param(2, $doc_name)       
   or die "Bind failed for doc_name: $dbh->errstr";
$upd_idx1->bind_param(3, int($doc_off),  { TYPE => SQL_INTEGER }) 
   or die "Bind failed for doc_off: $dbh->errstr";
$upd_idx1->bind_param(4, int($doc_len),  { TYPE => SQL_INTEGER }) 
   or die "Bind failed for doc_len: $dbh->errstr";
$upd_idx1->bind_param(5, int($comp_off), { TYPE => SQL_INTEGER }) 
   or die "Bind failed for comp_off: $dbh->errstr";
$upd_idx1->bind_param(6, int($comp_len), { TYPE => SQL_INTEGER }) 
   or die "Bind failed for comp_len: $dbh->errstr";

my $row_count = $upd_idx1->execute();
[download]

Since the API seems determined to interpret the passed numeric value as a string I have also tried changing the SQL to use the CHAR() function thus:

"CHAR(DOC_OFF) = ?"

for each of the integer fields. This at least prevents a fatal error, but it also results in no rows being found to update (Yes, I tried this WITHOUT doing the bind as INTEGER!).

So no matter what combination of some or all of these strategies (add ZERO, divide by ONE, use INT function on variable, bind as TYPE SQL_INTEGER) to try and force values to be interpreted as integers, the execute() statement still fails with "Error in assignment" on the integer values.

Is there a solution to this problem, or is it just a "feature" of the DBD::DB2 module that you cannot use integers in placeholders?

Comment on DBI Placeholders and DB2 Integers Select or Download Code

Replies are listed 'Best First'.
Re: DBI Placeholders and DB2 Integers by mje (Curate) on Apr 11, 2012 at 08:06 UTC
As moritz has said, setting DBI_TRACE=15=x.log, running your script and looking at x.log might give you a clue. Annoyingly you cannot see the Changes file for DBD::DB2 on search.cpan.org unless you browse the sources. http://cpansearch.perl.org/src/IBMTORDB2/DBD-DB2-1.84/Changes doesn't seem to suggest any relevant bugs were fixed between 1.78 and 1.84. I can happily insert numbers into an integer column using bound parameters with DBD::ODBC and the db2 driver. Out of interest, exactly what is in ($enc_comp_off, $enc_comp_len, $enc_doc_off, $enc_doc_len) - you didn't show us.	[reply]
Re^2: DBI Placeholders and DB2 Integers by moritz (Cardinal) on Apr 11, 2012 at 08:38 UTC
FWIW search.cpan.org does have a diff tool, it's hidden behind the "other tools" link on the distribution page. diffs between 1.78 and 1.84. Perl 6 - second systems done right	[reply]
Re: DBI Placeholders and DB2 Integers by moritz (Cardinal) on Apr 11, 2012 at 07:36 UTC
There are two things off-hand that I'd try: first enable tracing. the DBI docs have a section on tracing, and it has helped me to diagnose DBD weirdnesses. Use a high tracing level. If that doesn't help, try newer version of your modules (and possibly even perl). If that's not easy on your current machine, try to do it on a different machine, just to see if solves your problem. After all your Perl is 3 major versions out of date, and DBD::DB2 has had 6 releases since your version. Perl 6 - second systems done right	[reply]
Re: DBI Placeholders and DB2 Integers by acka.au (Novice) on Apr 12, 2012 at 00:40 UTC
Thanks for the suggestions so far. Unfortunately I don't have much influence over the corporate AIX/Perl environment, so getting to try latest versions might take some time. Meanwhile I have used the DBI trace option on a few different code variations: 1. Execute with variable names as parameters 2. Bind each variable then execute 3. Execute with hard coded integer values as parameters 4. Execute with eval{} of integer variables as parameters The four code snippets and the resulting DBI trace are reproduced below. my $row_count = $upd_idx1->execute($uuid, $doc_name, $doc_off, $doc_le +n, $comp_off, $comp_len); -> execute for DBD::DB2::st (DBI::st=HASH(0x3036c894)~0x3036c75c ' +68c51749-38a3-4f11-910c-f854c2973ce1' '91FAAA' Math::BigInt=HASH(0x30 +36c4d4) Math::BigInt=HASH(0x3036ca14 ) Math::BigInt=HASH(0x3036c7ec) Math::BigInt=HASH(0x3036c51c)) thr#300 +2ee58 bind :p1 <== '68c51749-38a3-4f11-910c-f854c2973ce1' (attribs: <no attr +ibs>) bind :p1: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=1, S +CALE=0, Maxlen=0, (null) bind :p2 <== '91FAAA' (attribs: <no attribs>) bind :p2: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=12, +SCALE=0, Maxlen=0, (null) bind :p3 <== '0' (attribs: <no attribs>) bind :p3: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p4 <== '17555' (attribs: <no attribs>) bind :p4: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p5 <== '0' (attribs: <no attribs>) bind :p5: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p6 <== '86861' (attribs: <no attribs>) bind :p6: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) !! ERROR: '-99999' '[IBM][CLI Driver] CLI0112E Error in assignmen +t. SQLSTATE=22005' (err#1) [download] `--------------------------------------------------` [download] $upd_idx1->bind_param(1, $uuid) or die "Bind failed f +or UUID: $dbh->errstr"; $upd_idx1->bind_param(2, $doc_name) or die "Bind failed f +or doc_name: $dbh->errstr"; $upd_idx1->bind_param(3, $doc_off, SQL_INTEGER) or die "Bind failed f +or doc_off: $dbh->errstr"; $upd_idx1->bind_param(4, $doc_len, SQL_INTEGER) or die "Bind failed f +or doc_len: $dbh->errstr"; $upd_idx1->bind_param(5, $comp_off, SQL_INTEGER) or die "Bind failed f +or comp_off: $dbh->errstr"; $upd_idx1->bind_param(6, $comp_len, SQL_INTEGER) or die "Bind failed f +or comp_len: $dbh->errstr"; my $row_count = $upd_idx1->execute(); >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @3 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 1 'ad2c80e3-9f6c-43b9-a98a-1a6a8257c67a') thr#3002ee58 bind :p1 <== 'ad2c80e3-9f6c-43b9-a98a-1a6a8257c67a' (attribs: <no attr +ibs>) bind :p1: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=1, S +CALE=0, Maxlen=0, (null) <- bind_param= 1 >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @3 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 2 '91FAAA') thr#3002ee58 bind :p2 <== '91FAAA' (attribs: <no attribs>) bind :p2: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=12, +SCALE=0, Maxlen=0, (null) <- bind_param= 1 >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @4 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 3 Math::BigInt=HASH(0x3036ac98) 4) thr#3002ee58 bind :p3 <== '0' (attribs: <no attribs>) bind :p3: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) <- bind_param= 1 >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @4 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 4 Math::BigInt=HASH(0x3036cad4) 4) thr#3002ee58 bind :p4 <== '17555' (attribs: <no attribs>) bind :p4: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) <- bind_param= 1 >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @4 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 5 Math::BigInt=HASH(0x3036c5ac) 4) thr#3002ee58 bind :p5 <== '0' (attribs: <no attribs>) bind :p5: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) <- bind_param= 1 >> bind_param DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @4 g0 ima1 + pid#2338852) -> bind_param for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c92 +4 6 Math::BigInt=HASH(0x3036c684) 4) thr#3002ee58 bind :p6 <== '86861' (attribs: <no attribs>) bind :p6: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) <- bind_param= 1 >> execute DISPATCH (DBI::st=HASH(0x3036ca5c) rc1/1 @1 g0 ima1 +041 pid#2338852) -> execute for DBD::DB2::st (DBI::st=HASH(0x3036ca5c)~0x3036c924) +thr#3002ee58 !! ERROR: '-99999' '[IBM][CLI Driver] CLI0112E Error in assignmen +t. SQLSTATE=22005' (err#1) [download] `--------------------------------------------------` [download] my $row_count = $upd_idx1->execute($uuid, $doc_name, 0, 17555, 0, 8686 +1); -> execute for DBD::DB2::st (DBI::st=HASH(0x3036c81c)~0x3036c6e4 ' +076de5c1-f082-4994-85fd-132a9a098737' '91FAAA' 0 17555 0 86861) thr#3 +002ee58 bind :p1 <== '076de5c1-f082-4994-85fd-132a9a098737' (attribs: <no attr +ibs>) bind :p1: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=1, S +CALE=0, Maxlen=0, (null) bind :p2 <== '91FAAA' (attribs: <no attribs>) bind :p2: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=12, +SCALE=0, Maxlen=0, (null) bind :p3 <== '0' (attribs: <no attribs>) bind :p3: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p4 <== '17555' (attribs: <no attribs>) bind :p4: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p5 <== '0' (attribs: <no attribs>) bind :p5: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p6 <== '86861' (attribs: <no attribs>) bind :p6: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) <- execute= 1 [download] `--------------------------------------------------` [download] my $row_count = $upd_idx1->execute($uuid, $doc_name, eval{$doc_off}, e +val{$doc_len}, eval{$comp_off}, eval{$comp_len}); -> execute for DBD::DB2::st (DBI::st=HASH(0x3036c7ec)~0x3036c6b4 ' +a2b0940e-9120-43b5-bfef-a5986a4e236e' '91FAAA' Math::BigInt=HASH(0x30 +36aa28) Math::BigInt=HASH(0x3036c864 ) Math::BigInt=HASH(0x3036c33c) Math::BigInt=HASH(0x3036c414)) thr#300 +2ee58 bind :p1 <== 'a2b0940e-9120-43b5-bfef-a5986a4e236e' (attribs: <no attr +ibs>) bind :p1: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=1, S +CALE=0, Maxlen=0, (null) bind :p2 <== '91FAAA' (attribs: <no attribs>) bind :p2: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=12, +SCALE=0, Maxlen=0, (null) bind :p3 <== '0' (attribs: <no attribs>) bind :p3: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p4 <== '17555' (attribs: <no attribs>) bind :p4: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p5 <== '0' (attribs: <no attribs>) bind :p5: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) bind :p6 <== '86861' (attribs: <no attribs>) bind :p6: db2_param_type=1, db2_c_type=1, db2_type=0, PRECISION=4, S +CALE=0, Maxlen=0, (null) !! ERROR: '-99999' '[IBM][CLI Driver] CLI0112E Error in assignmen +t. SQLSTATE=22005' (err#1) [download] As you can see the only variation which "works" is the one with integer values hard coded, which obviously isn't a solution. This despite the fact that all methods see the trace of `db2_param_type=1, db2_c_type=1, db2_type=0` If anyone can read anything of significance from the trace I would be much obliged.	[reply] [d/l] [select]
Re^2: DBI Placeholders and DB2 Integers by Eliya (Vicar) on Apr 12, 2012 at 01:49 UTC
As it looks, your numbers are Math::BigInt objects, which the DBD driver may not be handling properly. Is there any specific reason for using bigints, or do you just have a `use bigint;` pragma somewhere in the program, which inadvertendly applies to a wider scope than necessary? (Note that the pragma is lexically scoped, so you can keep it restricted to the minimal scope required.) Anyhow, assuming you do not need bigints for those values, you might want to try converting those Math::BigInt objects to normal numbers by using `->numify`, e.g. `$doc_len->numify` [download] Or better yet, find out why they've become bigints in the first place...	[reply] [d/l] [select]
Re^3: DBI Placeholders and DB2 Integers by acka.au (Novice) on Apr 12, 2012 at 03:01 UTC
Well I am certainly not doing anything intentional to use BigInts. However I notice that the Math::Base36 module uses BigInts and I do use the Base36 module. In fact I use the Base36::decode_base36() function in the same part of code which is doing this DB2 update (I have to extract the integers from a Base36 encoded string). How do I limit the context of the BigInt? Alternatively, since I haven't used (or heard of) numify before, is that a standard fucntion in Perl v5.8.8 or do I need to pull in another module?	[reply]
Re^4: DBI Placeholders and DB2 Integers by Eliya (Vicar) on Apr 12, 2012 at 03:59 UTC
Re^5: DBI Placeholders and DB2 Integers by acka.au (Novice) on Apr 12, 2012 at 06:10 UTC