# SQLite COLLATE NOCASE?

**URL:** <https://clarionhub.com/t/sqlite-collate-nocase/5890>\
**Category:** questions\
**Tags:** sqlite\
**Created:** [January 26, 2023, 9:19pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890 "2023-01-26T21:19:39Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\
**Post date:** [January 26, 2023, 9:19pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/1 "2023-01-26T21:19:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [January 26, 2023, 9:55pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/2 "2023-01-26T21:55:41Z")

</div>

> [@Jane](#):
>
> Sorting a browse by clicking the column headers sorts case-sensitive. Is there a trick with COLLATE NOCASE to get around this behavior?

Is that header click stuff sorting by the queue, or as the data source provides?

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [January 26, 2023, 10:05pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/3 "2023-01-26T22:05:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\
**Post date:** [January 26, 2023, 11:32pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/4 "2023-01-26T23:32:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![FedericoNavarro](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@FedericoNavarro](https://clarionhub.com/u/FedericoNavarro)\
**Post date:** [January 26, 2023, 11:34pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/5 "2023-01-26T23:34:41Z")

</div>

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

```auto
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:

```auto
IF someflagtoorderbyfield2 !OR BRW1::View:Browse{PROP:SQLORDER} = 'MYT:FIELD2'
   BRW1::View:Browse{PROP:SQLORDER} = 'field2 COLLATE NOCASE
END

```

---

<div class="post-metadata">

**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\
**Post date:** [January 26, 2023, 11:59pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/6 "2023-01-26T23:59:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [January 27, 2023, 12:25am UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/7 "2023-01-27T00:25:11Z")

</div>

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.

 ![image](https://clarionhub-uploads.s3.eu-north-1.amazonaws.com/original/2X/1/1f01805e98b6f97053e76e2528f427a329545248.png)

[TestInvoice\_20230125\_120355.ZIP](https://clarionhub.com/uploads/short-url/iLaNAoELEbvGJZ7pXfqunWVqFzo.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()

```auto
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)  

```

---

<div class="post-metadata">

**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\
**Post date:** [January 27, 2023, 1:17am UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/8 "2023-01-27T01:17:34Z")

</div>

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](https://www.sqlite.org/gencol.html)  
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

---

<div class="post-metadata">

**Author:** ![anon23294430](https://clarionhub.com/user_avatar/clarionhub.com/anon23294430/32/3829_2.png) [@anon23294430](https://clarionhub.com/u/anon23294430)\
**Post date:** [January 27, 2023, 1:35am UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/9 "2023-01-27T01:35:23Z")

</div>

(post deleted by author)

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [January 27, 2023, 2:06am UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/10 "2023-01-27T02:06:34Z")

</div>

> [@Jane](#):
>
> SQLite

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.

---

<div class="post-metadata">

**Author:** ![FedericoNavarro](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@FedericoNavarro](https://clarionhub.com/u/FedericoNavarro)\
**Post date:** [January 27, 2023, 2:57pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/11 "2023-01-27T14:57:28Z")

</div>

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)

> [@Jane](#):
>
> I thought there might be a driver string or dictionary field property I had missed.

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:

```auto
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](https://clarionhub.com/uploads/short-url/2iNIl4Xl3ImjUs7DsbLbHbnzMLo.zip) (255,9 KB)  
, and SQLite database with sample content  
[Invoice.zip](https://clarionhub.com/uploads/short-url/mNskl3upmaFgN1aZWAzZNt2ize7.zip) (360,4 KB)

---

<div class="post-metadata">

**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\
**Post date:** [January 27, 2023, 4:45pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/12 "2023-01-27T16:45:05Z")

</div>

> [@FedericoNavarro](#):
>
> 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`

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

---

<div class="post-metadata">

**Author:** ![FedericoNavarro](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@FedericoNavarro](https://clarionhub.com/u/FedericoNavarro)\
**Post date:** [January 27, 2023, 5:11pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/13 "2023-01-27T17:11:50Z")

</div>

> [@Jane](#):
>
> While that does require remembering to annotate all relevant fields

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.

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [January 27, 2023, 5:35pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/14 "2023-01-27T17:35:21Z")

</div>

> [@FedericoNavarro](#):
>
> One solution can be at the time of table creation, use an script to assign the default collation for the field, eg

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](https://clarionhub.com/uploads/short-url/8gdqwH0laYdP426WlpOr4hdqIFZ.zip) (1.6 MB)

---

<div class="post-metadata">

**Author:** ![FedericoNavarro](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@FedericoNavarro](https://clarionhub.com/u/FedericoNavarro)\
**Post date:** [January 27, 2023, 5:57pm UTC](https://clarionhub.com/t/sqlite-collate-nocase/5890/15 "2023-01-27T17:57:16Z")

</div>

> [@CarlBarnes](#):
>
> I thought it had a Script to create the DB

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:

```auto
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);

```