yet another question about numbers and strings

Discussion in 'Perl Misc' started by hymie!, Mar 15, 2012.

  1. hymie!

    hymie! Guest

    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
    -------------------------------------------------------------------------------
    hymie!, Mar 15, 2012
    #1
    1. Advertising

  2. hymie!

    hymie! Guest

    In our last episode, the evil Dr. Lacto had captured our hero,
    Ben Morrow <>, who said:
    >
    >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
    -------------------------------------------------------------------------------
    hymie!, Mar 16, 2012
    #2
    1. Advertising

  3. hymie!

    hymie! Guest

    In our last episode, the evil Dr. Lacto had captured our hero,
    Ben Morrow <>, who said:
    >
    >Quoth (hymie!):


    >> I'm now running DBD::Sybase 1.14 , and I'm still having the same
    >> problem.
    >>
    >> ID bigint 8 not null
    >> 9.21814636810375e+18

    >
    >Odd. What do you get from this?
    >
    > use DBI;
    > use Devel::peek;
    >
    > 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
    -------------------------------------------------------------------------------
    hymie!, Mar 23, 2012
    #3
  4. hymie!

    hymie! Guest

    In our last episode, the evil Dr. Lacto had captured our hero,
    Ben Morrow <>, who said:
    >
    >Quoth (hymie!):
    >> In our last episode, the evil Dr. Lacto had captured our hero,
    >> Ben Morrow <>, who said:
    >> >Quoth (hymie!):

    >>
    >> >Odd. What do you get from this?
    >> >
    >> > use DBI;
    >> > use Devel::peek;
    >> >
    >> > 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

    >
    >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 Devel:peek 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
    -------------------------------------------------------------------------------
    hymie!, Mar 25, 2012
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Serdar C.
    Replies:
    2
    Views:
    503
    Serdar C.
    May 8, 2005
  2. Chris Uppal

    And yet another generics question

    Chris Uppal, Nov 2, 2005, in forum: Java
    Replies:
    11
    Views:
    511
    Chris Uppal
    Nov 4, 2005
  3. Berehem
    Replies:
    4
    Views:
    537
    Lawrence Kirby
    Apr 28, 2005
  4. Ben

    Strings, Strings and Damned Strings

    Ben, Jun 22, 2006, in forum: C Programming
    Replies:
    14
    Views:
    733
    Malcolm
    Jun 24, 2006
  5. one man army

    Numbers to strings to numbers again

    one man army, Dec 28, 2005, in forum: Javascript
    Replies:
    6
    Views:
    132
    one man army
    Dec 30, 2005
Loading...

Share This Page