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

[Former Member]

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?

[Former Member]

Hi Christine,

Is this a dynamic datawindow? Is it created as createsyntaxfromsql or create? If there's really a datawindow object, what's the sql inside it? Like when open the datawindow, the click on Design menu, then Data Source, what do you see there?

When that event fires (lost focus) could you display the dataobject that the dw contains, like:

messagebox('dw object',dw.dataobject) and investigate that dw object.

[Former Member]

It is created with create.

The querymode is set to YES.

There are actually several datawindows each associated with a different table. The user might put search criteria into several datawindows and then click our Select button.

My code grabs the WHERE clauses from all of these and assembles them into a SELECT with (potentially) multiple subqueries.

For example, here is what is being sent to the database:

SELECT ... FROM dbo.Samples

WHERE

      (((dbo.Samples.datereceived > '1-1-2012 0:0:0.000')))

AND (dbo.Samples.speciesid in ( SELECT dbo.Species.id FROM dbo.Species WHERE (((dbo.Species.commoname like '%[Rr][Yy][Ee]%'))) ) )

AND (dbo.Samples.varietyid in ( SELECT dbo.Varieties.id FROM dbo.Varieties WHERE (((dbo.Varieties.name like '%[Rr][Ee][Dd]%'))) ) )

AND (dbo.Samples.growerid in ( SELECT dbo.Clients.id FROM dbo.Clients WHERE (((dbo.Clients.name like '%[Ff][Aa][Rr][Mm]%'))) ) )

The WHERE clauses come to me by asking each data window for its datawindow.table.select and grabbing everything from WHERE on.

So, the WHERE's that I grabbed were:

• WHERE (((dbo.Samples.datereceived > '1-1-2012 0:0:0.000')))
• WHERE (((dbo.Species.commoname like '%[Rr][Yy][Ee]%')))
• WHERE (((dbo.Varieties.name like '%[Rr][Ee][Dd]%')))
• WHERE (((dbo.Clients.name like '%[Ff][Aa][Rr][Mm]%')))

And then our code assembles the select. When we go to Oracle, there will be lots of quotes around the table and column names.

SELECT * FROM dbo.Clients

WHERE

          ((("DBO"."CLIENTS"."ID" > 13000)))

AND (dbo.Clients.id in ( SELECT dbo.ClientClasses.id FROM dbo.ClientClasses WHERE ((("DBO"."CLIENTCLASSES"."CLASS" = 2))) ) )

It would take more text processing than I am comfortable with to inject calls to upper() or lower() programmatically on the char columns. I think bugs would be introduced that would only be noticed years from now. I wish PowerBuilder had a checkbox in the Properties area so that I would indicate that I want case insensitive selection. Solving this at the database level is sounding appealing.

I will do the messagebox('dw object',dw.dataobject) and post later. Thanks for the reply.

[Former Member]

Hi Christine,

I know you didn't want to do a lot of parsing, but this might be your best bet:

Assuming that the WHEREs you grab always follow this format:

     - the LIKE clause is surrounded by ()

     - the expression to the right of the LIKE always contains each character in UPPER and lower              case surrounded by []

This is what you need to do:

Create a function that receives the where clause as an argument and transform it into a non-case sensitive clause.

Your function will receive this:

• WHERE (((dbo.Species.commoname like '%[Rr][Yy][Ee]%')))

and will return this:

• WHERE (((UPPER(dbo.Species.commoname) like '%RYE%')))

Here is the function code:

//**************************************************************************

// Name: of_non_case_sensitive

//

// Purpose: Transform a SYBASE specific LIKE clause into a

// generic non sensitive SQL LIKE clause

//

// Argument Type Pass By Description

// --------- ---------- --------- ----------------------------

// as_clause string value SYBASE specific LIKE clause

//

// Return Type Description

// ----------- -------------------------------------------------------

// string Generic non sensitive SQL LIKE clause

//**************************************************************************

long ll_endpos = 0, ll_begpos = 0, ll_likepos

ll_likepos = POS(UPPER(as_clause), 'LIKE')

IF ll_likepos > 0 THEN

  DO

  ll_endpos = POS(as_clause, ']')

  IF ll_endpos > 0 THEN

  as_clause = LEFT(as_clause, ll_endpos - 2) + MID(as_clause, ll_endpos + 1)

  END IF

  LOOP WHILE ll_endpos > 0

  DO

  ll_endpos = POS(as_clause, '[')

  IF ll_endpos > 0 THEN

  as_clause = LEFT(as_clause, ll_endpos - 1) + MID(as_clause, ll_endpos + 1)

  END IF

  LOOP WHILE ll_endpos > 0

  ll_endpos = ll_likepos - 2

  ll_begpos = ll_endpos

  DO

  ll_begpos --

  LOOP WHILE MID(as_clause, ll_begpos, 1) <> '('

  as_clause = LEFT(as_clause, ll_begpos) + 'UPPER(' + MID(as_clause, ll_begpos + 1, ll_endpos - ll_begpos) + ')' + MID(as_clause, ll_endpos + 1)

END IF

RETURN as_clause

Now you just need to call this function to transform each one of the WHEREs you grabbed.

HTH,

Manuel.

[Former Member]

I agree with Brad, this job must be done at database level.

Both Oracle and Sybase have the ability to turn on/off case sentitivity in search requests (where clause). These parameters are often defined when we decide wich charset or order set to use inside the database.

If your database is case insentitive, both where clause will return the same records:

WHERE Clients.name like '%farm%'

WHERE Clients.name like '%FARM%'

Possible Results:

afarmland

AFARMBeth

...

[Former Member]

Hi Christine,

The DataWindow.Table.Select contains the SELECT statement used to create the datawindow. It can be dynamically changed, though. You can put code on the SqlPreview event of the datawindow to see what SQL statement is being sent to the database.

In Oracle you could replace LIKE with REGEXP_LIKE and use it's case insensitive matching option.

[Former Member]

like is an operator between the column name and the search value, i.e.

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

while regexp_like is a function and I need access to the column name.

WHERE regexp_like(dbo.Clients.name, '[Ff][Aa][Rr][Mm]')

If I had access to the column name I would use

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

[Former Member]

I'm not an Oracle user, but a quick hunt on the net found this.

According to the following article, you can turn on/off case sensitivity as you need it.

geekzspot: cAsE iNsEnSiTiVe Oracle

It says it's a session setting, so once you disconnect, and re-connect, it's all back to the normal way Oracle handles searches.

[Former Member]

How about using the upper (or lower) functions?  E.g.,

select ....

where upper(columname) like '%FARM%'

And, of course, your code would uppercase the value entered by the user (invisibly if needed).

[Former Member]

The problem is that PowerBuilder supplies everything before the word 'like'. Here is the sequence of events:

1. User types farm into the edit control for the column named name.
2. User clicks out of that control and the itemchanged() function is called. This is code written by my predecessor and that I am maintaining. itemchanged() grabs farm and changes it to
   like %[Ff][Aa][Rr][Mm]%
   and puts all of that back into the edit control with a call to settext() and then calls accepttext() to get it into the Primary Buffer. (farm will be put back into the edit control and the user never notices the change. It is seen only in the debugger when stepping through the code.)
3. accepttext() causes PowerBuilder to automatically generate the DataWindow.table.select string which will now look like:
   SELECT ...  WHERE (((~"DBO~".~"CLIENTS~".~"NAME~" like '%[Ff][Aa][Rr][Mm]%')))  

I don't have access to the column name to wrap it inside a call to upper() or lower(). I am supplying only half of the where clause (the half after the column name). And the [ ]'s that worked with our Sybase database do NOT work for the Oracle database and I have to change "my half" of the where clause to get case insensitive searches.

If I knew where PowerBuilder is parsing the SELECT statement maybe I could call lower() or upper().

[Former Member]

Maybe there's a computed field in the DW and that's where you need to check. That maybe where you can access the column name and wrap it with upper() or lower().

[Former Member]

Hi Christine;

  BTW: the where clause is actually an ASE "mask" ('%[Ff][Aa][Rr][Mm]%') - which is the same basic functionality as the Edit Mask in the DataWindow. I don't think Oracle supports this kind of advanced pattern matching.

  From what I see you will have to generate PL/SQL for both cases and exact length - "FARM" and "farm" and not "FARMING" or "farming" for example.

Good luck!

Regards ... Chris

[Former Member]

And also for "Farm" and for "FArm" (fast typists who don't release Shift soon enough). When the user's search string involves multiple words, the common cases are too numerous.

[Former Member]

Hi Christine;

   This basically a DML conversion exercise between Sybase (T-SQL)  and Oracle (PL-SQL). I would suggest consulting an Oracle DBA to see if there is an equivalent expression for the T-SQL that the DW code was using. Once you know that ... its basically a coding exercise to have the correct DML syntax output for the Oracle DBMS.

  BTW: The PosA ( ) and ReplaceA ( ) methods are obsolete. Just use the normal POS ( ) and Replace ( ) methods.

Good luck!

Regards ... Chris