Types of Inquiries

The following table lists the valid inquiries that can be performed using the inquire_sql statement:

Object	Data Type	Description
column_name	Character	Valid only in a data handler routine that retrieves data (in conjunction with a select or fetch statement); returns the name of the column for which the data handler was invoked. The receiving variable must be a minimum of 32 bytes; if the host language uses null-terminated strings, an additional byte is required.
columntype	Integer	Valid only in a data handler routine that retrieves data (in conjunction with a select or fetch statement); returns an integer indicating the data type of the column for which the data handler was invoked.
connection_target	Character	Returns the node and database to which the current session is connected; for example, 'bignode::mydatabase'.
dbmserror	Integer	Returns the number of the error caused by the last query. This number corresponds to the value of sqlerrd(1), the first element of the sqlerrd array in the SQLCA. To specify whether a local or generic error is returned, use the set_sql(errortype) statement.
endquery	Integer	Returns 1 if the previous fetch statement was issued after the last row of the cursor, 0 if the last fetch statement returned a valid row. This is identical to the NOT FOUND condition (value 100) of the SQLCA variable sqlcode, which can be checked after a fetch statement is issued. If endquery returns '1', the variables assigned values from the fetch are left unchanged.
errorno	Integer	Returns the error number of the last query as a positive integer. The error number is cleared before each embedded SQL statement. Errorno is meaningful only immediately after the statement in question. This error number is the same as the positive value returned in the SQLCA variable sqlcode, except in two cases: A single query generates multiple different errors, in which case the sqlcode identifies the first error number, and the errorno object identifies the last error. After switching sessions. In this case, sqlcode reflects the results of the last statement executed before switching sessions, while errorno reflects the results of the last statement executed in the current session. If a statement executes with no errors or if a positive number is returned in sqlcode (for example, +100 to indicate no rows affected), the error number is set to 0.
errortext	Character	Returns the error text of the last query. The error text is only valid immediately after the database statement in question. The error text that is returned is the complete error message of the last error. This message can have been truncated when it was deposited into the SQLCA variable sqlerrm. The message includes the error number and a trailing end-of-line character. A character string result variable of size 256 must be sufficient to retrieve all error messages. If the result variable is shorter than the error message, the message is truncated. If there is no error message, a blank message is returned.
errortype	Character	Returns 'genericerror' if generic errors are returned to errorno and sqlcode, or 'dbmserror' if local DBMS Server errors are returned to errorno and sqlcode. For information about generic and local errors, see the chapter "Working with Transactions and Handling Errors."
messagenumber	Integer	Returns the number of the last message statement executed inside a database procedure. If there was no message statement, a zero is returned. The message number is defined by the database procedure programmer.
messagetext	Character	Returns the message text of the last message statement executed inside a database procedure. If there is no text, a blank is returned. If the result variable is shorter than the message text, the message is truncated. The message text is defined by the database procedure programmer.
object_key	Character	Returns the logical object key added by the last insert statement, or -1 (in the indicator variable) if no logical key was assigned.
prefetchrows	Integer	Returns the number of rows the DBMS Server buffers when fetching data using readonly cursors. This value is reset every time a readonly cursor is opened. If your application is using this feature, be sure to set the value before opening a readonly cursor. For details, see the chapter "Working with Embedded SQL."
programquit	Integer	Returns 1 if the programquit option is enabled (using set_sql(programquit). If programquit is enabled, the following errors cause embedded SQL applications to abort: Issuing a query when not connected to a database Failure of the DBMS Server Failure of communications services Returns 0 if applications continue after encountering such errors.
querytext	Character	Returns the text of the last query issued; valid only if this feature is enabled. To enable or disable the saving of query text, use the set_sql(savequery=1\|0) statement. A maximum of 1024 characters is returned; if the query is longer, it is truncated to 1024 characters. If the receiving variable is smaller than the query text being returned, the text is truncated to fit. If a null indicator variable is specified in conjunction with the receiving host language variable, the indicator variable is set to -1 if query text cannot be returned, 0 if query text is returned successfully. Query text cannot be returned if (1) savequery is disabled, (2) no query has been issued in the current session, or (3) the inquire_sql statement is issued outside of a connected session.
rowcount	Integer	Returns the number of rows affected by the last query. The following statements affect rows: insert, delete, update, select, fetch, modify, create index, create table as select, and copy. If any of these statements runs successfully, the value returned for rowcount is the same as the value of the SQLCA variable sqlerrd(3). If these statements generate errors, or if statements other than these are run, the value of rowcount is negative and the value of sqlerrd(3) is zero. Exception: for modify to truncated, inquire_sql(rowcount) always returns 0.
savequery	Integer	Returns 1 if query text saving is enabled, 0 if disabled.
session	Integer	Returns the session identifier of the current database session. If the application is not using multiple sessions or there is no current session, session 0 is returned.
table_key	Character	Returns the logical table key added by the last insert statement, or -1 (in the indicator variable) if no logical key was assigned.
transaction	Integer	Returns a value of 1 if there is a transaction open.

Permissions

All users are permitted to use this statement.

Related Statements

Examples: Inquire_sql

The following are Inquire_sql statement examples:

Execute some database statements, and handle errors by displaying the message and aborting the transaction.
exec sql whenever sqlerror goto err_handle;

exec sql select name, sal
    into :name, :sal
    from employee
    where eno = :eno;

if name = 'Badman' then
    exec sql delete from employee
        where eno = :eno;
else if name = 'Goodman' then
    exec sql update employee set sal = sal + 3000
        where eno = :eno;
end if;

exec sql commit;

...

err_handle:

exec sql whenever sqlerror continue;
exec sql inquire_sql (:err_msg = errortext);
print 'INGRES error: ', sqlca.sqlcode;
print err_msg;
exec sql rollback;
The following example demonstrates the use of the whenever statement for intercepting trace messages from a database procedure. The messages are written to a trace file.
exec sql whenever sqlerror stop;
exec sql whenever sqlmessage call trace_message;

exec sql execute procedure proc1 into :retstat;
...

/* Inside the "trace_message" host language
     procedure */
exec sql inquire_sql (:msgnum = messagenumber,
    :msgtxt = messagetext);

if (msgnum = 0) then

    print logfile, msgtxt;

else

    print logfile, msgnum, '-'msgtxt;

end if;