Script to extract prepared SQL statements from LEVEL 3 and INOUT server side SQL logging

Python script to extract SQL (prepared) statements from the SQL_server_x_YYYYMMDD_hhmmss_A.log files

How to extract SQL prepared statements recorded with server side SQL logging LEVEL 3 and INOUT introduced in OpenEdge 11.7:

SET PRO_SERVER LOG ON WITH (STATEMENT, INOUT, QUERY_PLAN) AND WITH LEVEL 3;
COMMIT;

Use-Cases: To replay SQL statements recorded from production to isolate which statement is causing application errors or to compare query performance differences between environments.

Server Side SQL logging capabilities were enhanced in OpenEdge 11.7 :

SET PRO_SERVER LOG [ ON | OFF ] 
[ WITH ({ STATEMENT, INOUT, QUERY_PLAN })] 
[[AND] 
WITH LEVEL 1|LEVEL 2|LEVEL 3|LEVEL 4];

For further information Refer to Article  SQL-92: How to turn SQL statement logging on and off?  

To capture, extract and replay SQL prepared statements:

The following are provided in the attachment to this Article: 000091419_sql_parse_and_replay.zip

1. enableSqlLogging.bat or enableSqlLogging.sh

• Edit the sqlexp command to connect to the database under investigation with the correct credentials
• Enables SQL server side logging:
  SET PRO_SERVER LOG ON WITH (STATEMENT, INOUT, QUERY_PLAN) AND WITH LEVEL 3;

2. disableSqlLogging.bat or disableSqlLogging.sh 

• Edit the sqlexp command to connect to the database under investigation with the correct credentials
• Disables SQL server side logging:
  SET PRO_SERVER LOG OFF;

3. sqlExtract.py

A python script to parse SQL Statements captured with Server Side SQL logging LEVEL 3 and INOUT

Log files can be concatenated into a single file when more than one needs to be processed
To run the extraction process execute the python script directly:
Alternatively use sqlExtract.bat (Windows) or sqlExtract.sh (Linux/Unix) provided as sample scripts:

• If the location of the Python executable is not in the Environment PATH, these scripts need to be edited in order that python.exe can be found.
  $   sqlExtract.bat SQL_server_3_20190811_021004_A.log outputScript.txt
  $   ./sqlExtract.sh SQL_server_3_20190811_021004_A.log outputScript.txt

SQL Statement Output (outputScript.txt)

Non prepared SQL statements will each be written on a separate text line in the python output file.
Prepared SQL statements, will be presented as follows in the python script output file:

SELECT * FROM pub.tableName WHERE someColumn = ? AND someOtherColumn = ?
[0]:  = (11)
[1]:  = (2018)

4. SqlBatchExecParamQueries.bat and SqlBatchExecParamQueries.sh

• To replay the SQL (prepared) statements in a controlled environment for further analysis
• Optionally to only execute SQL SELECT statements, use -onlySqlSelects

References to Other Documentation:

OpenEdge Data Management: SQL Reference, OpenEdge SQL Statements: "SET PRO_SERVER LOG"
https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf/set-pro-server-log.html