# SQL Executor This component is used to execute single or multiple SQL queries on the target database. The component can be configured with SQL statements that will be executed against the target database. The results of the query are sent to other components for further processing. This component is useful for tasks such as data retrieval, data updates, and database schema modifications. ![](https://support.xnarum.com/download/manuals/ISM-Manual-2023.fld/image211.png) If Multiple Query attribute is checked, each query generates its own result data.

Otherwise, SQL Executor executes whole queries in a single statement. Whether those whole queries are acceptable depends on the database and jdbc driver. The sample below is the result to maria DB and with mysql jdbc driver.

## **Input**

Attribute	Description
System Id	Database system id
Use Data Structure?	Map query result as data structure?
Save To File?	Save the query result into a file backed up list? This option is particularly useful when dealing with large record sizes. High volumes of records can lead to an OutOfMemory error, making this option a valuable tool for managing memory usage and avoiding potential issues.
Query	Query to be executed. Multiple queries are allowed. Queries are separated by semi-colon.
Multiple Query?	If checked, Query data is parsed, delimited by semi colon, and executed one by one. If unchecked, Query data is executed in one statement.
Query Type	Query type Literal - the query is constructed as a literal. Prepared - the query is constructed with ?.
Operation Type	Query or Stored procedure
Output Parameters	If operation type is procedure, output parameters can be assigned.

Query can be generated from table layout. 1\) Choose a database. 2\) Click generate (

) button and generate query.

Query can contain parameters.

If Query Type is prepared, !\[Graphical user interface, text, application Description automatically generated]\() The query is converted into this sql. | select \* from source where year = year(curdate()) and zone = ? and today = ? | | ----------------------------------------------------------------------------- | Otherwise, parameters are replaced with the real values and single quotation for string type values. | select \* from source where year = year(curdate()) and zone = 'zone01' and today = '2023-03-01' | | ----------------------------------------------------------------------------------------------- | If Save To File is checked, and operation is select, then the result set is stored in a file. The list in the memory has the index information. It does not have the data. The real data is stored in a temporary file. This file is generated at the directory which java.io.tmpdir property points to with .fjs extension. The file is deleted after the list is garbage collected. If the expected record size of the input is huge, Save To File should be checked. This component gets the database connection from the information of system id. But the connection info can be acquired through parameterized way out of ISM. If these properties exist, this component use these properties instead of system id.

Attribute	Description
Connection String	Custom connection string
User ID	Custom user id
Password	Custom password
Driver Class	Custom JDBC driver class

Thiese custom properties can be used for the databases like FireBird or Cubrid which ISM does not support through System menu. |

| | ---------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | FireBird | Cubrid | ## **Output**

Attribute	Description
ResultCode	Error code from the database
ResultMessage	Error message from the database
ResultCount	Retrieved count for Select, Update/Insert/Delete count for Update/Insert/Delete operations. If multiple queries are executed, ResultCount-index_number will be generated. The index number starts from 0.
ResultArray	Retrieved data set. If multiple queries are executed, ResultArray-index_number will be generated.
DataStructureId	Data structure id of the retrieved data set

If UseDataStructure property is yes, the result set is carried in this parameter - MyResult. The default name of this parameter is *DataStructureId* and is duplicate with another parameter. The name should be changed if this result set is used in mapping. *DataStructureId -> other name*

## **Example** This example flow simply generates the result set.

--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://ism-docs.xnarum.com/tasks/db/sql-executor.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.