SQLNativeSql

Purpose

SQLNativeSql() is used to show how DB2^® UDB CLI interprets vendor escape clauses. If the original SQL string passed in by the application contains vendor escape clause sequences, then DB2 UDB CLI returns the transformed SQL string that is seen by the data source (with vendor escape clauses either converted or discarded, as appropriate).

Syntax

SQLRETURN   SQLNativeSql     (SQLHDBC           ConnectionHandle,
                              SQLCHAR           *InStatementText,
                              SQLINTEGER        TextLength1,
                              SQLCHAR           *OutStatementText,
                              SQLINTEGER        BufferLength,
                              SQLINTEGER        *TextLength2Ptr);

Function arguments

Table 1. SQLNativeSql arguments
Data type	Argument	Use	Description
SQLCHAR *	InStatementText	Input	Input SQL string.
SQLCHAR *	OutStatementText	Output	Pointer to buffer for the transformed output string.
SQLHDBC	ConnectionHandle	Input	Connection handle.
SQLINTEGER *	TextLength2Ptr	Output	The total number of bytes available to return in OutStatementText. If the number of bytes available to return is greater than or equal to BufferLength, the output SQL string in OutStatementText is truncated to BufferLength - 1 bytes. The value SQL_NULL_DATA is returned if no output string is generated.
SQLINTEGER	BufferLength	Input	Size of buffer pointed by OutStatementText.
SQLINTEGER	TextLength1	Input	Length of InStatementText.

Usage

This function is called when the application wants to examine or display the transformed SQL string that is passed to the data source by DB2 UDB CLI. Translation (mapping) only occurs if the input SQL statement string contains vendor escape clause sequences.

There are no vendor escape sequences on iSeries; this procedure is provided for compatibility purposes. Also, note that this procedure can be used to evaluate an SQL string for syntax errors.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Error conditions

Table 2. SQLNativeSql SQLSTATEs
SQLSTATE	Description	Explanation
01004	Data truncated	The buffer OutStatementText is not large enough to contain the entire SQL string, so truncation occurred. The argument TextLength2Ptr contains the total length of the untruncated SQL string. (Function returns with SQL_SUCCESS_WITH_INFO.)
08003	Connection is closed	The ConnectionHandle does not reference an open database connection.
37000	SQL syntax that is not valid	The input SQL string in InStatementText contained a syntax error.
HY001	Memory allocation failure	DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	The argument InStatementText, OutStatementText, or TextLength2Ptr is a null pointer.
HY090	String or buffer length that is not valid	The argument TextLength1 is less than 0, but not equal to SQL_NTS. The argument BufferLength is less than 0.

Restrictions

None.

Example

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

/* From CLI sample native.c */
/* ... */
    SQLCHAR in_stmt[1024], out_stmt[1024] ;
    SQLSMALLINT pcPar ;
    SQLINTEGER indicator ;
/* ... */
    /* Prompt for a statement to prepare */
    printf("Enter an SQL statement: \n");
    gets((char *)in_stmt);
   
    /* prepare the statement */
    rc = SQLPrepare(hstmt, in_stmt, SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    SQLNumParams(hstmt, &pcPar);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    SQLNativeSql(hstmt, in_stmt, SQL_NTS, out_stmt, 1024, &indicator);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    if ( indicator == SQL_NULL_DATA ) printf( "Invalid statement\n" ) ;
    else {
       printf( "Input Statement: \n %s \n", in_stmt ) ;
       printf( "Output Statement: \n %s \n", in_stmt ) ;
       printf( "Number of Parameter Markers = %d\n", pcPar ) ;
    }
 
    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;

References