Debugging SQL queries

To evaluate certain SQL queries, it is beneficial to have a log file with bind variables appearing inline within the query.

You can use the debug:extract-sql-to-file function to obtain this alternative format by passing in a log file with SQL adapter logging as a parameter. This function separates the SQL queries in the input log file from other log entries and extracts them into an output log file in a simpler format that allows for easy debugging.

For the log file to be generated, the log level for the SQLAdapter class in the log4j.properties file must be set to DEBUG. The following line of code specifies this log level:
Dlog4j.logger.nexj.core.persistence.sql.SQLAdapter=DEBUG

Info

Extracting SQL dumps from large log files can impact your system performance. If you have a large log file, it is recommended that you split the log file into smaller files of 50 MB or less before extracting the SQL dump.

Example of the debug:extract-sql-to-file function

The following example shows the debug:extract-sql-to-file function run on an input log file named SystemOut.log to generate an output log file named SystemOut_sqllog.sql:

SQL

(debug:extract-sql-to-file
   "C:\\work\\logs\\SystemOut.log"
   "C:\\work\\logs\\SystemOut_sqllog.sql"
   'mssql
   #t
   #t
)

While scanning the input log file for SQL dumps, the function looks for lines such as the following.

TEXT

; 13:56:02,141 DEBUG [SQLAdapter] (http-nio-7080-exec-2) select top 1 A.id, A.data from NJClientState A where A.principalId = ? and A.application = ?
	Bind[0] = 00000000000010008000BEEF0000000C
	Bind[1] = 'Admin'

In the output log file, these lines appear as follows:

TEXT

select top 1 clientState_A.id, clientState_A.data from NJClientState clientState_A where clientState_A.principalId = 0x00000000000010008000BEEF0000000C and clientState_A.application = N'Admin'

You can then run a third-party formatter to expand the SQL queries in the output file and evaluate them further.