unsigned 64 bit value in sqlite with c bindings

Question

I am trying to store and retrieve unsigned 64 bit integer values in sqlite through c bindings.

e.g. 18446744073709551615 (one less than 2 to the power of 64) I seem to have managed to store this value in a database. When I run sqlite3 on the command line and select the column, I get back 1.84467440737096e+19

The problem that I have is retrieving the value using c bindings. I'm calling sqlite3_column_int64. This function returns back a sqlite3_int64 value, which is signed. When I call the function it returns back 9223372036854775807, which is the maximum size of a signed 64 bit integer (one less than 2 to the power of 63) There doesn't seem to be a sqlite3_column_uint64 function.

Any ideas?

score 2 · Accepted Answer · answered Sep 27 '18 at 20:16

2

Unsigned 64-bit integers cannot be directly represented by any of SQLite's data types.

You have to store them as text, or as a blob. In neither case will the database be able to do computations on them.

answered Sep 27 '18 at 20:16

CL.

173,858
17
217
259

[An other answer](https://stackoverflow.com/a/7337945/1182849) says it is possible. Also, the link you gave says that **INTEGER** can be 8 Bytes long which is 64bits. I'm confused ! – grorel Dec 19 '18 at 15:49
SQLite's 64-bit integers are signed, not unsigned (as mentioned in the question). – CL. Dec 19 '18 at 20:14

score 0 · Answer 2 · answered Feb 02 '21 at 00:41

It is possible to store 64-bit unsigned integers as native 8 byte values without having to convert them to text or a blob.

Define your table column with the INTEGER type. Before statement binding or other write operations [using the *_int64() variants] do a bit-for-bit cast to a signed 64-bit value, then write the value.

Reverse the process when reading. There will be no loss of precision.

Practically: Sometimes it helps to just think of your large value as an unsigned value that is 63 bits long. As long as you never set the 64th bit, no conversion will be necessary, and everything will work, and behave as expected. (And don't forget runtime/debugging checks to ensure you're enforcing the 63 bit rule.)

That said: If you do use the bit-for-bit casting method, you cannot depend on SQLite native calculations using that column (unless you're really good at understanding how the signed value is encoded.) You can test for equality without too much trouble since you're already good at converting the value using a bit-for-bit cast.

unsigned 64 bit value in sqlite with c bindings

2 Answers2