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()
| 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.
| 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
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.
| 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:
|
| HY090 | Invalid string or buffer length. | This SQLSTATE is returned for one or more of the following reasons:
|
Example
SQLNativeSql() to return the content of an SQL string before and after the string was modified by the ODBC driver. /* ... */
/* Get an environment handle (henv) */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_HANDLE_NULL, &henv );
/* Get a connection handle (hdbc) */
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc );
/* Call SQLNativeSql to compare the original SQL string */
/* with the modified SQL string */
rc = SQLNativeSql(hdbc, in_stmt, SQL_NTS, out_stmt, 1024, &indicator);
if (rc !=SQL_SUCCESS) {
...
}
else {
printf("Input Statement: \n stmt);
printf("Output Statement: \n stmt);
}
...
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);