16.8.3 SQLNativeSql, SQLNativeSqlW : Hitachi Advanced Database Application Development Guide

(1) Function

This ODBC function returns an SQL character string as modified by the HADB ODBC driver. This function does not execute the SQL statement.

To Page Top

(2) Format

For SQLNativeSql

SQLRETURN SQLNativeSql
(
  SQLHDBC         ConnectionHandle,         /* In */
  SQLCHAR       * InStatementText,          /* In */
  SQLINTEGER      TextLength1,              /* In */
  SQLCHAR       * OutStatementText,         /* Out */
  SQLINTEGER      BufferLength,             /* In */
  SQLINTEGER    * TextLength2Ptr            /* Out */
)

For SQLNativeSqlW

SQLRETURN SQLNativeSqlW
(
  SQLHDBC         ConnectionHandle,         /* In */
  SQLWCHAR      * InStatementText,          /* In */
  SQLINTEGER      TextLength1,              /* In */
  SQLWCHAR      * OutStatementText,         /* Out */
  SQLINTEGER      BufferLength,             /* In */
  SQLINTEGER    * TextLength2Ptr            /* Out */
)

To Page Top

(3) Arguments

ConnectionHandle

Specifies a connection handle.

InStatementText

Specifies the source SQL character string. Comments (bracketed between /* and */) are not permitted within an SQL character string, but specifications bracketed between /*>> and <<*/ (for example, index specifications) are permitted within an SQL character string.

TextLength1

Specifies the length^# of *InStatementText.

If the SQL character string specified in InStatementText ends with a null terminating character, you can specify SQL_NTS.

OutStatementText

Specifies a pointer to the buffer in which the SQL character string obtained after conversion is to be returned.

BufferLength

Specifies the length^# of *OutStatementText.

This length includes the length of the null terminating character. SQL_NTS cannot be specified.

TextLength2Ptr

Specifies a pointer to the buffer that returns the total valid length^# to be returned to *OutStatementText. The HADB ODBC driver returns the valid length^# of the SQL character string. This length does not include the null terminating character.

Important: If the length^# of the SQL character string stored here is greater than the value of BufferLength without the length^# of the null terminating character, the character string stored in OutStatementText is truncated to the length^# equivalent to BufferLength without the null terminating character, and then the null terminating character is added at the end.

#

The length must be in bytes for SQLNativeSql and in characters for SQLNativeSqlW.

To Page Top

(4) Return value

This ODBC function returns SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

To Page Top

(5) SQLSTATE

This ODBC function returns one of the following SQLSTATE values:

SQLSTATE	Description	Remarks	Returned
`01000`	General warning	--	N
`01004`	Character string data was right-truncated	The entire SQL character string could not be stored because the `*OutStatementText` buffer was too small (the information was truncated). The function returns `SQL_SUCCESS_WITH_INFO`.	Y
`01S51`	Code replacement occurred during conversion of character encoding	A character code that cannot be converted was detected and then replaced with the specified character. The function returns `SQL_SUCCESS_WITH_INFO`.	Y
`08003`	Connection does not exist	`ConnectionHandle` is not a connection status.	Y
`08S01`	Communication link failure	--	N
`22007`	Invalid datetime format	An invalid date, time, or time stamp value is specified in the escape clause of `*InStatementText`.	N
`24000`	Invalid cursor status	The cursor specified by the statement is located before or after the result set.	N
`5C002`	Character encoding conversion error	A character code that cannot be converted was detected.	Y
`5C051`	The text character string for an SQL statement exceeded 16,000,000 characters	Even if the text character string for an SQL statement consists of 16,000,000 or fewer characters, the result after character encoding conversion by the driver manager might exceed 16,000,000 characters. `SQLSTATE` is also returned in such a case.	Y
`HY000`	General error	--	N
`HY001`	Memory allocation error	--	N
`HY009`	Invalid use of null pointer	`*InStatementText` is a null pointer.	Y
`HY010`	Function sequence error	--	Y
`HY013`	Memory management error	The function call could not be processed because the memory object could not be accessed.	N
`HY090`	Invalid string or buffer length	The value specified in `TextLength1` is less than `0` and not `SQL_NTS`. `BufferLength` is less than `0`, but `OutStatementText` is not a null pointer. An invalid value (`SQL_NTS`) was specified in `BufferLength`.	Y
`HY109`	Invalid cursor position	The current row of the cursor has already been deleted or cannot be fetched.	N
`HYT01`	Connection timeout expired	A connection timeout occurred before the data source responded to the request. The connection timeout value can be specified by using `SQL_ATTR_CONNECTION_TIMEOUT` in `SQLSetConnectAttr` or `SQLSetConnectAttrW`.	N
`IM001`	Driver does not support this function	--	N

Legend:

Y: This SQLSTATE might be returned by the HADB ODBC driver.

N: This SQLSTATE is not returned by the HADB ODBC driver.

--: None

To Page Top

(6) Syntax rules for escape clause

This ODBC function converts any escape clauses in the specified SQL statement to a format that can be executed by HADB, and then returns the SQL statement. The following are the syntax rules for an escape clause:

escape-clause ::= escape-sequence-for-date-or-time-or-time-stamp
               | escape-sequence-for-escape-character-in-LIKE-predicate
               | escape-sequence-for-outer-join
               | scalar-function-escape-sequence
escape-sequence-for-date-or-time-or-time-stamp ::= date-escape-sequence
                                     | time-escape-sequence
                                     | time-stamp-escape-sequence
date-escape-sequence ::=
          escape-start-code d predefined-character-string-representation-of-date-data ^#1 escape-end-code
time-escape-sequence ::=
          escape-start-code t predefined-character-string-representation-of-time-data ^#2 escape-end-code
time-stamp-escape-sequence ::=
          escape-start-code ts predefined-character-string-representation-of-time-stamp-data ^#3escape-end-code
escape-sequence-for-escape-character-in-LIKE-predicate ::=
          escape-start-code escape escape-character escape-end-code
escape-sequence-for-outer-join ::= escape-start-code oj joined-table escape-end-code
scalar-function-escape-sequence ::= escape-start-code fn scalar-function escape-end-code
scalar-function ::= scalar-function-in-default-format ^#4
escape-start-code ::= '{'
escape-end-code ::= '}'

#1: Character string representation 'YYYY-MM-DD'
#2: Character string representation 'hh:mm:ss[.nn...n]' nn...n is the fractional seconds precision with a length of p digits. n is a number from 0 to 9, and p is 0, 3, 6, 9, or 12.
#3: Character string representation 'YYYY-MM-DD hh:mm:ss[.nn...n]' nn...n is the fractional seconds precision with a length of p digits. n is a number from 0 to 9, and p is 0, 3, 6, 9, or 12.
#4: For details about the scalar function in the default format, see Table 16‒7: Conversion formats of scalar functions whose default format differs from the HADB format.

Note that an escape clause cannot be specified in an underlined part. Because the ODBC driver does not perform syntax analysis on the underlined parts, these parts will remain the same after conversion and will be subject to syntax analysis by the HADB server.

The following keywords can be used in escape sequences. These keywords are not case sensitive.

d in a date escape sequence
t in a time escape sequence
ts in a time stamp escape sequence
escape in an escape sequence of an escape character of a LIKE predicate
oj in an outer join escape sequence
fn in a scalar function escape sequence

The escape clause entry rules are as follows:

The space can be used as the delimiter character in an escape clause.
The delimiter can be inserted following an escape start code, following a keyword, and before an escape end code.
You can specify multiple escape clauses in a single SQL statement.
The HADB ODBC driver converts the escape clauses in an SQL statement to a format that can be executed by HADB. Note that only the part of each escape clause that is enclosed in curly brackets is converted. The driver converts nothing outside the escape clauses.

The following table shows the escape clause conversion rules.

Table 16‒6: Escape clause conversion rules
Escape clause	Before conversion	After conversion
Date	`escape-start-code` `d` `predefined-character-string-representation-of-date-data escape-end-code`	`predefined-character-string-representation-of-date-data`
Time	`escape-start-code` `t` `predefined-character-string-representation-of-time-data escape-end-code`	`predefined-character-string-representation-of-time-data`
Time stamp	`escape-start-code` `ts` `predefined-character-string-representation-of-time-stamp-data escape-end-code`	`predefined-character-string-representation-of-time-stamp-data`
LIKE	`escape-start-code` `escapeescape-character escape-end-code`	`escape escape-character`
Outer join	`escape-start-code` `oj` `joined-table` `escape-end-code`	`joined-table`
Scalar function	`escape-start-code` `fn` `scalar-function` `escape-end-code`	`scalar-function-in-HADB-format`^#

#

The ODBC driver converts a scalar function in the default format to the HADB format.

The table below shows the conversion formats of scalar functions whose default format differs from the HADB server format.

In general, the ODBC driver does not check the number of arguments in a scalar function.

Table 16‒7: Conversion formats of scalar functions whose default format differs from the HADB format
Scalar functions	Format before conversion	Format after conversion (HADB format)
Mathematical function	`CEILING(number)`	`CEIL(number)`
	`LOG(float)`	`LN(float)`
	`RAND([number,` `number])`	`RANDOM([number,` `number])`
	`TRUNCATE(number[, places])`	`TRUNC(number[,` `places])`
String function	`CHAR(code)`	`CHR(code)`
	`LCASE(string)`	`LOWER(string)`
	`OCTET_LENGTH(string)`	`LENGTHB(string)`
	`SUBSTRING(string,` `start[,` `length])`	`SUBSTR(string,` `start[,` `length])`
	`UCASE(string)`	`UPPER(string)`
Time and date functions	`CURRENT_DATE()`	`CURRENT_DATE`
Time and date functions	`CURRENT_TIME()`	`CURRENT_TIME`

To Page Top