Case-insensitive searching

[abmusse]

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

Is there an equivalent to "exec sql SET OPTION SRTSEQ=*LANGIDSHR" as in an RPGLE program? This tells the program to perform case-insensitive searches when executing SQL. Could we run this before running a statement, or by setting a statement option? If this was a setting on a statement, not a connection that would be great. We can always use the upper() and lower() sql functions if necessary. Any help is appreciated.

[abmusse]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

@krisbaehr , my best guess is to call setConnAttr() specifying SQL_ATTR_CONN_SORT_SEQUENCE to SQL_ATTR_JOB_SORT_SEQUENCE, but I'm not sure that will give you exactly what you're looking for. But of course, that's on a connection, not a statement. I thought SRTSEQ only applied to sorting, not case sensitivity when matching.

What is your use case? Are you just doing some % inside a select WHERE clause?

[abmusse]

Original comment by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

@ThePrez When I run an SQL statement, I'd like to be able to specify case-sensitivity. For example, when providing a method for searching for companies by name, I'd like to be able to indicate whether the SQL query should be case-insensitive ('abc' = 'ABC'). Setting the SRTSEQ is the way RPGLE indicates to DB2 to do it, and that may happen at compile time.

SRTSEQ = *LANGIDSHR indicates that a shared-weight sort table is used for the language id. When using shared-weight, the end result is that 'abc' and 'ABC' are equivalent on an SQL statement's where clause.
SRTSEQ = *HEX is the default, at least for us. This indicates that a sort sequence table is not used. In this case 'abc' <> 'ABC' on the where clause.
The setting used for SRTSEQ may have an impact on the order by clause too, but I'm not sure.

Like I mentioned in the first post, we can perform case-insensitive searches like this manually, but it would be handy if we could set an option instead:
select *
from companies
where lower(name) = lower(searchCriteria)

If this is an option, it would be nice if it wasn't specified at the connection level since the connections get re-used in a pool.

Let me know if you need further clarification, I appreciate all you guys do.

[dmabupt]

Consulted some Db2 experts. This feature is not supported.

It depends on the user to handle the case-insensitive searching like

upper('a')
upper(?)
upper(:var1)