`SQLNativeSql()` - Get native SQL text

SQLNativeSql() indicates how Db2 ODBC interprets vendor escape clauses. If the original SQL string that the application passes contains vendor escape clause sequences, Db2 ODBC passes a transformed SQL string to the data source. The SQL string is passed with vendor escape clauses that are either converted or discarded.

ODBC specifications for `SQLNativeSql()`

Table 1. `SQLNativeSql()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	No	No

Syntax

SQLRETURN   SQLNativeSql     (SQLHDBC           hdbc,
                              SQLCHAR      FAR  *szSqlStrIn,
                              SQLINTEGER        cbSqlStrIn,
                              SQLCHAR      FAR  *szSqlStr,
                              SQLINTEGER        cbSqlStrMax,
                              SQLINTEGER   FAR  *pcbSqlStr);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLNativeSql()` arguments
Data type	Argument	Use	Description
SQLHDBC	hdbc	input	Specifies the connection handle.
SQLCHAR *	szSqlStrIn	input	Points to a buffer that contains the input SQL string.
SQLINTEGER	cbSqlStrIn	input	Specifies the length, in bytes, of the buffer to which the szSqlStrIn argument points.
SQLCHAR *	szSqlStr	output	Points to buffer that returns the transformed output string.
SQLINTEGER	cbSqlStrMax	input	Specifies the size of the buffer to which the szSqlStr argument points.
SQLINTEGER *	pcbSqlStr	output	Points to a buffer that returns the total number of bytes (excluding the nul-terminator) that the complete output string requires. If this string requires a number of bytes that is greater than or equal to the value in the cbSqlStrMax argument, the output string is truncated to cbSqlStrMax - 1 bytes.

Usage

Call this function when you want to examine or display a transformed SQL string that is passed to the data source by Db2 ODBC. Translation (mapping) only occurs if the input SQL statement string contains vendor escape clause sequences.

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

Return codes

After you call SQLNativeSql(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. `SQLNativeSql()` SQLSTATEs
SQLSTATE	Description	Explanation
01004	Data truncated.	The output string is truncated because the buffer to which the szSqlStr argument points is not large enough to contain the entire SQL string. The argument pcbSqlStr contains the total length, in bytes, of the untruncated SQL string. (`SQLNativeSql()` returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
08003	Connection is closed.	The hdbc argument does not reference an open database connection.
37000	Invalid SQL syntax.	The input SQL string that the szSqlStrIn argument specifies contains a syntax error in the escape sequence.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY009	Invalid use of a null pointer.	This SQLSTATE is returned for one or more of the following reasons: The argument szSqlStrIn is a null pointer. The argument szSqlStr is a null pointer.
HY090	Invalid string or buffer length.	This SQLSTATE is returned for one or more of the following reasons: The argument cbSqlStrIn specifies a value that is less than 0 and not equal to SQL_NTS. The argument cbSqlStrMax specifies a value that is less than 0.

Example

The following example shows an application that uses SQLNativeSql() to print the final version of an SQL statement that contains vendor escape clauses.

Figure 1. An application that prints a translated vendor escape clause

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

SQLNativeSql() - Get native SQL text

ODBC specifications for SQLNativeSql()