Case-Insensitive Queries using ODBC on IBM i
When querying for character data using SQL, case matters. “A” is not equal to “a”. Even so, you might want to find all matches, whether uppercase, lowercase, or mixed case.
If you use ODBC, the IBM i driver offers keywords to provide case-insensitivity.
We will demonstrate using the sample table QIWS.QCUSTCDT, which contains a column called CITY that includes a mixed case value, ‘Dallas’.
I can test using the standard isql command, specifying my default ODBC Data Source Name (DSN), which is *LOCAL:
|
1 |
isql -v -k DSN=*LOCAL |
From the isql command line I search for uppercase DALLAS. I get back no matching rows:
|
1 2 3 4 5 6 |
SQL> select * from QIWS.QCUSTCDT where city = 'DALLAS' +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ | CUSNUM | LSTNAM | INIT| STREET | CITY | STATE| ZIPCOD | CDTLMT| CHGCOD| BALDUE | CDTDUE | +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ SQLRowCount returns -1 |
To create a case-insensitive connection, I then edited /QOpenSys/etc/odbc.ini and added an additional DSN called NOCASE:
|
1 2 3 4 5 6 7 8 9 10 |
[NOCASE] Description = Default IBM i local database Driver = IBM i Access ODBC Driver System = localhost UserID = *CURRENT Pooling = No ; these are the added keywords for case-insensitivity SortWeight = 0 SortSequence = 2 LanguageID = ENU |
By adding the combination of keywords SortWeight = 0, SortSequence = 2, and LanguageID = ENU (use your own language), this DSN becomes case-insensitive.
Then I test with my new NOCASE DSN:
|
1 |
isql -v -k DSN=NOCASE |
Testing whether we can search for uppercase DALLAS against mixed case Dallas:
|
1 2 3 4 5 6 |
SQL> select * from QIWS.QCUSTCDT where city = 'DALLAS' +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ | CUSNUM | LSTNAM | INIT| STREET | CITY | STATE| ZIPCOD | CDTLMT| CHGCOD| BALDUE | CDTDUE | +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ | 938472 | Henning | G K | 4859 Elm Ave | Dallas| TX | 75217 | 5000 | 3 | 37.00 | 0 | | 593029 | Williams| E D | 485 SE 2 Ave | Dallas| TX | 75218 | 200 | 1 | 25.00 | 0 | |
It worked!
Note: If, instead of editing your DSN, you provide connection string keywords to your application directly, the keywords are SORTWEIGHT, SORTSEQUENCE, and LANGUAGEID.
For more information on connection strings and DSNs, see ODBC Connection Strings for IBM i Db2.
Leave a Reply
Want to join the discussion?Feel free to contribute!