Debugging SQL statements

Set the FGLSQLDEBUG environment variable to print SQL debug info.

SQL debug information is printed by the runtime system when using a value different from zero in the FGLSQLDEBUG environment variable or with the fgl_sqldebug() built-in function.

The debug messages are sent to the standard error stream (stderr). Consider to redirect the standard error output into a file, to inspect the debug log after program execution.

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.

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.

It is also possible to use the fgl_sqldebug() built-in function to set the SQL debug level by program:

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