CODEWITHSUNDEEP
c++databaseoracle11guint64occi
Marco Fiocco
Marco Fiocco

Reputation: 352

Best way to store an unsigned 64 bit integer in Oracle Database 11g using OCCI

I'm using version 11.2 and there is no direct support for uint64 in table fields. What do you suggest to do performacewise? It must be the primary key.

Thanks

Upvotes: 1

Views: 2773

Answers (5)

EvilTeach
EvilTeach

Reputation: 28882

Possibly convert it to char, then insert it as a char, with a to_number to put it in the correct format?

Upvotes: 1

Dmytro Ovdiienko
Dmytro Ovdiienko

Reputation: 1156

Based on information in the Oracle® C++ Call Interface Programmers Guide in the chapter 5.1.3.18

You should not have to use NUMBER as an external data type. If you do use it, Oracle returns numeric values in its internal 21-byte binary format and expects this format on input. The following discussion is included for completeness only.

Oracle stores values of the NUMBER data type in a variable-length format. The first byte is the exponent and is followed by 1 to 20 mantissa bytes. The high-order bit of the exponent byte is the sign bit; it is set for positive numbers and it is cleared for negative numbers. The lower 7 bits represent the exponent, which is a base-100 digit with an offset of 65.

To calculate the decimal exponent, add 65 to the base-100 exponent and add another 128 if the number is positive. If the number is negative, you do the same, but subsequently the bits are inverted. For example, -5 has a base-100 exponent = 62 (0x3e). The decimal exponent is thus (~0x3e)-128-65 = 0xc1-128-65 = 193-128-65 = 0.

Each mantissa byte is a base-100 digit, in the range 1 to 100. For positive numbers, the digit has 1 added to it. So, the mantissa digit for the value 5 is 6. For negative numbers, instead of adding 1, the digit is subtracted from 101. So, the mantissa digit for the number -5 is: 101-5 = 96. Negative numbers have a byte containing 102 appended to the data bytes. However, negative numbers that have 20 mantissa bytes do not have the trailing 102 byte. Because the mantissa digits are stored in base-100, each byte can represent two decimal digits. The mantissa is normalized; leading zeroes are not stored.

In order to read/write 64 bit integer you should call getNumber().toBytes() / getNumber().fromBytes() and then apply the logic explained above.

Upvotes: 0

Mochamad Aris Zamroni
Mochamad Aris Zamroni

Reputation: 51

You can use number(20,0) as 64 bit integer is maxed at 18,446,744,073,709,551,615.
Using number type allows mathematical operationtoo.

Upvotes: 1

Marco Fiocco
Marco Fiocco

Reputation: 352

I'm using a RAW(8) data type, and write it with:

uint64 i;
Bytes key((unsigned char*)&i, 8);
statement->setBytes(1, key);

Fast and compact, and seems to work well.

Upvotes: 1

WhozCraig
WhozCraig

Reputation: 66254

I know nothing about Oracle, but MS SQL is plagued similarly, and I ended up storing my 64-bit unsigned ints in binary(8) fields. If Oracle has similar field caps (and i can't imagine it doesn't) perhaps the same would work for you.

The upshot on SQL Server is binary(n) fields compared against other binary(n) fields effectively compare as byte-arrays, and if sized the same, it means they also compare as big-endian representation (if that is how you stored them, and you would be nuts not to).

Sorry I'm not Oracle savvy. Gotta dance with the one that brought ya =)

Upvotes: 1

Related Questions