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?

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 'some.lower.case@email.address' 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.