1 Reply Latest reply: Sep 24, 2012 7:07 PM by jbo5112 RSS

    BigInt / __int64 support in ODBC driver 11

      Hi - I am trying to use BigInt via ODBC - does the v11 odbc driver (or any driver) support this?


      (Client: Win7-64 running a 32-bit client app, Server: Windows Server 2003, Oracle 11g)
        • 1. Re: BigInt / __int64 support in ODBC driver 11
          This may not be helpful to you anymore, but when a question shows up as the first hit to multiple Google searches, it deserves an answer.

          To the best of my knowledge, the first (and currently only) support Oracle provided for BigInt on a 32-bit client is with OCI on Oracle 11.2. I personally use OTL (Oracle, Odbc and DB2-CLI Template Library) for a connection library wrapper in C++, which I think makes the .NET interface look like some sort of cross between assembly and rewriting War and Peace. I only have to change a few #defines to switch between OCI and ODBC (or anything else it supports), plus it has options to transparently convert my unsupported BigInt's into strings and back.

          Assuming you're staying with ODBC, the most straightforward workaround is to hope a 32-bit int is big enough. You can also use a double, which accurately gives you a range of all integers from -2^53 to 2^53 without rounding errors or losing precision. If you need the full 64-bit precision, you have 2 options. The easiest is to transport the number as a string in ODBC and have both Oracle and your program converting between strings and 64-bit ints. It would usually be more efficient (but more difficult) to break the 64-bit int into 2 32-bit ints that get reassembled. This is easiest (for both programming and CPU) to do with bitwise operators, but unfortunately Oracle has some of the worst support of any database. A rough example:

          // constant
          LARGE_INT=4294967296 // 2^32

          // split integer
          regular_int2=bigint-(regular_int1*(LARGE_INT)); // this result needs to be unsigned

          // different methods to combine integers
          mybigint=(regular_int1<<32) | regular_int2;

          As research, I've personally tried the latest ODBC driver that comes with the instant client against Oracle and Oracle, everything running on 64-bit Linux. Those platforms do not support BigInt w/ ODBC, and would probably get support before 32-bit ODBC clients and servers. Microsoft provides an ODBC driver for Oracle, as well as there being an ODP.NET driver for Oracle, but they don't appear to support BigInt either. Easysoft also provides ODBC drivers for a fee ($2,800-$44,000 per license) that looks like it currently supports BigInt for a 64-bit client (via OCI 11.1 support), but when a driver costs more than my server I don't much care what it does.