yet another question about numbers and strings

[hymie!]

Greetings.

I'm not entirely sure if I have a Perl problem, a MSSQL problem, or a
DBI problem. But it's a problem, and maybe somebody can help me
resolve it.

I have an MSSQL database. It includes a field for ID numbers
that are about 18 digits long.

I have a generic searching tool that I use on this database. It basically
does this:

use DBI;
my $dbh=DBI->connect("DBI:Sybase:$host",$user,$pass) or
die "Couldn't connect to MSSQL: $DBI::errstr\n";
$sth = $dbh->prepare("select * from $ARGV[0]");
$sth->execute;
while (my @row = $sth->fetchrow_array)
{
foreach my $i (0 .. $#row)
{
print "$headings[$i]\t$defs[$i]\n\t$row[$i]\n";
}
print "==========\n";
}

Sadly, I end up with a response that looks like this:
ID bigint 8 not null
9.21474030305498e+18

I'm not doing any mathematical operations on the number. I just need
to know what the actual number is.

Again, I have no idea if this means MSSQL is sending me data in exp format,
or if Perl is doing the changing for me.

But if anybody knows how to fix it, I'd be grateful.

--hymie! http://lactose.homelinux.net/~hymie (e-mail address removed)
-------------------------------------------------------------------------------

 

[hymie!]

In our last episode, the evil Dr. Lacto had captured our hero,

At a guess: you are using a 32bit build of perl, and it isn't built with
-Duse64bitint?

No, I'm pretty sure (but not positive) that it's a 64-bit perl.

This is perl, v5.8.5 built for x86_64-linux-thread-multi
use64bitint=define use64bitall=define uselongdouble=undef
optimize='-O2 -g -pipe -m64',
libpth=/usr/local/lib64 /lib64 /usr/lib64
Compile-time options: DEBUGGING MULTIPLICITY USE_ITHREADS USE_64_BIT_INT
USE_64_BIT_ALL USE_LARGE_FILES PERL_IMPLICIT_CONTEXT

At a further guess: you are using DBD::Sybase version 1.09 or older?

Yes, I am using Sybase 1.07 .

* In DBD::Sybase 1.09 and before, certain large numeric types (money,
* bigint) were being kept in native format, and then returned to the
* caller as a perl NV data item. An NV is really a float, so there was
* loss of precision, especially for bigint data which is a 64bit int.

I will look into updating it to a newer version. Thanks for the tip.

--hymie! http://lactose.homelinux.net/~hymie (e-mail address removed)
-------------------------------------------------------------------------------

 

[hymie!]

In our last episode, the evil Dr. Lacto had captured our hero,

Quoth (e-mail address removed) (hymie!):

Odd. What do you get from this?

use DBI;
use Devel:eek;

my $dbh = DBI->connect("dbi:Sybase:...", ...);
my $bigint = $dbh->selectcol_arrayref(<<SQL);
SELECT bigint FROM table WHERE ...
SQL

Dump $bigint;

SV = PVNV(0x7c9f60) at 0x7eef40
REFCNT = 1
FLAGS = (NOK,POK,pNOK,pPOK)
IV = 9218146368103748608
NV = 9.21814636810375e+18
PV = 0x7ee710 "9.21814636810375e+18"\0
CUR = 20
LEN = 35

The perldoc that I have does not identify what a PVNV is, nor the NOK
flag. But the doc implies that, because the POK and NOK flags are set,
it will refer to the NV and PV fields, and not the IV field because the
IOK flag is not set.

Oh, I found it in perlguts. NV is a double.

But beyond that, I don't know what this is telling me.

--hymie! http://lactose.homelinux.net/~hymie (e-mail address removed)
-------------------------------------------------------------------------------

 

[hymie!]

In our last episode, the evil Dr. Lacto had captured our hero,

Quoth (e-mail address removed) (hymie!):

Have you stripped some of the Dump output, or did you run something
different? I was expecting $bigint to be an arrayref.

I'm sorry, I misunderstood. I didn't actually use selectcol_arrayref.
I just took my existing script and added Develeek to it.

Here is one result:

SV = RV(0x759088) at 0x63e1a0
REFCNT = 1
FLAGS = (PADBUSY,PADMY,ROK)
RV = 0x6f9350
SV = PVAV(0x6f8b90) at 0x6f9350
REFCNT = 1
FLAGS = (PADBUSY,PADMY)
IV = 0
NV = 0
ARRAY = 0x7eba90
FILL = 9
MAX = 13
ARYLEN = 0x0
FLAGS = (REAL)
Elt No. 0
SV = NV(0x5289b8) at 0x7d49a0
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 1.71104846977343e+18
Elt No. 1
SV = NV(0x5289c0) at 0x7eb090
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 1.69309275828193e+18
Elt No. 2
SV = NV(0x5289d0) at 0x7eb0a0
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 1.69318491698104e+18
Elt No. 3
SV = NV(0x5289d8) at 0x7eb0b0
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 1.68108084934812e+18

Here is a second result:

SV = RV(0x759938) at 0x630fb0
REFCNT = 1
FLAGS = (PADBUSY,PADMY,ROK)
RV = 0x6f9590
SV = PVAV(0x6f9040) at 0x6f9590
REFCNT = 1
FLAGS = (PADBUSY,PADMY)
IV = 0
NV = 0
ARRAY = 0x7ec180
FILL = 9
MAX = 13
ARYLEN = 0x0
FLAGS = (REAL)
Elt No. 0
SV = NV(0x5313a8) at 0x7eb4c0
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 9.21814636810375e+18
Elt No. 1
SV = NV(0x5313b0) at 0x7eb620
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 9.21474030305498e+18
Elt No. 2
SV = NV(0x5313c0) at 0x7eb630
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 9.21281446114217e+18
Elt No. 3
SV = NV(0x5313c8) at 0x7eb640
REFCNT = 1
FLAGS = (NOK,pNOK)
NV = 9.2061546186557e+18


I tried to do a "SELECT TOP 10" and "SELECT TOP 20" , but it only seems
to report 4.

Are you *sure* you're using the newer version?

I couldn't find a specific "print version" command to add to my script,
but I try this:

use DBD::Sybase 1.15;

the resulting error is

DBD::Sybase version 1.15 required--this is only version 1.14 at ./test.pl
line 13.

So yes, I'm as sure as I can be that I'm using the newer version.

(And that you didn't
define SYB_NATIVE_NUM?)

Not that I can tell:

/*
*
* #define SYB_NATIVE_NUM
*/

Something else you could try, if MSSQL
will let you, is to cast the bigint to varchar or something equivalent
before returning it. CAST(column AS varchar) would be the SQL92 syntax;
I don't know if MSSQL implements that or if there's some equivalent.

Yes, that exists and works, if I know the names of the columns in advance.
My intent was to make this a generic script that would work on any table.
But maybe I can do something with that idea.

--hymie! http://lactose.homelinux.net/~hymie (e-mail address removed)
-------------------------------------------------------------------------------