Hitachi

JP1 Version 12 JP1/Automatic Operation Service Template Reference


4.16.9 Execute SQL Query

Function

This plug-in connects to a database server via ODBC and issues SQL statements.

This plug-in reads SQL statements sequentially from the text file specified by the Query File Path (Remote) property (database.queryFilePath), and then executes the statements one by one. (This text file is hereafter referred to as the "query file".) Then, as the execution results, CSV files are output to the folder specified by the Output Destination Folder Path (Remote) property (database.outputFolderPath). These CSV files are assigned names in the format "SQLRES_nnn.csv" (where nnn is the three-digit sequence number).

Example: SQLRES_001.csv

In the query file, each SQL statement must be specified on a single line. The query file can contain a maximum of 999 lines.

For each SQL statement in the query file, a CSV file is output as the execution result.

For reference-related SQL statements, this plug-in first outputs a header line and then the execution result. The default character encoding of the database control server is used for the CSV file.

For update-related SQL statements, no execution result is output. No CSV files are created, but sequence numbers are still issued. Sequence numbers for CSV files that were not created are treated as missing numbers.

The following is an example of the input query file and an example of the CSV files that are created.

Input example:

Select * from TBL_APP_USERS ... (Line 1)

Insert into TBL_APP_USERS(ID,NAME,DISCRIPT) Values(888,'Scott Green,'Newcomer') ... (Line 2)

Select * from TBL_APP_USERS ... (Line 3)

Output example:

SQLRES_001.csv (Execution result of line 1)

ID,NAME,DISCRIPT

111,John Green,Sample 1

222,Virgil Green,Sample 2

SQLRES_003.csv (Execution result of line 3)

ID,NAME,DISCRIPT

111,John Green,Sample 1

222,Virgil Green,Sample 2

888,'Scott Green,'Newcomer'

The ODBC-related terms used in this document are the display names used in the tool ODBC Data Source Administrator. These terms are as follows:

- ODBC Data Source Administrator

A standard tool in Windows for creating and managing data sources

- Data source

A collection of information that is necessary for connecting to a database server.

Data sources are created by using ODBC Data Source Administrator.

- Data source name (DSN)

A name that identifies a data source.

In ODBC Data Source Administrator, a data source name is also referred to as a DSN.

- ODBC driver

In ODBC Data Source Administrator, an ODBC driver is also referred to as a "driver".

This plug-in requires the following servers:

- Database control server (execution-target server)

A server that connects to a database server to execute ODBC commands

- Database server

A server on which the DBMS to be used is installed

Use situation

This plug-in can be used when a user wants to access data in a database by using SQL.

Prerequisites

For the most recent information about the prerequisite products for the system, prerequisite products for the execution-target system, and the supported OSs for the prerequisite products for the execution-target system, see the Release Notes.

Prerequisite products for the system:

JP1/Automatic Operation 11-00 or later

Prerequisite products for the execution-target system:

(1) Database server

- Oracle 10g Standard/Enterprise

- Oracle 11g Standard/Enterprise

- Oracle 12c Standard/Enterprise

(2) Database control server

- Microsoft .Net Framework 2.0 or later

- 32-bit edition of the ODBC driver that is compatible with the DBMS of the database server

Supported OSs for the prerequisite products for the execution-target system:

(1) Database server

- A server that meets the requirements for the DBMS

(2) Database control server

- Windows Server 2008 R2 Standard/Enterprise/Datacenter

- Windows Server 2012 Standard/Datacenter, Windows Server 2012 R2 Standard/Enterprise/Datacenter

Note: Environments where Server Core is installed are excluded.

Conditions for using the prerequisite products for the execution-target system:

(1) Conditions related to the network configuration

- The database control server must be able to connect to the database server via ODBC.

(2) ODBC-related conditions

- An ODBC driver must be correctly installed on the database control server.

- Data sources must have been created by using ODBC Data Source Administrator on the database control server.

- All settings for the created data sources must be specified correctly, and connectivity to the database server must have been verified by using those data sources.

Cautions

(1) If multiple SQL statements are specified on one line in the text file specified by the Query File Path (Remote) property (database.queryFilePath), this plug-in terminates abnormally. Note: This file is hereafter referred to as the "query file".

(2) The encoding type specified by the Encoding Type property (common.encodeType), the encoding type of the query file, and the encoding type set in the database must be the same. For details about the database encoding type, see the ODBC driver and DBMS specifications.

(3) If files already exist in the folder specified by the Output Destination Folder Path (Remote) property (database.outputFolderPath), the existing files are overwritten. For this reason, make sure the specified folder path is correct. Note: This folder is hereafter referred to as the "output-destination folder".

(4) If any SQL statement specified in the query file fails, processing stops and rolls back. None of the SQL statements following the first statement that failed will be executed. Rollback also occurs if no more files can be written to the output-destination folder because of insufficient free space. In such a case, none of the SQL statements following the first statement that failed will be executed.

(5) If a rollback occurs, the results of the SQL statements that were successfully executed are output to the output-destination folder. In such a case, incomplete CSV files might be output.

(6) If the CREATE, ALTER, or DROP statement is executed before rollback occurs, the execution results of these statements might not be restored after rollback, depending on the specifications of the DBMS being used.

(7) If this plug-in attempts to perform rollback via the ODBC driver when settings specific to the ODBC driver have been specified, the DBMS might not restore data. For details, see the specifications for the ODBC driver and the DBMS.

(8) For any service that uses this plug-in, treat the property mapped to the Database User Password property (database.dbUserPassword) as a password.

(9) For security reasons, properly manage the query file. In particular, take care that the file is not tampered with or replaced by another file. In addition, for the database account to be used, grant the minimum permissions necessary.

(10) Usually, to create data sources, you must start the ODBC Data Source Administrator administrative tool in the Control Panel. However, this plug-in is used to handle 32-bit applications. For this reason, to start the ODBC Data Source Administrator administrative tool, specify the absolute path (%SystemRoot%\SysWOW64\odbcad32.exe) of the tool in the command line.

(11) Data sources are categorized into system data sources and user data sources. This plug-in supports only system data sources.

(12) This plug-in calls the 32-bit edition of PowerShell.exe by referencing the environment variable SystemRoot. For this reason, if the value of this environment variable was changed from the value set during installation of the OS on the database control server, this plug-in might terminate abnormally.

(13) If an SQL statement specified on a line of the query file is too long, this plug-in might terminate abnormally. The maximum amount of data that the plug-in can read is 4 GB of data per line, as long as the memory of the database control server is not full. However, even if the SQL statement is less than 4 GB, the statement might exceed the limit of the ODBC driver. For details, see the specifications of the ODBC driver and the DBMS.

(14) If a line in the CSV file generated during the execution of an SQL statement is too long, this plug-in might terminate abnormally. The maximum amount of data that the plug-in can write to a CSV file is 4 GB of data per line, as long as the memory of the database control server is not full. However, even if the line to be written is less than 4 GB, the line might exceed the limit of the ODBC driver. For details, see the specifications of the ODBC driver and the DBMS.

(15) Character strings specified for the database.dbUserId and database.dbUserPassword properties are case-sensitive.

Version

02.00.00

Plug-in tags

Control Database,Windows,Oracle Database

Plug-in name displayed in the task log

dbIssueSqlQueries

Return code

0: Normal

12: Error (Mistake by user): Invalid property

23: Error (Invalid environment): The environment requirements are not met.

27: Error (Check the task log for details of the error): Unidentified error

41: Error (An error has been detected in the component): Property not entered (An error has been detected in the component script)

Property list

The following table lists the properties:

Property key

Property name

Description

Default value

I/O type

Required

plugin.destinationHost

Host name of the execution target server

Specify the host name or IP address of the server on which this plugin will be executed. IPv6 addresses are not supported.

--

Input

R

database.queryFilePath

Query File Path (Remote)

Specify the full path of the text file containing SQL statements. You can specify a maximum of 1,024 chars. In the file, do not specify multiple statements on one line or a single statement on some lines.The number of lines in file must not exceeded 999.

--

Input

R

common.encodeType

Encoding type

Specify the name of the encoding type of the text file indicated by the query file path. You can specify EUC-JP, SJIS, UTF-8, or UTF-16.

UTF-8

Input

R

odbc.dataSourceName

ODBC Data Source Name

Specify the name of the data source that was created on the database control server by using the ODBC Data Source Administrator.

--

Input

R

database.dbUserId

Database User ID

Specify the account ID to be used to log in to the database server.

--

Input

R

database.dbUserPassword

Database User Password

Specify the password of the account to be used to log in to the database server.

--

Input

R

database.outputFolderPath

Output Destination Folder Path (Remote)

Specify the full path of the folder where SQL execution results are output.For the output files, the default character-encoding of the database control server is used.

--

Input

R

common.returnValue

Return value for the plugin

The return value of this plugin stored.

--

Output

O