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.
...
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 the retrieves the fields in the view are retrieved from 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.
...
| Code Block |
|---|
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).
...
| Code Block |
|---|
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.
...
Figure 1: SQLite Database Browser
But, be aware, it can lock up on a badly formatted query.
...
| Code Block |
|---|
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!
...
| Code Block |
|---|
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?
...
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]
...
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.
...
Figure 8: Deleting from the Wizarded app
though, instead of an ErrorCode 33, now I get a 35. [Expletive deleted – much more]
...
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!
...
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-.
...
| Code Block |
|---|
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()) & '"'...
| Code Block |
|---|
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) = ' ';
...
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.)
...
| Code Block |
|---|
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:
| Code Block |
|---|
Open(Dummy)
dummy{Prop:SQL} = 'select count(*) as total from articles;'
Next(Dummy)
TotalArticles = DUM:Field1 |
and TotalArticles should contain the number I want.
| Code Block |
|---|
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.
...