SQLite Type Problem

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

SQLite Type Problem

Post by phaworth » Tue Jan 26, 2010 3:10 am

I've run into what seems to be a pretty serious problem using Revolution with SQLite.

I have a column with type INTEGER. Most of the time, it contains just integers with no decimal places. Occasionally, it contains a value that includes two decimal places and SQlite obligingly changes its type to REAL in those circumstances. However, the Rev db routines for getting data out of the database don't take account of the changed type; they still treat the column as type INTEGER which results in the number being truncated and losing the decimal places.

Retrieving data from this column in the Firefox SQLite manager tool produces the correct result whether the value in the column is INTEGER or REAL.

There is another twist to this. If I use the SUM function on the column in question in a SELECT statement to get a total of the values in it, I get the correct result, including the decimal places!

It appears the Rev uses the type of a column defined in the CREATE statement for the table, not the actual type of the data. Has anyone else run into this and perhaps found a way round it?


Pete

Janschenkel
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 977
Joined: Sat Apr 08, 2006 7:47 am
Contact:

Re: SQLite Type Problem

Post by Janschenkel » Tue Jan 26, 2010 10:29 am

Rev hes nothing to do with this issue, it's just how SQLite works. To SQLite, everything is a string, and it happily stores integers and decimals as strings internally - there is no actual checking of the content. See also this entry in the SQLite FAQ: SQLite lets me insert a string into a database column of type integer!

Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: SQLite Type Problem

Post by phaworth » Tue Jan 26, 2010 7:31 pm

SQlite does not have strong typing, that's true, but the SQLite site goes into great detail as to what happens when data of a different type is inserted into a column and it does not store everything as strings. See http://www.sqlite.org/datatype3.html for complete information.

If you insert a decimal number into a column defined as INTEGER, SQLite stores it as a REAL type so that the decimals are preserved. There is a typeof function so you can discover exactly what type the data is in a specific column in a specific row and deal with it appropriately. SQLite's datatyping makes it imperative that any program that accesses data check the actual type of the data it is retrieving and not rely on how that data is defined in the schema. Rev clearly doesn't do that whereas the Firefox SQLIte Manager and the sqlite3 command line interface do.

SO I have to disagree and say that this is in fact a problem in the Rev db functions when used with SQLite.

Pete

Simon Knight
Posts: 919
Joined: Wed Nov 04, 2009 11:41 am

Re: SQLite Type Problem

Post by Simon Knight » Sun Feb 21, 2010 12:40 pm

Did you find a work around to this problem? I am thinking of migrating a SQLlite Db from a RealBasic front end to a new RunRev front end and I am attempting to identify what problems I will have to be overcome. In this case would defining all the fields as strings solve the problem from within RunRev?

Simon
best wishes
Skids

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: SQLite Type Problem

Post by phaworth » Sun Feb 21, 2010 6:37 pm

Well I guess it depends what you mean by a workaround. I have not found a way to force Rev to observe the correct datatype of a value instead of assuming what is defined in the schema so I guess from that perspective the answer would be no. My solution was to unload all the data from my db, redefine the schema to use REAL columns instead of INTEGER for every column where I expected to include numbers with decimal places, the reload the data. That gives rise to other issues of accuracy when doing calculations on REAL fields but in my case I only care about the first two decimal places so I just round everything to two decimal places.

I've come across other areas where Rev doesn't correctly handle SQLite syntax. At one time, it had problems with a SUBSTR function within a SELECT, while other SQLite management tools would process the exact same statement correctly. It seems like Rev does some parsing on SQL statements before passing them to the underlying db engine for reasons I don;t understand. It's also possible some of these problems are caused by the fact that Rev includes it's own copy of the SQLite library (as do all SQLite tools I believe) so if they were using an older version of that library, it could include bugs that had been fixed in the library version used by the other SQLite tools I have on my computer.

Off the top of my head, defining everything as text doesn't feel right but I can't point to anything specific. I think if you stick with the correct datatype definitions for the data you use, you'll probably be fine. I stick with TEXT for text, date, and time fields, INTEGER for fields that will only ever contain whole numbers, and REAL for fields that may contain decimal places. The only other grey area is what to do for fields that hold whatever represents true and false. SQLite includes a BOOLEAN datatype but it actually gets treated as INTEGER internally. But Rev of course uses the strings "true" and "false" so if your db will never be used by any other application than Rev, you might choose to use TEXT fields to represent boolean values and store the Rev true/false strings in there.

Despite all that, I am happy with using SQLite with REV. It's an amazing db system for the price! And no doubt other db systems that you have to pay for would suffer from their own set of problems, so I wouldn't tell you not to use SQLite.

Pete

Post Reply