8.2.26 nativeSQL(String sql) : Hitachi Advanced Database Application Development Guide

(1) Function

This method converts escape clauses in a specified SQL statement to a format that can be executed by HADB.

(2) Format

public synchronized String nativeSQL(String sql) throws SQLException

To Page Top

(3) Arguments

String sql: Specifies an SQL statement.

To Page Top

(4) Return value

The method returns an SQL statement that can be executed by HADB.

If null is specified for sql, the method returns null. If an empty string is specified for sql, the method returns an empty string.

To Page Top

(5) Exceptions

The JDBC driver throws an SQLException in the following cases:

The Connection object is closed.
The format of an escape clause in the specified SQL statement is invalid for the following reason:
- { and a keyword are specified, but } is missing.
The specified SQL statement exceeds 16,000,000 characters.

To Page Top

(6) Syntax rules for escape clause

This method 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 default-character-string-representation-of-date-data^#1 escape-end-code
time-escape-sequence ::=
          escape-start-code t default-character-string-representation-of-time-data^#2 escape-end-code
time-stamp-escape-sequence ::=
          escape-start-code ts default-character-string-representation-of-time-stamp-data^#3 escape-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[.f...]'
#3: Character string representation 'YYYY-MM-DD hh:mm:ss[.f...]'
#4: For details about the scalar function in the default format, see 7.8 Scalar functions that can be specified in the escape clause.

Note that an escape clause cannot be specified in an underlined part. Because the JDBC driver does not perform syntax analysis on the underlined parts, they 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 JDBC 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 8‒5: Escape clause conversion rules
Escape clause	Before conversion	After conversion
Date	`escape-start-code` `d` `default-character-string-representation-of-date-data` `escape-end-code`	`default-character-string-representation-of-date-data`
Time	`escape-start-code` `t` `default-character-string-representation-of-time-data escape-end-code`	`default-character-string-representation-of-time-data`
Time stamp	`escape-start-code` `ts` `default-character-string-representation-of-time-stamp-data` `escape-end-code`	`default-character-string-representation-of-time-stamp-data`
LIKE	`escape-start-code` `escape` `escape-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 JDBC 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 JDBC driver does not check the number of arguments in scalar functions.

Table 8‒6: Conversion formats of scalar functions whose default format differs from the HADB server format
Scalar functions	Format before conversion	Format after conversion (HADB format)
Mathematical function	`CEILING(number)`	`CEIL(number)`
	`LOG(float)`	`LN(float)`
	`LOG10(float)`	`LOG(10,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	`CURDATE()`	`CURRENT_DATE`
	`CURRENT_DATE()`	`CURRENT_DATE`
	`CURRENT_TIME()`	`CURRENT_TIME`
	`CURRENT_TIMESTAMP()`	`CURRENT_TIMESTAMP`
	`CURTIME()`	`CURRENT_TIME`
	`NOW()`	`CURRENT_TIMESTAMP`
System function	`USER()`	`CURRENT_USER`

To Page Top