Auto-Complete without DISTINCT

Textboxes in Profound UI can be setup to provide auto-complete hints when a user types into the box. One powerful and simple way to configure the autocomplete is via "Database-Driven Auto-Complete" (Adding Auto-complete to Textboxes).

By default when a textbox uses database-driven auto-complete, Profound UI queries a database server using the "DISTINCT" SQL keyword to provide results with duplicates eliminated.

When the DISTINCT keyword is used in a query, some databases throw errors when columns in the ORDER BY clause are not in the SELECT clause, causing results to not appear.

For example, like the following configuration, which puts the column, PNAME, in the SELECT clause and PRID in the ORDER BY clause:

Open

The default database-driven auto-complete setup will not work with such a setup. A couple solutions are described below to get such a setup working.

Write Custom Web-services

One approach to getting autocomplete results working without using the default, DISTINCT, is to write your own web-service to provide the autocomplete results without using DISTINCT in a query. With a web-service built, you can specify it in the "choices url" property: .

Universal Display Files allow simple web-services to be written in RPG. In fact, a template ships out of the box to get started writing auto-complete web-services with a few clicks in JumpStart:

Open

Profound.js can also be used to provide web-services, and JumpStart also has an auto-complete template for Profound.js:

The advantages of web-services are that they only affect the Rich Display programs that call them; they do not affect Rich Display files where DISTINCT is desired in the SELECT clause (i.e. columns specified in the "choice options field" or "choice values field" properties).

Use the PUI_AUTOCOMPLETE_WITHOUT_DISTINCT Configuration Option

If using web-services is not desired, and you want all database-driven auto-completes to not use DISTINCT in queries, then there is a configuration option available: PUI_AUTOCOMPLETE_WITHOUT_DISTINCT.

Add the following line to the httpd.conf file for the Profound UI instance for which DISTINCT will not be used with database-driven auto-complete results:

The line can be in the config file's global scope, outside of any VirtualHost or Directory blocks. (For example, below the timeout directives is a good location.) If the instance name is PROFOUNDUI, then the config file is in the IFS path, /www/profoundui/conf/httpd.conf by default.

The Profound UI instance must be restarted in order for the change to take effect.

The configuration option is available in releases of Profound UI later than Version 6 Fix Pack 12.0.