...
...
| Button handy | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
This type of data source helps you obtain the values which that will populate the custom field using SQL scripts.
The purpose of SQL Data Source is to bring fast display results on the screen for your custom fields. Writing 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 that you need from the database.
Also, for the custom fields with autocomplete, you have to configure your own SQL Autocomplete Script for the custom fields with autocomplete.
| Info |
|---|
ImportantIf you want to use two columns at in the select statement, note that the first column will be considered as is the label, and the second will be is the value. |
The SQL Data Source has the following fields:
JNDI or Name of the datasource — Datasource name — represents the resource database name of database used. Here you type . Type the name of the database being used. If you want to configure a new data sourcedatasource, 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 set for Min. chars.
SQL Autocomplete Script — represents the script with autocomplete made by the user for custom fields.
After you have selected 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 on to 0, you need to write type the following sql SQL scripts: SQL Initial Script and SQL Autocomplete Script.
...
SQL Initial Script
| Code Block |
|---|
select brand from car_parts where name like 'Door'; |
SQL Autocomplete Script
| Code Block |
|---|
select name from car_parts
where brand like {query} || '%'; |
...
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
| Code Block |
|---|
select brand from car_parts
where name like {query} || '%'; |
...
If you have a custom field with no autocomplete, you need to type the following SQL Initial Script.
...
...
SQL Initial Script
| Code Block |
|---|
select id from car_parts where brand like 'Toyota'; |
| Note |
|---|
If you want to To use the like operator in your sql 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.
...
However, for Oracle databases we have use the following special cases:
...
| Code Block | ||
|---|---|---|
| ||
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:
|