SQLNativeSql - Get native SQL text

SQLNativeSql - Get native SQL text

SQLNativeSql() is used to show how DB2^® UDB CLI interprets vendor escape clauses. If the original SQL string that is passed by the application contains vendor escape clause sequences, 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

SQLHDBC ConnectionHandle Input Connection handle.
SQLCHAR * InStatementText Input Input SQL string.
SQLINTEGER TextLength1 Input Length of InStatementText.
SQLCHAR * OutStatementText Output Pointer to buffer for the transformed output string.
SQLINTEGER BufferLength Input Size of buffer pointed by OutStatementText.
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.

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 the i5/OS^® operating system; this function is provided for compatibility purposes. Also, note that this function 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

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 ) ;
 
Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHDBC	ConnectionHandle	Input	Connection handle.
SQLCHAR *	InStatementText	Input	Input SQL string.
SQLINTEGER	TextLength1	Input	Length of InStatementText.
SQLCHAR *	OutStatementText	Output	Pointer to buffer for the transformed output string.
SQLINTEGER	BufferLength	Input	Size of buffer pointed by OutStatementText.
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.

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.