SQLite: The Bumps in the Road

Owned by David Harms
Last updated: Sept 05, 2013Version comment

by Unknown user

Any “conversion,” re-factoring, of an application, as in moving to SQL from flat files, will pose issues. The odds of getting a clean compile the first or second time (or third, fourth …), much less a correctly functioning app out of the box are … not especially good.

For Instance...

It is well known, and frequently forgotten (I certainly did), that any file field using against a SQL back ended Browse, Report or Process template procedure must be in the procedure's View, because SQL only retrieves the fields named in the view from the database. This is not the case for the same procedure types run against flat file systems as the entire record is always retrieved.

So, for example, to use an Amount field and a Debit/Credit mark field to differentially display debits on the left and credits on the right in a browse, I use this code:

Case Che:DRCR
Of 'D'
  Debit_ = Che:Amount
  Credit_ = ''
Of 'C'
  Credit_ = Che:Amount
  Debit_ =''
End

With .DAT and .TPS files, this code works perfectly. But with a SQL back end, if either CHE:DRCR or CHE:Amount is not in the View (either populated in the browse box or added to the “Hot Fields” list), the browse display will be … unpredictable … bizarre.

Similarly, filters may or may not - usually “not” - work correctly after the move to SQL. Dates in filters are especially prone to failing compared to .DAT and .TPS files. Filters on dates frequently fail to filter at all against a SQL db (SQL dates are often stored @D12 while Clarion stores even Date type fields as Longs). There is a real “gotcha” here: replacing the Filter expression with a {PROP:SQL} or removing the Filter expression and adding an embedded {PROP:SQLFilter} (on the View) – part of the objective in moving to SQL, one would think – often doesn't fix the filter and, in fact, can introduces new errors (usually BIND errors, at least in my experience).

Since there are well known issues in the move from flat files to SQL, I'm not all that concerned should one of the “knows” occurs. My question is whether there are any new things to look out for when moving from flat files to SQL with SQLite.

And, yes, there are some new things, new adventures, in SQLite.

The Test Apps

I selected two apps to convert to SQLite and test. Both are apps I wrote solely for myself, there are no other users. One is my checkbook app – I showed parts of it in my CMag article on running balances – the other is an app I use to keep track of articles and links for my personal web site – I showed parts of it in my CMag articles on building RSS .XML files.

The checkbook app uses the code, above, for taking a debit/credit flag and an amount field and displaying debits and credits in a standard accountancy manner (two columns, debits in the left, credits in the right). It also features heavy filtering in both browses and reports (see the previous comments on filter failure). Browses on CHECKS are filtered by account (having only one check file allows me to search for similar items regardless of the account, unlike Quicken). Reports are filtered by dates (definitely see the previous comments on filter failure). So this app should give me a good idea about how SQLite handles filters. Oh yes, this app is still in the Clarion template chain.

The RSS builder features a pair of radio buttons on the main browse that dynamically change the browse's filter. Currently, when the option control is Accepted:

Of ?Filter_
  Case Filter_
 Of 1
   BRW1.SetFilter('~ART:DatePosted or ART:DatePosted > Today()')         
 Else
   BRW1.SetFilter('')
 End
 ThisWindow.Reset(1)
 Select(?Browse:1)

The browse also features listbox header sorting. Lots of interesting test cases. This app is ABC and uses .TPS files.

Instead of walking through the conversion process, I am going to highlight several anomalous behaviors and what I learned or how I “fixed” it or when I decided I'd had enough and threw in the towel.

SQL syntax

It's been a while since I've worked with SQL, so preparatory to working with SQLite, I converted my data files from .DAT and .TPS to SQLite (as described in my previous article). There are several free SQLite browsers: I found “SQLite Database Browser,” which provides

  • a graphic display of the db schema (with creation scripts)
  • a data browser (on the second tab)
  • a SQL executor to test SQL statements against SQLite db's

Figure 1: SQLite Database Browser

But, be aware, it can lock up on a badly formatted query.

Working with some simple SELECT tests, the first thing I discovered is that SQLite is case-sensitive. I do not recall (as I said, it's been a while) whether MySQL or MS-SQL is case-sensitive but SQLite is, forewarned is forearmed. [By default, but this can be changed if you use a script to create the tables. See http://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing Ed.]

The next thing I discovered was a very peculiar behavior in WHERE clauses.

My CHECKS file has a column, AcctID. I issued the following query:

Select * from checks where acctid = “C/R”;

There are checks written against acctid “C/R.” No items were returned.

The acctid field is a four character string. So I tried:

Select * from checks where trim(acctid) = “C/R”;

and

Select * from checks where acctid = “C/R ”;

both of these worked correctly. In fact, any query I issued against the CHECKS file required padding the WHERE criterion to the full length of the field or TRIMming the variable(s).

Yet, queries against the RSS builder files (converted from .TPS) did not require either kludge. Puzzlement, as Yul Bryner might say.

Forewarned … again.

GET(file, key)

When I open the check browse, I have to select an account from a drop down. Once I have a selection in the drop down, I get the balances from the file that holds them:

ACC:ACCTID = Loc:Acct
Get(Accounts,ACC:ACCOUNTKEY)

and make various other assignments.

I noticed that the balance, displayed below the browse box was always showing 0 (zero). But, the files were open, the variables primed correctly:

Figure 2: Get(Accounts,ACC:AccountKey)

Except that the record was not being found!

On the other hand:

 Set(ACC:ACCOUNTKEY,ACC:ACCOUNTKEY)
 Loop
   Next(Accounts)
   If ErrorCode()
     BREAK
   END
   If ACC:ACCTID = Loc:Acct
     Break
   End
 End

in the same embed, worked “first time, every time.” [Expletive deleted – big time]

The ACCOUNTS file has one key, it is Unique, it is Primary, it is on ACCTID:

Figure 3: Account file layout (DCT)

What is going on here?

To make a long (long) story short … the Unique ID field must be first in the DCT layout for SQLite to cooperate:

Figure 4: Adjusted ACCOUNTS file layout

Figure 5: Old and New Layouts Compare

I've never before heard of such a requirement (though, from a recent news group posting by Geoff Bomford, MS-SQL may well share this peculiarity). But, problem solved.  [Expletive deleted – some more]

Keys and Indices

The next adventure occurred when I tried to change a check, press “OK” and save the record (or, even, just bring it up in change mode and immediately press “OK”). This is important because Insert, Update and Delete operations all have to update the running balance (in the ACCOUNTS file).

Every time I tried to save the form, I got an error:

Figure 6: Saving a Change (APP)

Trying to delete a record from the ACCOUNTS file gave similar results:

Figure 7: Deleting an Account (APP)

I can't add, update or delete CHECKS. I can't delete ACCOUNTS. Two other files in the app show no problems of any kind for any CRUD operation.

It turns out it cannot be my embedded code (there certainly is quite a bit in the Checks browse and form). I wizarded up an app on the DCT, hence no embedded code, and got the same results trying to delete a record:

Figure 8: Deleting from the Wizarded app

though, instead of an ErrorCode 33, now I get a 35. [Expletive deleted – much more]

Making another long story, short, the CHECKS file has another Unique key:

Figure 9: CHECKS file layout (DCT)

Moving the fields around, to group the other unique key elements together and toward the top:

Figure 10: New CHECKS File Layout

Figure 11: Old and New Layouts Compared

Records could now be added, updated or deleted. And, from the previous … adventure … the balances updated and displayed. Correctly, no less!

Could only moving the SysID to the top of the layout have fixed the problem? This is possible; my note taking broke down. Just be aware that it is possible that multiple layout changes may be necessary.

NULL handling and Date format

These two “cuties” came as twins, conjoined twins. Obstreperous conjoined twins.

In the RSS builder, I have a field to indicate the date on which I posted an article. If there is no date, the item is unposted. Similarly, if the date is in the future (I plan ahead … sometimes), it has not yet been posted. So if I want to filter a browse and see only the unposted items:

BRW1.SetFilter('~ART:DatePosted or ART:DatePosted > Today()')

does the job (.TPS files).

Question: how does SQLite handle the items with no entry – i.e., unposted items - in DatePosted? MS-SQL handles these as NULLs, so too, if I recall correctly, does MySQL.

Using the SQL executor in the SQLite Database Browser:

Select * from articles where DatePosted is NULL;

returned no rows. It should have returned almost 70 rows.

I conclude that SQLite handles “no entry” in the DatePosted field much like Clarion handles a “no entry.” That is, it handles them as 0 (zero), which is not NULL. However, neither

Select * from articles where DatePosted = “0”;

nor

Select * from articles where ~DatePosted;

returned any rows. Bother!

What's a poor programmer to do? Browsing the ARTICLES file in the SQLite Database Browser:

Figure 12: SQLite Date Formatting

reveals the answer. Where MySQL and MS-SQL store Date data types in D12 format (or, at least, understand D12 format), SQLite has its own unique date format: yyyy-mm-dd. Checking the Clarion docs, this looks like it could be D12-.

Except that Clarion, while supporting @D12, does not support @D12-.

To deal with this, I added a new function to my StringClass (included for download at the end):

StringCompare.SQLiteDateFromClarion   PROCEDURE(Long pDate)
  CODE
  Return Format(Year(pDate),@n04) & '-' & Format(Month(pDate),@n02) & '-' & Format(Day(pDate),@n02)

to format dates in a manner SQLite understands. So my filter for unposted articles becomes:

BRW1::View:Browse{PROP:SQLFilter} = 'DatePosted = "0000-00-00" or DatePosted > "' & sc.SQLiteDateFromClarion(Today()) & '"'

which does work. In fact, the code, above, used to change the browse based on which radio button is pressed, after moving to the .ApplyFilter embed, becomes:

ApplyFilter, Before Parent
Case Filter_
Of 1
  BRW1::View:Browse{PROP:SQLFilter} = 'DatePosted = "0000-00-00" or DatePosted > "' & sc.SQLiteDateFromClarion(Today()) & '"'
ELSE
  BRW1::View:Browse{PROP:SQLFilter} = 'ArticleID <> 0' ! dummy
END
Return

To be sure that SQLite handles “no entry” similarly to the way Clarion does, I tried a string field:

Select * from checks where TRIM(deduct) = ' ';

and I did get a (correct) return set.

Repeating Records

The next thing I noticed was repeating records when scrolling to the bottom of the browse:

Figure 13: Repeating Records at Browse Bottom

(For a short while, I recall this also occurred when scrolling up from the top of the browse.)

Normally this is a sign, seen frequently in SQL browses, that the rows are not uniquely identified. The usual fix is to add the unique key field(s), to the View. But, I knew and had already done this.

To make matters worse, when I scrolled to the top of this page, I couldn't scroll up to previous page(s). And, yes, there were previous pages. VCR “go first” worked but then I couldn't scroll down to the next page.

Further perplexing me: if I pressed the “Show All” radio button, to remove filtering, these symptoms did not appear. Similarly, if I clicked on a list box header, invoking list box sorting, no symptoms.

Further, if I insert a new record or change an existing one, the browse does not return to the current record. Yes, I tried every form of regetting I could think of....

In other words, this does not look like a failure to uniquely identify each row. Beyond that, BTSOOM.

The fix for most of the issues (except for the last mentioned problem), is to change the browse from Page Loaded to File Loaded. Why does it work? BTSOOM (still).

The Dummy Table Technique

This is a technique for getting information out of SQL and into variables to which I have access.(See Getting Useful Information Out of SQL, Part 2 ).

This technique relies on a dummy table, created in the DCT:

Dummy FILE,DRIVER('SQLite'),OWNER(GLO:dbName),PRE(DUM),BINDABLE,THREAD
Record         RECORD,PRE()
Field1           CString(256)
               END
      End

Note the absence of the CREATE attribute on the file.

So if I want a count of total articles:

Open(Dummy)
dummy{Prop:SQL} = 'select count(*) as total from articles;'
Next(Dummy)
TotalArticles = DUM:Field1

and TotalArticles should contain the number I want.

dummy{Prop:SQL} = 'select count(*) as published from articles where dateposted <> "0000-00-00" and dateposted <= "' & sc.SQLiteDateFromClarion(Today()) & '";'
Next(Dummy)
Published = DUM:Field1

and Published should contain a count of published articles.

Doesn't work.

Every method I tried gave me a File Not Found error (ErrorCode 2). I just couldn't get it to work and stopped trying.

Conclusions

SQLite handles “no entries” (“nulls”) very much like Clarion. So, except for dates, no learning curve here.

SQLite has a peculiar date format. Use the new StringClass method and save yourself the hassle.

Does SQLite offer SQL with fewer headaches? Yes, as previously discussed, it does. Technically.

Is SQLite a suitable replacement for .DAT and .TPS files? Not 100% but fairly close; I did, after all, still have some minor program misbehaviors.

Does SQLite offer all the expected benefits of SQL? Until I can figure out how to get result sets into my own variables, no, it does not.