SQL Data Source
- Vanya Kiritzova
- Jonathan Muse
SQL Data Source lets you populate custom fields with database values using SQL scripts. You can configure how data is retrieved and displayed through initial and autocomplete scripts.
Configuring an SQL data source for your Power custom field involves the following steps:
Selecting the SQL Data Source type.
Selecting the database connection (JNDI or direct name).
For information about configuring the SQL database, see the Add and configure a new Power custom field page.
Create the required SQL scripts:
SQL initial script;
(Optional) SQL autocomplete script.
Script type | Description | Required for |
|---|---|---|
SQL Initial Script | Defines database queries to retrieve field values. |
|
SQL Autocomplete Script | Controls how search results appear as users type. |
|
SQL query results format
SQL queries can return data in two formats.
Select Format | How It Works | Example Query | Result Structure |
|---|---|---|---|
Single column | The selected value is used for both display and storage |
| Value "Project A" appears and is stored as "Project A". |
Two columns | The first column is shown to users; the second column is stored in the system. |
| Shows "Project A" to users; stores "PRJ-01" in the system. |
SQL configuration examples
Example 1: Autocomplete Field (Minimum Characters = 0)
Requires both initial and autocomplete scripts to configure.
SQL Initial Script
select manufacturer from cars
where status = 'available';This script provides an initial dropdown list of manufacturers that have available cars.
SQL Autocomplete Script
select model from cars
where manufacturer like {query} || '%'
and status = 'available';This script returns car models whose manufacturer name starts with the user's typed text.
For autocomplete scripts using the like operator, use the concatenation symbol (||) to combine the user's input ({query}) with the % symbol.When using the like operator in SQL scripts, use the concatenation symbol (||) to combine the query with the % symbol.
Example 2: Autocomplete Field (minimum characters > 0)
Requires both initial and autocomplete scripts to configure.
SQL Autocomplete Script
select brand from car_parts
where name like {query} || '%';This script filters car part names based on what the user types, showing matches that start with the typed text.
Example 3: Field without autocomplete
Requires only initial script.
SQL Initial Script
select id from car_parts
where brand like 'Toyota';This script retrieves the IDs of all Toyota car parts.
Using SQL Datasource with different databases
The SQL syntax for filtering results varies depending on your database type. Here's how to structure your queries for different databases.
Standard SQL syntax
Most databases use the concatenation operator (||) to combine the search query with wildcards.
select name from TestTable
where name like {query} || '%'Oracle database syntax
Oracle databases require special handling of the LIKE operator and wildcards, with syntax varying by JDBC driver version.
Use these patterns when working with ojdbc6.jar:
Search Type | SQL Syntax | Description |
|---|---|---|
Start of text |
| Finds values that begin with the search text. |
End of text |
| Finds values that end with the search text. |
Any position |
| Finds values containing the search text. |
Due to a
LIKEoperator parsing issue in ojdbc7.jar, use theCONCAToperator instead:
Search Type | SQL Syntax | Description |
|---|---|---|
Start of text |
| Finds values that begin with the search text. |
End of text |
| Finds values that end with the search text. |
Any position |
| Finds values containing the search text. |
Related content
Need support? Create a request with our support team.
Copyright © 2005 - 2025 Appfire | All rights reserved.