DBI problem -- storing large value into an INT8 field

[mailbox]

I'm having trouble using the DBI module
with an INFORMIX database. I have a table
with a column variable called "filesize" defined
as INT8. Given a value greater than 2**31,
say 4154628096, that I want to store into it,
if I code

$S = $dbk->prepare("UPDATE stat_daily SET filesize = 4154628096")
$S->execute();

....this will store the correct value. But if I code

$Size = 4154628096;
$S = $dbk->prepare("UPDATE stat_daily SET filesize = ?");
$S->execute($Size);

....it stores a meaningless value. Our version of Perl is not
64-bit enabled and upgrading is not an option. We get away
with doing precise integer arithmetic with large values
within our scripts presumably because Perl's native floating
point uses a large number of fraction bits. But that fact
doesn't help me here. Is there a workaround? (If there's
anything in perldoc DBI on this, I've missed it...)

 

[DJ Stunks]

I'm having trouble using the DBI module
with an INFORMIX database. I have a table
with a column variable called "filesize" defined
as INT8. Given a value greater than 2**31,
say 4154628096, that I want to store into it,
if I code

$S = $dbk->prepare("UPDATE stat_daily SET filesize = 4154628096")
$S->execute();

...this will store the correct value.

because this passes the information as a simple string to the database
for interpretation.

But if I code
$Size = 4154628096;
$S = $dbk->prepare("UPDATE stat_daily SET filesize = ?");
$S->execute($Size);

...it stores a meaningless value. Our version of Perl is not
64-bit enabled and upgrading is not an option.

I assume the issue is that Perl (without 64bitint support) cannot
assign the value 4154628096 to a scalar (try printing $Size after the
assignment...) therefore my suggestion is to set $Size to the
stringified version:

$Size = '4154628096';

you may then need to cast the filesize in your UPDATE as an integer
from a string (or not, depending on how forgiving Informix is). As the
DBI docs indicate you can do this on the database side, or you can do
it on the Perl side as follows:

use DBI qw{ :sql_types };

my $sql = 'UPDATE stat_daily SET filesize = ?';
my $size = '4154628096';

$sth->prepare( $sql );
$sth->bind_param(1, $size, SQL_INTEGER);
$sth->execute();

Your setup is unique enough that I can't test any of this, so try it
out.

HTH,
-jp

 

[DJ Stunks]

$sth->prepare( $sql );

by which I meant

my $sth = $dbh->prepare( $sql );

of course ~

-jp

 

[mailbox]

(e-mail address removed) wrote:
assignment...) therefore my suggestion is to set $Size to the
stringified version:

$Size = '4154628096';

you may then need to cast the filesize in your UPDATE as an integer
from a string (or not, depending on how forgiving Informix is). As the
DBI docs indicate you can do this on the database side, or you can do

That's it! All I had to do, in fact was stringify $Size:
$StrSize = sprintf("%s", $Size);
....and DBI took it in the original format of my execute statement
without
any kind of casting:
$I->execute($StrSize);

Earlier, though, I had tried
$S = $dbk->prepare("UPDATE stat_daily SET size = '4154628096' ");
....and got an error message, and this failure diverted me away from
the stringifying solution. Thanks very much.