This type of data source helps you obtain the values that will populate the custom field using SQL scripts.

The purpose of SQL Data Source is to display results on the screen for your custom fields. When writing the SQL Initial Script for the custom fields with no autocomplete or with autocomplete and value 0 set for Min. chars, you can configure the details for the information you need from the database.

Also, you have to configure your own SQL Autocomplete Script for the custom fields with autocomplete.

Important

If you want to use two columns in the select statement, the first column is the label, and the second is the value. If you only have one column in the select statement, this will be both the label and value.

The SQL Data Source has the following fields:

JNDI or Datasource name — represents the database name. Type the name of the database being used. If you want to configure a new datasource, click on Not Enough? Try adding your own, new datasource!
SQL Initial Script — represents the initial script which filters the results from the database for custom fields with or without autocomplete and the value set to 0 for Min. chars.
SQL Autocomplete Script — represents the script with autocomplete made by the user for custom fields.

After you select the SQL Data Source, you will have one of the following options.

Option 1

If you have a custom field with autocomplete and you want to set Min. chars to 0, type the following SQL scripts: SQL Initial Script and SQL Autocomplete Script.

SQL Initial Script

select brand from car_parts
where name like 'Door';

SQL Autocomplete Script

select name from car_parts
where brand like {query} || '%';

Option 2

If you have a custom field with autocomplete and you want to set Min. chars on a value greater than 0, you need to type the following SQL Autocomplete Script.

SQL Autocomplete Script

select brand from car_parts
where name like {query} || '%';

Option 3

If you have a custom field with no autocomplete, you need to type the following SQL Initial Script.

SQL Initial Script

select id from car_parts
where brand like 'Toyota';

If you want to use the like operator in your SQL scripts, you have to use the concatenation symbol between query and the % symbol.

Using SQL Datasource on multiple databases

For most databases, the process described above works just fine.

select name from TestTable
where name like {query} || '%'

However, for Oracle databases use the following special cases:

For searching in the beginning of the text:

select name from TestTable
where name like {query%}

For searching in the end of the text:

select name from TestTable
where name like {%query}

For searching anywhere in the text:

select name from TestTable
where name like {%query%}

Note

We tested the above scripts using ojdbc6.jar.

There is a bug that results in an issue where the LIKE operator causes a problem in the parser, existing in ojdbc7.jar, that makes the scripts unusable for Oracle.

As a workaround, we recommend that you use the concat operator:

For searching in the beginning of the text:

select name from TestTable
where name like concat({query}, '%')

For searching in the end of the text:

select name from TestTable
where name like concat('%', {query})

For searching anywhere in the text:

select name from TestTable
where name like concat(concat('%', {query}), '%')