Ways of ensuring integers in the database are seen as numbers in perl?
- From: martin.evans@xxxxxxxxxxxx (Martin Evans)
- Date: Tue, 27 Jan 2009 15:21:55 +0000
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
.
- Follow-Ups:
- Re: Ways of ensuring integers in the database are seen as numbers in perl?
- From: Peter J. Holzer
- Re: Ways of ensuring integers in the database are seen as numbers in perl?
- From: Stephen Carville
- Re: Ways of ensuring integers in the database are seen as numbers in perl?
- Prev by Date: RE: Recurse gives me No more handles
- Next by Date: ODBC - SQL Server image data problems with recent releases of ODBC
- Previous by thread: online redefinition via dbms_redefinition causes ORA-12096 and ORA-00942 for statement handle
- Next by thread: Re: Ways of ensuring integers in the database are seen as numbers in perl?
- Index(es):
Relevant Pages
|
