How to do a UniData Case Insensitive Search with Uniquery

Question

Unfortunately I have to do some interaction with IBM's UniData database system. I am doing this from c# code with UniObjects for .net.

I am building an ASP.NET search page that has a single search box. The problem I am having is that the criteria is case sensitive. How can I do a case insensitive search with UniQuery?

I could return everything and achieve case insensitivity in my Linq to XML statement, but that will lead to performance problems as it's not very efficient.

Here is the code that I have written:

using IBMU2.UODOTNET;
using UniObjectsHelper;
using System.Xml.Linq;
...
    void DoSearch()
    {
        XElement xml;

        using (UniSession us = UniHelper.OpenSession((UniDataConfig)ConfigurationManager.GetSection("unidataConfig")))
        {
            UniCommand cmd = us.CreateUniCommand();

            // this is probably insecure. I will deal with that later
            cmd.Command = string.Format(@"LIST UT.OPERS WITH @ID = ""{0}"" OR WITH LAST.NAME = ""{0}"" OR WITH FIRST.NAME = ""{0}""  OR WITH MIDDLE.NAME = ""{0}"" LAST.NAME FIRST.NAME MIDDLE.NAME TOXML", txtSearch.Text);
            cmd.Execute();

            xml = XElement.Parse(cmd.Response);
        }

        gvwResults.DataSource = from x in xml.Descendants("UT.OPERS")
                                select new
                                {
                                    User = x.Attribute("_ID").Value,
                                    FirstName = x.Attribute("FIRST.NAME").Value,
                                    LastName = x.Attribute("LAST.NAME").Value,
                                    MiddleName = x.Attribute("MIDDLE.NAME").Value
                                };
        gvwResults.DataBind();                                
    }

EDIT

I found this:

UDT.OPTIONS 92

U_INSENSITIVE_MATCH

This option affects queries run on data that contains Pick®-style conversions in dictionary definitions. The Pick®-style processing codes MCL, MCT, and MCU convert the case of characters. These conversions are applied to the data before the comparison and selection, thus omitting matching characters of unlike case. UDT.OPTIONS 92 makes LIKE convert both the data and the literal on which the selection is based, so that the selection is, in effect, not based on case.

I don't really know what "Pick®-style processing codes MCL, MCT, and MCU" are. Can anyone explain?

Answer 1

I looked around a bit and I cannot find a case-insensitive SELECT, LIST, or SORT command in UniQuery nor a switch/setting to modify the case-sensitivity. Unbelievable, eh?

Here's an idea though:

You can call .ToLower on txtSearch.Text and set the conversion code (attribute 3) to MCL in the UT.OPERS dictionary for LAST.NAME, FIRST.NAME, etc. Apples to apples.

One thing I found in testing this is that it only works if you surround each of your selection criteria with wildcard brackets, e.g.: ...WITH LAST.NAME = ""[{0}]""

If you don't want to modify your stock dictionaries for LAST.NAME, etc. you can create new dictionary items and prefix them with L_ (or something) to distinguish them.

EDIT:

• MCL converts text to lower-case
• MCT converts text to proper-case
• MCU converts text to upper-case

If you put any one of these "Pick-style" conversion codes in attribute 3 of the dictionary that describes your field, the conversion will run every time you use the dictionary.

For example, if you added "MCL" to your LAST.NAME field, when you did LIST UT.OPERS LAST.NAME all of the last names would be formatted in lower-case regardless of how the data is actually stored.

What I believe the UDT.OPTION 92 does is it ensures that the literal in your select criteria is also converted using the same conversion code that is in the dictionary thus giving you case-insensitivity.

SELECT UT.OPERS WITH LAST.NAME = "Smith"

Would be converted to:

SELECT UT.OPERS WITH LAST.NAME = "smith"

before the comparison occurred.

Essentially, what UDT.OPTION 92 will do for you is prevent you from having to call .ToLower in the idea I presented above. Not much bang for the buck, IMHO.

Answer 2

You wont have to create any computed columns or dictionary items to achieve case insensitive searching in Unidata/Datatel.

I found some documentation that suggest option 92 be turned on and I should use some MCL code and the OCONV function. I couldn't quite get it to work. BUT! I WAS ON THE RIGHT TRACK!

I even got this answer concerning case insensitive queries from an engineer at Rocket Software (the company that obtained UniData from IBM):

Technically no, there is no case-insensitive select statement.
However, you can do things that make your UniQuery statements behave the same way.
You can create a dictionary item on an attribute that converts it to either all upper or lower case. In the example below the dictionary item converts field 2 to all lower case.

EXAMPLE:

AE DICT VOC F2.CASE
001: D
002: 2
003: MCL
004:
005: 15L
006: S

UDT.OPTIONS 92 makes MCU, MCL and MCT type dictionaries behave differently. You can read about this in the UDT.OPTIONS Command Reference available in the UniData online documentation.

So, he was talking about going to the trouble before hand to create these extra dictionary items, which is something I can't abide by. It's just too much effort. Thanks to Scott Crosby at Alamance Community College for sending me this:

Man, you asked this ages ago, and I never got back to you. I remember you asking when I was sifting through some code, working on a project. Your question was about querying the Unidata DB, but more specifically, using case in-sensitive searches. The only solution I came up with is to use OCONV with the MCL code, to force Unidata to do a strtolower on the data before comparing. You probably already found a way to do this, but here it is anyway!

$query = "LIST PERSON WITH EVAL\"OCONV(PERSON.EMAIL.ADDRESSES,'MCL')\" LIKE '" . strtolower($email) . "' PERSON.EMAIL.ADDRESSES ID.SUPP NOPAGE TOXML ELEMENTS WITHDTD";

Bascially, I wanted to search PERSON.EMAIL.ADDRESSES for $email (from PHP app), to see if it exists in the database. Thanks, Scott C. Crosby

So, when you take the PHP and XML stuff out of his example, the command looks like this:

LIST PERSON WITH EVAL"OCONV(PERSON.EMAIL.ADDRESSES,'MCL')" LIKE '[email protected]' PERSON.EMAIL.ADDRESSES ID.SUPP NOPAGE TOXML ELEMENTS WITHDTD";

The syntax, WITH EVAL"OCONV(FILE.FIELD.NAME,'MCL')" LIKE 'lower case search text' gets us what we want. It's not the prettiest thing in the world, but it's easy to do and it works.