Call Level Interface Guide and Reference

SQLGetSQLCA - Get SQLCA Data Structure

Purpose

Specification:

DB2 CLI 2.1

SQLGetSQLCA() is used to return the SQLCA associated with the preparation, and execution of an SQL statement, fetching of data, or the closing of a cursor. The SQLCA may return information in addition to what is available using SQLError().

Note:

SQLGetSQLCA() must not be used as a replacement for SQLGetDiagField() and SQLGetDiagRec().

For a detailed description of the SQLCA structure, refer to the SQLCA appendix in theSQL Reference

An SQLCA is not available if a function is processed strictly on the application side, such as allocating a statement handle. In this case, an empty SQLCA is returned with all values set to zero.

Syntax

SQLRETURN   SQLGetSQLCA      (SQLHENV           EnvironmentHandle, /* henv */
                              SQLHDBC           ConnectionHandle,  /* hdbc */
                              SQLHSTMT          StatementHandle,   /* hstmt */
                              struct sqlca FAR  *SqlcaPtr);        /* pSqlca */

Function Arguments

Table 117. SQLGetSQLCA Arguments

Data Type	Argument	Use	Description
SQLHENV	EnvironmentHandle	input	Environment Handle. To obtain the SQLCA associated with an environment, pass a valid environment handle. Set ConnectionHandle and StatementHandle to SQL_NULL_HDBC and SQL_NULL_HSTMT respectively.
SQLHDBC	ConnectionHandle	input	Connection Handle. To obtain the SQLCA associated with a connection, pass a valid database connection handle, and set StatementHandle to SQL_NULL_HSTMT. The EnvironmentHandle argument is ignored.
SQLHSTMT	StatementHandle	input	Statement Handle. To obtain the SQLCA associated with a statement, pass a valid statement handle. The EnvironmentHandle and ConnectionHandle arguments are ignored.
SQLCA *	sqlCA	output	SQL Communication Area

Usage

The handles are used in the same way as for the SQLError() function. To obtain the SQLCA associated with:

• An environment, pass a valid environment handle. Set ConnectionHandle and StatementHandle to SQL_NULL_HDBC and SQL_NULL_HSTMT respectively.

• A connection, pass a valid database connection handle, and set StatementHandle to SQL_NULL_HSTMT. The EnvironmentHandle argument is ignored.

• A statement, pass a valid statement handle. The EnvironmentHandle and ConnectionHandle arguments are ignored.

If diagnostic information generated by one DB2 CLI function is not retrieved before a function other than SQLError() is called with the same handle, the information for the previous function call is lost. This is true whether or not diagnostic information is generated for the second DB2 CLI function call.

If a DB2 CLI function is called that does not result in interaction with the DBMS, then the SQLCA will contain all zeroes. Meaningful information will usually be returned for the following functions:

• SQLBrowseConnect()
• SQLCancel(),
• SQLCloseCursor()
• SQLColAttribute()
• SQLColumnPrivileges()
• SQLColumns()
• SQLConnect(), SQLDisconnect()
• SQLCopyDesc()
• SQLDataSources()
• SQLDescribeCol()
• SQLDescribeParam()
• SQLEndTran()
• SQLExecDirect(), SQLExecute()
• SQLFetch()
• SQLFetchScroll()
• SQLForeignKeys()
• SQLFreeHandle()
• SQLGetData() (if LOB column is involved)
• SQLMoreResults()
• SQLPrepare()
• SQLPrimaryKeys()
• SQLProcedureColumns()
• SQLProcedures()
• SQLRowCount()
• SQLSetConnectAttr() (for SQL_ATTR_AUTOCOMMIT, and SQL_ATTR_DB2EXPLAIN)
• SQLStatistics()
• SQLTables()
• SQLTablePrivileges()
• SQLTransact()

If the database connection is to a DB2 Universal Database Version 2 server or later, there are two fields in the SQLCA that may be of particular interest:

• The SQLERRD(3) field (example, sqlca.errd[2]):

  • After PREPARE, contains an estimate of the number of rows that will be returned to the user when the statement is executed. An application can inform the user of this information to help assess whether the appropriate query has been issued.

  • After INSERT, DELETE, UPDATE, contains the actual number of rows affected.

  • After Compound SQL processing, contains an accumulation of all sub-statement rows affected by INSERT, UPDATE or DELETE statements.

• The SQLERRD(4) field (example, sqlca.errd[3]):

  • After a PREPARE, contains a relative cost estimate of the resources required to process the statement.

    This is the number that is compared to the DB2ESTIMATE configuration keyword as described in Configuration Keywords, and the SQL_ATTR_DB2ESTIMATE connection attribute as described in the function description, SQLSetConnectAttr - Set Connection Attributes.

  • After Compound SQL processing, contains a count of the number of successful sub-statements.

Return Codes

• SQL_SUCCESS
• SQL_ERROR
• SQL_INVALID_HANDLE

Diagnostics

None.

Restrictions

None.

Example

/* From CLI sample getsqlca.c */
/* ... */
    /* execute the SQL statement in "sqlstr"    */
 
    rc = SQLPrepare(hstmt, sqlstr, SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    printf(" Relative Cost=[%ld] Estimated # rows=[%ld]\n"
           " Continue with execution(Y or N)?\n",
           sqlca.sqlerrd[3], sqlca.sqlerrd[2]);
    gets((char *)prompt);
    if (prompt[0] == 'n' || prompt[0] =='N')
        return(0);
 
    if ( rc != SQL_SUCCESS )
       CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLExecute(hstmt);
 

References

• SQLGetDiagRec - Get Multiple Fields Settings of Diagnostic Record

[ DB2 List of Books | Search the DB2 Books ]