Status Information

The following functions enable an embedded SQL application program to obtain status information:

session_priv
Returns session privilege information.
dbmsinfo
Returns information about the current session.
inquire_sql
Returns information about the last database statement that was executed.
SQLCA (SQL Communications Area)
Returns status and error information about the last SQL statement that was executed.
SQLCODE and SQLSTATE
These are the stand-alone variables in which the DBMS returns status information about the last SQL statement that was executed.

session_priv Function

The session_priv function determines whether the current session has a subject privilege, or can request it. The input is any single subject privilege name, such as operator, specified as a text string in upper or lower case.

The following values are returned:

Y
Indicates session has privilege.
N
Indicates session does not have the privilege.
R
Indicates the session can request the privilege and make it active.

The following example checks whether the current session has auditor privilege:

SELECT session_priv('AUDITOR');

dbmsinfo Function

A dbmsinfo function is a SQL function that returns a string containing information about the current session. Use this statement in a terminal monitor or in an embedded SQL application.

The dbmsinfo function is used in a select statement as follows:

select dbmsinfo ('request_name')

For example, to find out which runtime of Ingres you are using, enter:

select dbmsinfo('_version');

Use the dbmsinfo function in where clauses in select statements. For example:

exec sql select dept from employee
where ename=dbmsinfo('username');

Valid Request Names for dbmsinfo Function

The following table lists valid request_names that can be used with the dbmsinfo function:

Request Name	Description
autocommit_state	Returns 1 if autocommit is on and 0 if autocommit is off.
_bintim	Returns the current time and date in an internal format, represented as the number of seconds since January 1, 1970 00:00:00 GMT.
_bio_cnt	Returns the number of I/Os to and from the front-end client (application) that created your session.
collation	Returns the collating sequence defined for the database associated with the current session. This returns blanks if the database is using the collating sequence of the machine's native character set, such as ASCII or EBCDIC.
connect_time_limit	Returns the session connect time limit or -1 if there is no connect time limit.
create_procedure	Returns Y if the session has create_procedure privileges in the database or N if the session does not.
create_table	Returns Y if the session has create_table privileges in the database or N if the session does not.
_cpu_ms	Returns the CPU time for your session in milliseconds.
current_priv_mask	Returns the decimal number representing a mask of internal privilege bits currently enabled for the user.
cursor_default_mode	Returns the default mode for the cursor.
cursor_update_mode	Returns the mode of the current user.
database	Returns the database name.
datatype_major_level	Returns -2147483648 unless the user datatypes are in use.
datatype_minor_level	Returns 0 unless the user datatypes are in use.
dba	Returns the user name of the database owner.
db_admin	Returns Y if the session has db_admin privileges, and N if the session does not have db_admin privileges.
db_cluster_node	Returns the machine you are connected to. Valid even if not clustered.
db_count	Returns the number of distinct databases opened.
db_real_user_case	Returns lower, upper, or mixed.
dbms_bio	Returns the cumulative non-disk I/O's performed by the server hosting session.
dbms_cpu	Returns the cumulative CPU time for the DBMS Server, in milliseconds, for all connected sessions.
dbms_dio	Returns the cumulative disk I/O's performed by the server hosting session.
db_delimited_case	Returns LOWER if delimited identifiers are translated to lower case, UPPER if delimited identifiers are translated to upper case, or MIXED if the case of delimited identifiers is not translated.
db_name_case	Returns LOWER if regular identifiers are translated to lower case or UPPER if regular identifiers are translated to upper case.
db_privileges	Returns a decimal integer which represents a bit mask of "Subject" privileges.
db_tran_id	Returns the 64 bit internal transaction ID as two decimal numbers.
_dio_cnt	Returns the number of disk I/O requests for your session.
_et_sec	Returns the elapsed time since the start of your session, in seconds.
flatten_aggregate	Returns Y if the DBMS Server is configured to flatten queries involving aggregate subselects; otherwise, returns N. (Query flattening options are specified when the DBMS Server is started.)
flatten_singleton	Returns Y if the DBMS Server is configured to flatten queries involving singleton subselects; otherwise, returns N. (Query flattening options are specified when the DBMS Server is started.)
group	Returns the group identifier of the session or blanks if no group identifier is in effect.
idle_time_limit	Returns the session idle time limit or -1 if there is no idle time limit.
ima_server	Equivalent to IMA registration exp.gwf.gwm.glb.this_server, which returns the listen address of the attached server.
ima_session	Returns the internal session ID in decimal format.
ima_vnode	Equivalent to IMA registration exp.gwf.gwm.glb.def_vnode configuration value if set for the connected server. If not set, defaults to the local host name.
initial_user	Returns the user identifier in effect at the start of the session.
language	Returns the language used in the current session to display messages and prompts.
lockmode	Returns Y if the user possesses lockmode database privileges or N if the user lacks these privileges.
lp64	Returns Y if 64 bit pointers are in use, or N if 32 bit addresses are used.
maxconnect	Returns the current connect time limit, as set by the set maxconnect statement, or the initial value if no connect time limit has been set.
maxcost	Returns the value specified in the last set maxcost statement. If no previous set maxcost statement was issued or if set nomaxcost was specified last, this returns the same value as the request name query_io_limit.
maxcpu	Returns the value specified in the last set maxcpu statement. If no previous set maxcpu statement was issued or if set nomaxcpu was specified last, this returns the same value as the request name query_io_limit.
maxidle	Returns the current idle time limit, as set with the set maxidle statement, or the initial value if no idle time limit has been set.
maxio	Returns the value specified in the last set maxio statement. If no previous set maxio statement was issued or if set nomaxio was specified last, this returns the same value as the request name query_io_limit.
maxquery	Same as maxio.
maxrow	Returns the value specified in the last set maxrow statement. If no previous set maxrow statement was issued or if set nomaxrow was specified last, this returns the same value as the request name query_row_limit.
maxpage	Returns the value specified in the last set maxpage statement. If no previous set maxpage statement was issued or if set nomaxpage was specified last, this returns the same value as the request name query_io_limit.
max_page_size	Returns the size of the largest enable page cache in bytes.
max_priv_mask	Returns the decimal number representing a mask of internal privilege bits for which privileges the user might possess if all his/her privileges were enabled.
max_tup_len	Returns the max width for a non-segmented tuple. This depends on max_page_size.
on_error_state	Returns the current setting for transaction error handling: rollback transaction or rollback statement. To set transaction error handling, use the set session with on_error statement.
page_size_2k	Returns Y if this size cache is enabled.
page_size_4k	Returns Y if this size cache is enabled.
page_size_8k	Returns Y if this size cache is enabled.
page_size_16k	Returns Y if this size cache is enabled.
page_size_32k	Returns Y if this size cache is enabled.
page_size_64k	Returns Y if this size cache is enabled.
pagetype_v1	Returns Y if this page type is supported.
pagetype_v2	Returns Y if this page type is supported.
pagetype_v3	Returns Y if this page type is supported.
_pfault_cnt	Returns the number of page faults for the server.
query_cost_limit	Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_cpu_limit	Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_flatten	Returns Y if the query flattening is in effect or N if the query flattening is not in effect.
query_io_limit	Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_language	Returns sql or quel.
query_page_limit	Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_row_limit	Returns the session value for query_row_limit or -1 if no limit is defined for the session.
role	Returns the role identifier of the session or blanks if no role identifier is in effect.
security_audit_log	Returns the name of the current security auditing log file if it is enabled and the user has maintain_audit privileges, otherwise, remains blank.
security_audit_state	Returns the current Ingres security audit state. The following values are returned: (blank) – Ingres security auditing is not available STOP – Security auditing is stopped SUSPEND – Security auditing is suspended ACTIVE – Security auditing is active
security_priv	Returns Y if the effective user has the security privilege or N if the effective user does not have the security privilege.
select_syscat	Returns Y if the session has select_syscat privilege or N if the session does not have select_syscat privilege.
session_id	Returns the internal session identifier in hexadecimal.
session_priority	Returns the current session priority.
session_priority_limit	Returns the highest session priority that can be set, or an empty string if no session priority limit applies.
session_user	Returns the current effective user ID of the session.
system_user	Returns the system user ID.
table_statistics	Returns Y if the session has table_statistics privilege, or N if the session does not have table_statistics privilege.
terminal	Returns the terminal address.
transaction_state	Returns 1 if currently in a transaction and returns 0 if not currently in a transaction.
tup_len_2k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_4k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_8k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_16k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_32k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_64k	Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
ucollation	Returns Unicode collation. The default is "udefault."
unicode_level	Returns the Unicode level for this database. Returns 0 if there is no Unicode level, otherwise returns 1 if the database is created with -n flag.
unicode_normalization	Returns NFC if the database supports the NFC normalization form, and returns NFD if the database supports the NFD normalization form.
update_rowcnt	Returns qualified if inquire_sql(rowcount) returns the number of rows that qualified for change by the last query, or changed if inquire_sql(rowcount) returns the number of rows that were actually changed by the last query.
update_syscat	Returns Y if the effective user is allowed to update system catalogs or N if the effective user is not allowed to update system catalogs.
username	Returns the user name of the user currently running Ingres.
_version	Returns the Ingres runtime number.

inquire_sql Function

The inquire_sql function returns information about the status of the last SQL database statement issued by an application.

Information Provided by the inquire_sql Function

The inquire_sql function can provide the following information on the occurrence of an error:

inquire_sql can return the error number and the complete error text.
inquire_sql can retrieve the message number and text from a message statement that was executed inside a database procedure.
Determine if your session is returning local or generic errors, if a transaction is open, or what session is currently active (useful in multiple-session applications).

The inquire_sql function does not return status information about forms statements. To obtain information about the results of forms statements, use the inquire_frs statement.

For a complete list and description of available status information, see Inquire_sql in the chapter "Using SQL Statements."

SQL Communications Area (SQLCA)

The SQL Communications Area (SQLCA) consists of a number of variables that contain error and status information accessible by the program. This information reflects only the status of executed embedded SQL database statements. Forms statements do not affect these variables. Because each embedded SQL statement has the potential to change values in the SQLCA, the application must perform any checking and consequent processing required to deal with a status condition immediately after the statement in question. If it does not, the next executed SQL statement changes the status information in the variables.

Each host language implements the SQLCA structure differently. For instructions on how to include the SQLCA in your applications, see the Embedded SQL Companion Guide.

Variables that Compose SQLCA

The following list describes the variables that compose the SQLCA (not all of the variables are currently used):

SQLCA Variable	Description
sqlcaid	An 8-byte character string variable initialized to SQLCA. This value does not change.
sqlcabc	A 4-byte integer variable initialized to the length in bytes of the SQLCA, 136. This value also does not change.
sqlcode	A 4-byte integer variable indicating the SQL return code. Its value falls into one of three categories:
	= 0—The statement executed successfully (though there have been warning messages: check sqlwarn0). < 0—An error occurred. The value of sqlcode is the negative value of the error number returned to errorno. A negative value sets the sqlerror condition of the whenever statement. > 0—The statement executed successfully but an exception condition occurred. The following values are returned: 100- Indicates that no rows were processed by a delete, fetch, insert, select, update, modify, copy, create index, or create as select statement. This value (100) sets the not found condition of the whenever statement. 700- Indicates that a message statement in a database procedure has just executed, setting the sqlmessage condition of the whenever statement. 710- Indicates that a database event was raised.
sqlerrm	A varying length character string variable with an initial 2-byte count and a 70-byte long buffer. This variable is used for error messages. When an error occurs for a database statement, the leading 70 characters of the error message are assigned to this variable. If the message contained within the variable is less than 70 characters, the variable contains the complete error message. Otherwise, the variable contains a truncated error message. To retrieve the full error message, use the inquire_sql statement with the errortext object. If no errors occur, sqlerrm contains blanks. For some languages this variable is divided into two other variables: sqlerrml—A 2-byte integer count indicating how many characters are in the buffer. sqlerrmc—A 70-byte fixed length character string buffer.
sqlerrp	8-byte character string variable, currently unused.
sqlerrd	An array of six 4-byte integers. Currently only sqlerrd(1) and sqlerrd(3) are in use. Sqlerrd(1) is used to store error numbers returned by the server. For more information about the values returned in sqlerrd(1), see Types of Errors. Sqlerrd(3) indicates the number of rows processed by a delete, fetch, insert, select, update, copy, modify, create index, or create as select statement. All other database statements reset this variable to zero. Some host languages start array subscripts at 0. In these languages (C, BASIC), use the subscript, 2, to select the third array element.
sqlwarn0- sqlwarn7	A set of eight 1-byte character variables that denote warnings when set to W. The default values are blanks. sqlwarn0—If set to W, at least one other sqlwarn contains a W. When W is set, the sqlwarning condition of the whenever statement is set. sqlwarn1—Set to W on truncation of a character string assignment from the database into a hostvariable. If an indicator variable is associated with the host variable, the indicator variable is set to the original length of the character string. sqlwarn2—Set to W on elimination of nulls from aggregates. sqlwarn3—Set to W when mismatching number of result columns and result host variables in a fetch or select statement. sqlwarn4—Set to W when preparing (prepare) an update or delete statement without a where clause. sqlwarn5—Currently unused. sqlwarn6—Set to W when the error returned in sqlcode caused the abnormal termination of an open transaction. sqlwarn7—Currently unused.
sqlext	An 8-byte character string variable not currently in use.

SQLCODE and SQLSTATE

SQLCODE and SQLSTATE are variables in which the DBMS returns ANSI/ISO Entry-92-compliant status codes indicating the results of the last SQL statement that was executed.

SQLCODE Variable

SQLCODE is an integer variable in which the DBMS returns the status of the last SQL statement executed. For details about the requirements for declaring the SQLCODE variable in embedded programs, see the Embedded SQL Companion Guide.

Note: The ANSI Entry SQL-92 specification describes SQLCODE as a depreciated feature, and recommends using the SQLSTATE variable, described in the following section.

Values Returned by SQLCODE

The values returned in the standalone SQLCODE variable are the same as those returned in the sqlcode member of the SQLCA structure. The value of SQLCODE is meaningful only in the context of a session.

The values returned in SQLCODE are listed in the following table:

Value	Description
0	Successful completion.
+100	No rows were processed by a delete, fetch, insert, select, update, modify, copy, create index, or create as...select statement. This value (+100) sets the not found condition of the whenever statement.
+700	A message statement in a database procedure has just executed, setting the sqlmessage condition of the whenever statement.
+710	A database event was raised.
Negative Value	An error occurred. The value of SQLCODE is the negative value of the error number returned to errorno. (For information on errorno, see Error Information Obtained Using Inquire Statements.) A negative value sets the sqlerror condition of the whenever statement.

SQLSTATE Variable

The SQLSTATE variable is a 5-character string in which the DBMS Server returns the status of the last SQL statement executed. The values returned in SQLSTATE are specified in the ANSI/ISO Entry SQL-92 standard. For details about the requirements for declaring the SQLSTATE variable in embedded programs, see the Embedded SQL Companion Guide.

Note: If queries are executed while connected (through an Enterprise Access product) to a DBMS server that doesn't support SQLSTATE, SQLSTATE is set to 5000K (meaning SQLSTATE not available). This result does not necessarily mean that an error occurred. To check the results of the query, use one of the other error-checking methods.

SQLSTATE is not available within database procedures; however, a routine that directly executes a database procedure can check SQLSTATE to determine the result of the procedure call.

The following example illustrates the use of SQLSTATE in an embedded program:

exec sql begin declare section;
character SQLSTATE(5)
exec sql end declare section;\
exec sql connect mydatabase;
if SQLSTATE <> "00000" print 'Error on connection!'

For a list mapping Ingres generic errors to SQLSTATE values, see the appendix "Generic Error Codes and SQLSTATE."