SQLite Real Number Issue

[swhite]

Hi

Since SQLite does not assign a type to a column I have an issue where a column that I want to always be a real number sometimes is returned as an Integer. For example 50.01 is a real value and returned as real by SQLite but 50.00 is a real value but it is stored in SQLite as 50 and then returned as an integer. So when I want to use the PB GetDatabaseDouble() I do not get a value return instead I have to use GetDatabaseLong(). Currently I am checking the column type every time I want to get the value from the database but I wondered if there is a better scenario to ensure the value is correctly read from the data base.

Thanks,
Simon

[skywalk]

Don't understand your question?
Open your database in a db browser and inspect the table schema.
Or, if you are the creator, make sure to assign a Double to your desired columns.
CREATE TABLE Keywords (Word TEXT, Item TEXT, img BLOB, `X(dB)` DOUBLE);

[deeproot]

I've been using large SQLite databases with PureBasic for years and never had a problem with Real versus Integer columns!

For example 50.01 is a real value and returned as real by SQLite but 50.00 is a real value but it is stored in SQLite as 50 and then returned as an integer.

Not the whole story - while SQLite allows storing any type in most columns, it also assigns a "type affinity" to each column which can distinguish between Real or Integer.

See https://www.sqlite.org/datatype3.html

skywalk's advice is good - CREATE TABLE determines the type affinity.

[swhite]

Hi

After further investigation I found the source of the problem. The column had been created with "num" type and not the "real" type. Once I changed the column to real everything worked correctly.

Thanks,
Simon