SQLNativeSql - Get native SQL text

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

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLNativeSqlW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

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 for i CLI. Translation (mapping) only occurs if the input SQL statement string contains vendor escape clause sequences.

There are no vendor escape sequences on the IBM® i 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 for i 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 ) ;