Case insensitive searches

[Candy M.]

I have a question for the group to see how others handle case insensitive searches. In our database tables we hold data in upper/lower case in the columns.
Suppose a user does a search (will use CONSTRUCT statement), on a name and the individual enters "candy*".   How would I have it find both records "Candy" and "CANDY" stored in that table for that column.   The user does not want to have to enter it exactly as it appears on the record.

I'm pretty sure that the BTS (Basic Text Search) would find it in the Informix IDS database, but you would need to create an index on the columns you
want that valid for and that can be a lot of columns for 900+ tables in the database.  We do have it on some columns in our inventory module though.

So I was wondering what others do and maybe I'm missing something simple so that it is an easy solution.   I didn't get the question in time from our team while at the conference so I'm asking it here.

We also got a comment this week from a prospect while doing a demo of our software (comment being that we did NOT handle case insensitive searches), so it would be nice to have it solved. :-)

Thanks,
Candy

[Gary C.]

Hi

We wanted this feature for various look up functions and simply used SQL functions:

Code Select Expand



define sName,
     sSearch string

let sSearch = "%", sName.toLowerCase().trim(), "%"

select * from customers
    where lower(accname) like sSearch



We are using this against MySQL.

Hope that helps.

Gary

[Sebastien F.]

Hello,

There is a feature request to enhance CONSTRUCT search wildcards, it is registered as FGL-1418:

https://4js.com/support/issue/?id=FGL-01418#startissue

But so far, we do not plan to enhance CONSTRUCT.

You may want to write your own query dialog, for example as in this demo:

https://github.com/FourjsGenero/fgl_query_dialog

(it has a case-sensitive option)

Seb

[Candy M.]

Thank you Gary and Seb.  The dynamic dialog is a good idea but we have a lot of forms that are nicely laid out and the users are familiar with them and comfortable using them to search.   So we are restricted to using them for now.

It seems that our only option for now is to parse the where clause of the CONSTRUCT statement and add LOWER() or UPPER() around the columns that we want to be case insensitive.  I could see that some users may want to just search on an exact match and others want case insensitive, so we could add a preference to the user's account.   And there are some CHAR, VARCHAR or LVARCHAR columns we do not want to have that option, so we could put an indicator for the table/column in our system documentation database that indicates it is case insensitive or not.

The programmer does not have any control of what happens in that where clause except for the values and search instructions that are entered in the field (ie >, <, etc.)  What might be useful if a method could be applied in the BEFORE CONSTRUCT that would instruct the BDL what to do with the column in the column list upon execution of the CONSTRUCT.   So for example:

Code (genero) Select Expand


        CONSTRUCT where_clause ON column1,column2,column3,column4 FROM s_form.column1, s_form.column2, s_form.column3, s_form.column4
        BEFORE CONSTRUCT
#
#              Apply SQL functions according to user preferences and according to table/column restrictions (if conditions to be added later)
#
                CALL CONSTRUCTDIALOG.applyFunction("column1","LOWER")
                CALL CONSTRUCTDIALOG.applyFunction("column3","LOWER")
         END CONSTRUCT

(CONSTRUCTDIALOG would be the ui.Dialog but the method applyFunction only applies to dialog while in CONSTRUCT statement)

Then, if the user enters a string, say  'abc'  for column3, then the value of where_clause would be:

         LOWER(column1)='abc'

And the programmer could apply uppercase or lowercase functions in the AFTER FIELD blocks or maybe traverse the AUI tree and force a field to be upshifted or downshifted.   So basically no parsing of the where clause needs to be done afterwards.   :-)

Those are my thoughts after thinking about it further.

Candy

[Reuben B.]

As Seb indicated, there is an existing task where we have previously considered adding additional syntax to the CONSTRUCT https://4js.com/support/issue/?id=FGL-01418.  If this is something you want in the product, then as I said at the recent developer conference, now is the perfect time just after a major release to start saying, hey this is what I'd like to see in the next major release.  So please contact your local support representative and say please add me to the list of requestors for FGL-1418.

Without raising expectations too much though, note the start date for FGL-1418 is 2007.  From that you can imply that if it was a trivial issue, it would have been implemented a long time ago.  So if we said ^ABC maps to LOWER(fieldname) = LOWER("ABC"), what if the user really wanted fieldname = "^ABC".  etc.  More requestors there are, more chance of being implemented.

What I will also point out is that Informix has historically been a case sensitive database.  Customers migrating to other databases may have come across this as the target database maybe case insensitive so you have to take care at installation.  A good example is SQL Server http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_odiagmsv_041.html.  More recent versions of Informix allow for case insensitivity https://www.ibm.com/developerworks/data/library/techarticle/dm-1108caseinsensitive/index.html so that maybe one avenue you wish to explore.

Otherwise if you are looking at parsing where clauses and/or looking at GET_FLDBUF(fieldname) and/or using ui.Dialog.getFieldBuffer, to see what user entered into a field, I'll also point out that using multi-dialog allows you to get the where clause for each field e.g.

Code (genero) Select Expand

DIALOG
   CONSTRUCT BY NAME sql_field1 ON field1
   END CONSTRUCT
   CONSTRUCT BY NAME sql_field2 ON field1
   END CONSTRUCT
...
END DIALOG
LET sql_field1 = parse(sql_field1)
LET sql_field2 = parse(sql_field2)

However you are then probably getting into area of too much repeated coding, and then I'd suggest dynamic dialogs.  You said your users were comfortable with the existing forms and you did not want to change them that much, your dynamic dialog code could use the existing form.

So if your existing code was ...

Code (genero) Select Expand

CONSTRUCT where_clause ON column1,column2,column3,column4 FROM s_form.column1, s_form.column2, s_form.column3, s_form.column4
END CONSTRUCT

that can become ...

Code (genero) Select Expand

LET where_clause = dynamic_construct("column1,column2,column3,column4","s_form.column1, s_form.column2, s_form.column3, s_form.column4")


where dynamic construct is a library function that does a dynamic dialog that does the CONSTRUCTs and parses the individual fields to apply your additional syntax for case insensitivity to generate the desired where clause.

Reuben

[Candy M.]

Thank you Reuben.

I wasn't aware that you could use a dynamic dialog with an existing form;  I was thinking the form would be dynamic as well.   I think that could work very nicely.  But we do have comboboxes on the form.   Would they still work correctly?  I'll try that out and see what happens and thanks for correcting me on that.

The Informix database is something we may want to pursue, but it does raise other issues we'll have to deal with.  Looks like we do have a lot of options.   
Thanks again Reuben.   You're always very helpful.

Candy

[Candy M.]

Reuben,
     If it is a case insensitive database, will the BDL treat the NCHAR and NVARCHAR like CHAR and VARCHAR?
So lets say the customer name is NVARCHAR(50) and in the code you have customer_name  LIKE customer.name, will it treat that just like it was a VARCHAR?   
     Also when we create  the database schema (fgldbsch) on database columns , will it work the same?

Thanks,
Candy

[Sebastien F.]

1) I don't think it's a good idea to suggest programmers to replace regular static CONSTRUCT BY NAME sql ON col1, col2, ... by a multiple-dialog with a CONSTRUCT for each column, or with dynamic dialogs... Dynamic dialogs are to be used in specific cases, do not replace all your existing static dialogs with dynamic dialogs.

2) Parsing SQL statements must be done with care: You must at least implement a lexer (to distinguish keywords from string literals), to deal with cases like:  ... WHERE customer = 'customer' ...

3) Using NCHAR/NVARCHAR and the (database-global!) Informix case-insensitive option must be investigated and used with care.
This can introduce side effects. For example, the sorting of NCHAR/NVARCHAR columns uses order of collation based on the locale.
https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1405.htm
fgldbsch supports Informix NCHAR/NVARCHAR columns: Program variables defined LIKE such columns will be like CHAR/VARCHAR variables.
Note also that with other database servers, the semantics of NCHAR/NVARCHAR types are totally different from Informix (different codeset, different length semantics).

4) Other idea to be investigated: Use a view that is defined with UPPER(col) expressions from the source table, and use UPSHIFT attribute in the CONSTRUCT fields, by modifying the form at runtime... However, this requires a lot of code change (see charcase.tgz example attached).

5) Ideally, for minimal user code change, the CONSTRUCT should be extended.

Seb

[Candy M.]

1) I don't think it's a good idea to suggest programmers to replace regular static CONSTRUCT BY NAME sql ON col1, col2, ... by a multiple-dialog with a CONSTRUCT for each column, or with dynamic dialogs... Dynamic dialogs are to be used in specific cases, do not replace all your existing static dialogs with dynamic dialogs.

I totally agree.  I started looking at it and realized it was only for CONSTRUCT BY NAME and we don't use CONSTRUCT BY NAME. So we will not be following that path.

2) Parsing SQL statements must be done with care: You must at least implement a lexer (to distinguish keywords from string literals), to deal with cases like:  ... WHERE customer = 'customer' ...

We do actually have a couple of functions that will parse the SQL that were written pre-4js.  Customers wanted to see what were the choices they made in a query by form in their reports they ran.   So we put it in more readable form, getting the actual description of the column from our system documentation, etc.   Then, we place that more readable string below the heading of the report.   This will probably be the route we go and maybe now we can use the stringTokenizer as an aid.

3) Using NCHAR/NVARCHAR and the (database-global!) Informix case-insensitive option must be investigated and used with care.
This can introduce side effects. For example, the sorting of NCHAR/NVARCHAR columns uses order of collation based on the locale.
https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1405.htm
fgldbsch supports Informix NCHAR/NVARCHAR columns: Program variables defined LIKE such columns will be like CHAR/VARCHAR variables.
Note also that with other database servers, the semantics of NCHAR/NVARCHAR types are totally different from Informix (different codeset, different length semantics).

I totally agree. Definitely uncharted territory. 

4) Other idea to be investigated: Use a view that is defined with UPPER(col) expressions from the source table, and use UPSHIFT attribute in the CONSTRUCT fields, by modifying the form at runtime... However, this requires a lot of code change (see charcase.tgz example attached).

We do actually modify the form at run-time.   We have all our forms loaded in a database, then the user can hide fields they don't want, upshift their fields, etc.

5) Ideally, for minimal user code change, the CONSTRUCT should be extended.

Yes! Not sure where this is in our priorities.  Will need to check with the boss.  :-)

Candy