Call Level Interface Guide and Reference

SQLNativeSql - Get Native SQL Text

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

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

Syntax

SQLRETURN   SQLNativeSql     (
                SQLHDBC           ConnectionHandle,  /* hdbc */
                SQLCHAR      FAR  *InStatementText,  /* szSqlStrIn */
                SQLINTEGER        TextLength1,       /* cbSqlStrIn */
                SQLCHAR      FAR  *OutStatementText, /* szSqlStr */
                SQLINTEGER        BufferLength,      /* cbSqlStrMax */
                SQLINTEGER   FAR  *TextLength2Ptr);  /* pcbSqlStr */

Function Arguments

Table 126. 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 (excluding the null-terminator) 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.

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 (excluding the null-terminator) 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.

Usage

This function is called when the application wishes to examine or display the transformed SQL string that would be passed to the data source by DB2 CLI. Translation (mapping) would only occur if the input SQL statement string contains vendor escape clause sequence(s). For more information on vendor escape clause sequences, refer to Using Vendor Escape Clauses.

DB2 CLI can only detect vendor escape clause syntax errors; since DB2 CLI does not pass the transformed SQL string to the data source for preparation, syntax errors that are detected by the DBMS are not generated at this time. (The statement is not passed to the data source for preparation because the preparation may potentially cause the initiation of a transaction.)

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 127. SQLNativeSql SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. The buffer OutStatementText was 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 Invalid SQL syntax. The input SQL string in InStatementText contained a syntax error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY009 Invalid argument value. The argument InStatementText is a NULL pointer.
The argument OutStatementText is a NULL pointer.
HY090 Invalid string or buffer length. The argument TextLength1 was less than 0, but not equal to SQL_NTS.
The argument BufferLength was less than 0.

SQLSTATE	Description	Explanation
01004	Data truncated.	The buffer `OutStatementText` was 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	Invalid SQL syntax.	The input SQL string in `InStatementText` contained a syntax error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY009	Invalid argument value.	The argument `InStatementText` is a NULL pointer. The argument `OutStatementText` is a NULL pointer.
HY090	Invalid string or buffer length.	The argument `TextLength1` was less than 0, but not equal to SQL_NTS. The argument `BufferLength` was less than 0.

Restrictions

None.

Example

/* 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

Using Vendor Escape Clauses

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]