[sqlite] storing unsigned 64 bit values

[Conor Lennon]

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 65)

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?

[Simon Slavin]

Post by Conor Lennon
e.g. 18446744073709551615 (one less than 2 to the power of 65)
I seem to have managed to store this value in a database.

What is the affiliation for that column ? Did you declare it as INTEGER or something else ?

Simon.

[Conor Lennon]

Post by Simon Slavin

Post by Conor Lennon
e.g. 18446744073709551615 (one less than 2 to the power of 65)
I seem to have managed to store this value in a database.

What is the affiliation for that column ? Did you declare it as INTEGER or something else ?
Simon.

It's declared as a unsigned integer

sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
    "id" integer NOT NULL PRIMARY KEY,
    "bigvalue" integer unsigned NOT NULL UNIQUE
);

[Hick Gunter]

"unsigned" is ignored by sqlite. Depending on how you inserted the value, it could be stored as a text or as a real value, irrespective of the declared type.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] Im Auftrag von Conor Lennon
Gesendet: Donnerstag, 27. September 2018 18:10
An: sqlite-***@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] storing unsigned 64 bit values

Post by Simon Slavin

Post by Conor Lennon
e.g. 18446744073709551615 (one less than 2 to the power of 65)
I seem to have managed to store this value in a database.

What is the affiliation for that column ? Did you declare it as INTEGER or something else ?
Simon.

It's declared as a unsigned integer

sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
"id" integer NOT NULL PRIMARY KEY,
"bigvalue" integer unsigned NOT NULL UNIQUE );

_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.

[Simon Slavin]

Post by Conor Lennon
It's declared as a unsigned integer

There is no such thing in SQLite. SQLite has an integer type, but it is an 8-byte signed integer.

<https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes>

If you don't need to sort on that column, just search and select, then you could store that value as TEXT or a BLOB, whichever is more convenient. If you need to sort but don't need perfect precision, you could store the value as REAL. But SQLite has no way of handing a 64-bit unsigned integer. Sorry.

Simon.

[Keith Medcalf]

Well, you could call it a shiny shoe integer. the "shiny shoe" part is just ignored, just like your use of the word unsigned. And no, the value stored was a IEEE-754 double precision floating point so you got to keep the high 53 bits are the rest were discarded (this is because the value was numeric (that is, all numbers) and could not fit in a signed integer, so the only way to store something of that magnitude is as a double-precision float.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-
Sent: Thursday, 27 September, 2018 10:10
Subject: Re: [sqlite] storing unsigned 64 bit values

On 27 Sep 2018, at 11:53am, Conor Lennon

Post by Conor Lennon
e.g. 18446744073709551615 (one less than 2 to the power of 65)
I seem to have managed to store this value in a database.

What is the affiliation for that column ? Did you declare it as

INTEGER or something else ?

Simon.

It's declared as a unsigned integer
sqlite> .schema mytable
CREATE TABLE IF NOT EXISTS "mytable" (
    "id" integer NOT NULL PRIMARY KEY,
    "bigvalue" integer unsigned NOT NULL UNIQUE
);
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Nathan Wagner]

so the only way to store something [larger than a signed 64-bit int]
is as a double-precision float.

I'd like to point out that you could *store* it as the hex or decimal
text representation of the integer. If you included leading zeros, it
would even sort correctly. If you actually need to do arithmetic on it
within sqlite, that's another matter.

(Or any non-weird base representation, for that matter.)

[Keith Medcalf]

If you wanted to store it as purple tree smoke signals you could do that too. However, the fact of the matter is that SQLite3 does not perform conversions to and from purple tree smoke signals, and the OP was not trying to store the value as a "hex or decimal representation of the number", or as a blob, or anything else. He was trying to store it as a signed integer. And it was clearly too large a magnitude for a signed integer. Of the numeric formats available that it could be stored as in the current version of SQLite3, that leaves an IEEE-754 binary64 as the only other option. Thus that is what was done.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

-----Original Message-----
From: sqlite-users [mailto:sqlite-users-
Sent: Thursday, 27 September, 2018 11:50
To: SQLite mailing list
Subject: Re: [sqlite] storing unsigned 64 bit values

so the only way to store something [larger than a signed 64-bit

int]

is as a double-precision float.

I'd like to point out that you could *store* it as the hex or decimal
text representation of the integer. If you included leading zeros,
it
would even sort correctly. If you actually need to do arithmetic on
it
within sqlite, that's another matter.
(Or any non-weird base representation, for that matter.)
--
nw
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Jens Alfke]

Post by Conor Lennon
The problem that I have is retrieving the value using c bindings.
I'm calling sqlite3_column_int64.
...
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)

How did you store the number? If you called sqlite3_bind_int64, then the value should survive the round-trip unscathed, even though SQLite will interpret the value as signed. But it’s still the same 64-bit pattern, and if you cast it from/to uint64_t it’ll work. (The only problem is that SQLite will think it’s a negative number, so sorting and some arithmetic won’t work properly. Addition and subtraction will, though.)

But it sounds like you added the value literally to the SQL statement; this is a bad idea for many reasons. It’s more expensive to run the query because it has to be parsed every single time, you don’t get type-checking or even syntax-checking, and if you ever try to do this with strings instead of ints, you can easily open yourself up to SQL-injection attacks. Don’t do it!

—Jens