Page Comparison

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 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.

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.

Image Removed

...

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 SQL Autocomplete Script.

Image Modified

...

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.

Image Modified  

...

SQL Initial Script

select id from car_parts
where brand like 'Toyota';

...

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%}

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

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

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