Retrieve data by executing SQL
Logic name
Retrieve data by executing SQL (Document Mapper/Variable Mapper/Merge Mapper)
Function
This logic returns data retrieved by executing SQL.
Data types
|
Location |
Type |
|---|---|
|
Logic output |
String |
Refer to the following for details:
-
Logic specifications (Document Mapper/Variable Mapper)
-
Logic specifications (Merge Mapper)
Properties
|
Category |
Property |
Description |
|---|---|---|
|
Required settings |
Resource name |
Select a connection resource to execute SQL. |
|
Required settings |
SQL statement |
Specify a SQL statement to be executed. |
|
Required settings |
Parameter definition |
Specify a parameter type. |
|
0 match record |
Processing option |
Specify how to handle the case of 0 matching record. |
|
0 match record |
Alternative string |
Specify the alternative string. |
|
Comment |
Comment |
You can write a description of the logic. It doesn't affect the conversion process. |
0 match record/Processing option
|
Option |
Description |
|---|---|
|
Raise exception |
Throws the following exception: com.appresso.ds.vp.mapper.fastconverter.NoSuchRecordException: ExecuteSelectQueryLogic - <SQL statement> |
|
Replace by alternative string |
Replaces with the value of Alternative string. |
Function details
SQL parameters
SQL parameters can be used in the Retrieve data by executing SQL logic.
How to use SQL parameters
A SQL parameter is described as "?{name}". You can map parameters to the increased input handlers.
For example, when you write "select value from test_table where key1 = ?{param1} and key2 => ?{param2}" in SQL statement, the two input handlers are configured allowing you to make a mapping to SQL parameters (param1 and param2).
Available connection resources
Connection resources for the following connectors can be specified in Resource name.
|
Connector type |
|---|
|
DB2 Connector |
|
MySQL Connector |
|
Oracle Connector |
|
PostgreSQL Connector |
|
SQL Server Connector |
|
JDBC Connector |
|
Amazon Aurora for MySQL Connector |
|
Amazon RDS for MySQL Connector |
|
Amazon RDS for Oracle Connector |
|
Amazon RDS for PostgreSQL Connector |
|
Amazon RDS for SQL Server Connector |
|
Azure SQL Database Connector |
Specification limits
-
"?" can't be used for a SQL parameter name.
-
If data of multiple lines or multiple columns is obtained, the first column on the first line is returned.
-
Depending on specifications of database systems and application programs, some types can't be used for SQL or SQL parameters.
= Remarks =For more details, contact the database vendors and application vendors.
Logic sample
Mapping definition
Logic settings and processing result
This logic executes SQL and obtains data from a table.
Property settings
|
Property |
Setting |
||||||
|---|---|---|---|---|---|---|---|
|
Resource name |
DB |
||||||
|
SQL statement |
select VALUE from test_table where DAY_ID = ?{param1} and STYLE = ?{param2} |
||||||
|
Parameter definition |
|
||||||
|
0 match record/Processing option |
Replace by alternative string |
||||||
|
0 match record/Alternative string |
NOT_EXIST |
Input data
|
1 |
Abbreviation |
|
2 |
Abbreviation |
|
3 |
Abbreviation |
|
4 |
Abbreviation |
|
5 |
Full |
|
6 |
Full |
|
7 |
Full |
Search target table
|
DAY_ID |
STYLE |
VALUE |
|---|---|---|
|
1 |
Abbreviation |
Sun. |
|
2 |
Abbreviation |
Mon. |
|
3 |
Abbreviation |
Tue. |
|
4 |
Abbreviation |
Wed. |
|
5 |
Abbreviation |
Thu. |
|
6 |
Abbreviation |
Fri. |
|
7 |
Abbreviation |
Sat. |
|
1 |
FULL |
Sunday |
|
2 |
FULL |
Monday |
|
3 |
FULL |
Tuesday |
|
4 |
FULL |
Wednesday |
|
5 |
FULL |
Thursday |
|
6 |
FULL |
Friday |
|
7 |
FULL |
Saturday |
Output data
|
Sun. |
|
Mon. |
|
Tue. |
|
Wed. |
|
Thursday |
|
Friday |
|
Saturday |
For a list of logics, refer to Mapper Logic Guide.