query_executor

1. Description

Query_executor program executes SQL files from specified folders in a user-defined order. The program executes only the .sql files present directly inside the specified folders and ignores files present inside nested folders.

2. Program location

<installation_path>/query_executor

3. Input

3.1 Mandatory Parameters

#	Parameters	Description	Example
1	connection-string	Connection credentials to connect to the database.	Click-here
2	root-directory-path	Root directory containing the folders with SQL files.	test-bed/data
3	folder-name	Comma separated list of folder names to be executed in the specified order.	folder_b,folder_a
4	log-file	Path to write logs.	test-bed/log.txt
5	diagnostics-log-file	Path to write diagnostics logs.	test-bed/diag_log.txt

3.2 Non Mandatory Parameters

#	Parameters	Description	Default value	Example
1	option	Additional parameters to be used for sqlcmd,sqlldr & sqlplus.The option parameters should be pipe seperated,containing the identification key and its value.	""	m,2|l,10
2	log-level	Level of diagnostics written to the log file.	info	error/warn/info/debug/trace/none
3	diagnostics-flag	The flag that decides whether performance diagnostics will be written to the diagnostics log file.	false	true or false
4	is-transaction-req	Indicates whether the query file should be considered as transaction.	false	true
5	mode	Execution mode for query processing. Further details are provided in the next section.	strict	strict/lenient

4. Mode Parameter

The mode parameter controls how query execution errors are handled.

• strict mode: If any error is encountered during execution of a SQL file, the entire transaction is rolled back and the SQL file execution fails.
• lenient mode: Errors encountered during query execution are ignored and execution continues wherever possible.

Note:

• Batch-termination errors in MSSQL cannot be ignored and will always cause the query file to be rolled back, even in lenient mode.
• All statements in a BEGIN ... END block are executed independently in MSSQL, but as a single unit in Oracle.
• In Oracle, DDL statements (such as CREATE, ALTER, and DROP) automatically commit any pending changes and those committed changes cannot be rolled back.

Example

Consider the following SQL file:

INSERT INTO DHVersion (Name, Version)
VALUES ('Application1', '1.0.0');

INSERT INTO DHVersion (Name, Version)
VALUES ('Application1', '1.0.0'); -- Primary Key Violation

INSERT INTO DHVersion (Name, Version)
VALUES ('Application2', '1.0.0');

In the above example:

• Query 1 is valid.
• Query 2 generates a Primary Key Violation error.
• Query 3 is valid.

strict mode

When executed in strict mode:

• The Primary Key Violation causes the SQL file execution to fail.
• The entire transaction is rolled back.
• Changes made by Query 1 and Query 3 are also rolled back.

Result:

Application1 -> Not inserted
Application2 -> Not inserted

lenient mode

When executed in lenient mode:

• The Primary Key Violation is ignored.
• Execution continues with the remaining queries.
• Successful queries are committed.

Result:

Application1 -> Inserted (from Query 1)
Application2 -> Inserted (from Query 3)

MSSQL Batch-Termination Error Example

INSERT INTO DHVersion (Name, Version)
VALUES ('Application1', '1.0.0');

CREATE TABLE DHVersion
(
    Name VARCHAR(100)
);

The second statement generates a batch termination error because the table already exists.

Result in both strict and lenient modes:

• SQL Server automatically terminates the batch.
• The transaction is rolled back.
• No changes from the SQL file are committed.

Common batch-termination errors include:

• Object already exists
• Incorrect SQL syntax
• Invalid column name
• Invalid object name
• Deadlock victim

5. Connection String

Connection strings for different Databases are given below:

5.1 MSSQL

{
  "db": "MSSQL",
  "schema": "dbo",
  "username": "sa",
  "password": "Pwd@123",
  "server": "localhost",
  "dbname": "TestDB",
  "options": {
    "t": "1"
  }
}

5.2 Oracle

{
  "db": "ORACLE",
  "schema": "RUNCONTROL_DEV_V6",
  "username": "RUNCONTROL_DEV_V6",
  "password": "Pwd@123",
  "server": "192.168.66.193:1521/orcl"
}

6. Output

Note: No output file is generated.

Output derivation logic:

The program executes SQL files from the folders specified in the folder-name parameter. Folders are executed in the exact order in which they are provided.

Within each folder:

• Only .sql files present directly inside the folder are considered.
• Files present inside nested folders are ignored.
• SQL files are executed in alphabetical order.

Example

folder_a
├── query1.sql
├── query2.sql
└── nested_folder
    └── query5.sql

folder_b
├── query3.sql
└── query4.sql

If the folder-name parameter is provided as:

folder_b,folder_a

The files will be executed in the following order:

query3.sql
query4.sql
query1.sql
query2.sql

In the above example:

• query3.sql and query4.sql are executed first because folder_b is specified before folder_a.
• query1.sql and query2.sql are executed next in alphabetical order.
• query5.sql is not executed because it is present inside a nested folder.

Note:

During execution, successfully processed SQL files are moved to a folder-specific executed directory.

For example:

folder_a  -> folder_a_executed_queries
folder_b  -> folder_b_executed_queries

Note:

After execution of all specified folders is completed:

• SQL files are moved back from the corresponding executed directories to their original folders.
• Executed directories are automatically deleted.

This mechanism helps in tracking pending and processed files during execution and also allows the same queries to be executed again in future runs.

Note:

The program writes execution details to the configured log file and diagnostic information to the configured diagnostics log file.

7. Sample Command

cargo run -- \
--connection-string "{
  \"db\": \"ORACLE\",
  \"schema\": \"RUNCONTROL_DEV_V6\",
  \"username\": \"RUNCONTROL_DEV_V6\",
  \"password\": \"RUNCONTROL_DEV_V6123\",
  \"server\": \"192.168.66.193:1521/orcl\",
  \"options\": {
    \"timeout\": \"30\"
  }
}" \
--root-directory-path test-bed/data \
--folder-name "folder_b,folder_a" \
--log-file test-bed/log.txt \
--diagnostics-log-file test-bed/diag.txt \
--log-level info \
--is-transaction-req false