CODEWITHSUNDEEP
sqlite
RussAwesome
RussAwesome

Reputation: 464

SQLite column with affinity TEXT still stores long number as INTEGER

I have a table called deliverysimp into which I am trying to insert some data. I am aware that the data types for the columns are just affinities and not restrictions, however I need to store the parcelid column below as TEXT.

CREATE TABLE IF NOT EXISTS deliverysimp (parcelid TEXT, expected integer, primary key (parcelid))

I am using the following javascript to insert the data to the database:

context.executeSql("INSERT INTO deliverysimp(parcelid, expected) values(?,?)",
    [
    '' + delivery.parcelid,
    delivery.expected
    ], function () { }, me.ErrorHandler);

You can see I have tried to add a blank '' + before the parcelid to try and force the affinity, but the behaviour is the same without; namely:

if I try to store the parcelid 33333333333322222222222222222222223 this is stored into the database as 3.3333333333322223e+34 and I need this to be a text/string representation.

Any ideas how I can get SQLite to honour this as TEXT?

Upvotes: 1

Views: 153

Answers (1)

varro
varro

Reputation: 2482

I suspect that you already have a string, just not the string you expected. Since the number you have cannot be represented by an 8-byte integer, it gets converted into a real number and that gets converted into a string, i.e., '3.3333333333322223e+34'. So, if you want the value to be '33333333333322222222222222222222223', then that's what you have to insert into the table.

To check, do a SELECT parcelid, TYPEOF(parcelid) FROM deliverysimp; using the sqlite3 command-line tool and see what you get.

Upvotes: 2

Related Questions