SQLGetSQLCA() returns the SQLCA (SQL communication
area) that is associated with preparing and executing an SQL statement,
fetching data, or closing a cursor. The SQLCA can return supplemental
information about the data that is obtained by SQLGetDiagRec().
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.
The following table
lists the data type, use, and description for each argument in this
function.
Table 2. SQLGetSQLCA() arguments
Data type
Argument
Use
Description
SQLHENV
henv
input
Specifies the environment handle.
SQLHDBC
hdbc
input
Specifies a connection handle.
SQLHSTMT
hstmt
input
Specifies a statement handle.
SQLCA *
pqlCA
output
Points to a buffer to receive the SQL communication
area.
Usage
The handles are used in the same
way as for the SQLGetDiagRec() function. To obtain
the SQLCA associated with different handle types, use the following
argument values:
For an environment handle: specify a valid environment handle,
set hdbc to SQL_NULL_HDBC and set hstmt and
SQL_NULL_HSTMT.
For a connection handle: specify a valid database connection handle
and set hstmt to SQL_NULL_HSTMT. The henv argument
is ignored.
For a statement handle: specify a valid statement handle. The henv and hdbc arguments
are ignored.
If diagnostic information that one Db2 ODBC function generates is not retrieved
before a function other than SQLGetDiagRec() is called
on the same handle, the diagnostic information for the previous function
call is lost. This information is lost regardless of whether the second Db2 ODBC function call generates
diagnostic information.
If a Db2 ODBC
function is called that does not result in interaction with the database
management system, then the SQLCA contains all zeros. Meaningful information
is returned in the SQLCA for the following functions:
SQLCancel()
SQLConnect(), SQLDisconnect()
SQLExecDirect(), SQLExecute()
SQLFetch()
SQLPrepare()
SQLEndTran()
SQLColumns()
SQLConnect()
SQLGetData (if a LOB column is involved)
SQLSetConnectAttr() (for SQL_ATTR_AUTOCOMMIT)
SQLStatistics()
SQLTables()
SQLColumnPrivileges()
SQLExtendedFetch()
SQLForeignKeys()
SQLMoreResults()
SQLPrimaryKeys()
SQLProcedureColumns()
SQLProcedures()
SQLTablePrivileges()
Return codes
After you call SQLGetSQLCA(),
it returns one of the following values:
SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE
Example
The following
example shows
an application that uses SQLGetSQLCA() to retrieve
diagnostic information from the SQLCA. Figure 1. An
application that retrieves diagnostic information
/******************************************************************/
/* Prepare a query and execute that query against a non-existent */
/* table. Then invoke SQLGetSQLCA to extract */
/* native SQLCA data structure. Note that this API is NOT */
/* defined within ODBC; it is unique to IBM CLI. */
/******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "sqlcli1.h"
void print_sqlca (SQLHENV, // prototype for print_sqlca
SQLHDBC,
SQLHSTMT);
int main( )
{
SQLHENV hEnv = SQL_NULL_HENV;
SQLHDBC hDbc = SQL_NULL_HDBC;
SQLHSTMT hStmt = SQL_NULL_HSTMT;
SQLRETURN rc = SQL_SUCCESS;
SQLINTEGER RETCODE = 0;
char *pDSN = "STLEC1";
SWORD cbCursor;
SDWORD cbValue1;
SDWORD cbValue2;
char employee [30];
int salary = 0;
int param_salary = 30000;
char *stmt = "SELECT NAME, SALARY FROM EMPLOYEES WHERE SALARY > ?";
(void) printf ("**** Entering CLIP11.\n\n");
/*****************************************************************/
/* Allocate environment handle */
/*****************************************************************/
RETCODE = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Allocate connection handle to DSN */
/*****************************************************************/
RETCODE = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
if( RETCODE != SQL_SUCCESS ) // Could not get a Connect Handle
goto dberror;
/*****************************************************************/
/* CONNECT TO data source (STLEC1) */
/*****************************************************************/
RETCODE = SQLConnect(hDbc, // Connect handle
(SQLCHAR *) pDSN, // DSN
SQL_NTS, // DSN is nul-terminated
NULL, // Null UID
0 ,
NULL, // Null Auth string
0);
if( RETCODE != SQL_SUCCESS ) // Connect failed
goto dberror;
/*****************************************************************/
/* Allocate statement handles */
/*****************************************************************/
rc = SQLAllocHandle(SQL_HANDLE_STMT, SQL_NULL_HANDLE, hDbc, &hStmt);
if (rc != SQL_SUCCESS)
goto exit;
/*****************************************************************/
/* Prepare the query for multiple execution within current */
/* transaction. Note that query is collapsed when transaction */
/* is committed or rolled back. */
/*****************************************************************/
rc = SQLPrepare (hStmt,
(SQLCHAR *) stmt,
strlen(stmt));
if (rc != SQL_SUCCESS)
{
(void) printf ("**** PREPARE OF QUERY FAILED.\n");
(void) print_sqlca (hStmt,
hDbc,
hEnv);
goto dberror;
}
rc = SQLBindCol (hStmt, // bind employee name
1,
SQL_C_CHAR,
employee,
sizeof(employee),
&cbValue1);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** BIND OF NAME FAILED.\n");
goto dberror;
}
rc = SQLBindCol (hStmt, // bind employee salary
2,
SQL_C_LONG,
&salary,
0,
&cbValue2);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** BIND OF SALARY FAILED.\n");
goto dberror;
}
/*****************************************************************/
/* Bind parameter to replace '?' in query. This has an initial */
/* value of 30000. */
/*****************************************************************/
rc = SQLBindParameter (hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER,
0,
0,
¶m_salary,
0,
NULL);
/*****************************************************************/
/* Execute prepared statement to generate answer set. */
/*****************************************************************/
rc = SQLExecute (hStmt);
if (rc != SQL_SUCCESS)
{
(void) printf ("**** EXECUTE OF QUERY FAILED.\n");
(void) print_sqlca (hStmt,
hDbc,
hEnv);
goto dberror;
}
/*****************************************************************/
/* Answer set is available -- Fetch rows and print employees */
/* and salary. */
/*****************************************************************/
(void) printf ("**** Employees whose salary exceeds %d follow.\n\n",
param_salary);
while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
{
(void) printf ("**** Employee Name %s with salary %d.\n",
employee,
salary);
}
/*****************************************************************/
/* Deallocate statement handles -- statement is no longer in a */
/* Prepared state. */
/*****************************************************************/
rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
/*****************************************************************/
/* DISCONNECT from data source */
/*****************************************************************/
RETCODE = SQLDisconnect(hDbc);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Deallocate connection handle */
/*****************************************************************/
RETCODE = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
if (RETCODE != SQL_SUCCESS)
goto dberror;
/*****************************************************************/
/* Free environment handle */
/*****************************************************************/
RETCODE = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
if (RETCODE == SQL_SUCCESS)
goto exit;
dberror:
RETCODE=12;
exit:
(void) printf ("**** Exiting CLIP11.\n\n");
return RETCODE;
}
/*****************************************************************/
/* print_sqlca invokes SQLGetSQLCA and prints the native SQLCA. */
/*****************************************************************/
void print_sqlca (SQLHENV hEnv ,
SQLHDBC hDbc ,
SQLHSTMT hStmt)
{
SQLRETURN rc = SQL_SUCCESS;
struct sqlca sqlca;
struct sqlca *pSQLCA = &sqlca;
int code ;
char state [6];
char errp [9];
char tok [40];
int count, len, start, end, i;
if ((rc = SQLGetSQLCA (hEnv ,
hDbc ,
hStmt,
pSQLCA)) != SQL_SUCCESS)
{
(void) printf ("**** SQLGetSQLCA failed Return Code =
goto exit;
}
code = (int) pSQLCA->sqlcode;
memcpy (state, pSQLCA->sqlstate, 5);
state [5] = '\0';
(void) printf ("**** sqlcode = %d, sqlstate = %s.\n", code, state);
memcpy (errp, pSQLCA->sqlerrp, 8);
errp [8] = '\0';
(void) printf ("**** sqlerrp = %s.\n", errp);
if (pSQLCA->sqlerrml == 0)
(void) printf ("**** No tokens.\n");
else
{
for (len = 0, count = 0; len < pSQLCA->sqlerrml; len = ++end)
{
start = end = len;
while ((pSQLCA->sqlerrmc [end] != 0XFF) &&;
(end < pSQLCA->sqlerrml))
end++;
if (start != end)
{
memcpy (tok, &pSQLCA->sqlerrmc[start],
(end-start));
tok [end-start+1] = '\0';
(void) printf ("**** Token # %d = %s.\n", count++, tok);
}
}
}
for (i = 0; i <= 5; i++)
(void) printf ("**** sqlerrd # %d = %d.\n", i+1, pSQLCA->sqlerrd_i]);
for (i = 0; i <= 10; i++)
(void) printf ("**** sqlwarn # %d = %c.\n", i+1, pSQLCA->sqlwarn_i]);
exit:
return;
}