Debugging SQL statements

Set the FGLSQLDEBUG environment variable to print SQL debug info.

SQL debug information is printed by the runtime system when the FGLSQLDEBUG environment variable is set to a value different from zero.

Possible values for the FGLSQLDEBUG environment variable are:

Table 1. FGLSQLDEBUG values
FGLSQLDEBUG	Description
`0`	Disable SQL debug log.
`-1`	Print global SQL debug message only when an SQL statement produces an error.
`1,2,3,4,...`	Print SQL debug message of the specified level, for each executed SQL statement.

The debug messages are sent to the standard error stream. If needed, you can redirect the standard error output into a file.

UNIX™ (shell) example:

FGLSQLDEBUG=3
export FGLSQLDEBUG
fglrun myprog 2>sqldbg.txt

When FGLSQLDEBUG is set with a positive value, the runtime system prints an SQL debug message for each SQL statement executed by the program.

SQL debug messages show detailed information about the SQL statement execution, for example:

SQL: DATABASE
 | 4gl source      : c.4gl line=2
 | loading driver  : [/opt/fgl/dbdrivers/dbmdefault]
 | db driver type  : ifx
 | sqlcode         : 0
 | curr driver     : ident='dbmdefault'
 | curr connection : ident='_1' (dbspec=[test1])
 | Execution time  :   0 00:00:00.02689
SQL: DELETE FROM mytable WHERE pkey IS NULL
 | 4gl source      : c.4gl line=4
 | sqlcode         : -206
 |   sqlstate      : 42000
 |   sqlerrd2      : -111
 |   sql message   : The specified table (mytable) is not in the database.
 |   sql msg param : mytable
 | curr driver     : ident='dbmdefault'
 | curr connection : ident='_1' (dbspec=[test1])
 | Execution time  :   0 00:00:00.00035

Note: The most important information is the SQL error code and the source code line where the SQL statement failed. For performance tuning, the execution time can be used to identify slow SQL statements.

If the SQL debug log generates too many messages, set FGLSQLDEBUG to -1, in order to produce an SQL debug message only for the SQL statements that produce an SQL error. Once the SQL statement producing the error is identified, you can find the source code line where the error occurred, to reproduce the problem. Before reproducing the error, set FGLSQLDEBUG to a high positive level, to get detailed debug information.

MAIN
    DATABASE stores
    DELETE FROM mytable WHERE pkey IS NULL  -- SQL debug if FGLSQLDEBUG is set
    CALL fgl_sqldebug(3)
    DELETE FROM mytable WHERE pkey IS NULL  -- SQL debug is enabled
    CALL fgl_sqldebug(0)
    DELETE FROM mytable WHERE pkey IS NULL  -- SQL debug if FGLSQLDEBUG is set
END MAIN

Note: When FGLSQLDEBUG is set to a positive value, an SQL debug header is printed before executing the underlying database driver code. If the driver code crashes or raises an assertion, you can easily find the last SQL instruction that was executed by the program, and report to your support center. When FGLSQLDEBUG is set to -1, the SQL debug header is not printed before executing the database driver code, because the SQL execution status (SQLCA.SQLCODE) is not known before executing the statement. If the driver code crashes, no error message will be printed. If you experience database driver crashes, use FGLSQLDEBUG with a positive value in order to identify the problem.