Hi,
I'm working with DBD::Oracle at the moment and getting a little
frustrated with integer numbers. I'd like my database returned integers
to look like integers to Perl but they currently don't until I add 0 to
them. Here is the problem:
The code calls an Oracle procedure which returns a reference cursor and
fetching from this cursor leads to a mixture of utf8 encoded strings and
integers. Mostly the data is fetched in one go with fetchall_arrayref
but occasionally it is bound and fetched one row at a time. Either way
the resulting array representing the database result-set is converted to
JSON via JSON::XS. For a simple case where 1 row is returned containing
1 integer column I get the resulting JSON:
["1"] # this is a JSON string
However, if I add 0 to the fields I know are integers before I convert
to JSON I get:
[1] # smaller and more accurate JSON number
The reason for this is that adding 0 to a Perl scalar seems to persuade
Perl the scalar is an integer. The difference from Deve::Peek is as follows:
$num as returned from database:
SV = PV(0x9068f04) at 0x90288d4
REFCNT = 2
FLAGS = (PADBUSY,PADMY,POK,pPOK,UTF8)
PV = 0x90c1120 "1"\0 [UTF8 "1"]
CUR = 1
LEN = 4
$num after adding 0:
SV = PVMG(0x8f6f900) at 0x90288d4
REFCNT = 2
FLAGS = (PADBUSY,PADMY,SMG,IOK,pIOK)
IV = 1
NV = 0
PV = 0x90c1120 "1"\0
CUR = 1
LEN = 4
MAGIC = 0x90c0d78
MG_VIRTUAL = &PL_vtbl_utf8
MG_TYPE = PERL_MAGIC_utf8(w)
MG_LEN = -1
The thing is that JSON::XS (and a number of other modules we've using)
uses SvIOKp (sv) (initially) to decide whether to try using this as a
number and this seems perfectly reasonable to me. The problem is that
now I have to go through all my result-sets from the database and add 0
to all the integers.
I was hoping binding the columns and declaring the bound type as
ora_type => ORA_LONG would return me numbers but as the code I include
below shows, this makes no difference.
Does anyone know of a way to get numbers (as I've described) back from
DBD::Oracle.
And before anyone points it out, I know scalars in perl are strings or
numbers depending on the context in which they are used but JSON::XS is
not the only module looking at the underlying scalar flags.
use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_types SQLCS_IMPLICIT);
use Devel::Peek;
use JSON::XS;
use Data::Dumper;
use Encode;
my $h = DBI->connect('dbi:Oracle:XX', 'xxx', 'xxx') or die "connect!";
eval {local $h->{PrintError} = 0;$h->do(q/drop table martin_long/)};
$h->do(q/create table martin_long (a integer)/);
$h->do(q/insert into martin_long values(1)/);
my $s = $h->prepare(q/select * from martin_long/);
$s->execute;
my $num;
$s->bind_col(1, \$num, {ora_type => ORA_LONG});
my @data;
my @altdata;
while ($s->fetch) {
push @data, $num;
print "\$num as returned from database:\n";
Dump($num);
$num = $num + 0;
print "\$num after adding 0:\n";
Dump($num);
push @altdata, $num;
}
print Dumper(\...@data);
print JSON::XS->new->encode(\...@data), "\n";
print JSON::XS->new->encode(\...@altdata), "\n";
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
