SQL Language Guide : 8. SQL Statements : SET_SQL
 
Share this page                  
SET_SQL
Valid in: ESQL
The SET_SQL statement specifies runtime options for the current session.
Use SET_SQL to switch sessions in a multiple session application, specify whether local or generic errors are returned to a session, change the default behavior when a connection error is experienced, or set trace functions. To determine the settings for the current session, use the INQUIRE_SQL statement.
SET_SQL can be used to override the II_EMBED_SET environment variable/logical. For information about II_EMBED_SET, see the System Administrator Guide.
Note:  While SET_SQL is not supported in OpenAPI, Iiapi_catchEvent() operates similarly to embedded SQL statement SET_SQL(DBEVENTHANDLER = dbevent_handler).
Syntax
The SET_SQL statement has the following format:
EXEC SQL SET_SQL (object = value {, object = value})
EXEC SQL SET_SQL (SESSION = NONE)
The valid objects and values for the SET_SQL statement are as follows:
Object
Data Type
Description
dbeventdisplay
Integer
Enables or disables the display of events as they are queued to an application. Specify 1 to enable display, 0 to disable display.
dbeventhandler
function pointer
Specifies a user-defined routine to be called when an event notification is queued to an application. The event handler must be specified as a function pointer.
dbmserror
Integer
Sets the value of the error return variable dbmserror.
errorhandler
function pointer
Specifies a user-defined routine to be called when an SQL error occurs in an embedded application. The error handler must be specified as a function pointer.
errorno
Integer
Sets the value of the error return variable errorno.
errortype
character string
Specifies the type of error number returned to errorno and sqlcode. Value can be either genericerror, specifying generic error numbers or dbmserror, specifying local DBMS Server error numbers. Generic error numbers are returned by default. For information about the interaction of local and generic errors, see the chapter “Working with Transactions and Handling Errors.”
gcafile
character string
Specifies an alternate text file to which the application writes GCA information. The default filename is iiprtgca.log. To enable this feature, use the set_sql printgca option.
If a directory or path specification is omitted, the file is created in the current default directory.
messagehandler
function pointer
Specifies a user-defined routine to be called when a database procedure returns a message to an application. The message handler must be specified as a function pointer.
prefetchrows
Integer
Specifies the number of rows the DBMS Server buffers when fetching data for readonly cursors. Valid arguments are:
• 0 (default) - The DBMS calculates the optimum number of rows to prefetch.
• 1 - Disables prefetching.
• n: (positive integer) - Specifies the number of rows the DBMS prefetches.
For details, see the chapter “Working with Embedded SQL.”
printgca
Integer
Turns the printgca debugging feature on or off. Printgca prints all communications (GCA) messages from the application as it executes (by default, to the iiprtgca.log file in the current directory). Specify 1 to turn the feature on or 0 to turn the feature off.
printqry
Integer
Turns the printqry debugging feature on or off. Printqry prints all query text and timing information from the application as it executes (by default to the iiprtqry.log file in the current directory). Specify 1 to turn the feature on or 0 to turn the feature off.
printtrace
Integer
Enables or disables trapping of DBMS server trace messages to a text file (by default, iiprttrc.log). Specify 1 to enable trapping of trace output, 0 to disable trapping.
programquit
Integer
Specifies whether the DBMS Server aborts on the following errors:
• An application issues a query, but is not connected to a database
• The DBMS Server fails
• Communications services fail
Specify 1 to abort on these conditions
qryfile
character string
Specifies an alternate text file to which the application writes query information. The default filename is iiprtqry.log. To enable this feature, use the set_sql printqry option.
If a directory or path specification is omitted, the file is created in the current default directory.
savequery
Integer
Enables or disables saving of the text of the last query issued. Specify 1 to enable, 0 to disable. To obtain the text of the last query, issue the inquire_sql(:query=querytext) statement. Use the inquire_sql(:status=savequery) statement to determine whether saving is enabled.
session
Integer
Sets the current session. Value can be any session identifier associated with an open session in the application.
tracefile
character string
Specifies an alternate text file to which the application writes tracepoint information; the default filename is iiprttrc.log. To enable this feature, use the set_sql printtrace option.
If a directory or path specification is omitted, the file is created in the current default directory.
Issuing the SET_SQL (SESSION = NONE) statement results in the state being identical to prior to the first CONNECT statement or following a DISCONNECT statement (no current session).