Inquire_sql
Provides diagnostic information during run time about various aspects of an application's interaction with the database.
Syntax
inquire_sql (fieldname = inquire_sql_constant
{, fieldname = inquire_sql_constant})
You can use the inquire_sql statement to retrieve various types of information about a database event that has been raised. The following are the various inquire_sql constants related to events:
fieldname
Specifies the name of a field to hold the value of the Ingres constant
inquire_sql_constant
Specifies the constant containing the information about the results of a database statement
Possible values for inquire_sql_constant are:
connection_name
Specifies the name assigned to the session with the connect dbname as connection_name syntax. If the connection name was not specified and a session id was specified, Ingres assigns the session the connection name of ii followed by the session id. If neither the session id nor the connection name was specified in the connect statement, the connection_name is the database name. The connection_name is a varchar of length 128.
connection_target
Is the full specification of the database to which the application is currently connected. The specification is returned as the node name followed by two colons and the database name. For example, mynode::mydb.
dbmserror
Specifies the error number (positive integer) of the last query statement
endquery
Returns an integer value:
The last select loop statement returned a valid row.
1 = Indicates that the previous select loop statement was issued after the last row of the selected data. The data is invalid because no more information remained to be retrieved.
When endquery returns 1, the variables assigned values from the query are not changed.
errorno
Specifies the error number (positive integer) for any error that occurred on the last query statement. Cleared before each DBMS statement, so the value of errorno is valid only when the inquire_sql statement is issued immediately after the query statement.
0 = Indicates that no error occurred
errortext
Specifies a character string containing the error text of the last query. Valid only when the inquire_sql statement immediately follows the query statement. A character string result variable of size 256 can retrieve most Ingres error messages.
The returned error text is the complete error message of the last error, including the error number and a trailing end-of-line character. 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
Returns genericerror (character string) if Ingres is returning generic error numbers to errorno. Returns dbmserror if Ingres is returning local DBMS error numbers to errorno. See your query language reference guide for information about the interaction of generic and local DBMS errors.
dbeventname
Specifies the name of a database event that was raised. The receiving variable must be a character string.
dbeventowner
Specifies the Ingres username of the user that created the database event that was raised. The receiving variable must be a character string.
dbeventdatabase
Specifies the name of the database in which the event was raised; this is always the current database. The receiving variable must be a character string.
dbeventtime
Specifies the date and time at which the database event was raised. The receiving variable must have an Ingres date format.
dbeventtext
Specifies the message text associated with the database event. The receiving variable must be a string of up to 256 characters. It must be of sufficient length to contain the message text; otherwise, the text is truncated.
messagenumber
Returns integer number of the last message statement executed inside a database procedure. The message text is determined by the user. If no message, a zero is returned.
messagetext
Indicates the message text of the last message statement executed inside a database procedure (character type). The message text is determined by the user. If the result variable is shorter than the message text, the message is truncated.
If no text, a blank is returned.
programquit
Returns integer value:
1 = If the FRS exits the application after any of the following errors:
• Errors resulting from attempting to execute a query without connection to a database
• Errors resulting from failure of the DBMS server while an application is running
• Errors resulting from failure of the communications server while an application is running
0 = The does not exit the application after the errors described above.
rowcount
Specifies the number of rows affected by the most recent query statement (an integer). Query statements: insert, delete, update, select. If the query is successful, the value of rowcount is the number of rows inserted, deleted, updated, or selected. If there are errors, or if statements other than these are run, the value of rowcount is negative. The following rules apply:
A singleton query always causes inquire_sql to return a value of 0, -1, or 1, because this query always terminates after finding the first matching row.
For attached queries, the value is the number of rows you actually view, controlled by the next statement in the submenu. The count() aggregate determines the number of rows qualifying for a query (for details, see your query language reference guide).
During execution of a select loop or retrieve loop, rowcount is undefined (except after the execution of a query language statement). After completion of the loop, the value of rowcount equals the number of rows through which the program looped.
session
Specifies the integer value assigned to the session with the connect session session_id statement. If the session parameter is not specified, Ingres assigns a unique session id to the session. The default connection is assigned to -1. Additional sessions are assigned positive integer values.
transaction
Returns integer value:
1 = A transaction is open
0 = No transaction is open
This information cannot be current.
Description
The 4GL inquire_sql statement provides various types of information about the state of the application at run time, including:
• The number of rows affected by the last query statement
• The error number returned by the last query statement, and the associated error text
• Status information about the current session and open transactions
• The number and/or text of a message statement executed inside a database procedure
• Information about a database event that has been raised
Inquire_sql returns the error number and rowcount from the database statement that precedes it. For this reason, be careful not to insert another database statement (such as a commit) between this query statement and the inquire_sql statement itself. For example, the order of statements must be insert (or other database statement), inquire_sql, commit.
By default, inquire_sql returns generic rather than local error numbers. This can be overridden with the logical II_EMBED_SET. You can also use the dbmserror parameter to inquire_sql. Variables declared to receive generic error numbers must be of integer type; smallint type is sufficient for local error numbers.
Example
Assign information about a deletion to the global variables called rcount, errno, and txt:
delete from employee
where empnum = :empnum;
inquire_sql (rcount = rowcount, errno =
errorno, txt = errortext);
if errno != 0 then
message 'Error occurred on delete:' + txt;
sleep 3;
elseif rcount =0 then
message
'No records with that employee number';
sleep 3;
endif;
Check for the error number and display an error message:
insert into orders (order_no, cust_no, order_date, total, status)
values (order_no, cust_no, date('today'), null, status) ;
inquire_sql (errorno = errorno) ;
if errorno != 0 then
rollback ;
message 'An error occurred saving changes.'
with style = popup ;
resume ;
endif ;
commit ;
/* assertion: no errors above; */
/* commit changes */