Re: DBI Placeholders and DB2 Integers

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.

Comment on Re: DBI Placeholders and DB2 Integers Select or Download Code

Replies are listed 'Best First'.
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
However I notice that the Math::Base36 module uses BigInts... In this case, it's probably easiest to ->numify them, which is a method provided by the Math::BigInt module. In other words, there's no need to load any other module. Your numbers already are objects of type Math::BigInt, so you can simply say `$doc_len->numify`, etc. (Don't use `->as_int` instead (as mentioned in the docs), because this method wouldn't remove the Math::BigInt type...)	[reply] [d/l] [select]
Re^5: DBI Placeholders and DB2 Integers by acka.au (Novice) on Apr 12, 2012 at 06:10 UTC