This type of data source helps you obtain the values which will populate the custom field using SQL scripts.
The purpose of SQL Data Source is to bring fast results on the screen for your custom fields. 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 that you need from the database.
Also, for the custom fields with autocomplete, you have to configure your own SQL Autocomplete Script.
Important
If you want to use two columns at the select statement, note that the first column will be considered as the label, and the second will be the value.
If you have only one column in the select statement, this will be interpreted as label and value at the same time.
The SQL Data Source has the following fields:
- JNDI or Name of the datasource — represents the resource name of database used. Here you type the name of the database used.
- SQL Initial Script — represents the initial script which filters the results from database for custom fields with or without autocomplete and value 0 set for Min. chars.
- SQL Autocomplete Script — represents the script with autocomplete made by user for custom fields.
After you have selected 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 on 0, you need to write the following sql scripts: SQL Initial Script and SQL Autocomplete Script.
select brand from car_parts where name like 'Door';
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 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 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 several databases
For most databases, the process described above works just fine.
select name from TestTable
where name like {query} || '%'
However, for Oracle databases we have 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.
It seems that there is a bug that addresses the 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}), '%')