Anyone have any success using COLLATE NOCASE with SQLite in Clarion?
I was working through the re-written example app they’re playing with on the ClarionLive webinars,.
Sorting a browse by clicking the column headers sorts case-sensitive. Is there a trick with COLLATE NOCASE to get around this behavior?
jf
Is that header click stuff sorting by the queue, or as the data source provides?
I just tried this in my demo app, and it worked:
select * from mem.data ORDER BY ID COLLATE NOCASE
It was definitely different than without the collate.
But is there a setting to make that work with the browse template’s Extended Options / Enable Sort Header?
I couldn’t find anything in the Clarion help for the driver or any way of setting NOCASE on a field (or on the whole database) that would work for templated procedures.
Hi Jane,
I couldn’t watch the webinars, but I see the Clarion driver is not translating a view PROP:ORDER UPPER(field) to “ORDER BY field COLLATE NOCASE” but just “ORDER BY field”
One solution can be at the time of table creation, use an script to assign the default collation for the field, eg
CREATE TABLE myTable (
field1 INTEGER,
field2 TEXT COLLATE NOCASE,
...
Then if the query it get is SELECT * FROM myTable ORDER BY field2 it will order using NOCASE
Other solution is to set the PROP:SQLORDER manually (not PROP:ORDER), but since you are mentioning browse sort headers, it would imply some flags on some embed points or doing a string parsing on current PROP:ORDER to define a new PROP:SQLORDER, as PROP:ORDER is read/write and PROP:SQLORDER only write.
For example if you do on thiswindow.reset priority 5000:
IF someflagtoorderbyfield2 !OR BRW1::View:Browse{PROP:SQLORDER} = 'MYT:FIELD2'
BRW1::View:Browse{PROP:SQLORDER} = 'field2 COLLATE NOCASE
END
Thanks Federico and Jeff.
This is the first I’ve played with SQLite.
My specific curiosity is related to the example app that the ClarionLive guys are re-creating/modernizing. They decided to show off SQLite and found this issue.
I don’t know whether they care.
But as an example that might get contributed to Clarion, I’d think that it would probably be best not to need to have a custom script to create the database (I also thought of trying that) or overriding the template code to use PROP:SQLORDER.
I thought there might be a driver string or dictionary field property I had missed.
And Federico - you can always watch the webinars on youtube. Set the speed to 1.50 and sit back… 
jf
I sent Mike my Test Data APP I showed last Friday to add to his Repo. I’ll attached it here.
That App has Wizard Browses that use the File Keys. As shown below the lower case “van der Xxx” names sort after the "Zzzz " names.
TestInvoice_20230125_120355.ZIP (435.4 KB)
The Keys do have NOCASE. Not sure how the DB was setup or its exact def, I think they simply used Clarion Create()
Customer FILE,DRIVER('SQLite'),OWNER(Glo:Owner),NAME('Customer'),PRE(Cus),BINDABLE,CREATE,THREAD ! Customer's Information
GuidKey KEY(Cus:GUID),NAME('Customer_GuidKey'),NOCASE,PRIMARY !
CompanyKey KEY(Cus:CompanyGuid),DUP,NAME('Customer_CompanyKey'),NOCASE !
LastFirstNameKey KEY(Cus:LastName,Cus:FirstName),DUP,NAME('Customer_LastFirstNameKey'),NOCASE !
PostalCodeKey KEY(Cus:PostalCode),DUP,NAME('Customer_PostalCodeKey'),NOCASE !
StateKey KEY(Cus:State),DUP,NAME('Customer_StateKey'),NOCASE !
Record RECORD,PRE()
GUID STRING(16) !
CustomerNumber LONG !
CompanyGuid STRING(16) !
FirstName STRING(100) !
LastName STRING(100)
Carl,
Yeah, that’s what I found in my test app also. With file key or sorting by the extended header option.
I tried finding something within the Clarion environment (i.e., not using a special script to create the database, etc.) to get that to work right but didn’t come up with a way.
Thought somebody here might have more experience. It seems a logical thing to have available as a driver string or a prop:something…
SQLite supports “calculated” columns, although they call it something else: Generated Columns
So theoretically one could generate a upperLastName column, for example, that takes up no database space. But how would one do that within the Clarion environment?
jf
(post deleted by author)
I tested adding a duplicate value on a unique key and got some odd driver error like “unexpected error 19” instead of error 40 Creates Duplicate Key. Also the DUPLICATE() function did not work. It’s shown on the Friday video.
This makes me think the SQLite driver is not finished. It would be good to exercise as many features as possible.
Hi Jane,
I tested 6 alternatives to find possible solutions, 3 worked (Fields default collation on Table Definition, Overriding Binary Collation, Dictionary field option SELECTNAME), 1 worked with some caveats (PROP:SQLOrder) and 2 doesn’t worked (File and View PROP:NAME)
This is the easiest and (seemingly) safest solution. On external name of each NOCASE field, repeat its name | SELECTNAME=its name COLLATE NOCASE, eg:
LastName | SELECTNAME=LastName COLLATE NOCASE
Setting directly as:
LastName COLLATE NOCASE
causes an error 47 invalid structure when opening the table
(Edit.: And issuing this programatically (Customer{PROP:NAME,fieldno}=‘fieldname COLLATE NOCASE’ after the file is OPEN works for the Browse but fails in the Form for INSERT/UPDATE.
Customer{PROP:NAME,fieldno}='fieldname | SELECTNAME=fieldname COLLATE NOCASE' like the dictionary field option in this way, this works either before(but in the same thread) and after opening the file. No problems with browse or form.
The SELECTNAME change its name on the PROJECTION (SELECTed column names) and SELECTION (WHERE) and ORDER BY clause. Also on UPDATE queries WHERE clause (edited)
View’s columns PROP:NAME (Browse Box Behavior / SQLAdvanced ) only changes in PROJECTION but not in WHERE/ORDER BY so it doesn’t help.
Regarding PROP:SQLOrder :
-it over complicate things
-has limitations: the string sent is final, it doesn’t work for reverse order (Clarion doesn’t parse the string to insert DESC order by) so it needs to be redefined even when the user changes the scrolling direction:
BRW1.Fetch PROCEDURE(BYTE Direction)
CODE
ResetCount += 1
CurrentOrder = BRW1::View:Browse{PROP:ORDER}
IF SetSqlOrder AND INSTRING(')',BRW1::View:Browse{PROP:ORDER},1,1)
SSCurrentOrder.FromString(BRW1::View:Browse{PROP:ORDER})
SSCurrentOrder.ReplaceInContent('CUS:','A.') !Fixed strings for simplicity
SSCurrentOrder.ReplaceInContent('UPPER(','')
SSCurrentOrder.ReplaceInContent(')',' COLLATE NOCASE ASC ')
IF INSTRING('-',BRW1::View:Browse{PROP:ORDER},1,1) <> 0 XOR Direction <> FillForward
IF INSTRING('ASC',SSCurrentOrder.ToString(),1,1) !this has a simplified logic only to show the idea
SSCurrentOrder.ReplaceInContent(' ASC ,',' DESC *') !it doesn't cover all the combinations, like
SSCurrentOrder.ReplaceInContent(',',' DESC *') !mixed - and + without any UPPER
SSCurrentOrder.ReplaceInContent('*',',')
SSCurrentOrder.ReplaceInContent(' ASC ',' DESC ')
ELSE
SSCurrentOrder.ReplaceInContent(',',' DESC ,')
SSCurrentOrder.Append(' DESC ')
END
END
SSCurrentOrder.ReplaceInContent(' ASC ','')
SSCurrentOrder.ReplaceInContent('+','')
SSCurrentOrder.ReplaceInContent('-','')
BRW1::View:Browse{PROP:SQLORDER} = SSCurrentOrder.ToString()
CurrentSQLOrder = SSCurrentOrder.ToString()
ELSE
BRW1::View:Browse{PROP:SQLORDER} = ''
CurrentSQLOrder = ''
END
PARENT.Fetch(Direction)
-the worst caveat with this approach is that the WHERE clause doesn’t use COLLATE, it is generated as:
SELECT A.GUID, A.CUSTOMERNUMBER, A.COMPANYGUID,FirstName COLLATE NOCASE,LastName COLLATE NOCASE, A.STREET, A.CITY, A.STATE, A.POSTALCODE, A.PHONE, A.MOBILEPHONE, A.EMAIL FROM Customer A WHERE ( A.LASTNAME >= ? ) AND ( ( A.LASTNAME > ? ) OR ( ( A.FIRSTNAME >= ? ) )) ORDER BY A.LASTNAME COLLATE NOCASE,A.FIRSTNAME COLLATE NOCASE
thus causing to repeat records in a loop when scrolling and the highlighted record (currentchoice) sets over a name which location with the other collation is diffent (van de Canten vs Venere)
I attach Carl’s example posted with the dictionary and application changes
TestInvoiceSQLiteCollateNOCASE.zip (255,9 KB)
, and SQLite database with sample content
Invoice.zip (360,4 KB)
Brilliant!
Amazing digging, Federico! While that does require remembering to annotate all relevant fields, a lot of us are using external names anyway for JSON, etc.
Seems to me the easiest general-case solution.
jf
Yes, and first time is field by field, table by table, although it could be done programatically, on thread creation with a threaded class, or an extension that in procedure setup call a procedure to loop over each field and add that option to relevant fields, the criteria could be if it is an string field or has some special names or participates as a component in a key with NOCASE attribute.
In Clarion 9+ there is a SQLite example (attached below) that was the School TPS example changed to SQLite. So there’s another complete example to test SQLite.
I thought it had a Script to create the DB, but no … I just looked at the CreateDB() procedure and it simply does Clarion’s CREATE() for each File. The file declarations are identical to the TPS that simply have KEYs with NOCASE and no NAME() modifiers. I just tested and Lower Case data sorts after Upper Case on the NOCASE keys in browses, same as seen in Invoice.
After CreateDB() there is code to copy all the TPS files into the SQLite DB. So you can easily change various SQLite settings, then build a new DB.
School_SQLite.zip (1.6 MB)
There is another example app SqlScriptor that does create the DB with a Script, probably you recalled that one
It has a .sql file, this is a part:
CREATE TABLE Students(
Number INTEGER NOT NULL,
FirstName CHAR(20),
LastName CHAR(20),
Address CHAR(20),
Address2 CHAR(20),
City CHAR(20),
State CHAR(2),
Zip INTEGER,
Telephone CHAR(12),
Major INTEGER,
GradYear INTEGER,
Photograph CLOB,
CONSTRAINT KeyStudentNumber PRIMARY KEY (Number));
CREATE INDEX MajorKey ON Students(Major,LastName,FirstName);
CREATE INDEX KeyLastName ON Students(LastName);
CREATE INDEX KeyGradYear ON Students(GradYear,LastName,FirstName);