Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Overflow of $DBI::rows

by DanEllison (Sexton)
on Dec 07, 2012 at 15:51 UTC ( #1007766=perlquestion: print w/ replies, xml ) Need Help??
DanEllison has asked for the wisdom of the Perl Monks concerning the following question:

We are executing some simple updates via Perl and ODBC against a Teradata warehouse. We were using $DBI::rows to capture the number of rows affected, however we are now updating more than 2B rows and it appears $DBI::rows is overflowing. Any suggestions around this with out doing a separate SELECT COUNT(*)?

Comment on Overflow of $DBI::rows
Re: Overflow of $DBI::rows
by mbethke (Hermit) on Dec 07, 2012 at 16:21 UTC
    Sounds unlikely. I have absolutely no experience with Teradata and little with ODBC but I dare say if the variable overflows at 2^31 it's very likely to be an C-level integer. Moving to a 64bit-machine is not an option for you? Perhaps with this amount of data you'd benefit from it anyway. Other than that it's probably difficult to fix on 32bit platforms as larger values could be returned to Perl but would necessarily become inaccurate, which is probably worse than not being able to use them at all.
      We are in fact running 64bit Perl on a 64bit machine.
        Windows? When I run Makefile.PL for DBD-ODBC-1.41 on Linux, I get the following:
        unixODBC 2.3.1 DRIVERS............: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources USER DATA SOURCES..: /home/mb/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
        All 64bit sizes.
        dbdimp.h has this definition:
        SQLLEN RowCount; /* Rows affected by insert, update, delete * (unreliable for SELECT) */
        This member looks like it was the culprit here and somehow ended up as 32bit. The build output of DBD-ODBC should have some clues.
Re: Overflow of $DBI::rows
by roboticus (Canon) on Dec 07, 2012 at 16:29 UTC

    DanEllison:

    I'd try one of two things: Either tell the database to explicitly use a larger numeric datatype in the hope that the driver will handle it:

    select convert(numeric(15),count(*)) from ...

    or failing that, have the database convert it to a string:

    select convert(varchar(32),count(*)) from ...

    I've never used Teradata, so I don't know if the syntax would need tweaking, but I'd expect a trick like this to get you to your goal.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      I'm trying to avoid doing the separate SELECT COUNT(*). What we are performing is:
      my $sth = $dbh->prepare("UPDATE ...;") or die DBI::errstr; $sth->execute or die DBI::errstr; printf "Rows: %s\n", $sth->rows;
      and $sth->rows is returning a negative number.

        DanEllison:

        D'oh! Sorry about that. You were clear, I just had a brain glitch.

        I don't know a way to fix that, but I took a quick look at the ODBC Programmers Reference at msdn, and they're mentioning that the MDAC 2.7 ODBC libraries allow 64-bit values for the SQLLEN data type (used by SQLRowCount). If you're on a Windows platform, it might be sufficient to make sure you're using the latest ODBC libraries. (Of course, the Teradata end might still be using the 32 bit types, in which case you're probably out of luck.)

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Re: Overflow of $DBI::rows
by mje (Deacon) on Dec 10, 2012 at 10:19 UTC

    When DBD::ODBC is built 64 bit and has access to the correct header files SQLRowCount (the ODBC API to retrieve rows affected) should return an SQLLEN which on 64 bit platforms is 8 bytes. However, the DBI XS interface to DBD::ODBC has:

    int dbd_st_execute( SV *sth, imp_sth_t *imp_sth)

    and dbd_db_execute is supposed to return the rows affected. There are also other places in DBD::ODBC where the affected rows is cast to an int. I think fixes would be required in both DBI and DBD::ODBC. See also:

    dbd_xsh.h:int dbd_st_execute _((SV *sth, imp_sth_t *imp_sth));

    in dbd_xsh.h of DBI.

      Actually these ints should automatically 64 bits on any sane compiler where SQLLONG would be 64 bits, even if the standard doesn't require it. For clarity they should be fixed to size_t where negative values aren't used to signal errors, although I don't think any of them could be at fault here.

        They cannot be size_t for a start because they have to be able to contain negative values. I cannot comment as to what you believe is a sane compiler but ints and long ints on Windows 64 are 32 bits.

Re: Overflow of $DBI::rows
by mje (Deacon) on Dec 13, 2012 at 09:29 UTC

    You will find some changes in DBD-ODBC 1.42_1 which may be of interest to you. The interface between DBI and DBDs needs to change in order to support larger values of rows affected (it is an int right now). As this change would require all DBDs to be changed this is not going to happen right now. Until then I have made the following changes:

    • Add an odbc_rows method which returns the same as execute would except there is no overflowing.
    • changed execute so if it spots overflowing it will warn (if DBI Warn is set which it is by default) and then it returns INT_MAX so you can check for that.

    I'd appreciate any feedback on this if you have a chance to try it out as you appear to be the first person to insert/update such a huge number of rows and notice the problem.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1007766]
Approved by blue_cowdawg
Front-paged by blue_cowdawg
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2014-07-31 08:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (246 votes), past polls