[Variable Mapper]Retrieve data by executing SQL

Logic Name

Retrieve data by executing SQL

Function

Returns data retrieved by executing SQL.

Data Types

Location	Type
Logic output	String

Refer to "Logic Specifications" for details.

Properties

Category	Property	Description
Required	Destination	Select global resource.
Required	SQL statement	Specify SQL statement to be executed.
Required	Parameter definition	Specify 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	The comment will also be output to the specifications.It does not affect the conversion process.

0 match record / Processing option

Option	Description
Raise exception	Throws the following exception. Caused by:[com.appresso.ds.vp.mapper.fastconverter.NoSuchRecordException: ExecuteSelectQueryLogic - <SQL statement>
Replace by alternative string	Replaces with the value of [Alternative string].

Function Details

SQL Parameter

SQL parameter can be used in this logic.

How to use SQL parameter

SQL parameter is described as "?{name}". You can map parameters to increased input handler.
For example, when you write "select value from test_table where key1 = ?{param1} and key2 => ?{param2}" in [SQL statement], 2 input handlers are configured allowing you to make a mapping to SQL parameter (param1 and param2).

Available Global Resources

Global resources of the following adapters can be specified in [Resource name].

Adapter Type
Access Adapter
DB2 Adapter
MySQL Adapter
Oracle Adapter
PostgreSQL Adapter
SQL ServerAdapter
JDBC Adapter
ODBC Adapter
Dr.Sum Adapter
Amazon Aurora for MySQL Adapter
Amazon RDS for MySQL Adapter
Amazon RDS for Oracle Adapter
Amazon RDS for PostgreSQL Adapter
Amazon RDS for SQL Server Adapter
Azure SQL Database Adapter

Specification Limits

"?" cannot be used for SQL parameter name.

If data of multiple line or multiple column is obtained, you return the first column on the first line.

Depending to the database and application setting, there are some types that cannot be used for SQL or SQL parameter.
For more details, please contact database vendors and application vendors.

Logic Sample

Mapping Definition

Logic Settings and Processing Result

Execute SQL and obtain data from a table.

Property Settings

Property

Setting

Destination

SQL statement

select VALUE from test_table where DAY_ID = ?{param1} and LOCALE = ?{param2}

Parameter definition

Parameter name	Type
param1	int (int)
param2	varchar (String)

0 match record / Processing option

Replace by alternative string

0 match record / Alternative string

NOT_EXIST

Input Data

1	JAPANESE
2	JAPANESE
3	JAPANESE
4	JAPANESE
5	ENGLISH
6	ENGLISH
7	ENGLISH

Search target table

DAY_ID	LOCALE	VALUE
1	JAPANESE	Nichiyoubi
2	JAPANESE	Getsuyoubi
3	JAPANESE	Kayoubi
4	JAPANESE	Suiyoubi
5	JAPANESE	Mokuyoubi
6	JAPANESE	Kinyoubi
7	JAPANESE	Doyoubi
1	ENGLISH	Sunday
2	ENGLISH	Monday
3	ENGLISH	Tuesday
4	ENGLISH	Wednesday
5	ENGLISH	Thursday
6	ENGLISH	Friday
7	ENGLISH	Saturday

Result Data

Nichiyoubi

Getsuyoubi

Kayoubi

Suiyoubi

Thursday

Friday

Saturday