Skip to Content
0
Apr 08, 2014 at 11:33 PM

Case Insensitive Searches: How is the datawindow.table.select string put together?

406 Views

I am using PowerBuilder 12.5. I am maintaining PowerBuilder programs written years ago.

I have a datawindow where the user enters search criteria for queries. Right now we are using a Sybase database and for columns of type char, the user's string is transformed inside itemchanged() such that:

User's String ==> Transformation

------------------- ----------------------------------

farm like %[Ff][Aa][Rr][Mm]%

The result is a case insensitive search. We are migrating to an Oracle database which doesn't recognize [ ].

I could easily transform 'farm' to 'like %farm%' but I don't know how to wrap the column name inside the lower function. In other words, the datawindow.table.select looks like this (Table = Clients, Column = name):

SELECT ... WHERE Clients.name like '%farm%'

but I want it to look like:

SELECT ... WHERE lower(Clients.name) like '%farm%'

I don't appear to have a handle on the column name, just what appears after.

datawindow.table.select is auto generated every time either accepttext() or a user's click (focus lost) puts data into the Primary Buffer.

Sometimes the queries get rather long with several subqueries that use other tables. I would rather not try to do string manipulation with PosA() and ReplaceA() to "find" column names of a particular type and wrap them with a call to lower().

Where in the PowerBuilder code is the datawindow.table.select string put together? There appears to be a good deal of parsing going on there looking for keywords such as 'and', 'like', '=', ... Maybe I can insert a call to lower() there????

Or does someone have a better idea on how to do case insensitive searches using a datawindow?