drop-down combo box-like functionality for queries.

Question

Is it possible to provide the following type of fuctionality with informix client tools?

As the user types the first two characters of a name, the drop-down list is empty. At the third character, the list fills with just the names beginning with those three characters. At the fourth character, MS-Access completes the first matching name (assuming the combo's AutoExpand is on). Once enough characters are typed to identify the customer, the user tabs to the next field.

The time taken to load the combo between keystrokes is minimal. This occurs once only for each entry, unless the user backspaces through the first three characters again.

If your list still contains too many records, you can reduce them by another order of magnitude by changing the value of constant conSuburbMin from 3 to 4.

Answer 1

This requires a combination of two things, only one of which is partially under the control of Informix the DBMS or Informix the Client API.

First of all, you need the gadget that is accepting user input to asynchronously generate a query which matches what the user has typed, fetches some of the results from the DBMS, and shows them. Secondly, you need the DBMS to respond rapidly to such queries. Part of the issue is 'what form does the query take'. But the basic functionality is:

 SELECT TitleCaseName
   FROM ReferenceTable
  WHERE LowerCaseName[1,3] = 'abc';

You might or might not bother with 'first rows optimization'; you might or might not bother with an ORDER BY. Your code would only select the first N rows. You might do it with some prioritization information - most frequently used names, etc.

But this is logic is basically the same for any DBMS - give or take the details such as the choice of technique for dealing with case-mapping (function call vs column) and notation for substrings vs LIKE 'abc%'.

The tricky stuff, though, is the asynchronous combination of user-input plus collecting data from the DBMS; that is best handled with multiple threads, one dealing with the user input, one dealing with the DBMS and (possibly) one dealing with the display (or that might also be the one dealing with user input). And that requires hooking into the UI API - not something that the Informix APIs do of their own accord. The UI can get at Informix (or any other DBMS) easily enough through ODBC or any other faintly similar API.