storing unsigned 64 bit values

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Simon Slavin-3
On 27 Sep 2018, at 11:53am, Conor Lennon <[hidden email]> wrote:

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Conor Lennon


On 27/09/18 17:03, Simon Slavin wrote:
> On 27 Sep 2018, at 11:53am, Conor Lennon <[hidden email]> wrote:
>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: storing unsigned 64 bit values

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:[hidden email]] Im Auftrag von Conor Lennon
Gesendet: Donnerstag, 27. September 2018 18:10
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] storing unsigned 64 bit values



On 27/09/18 17:03, Simon Slavin wrote:
> On 27 Sep 2018, at 11:53am, Conor Lennon <[hidden email]> wrote:
>
>> 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
[hidden email]
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) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Simon Slavin-3
In reply to this post by Conor Lennon


On 27 Sep 2018, at 5:10pm, Conor Lennon <[hidden email]> wrote:

> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Keith Medcalf
In reply to this post by Conor Lennon

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-
>[hidden email]] On Behalf Of Conor Lennon
>Sent: Thursday, 27 September, 2018 10:10
>To: [hidden email]
>Subject: Re: [sqlite] storing unsigned 64 bit values
>
>
>
>On 27/09/18 17:03, Simon Slavin wrote:
>> On 27 Sep 2018, at 11:53am, Conor Lennon
><[hidden email]> wrote:
>>
>>> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Nathan Wagner
On Thu, Sep 27, 2018 at 11:05:24AM -0600, Keith Medcalf wrote:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

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-
>[hidden email]] On Behalf Of Nathan Wagner
>Sent: Thursday, 27 September, 2018 11:50
>To: SQLite mailing list
>Subject: Re: [sqlite] storing unsigned 64 bit values
>
>On Thu, Sep 27, 2018 at 11:05:24AM -0600, Keith Medcalf wrote:
>
>> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: storing unsigned 64 bit values

Jens Alfke-2
In reply to this post by Conor Lennon


> On Sep 27, 2018, at 3:53 AM, Conor Lennon <[hidden email]> wrote:
>
> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users