Page Comparison

...

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.

...

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).

...

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.

...

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:

...

 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:

...

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()) & '"'

...

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) = ' ';

...

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.

...