...

...

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

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

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

...

...

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

...

SQL Initial Script

select id from car_parts
where brand like 'Toyota';

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:

• 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}), '%')