Re: [sqlite] storing unsigned 64 bit values

[Jens Alfke]

> On Sep 27, 2018, at 3:53 AM, Conor Lennon  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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-
>boun...@mailinglists.sqlite.org] 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[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-
>boun...@mailinglists.sqlite.org] On Behalf Of Conor Lennon
>Sent: Thursday, 27 September, 2018 10:10
>To: sqlite-users@mailinglists.sqlite.org
>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
> 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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] storing unsigned 64 bit values

[Simon Slavin]

On 27 Sep 2018, at 5:10pm, Conor Lennon  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.



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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] storing unsigned 64 bit values

[Simon Slavin]

On 27 Sep 2018, at 11:53am, Conor Lennon  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[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?

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