11.3.2 Details about available SQL statements

Table 11-5 shows details about the SQL statements that can be used for remote database access (for details about these SQL statements, see the HiRDB Version 8 SQL Reference manual).

Table 11-5 Details about SQL statements usable for remote database access

CategorySQL statement formatUsable at distributed server
HXORS
Variable{:embeddedvariable [:indicatorvariable] | ? parameter}Y9Y9Y9Y9Y9
Table nameRD-node-name.authorization-identifier.tableidentifierY1Y1Y1Y1Y1
[authorization-identifier.] table-aliasY1Y1Y1Y1Y1
[authorization-identifier.] table-identifierY2Y2Y2Y2Y2
Table specification{[authorization-identifier.] table-identifier |correlation-name}Y3Y3YY3Y3
Column specification[table-specification.] column-nameYYYYY
[table-specification.] repetition-column-name[subscript]Y[Figure][Figure][Figure][Figure]
Value specification{literal | variable | USERYYYYY4
| CURRENT DATE
| CURRENT TIME
YY[Figure][Figure][Figure]
| [statement-label.] SQL-variable-name |[[authorization-identifier.] routine-identifier.] SQL-parameter-name}[Figure][Figure][Figure][Figure][Figure]
Term specification{column-specificationYYYYY
| [statement-label.] SQL-variable-name | [[authorization-identifier.] routine-identifier.] SQL-parameter-name}[Figure][Figure][Figure][Figure][Figure]
Set functionAVG, SUM, MAX, MIN, COUNTYYYYY
Scalar functionVALUEYY[Figure][Figure][Figure]
DATE, TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYS,YY[Figure][Figure][Figure]
DECIMAL, DIGITS, FLOAT, INTEGER, CHARACTER, HEX,YY[Figure][Figure][Figure]
LENGTH, SUBSTRYY5Y5[Figure][Figure]
Labeled duration(value-expression) {YEAR [S] | MONTH [S] | DAY [S] }YY[Figure][Figure][Figure]
Primary{(value-expression) | column-specification | value-specification | set-function | scalar-functionYYYYY4
| labeled-duration} YY[Figure][Figure][Figure]
Value expression{[{+ | -}] primary | value-expression {+ | - | * | / | ||} primary}YYYY4Y4
Comparison operators{= | < > | < | < = | > | > =}YYYYY4
Predicate{value- expression IS [NOT] NULLYYYYY4
| value- expression[NOT] LIKE value-specificationYYYYY4
| value-expression [NOT] BETWEEN value-expression AND value-expressionYYYYY4
| value-expression [NOT] IN {(value-specification [, value-specification] ... ) | subquery}YYYYY4
| value-expression comparison-operator value-expressionYYYYY
| value-expression comparison-operator subqueryYYY[Figure][Figure]
| value-expression comparison-operator{ ANY | ALL | SOME] subqueryYYY[Figure][Figure]
| EXISTS subqueryYYY[Figure][Figure]
| term-specification[NOT] XLIKE-pattern-characterY[Figure][Figure][Figure][Figure]
| ARRAY (repetition-column-name[,repetition-column-name]...) [ANY](retrieval-condition)Y[Figure][Figure][Figure][Figure]
Search condition{ [NOT] {(search-condition) | predicate}YYYYY4
| search-condition OR {(search-condition) | predicate}YYYYY
| search-condition AND {(search-condition) | predicate}}YYYYY
Selection expressionvalue-expression (other than a variable) | table-specification.*YYYYY
| [table-specification.] ROW}[Figure][Figure][Figure][Figure][Figure]
Query specification{SELECT [{ ALL | DISTINCT}]YYYY[Figure]
{* | selection-expression [. selection-expression] }YYYYY
FROM table-name [. table-name] ...YYYYY
[WHERE search-condition]YYYYY
[GROUP BY column -specification]YYYYY
[HAVING search-condition]YYYYY
Query expression{query-specification | (query-expression) | query-expression UNION [ALL] {query-specification | (query-expression)}}YYY[Figure][Figure]
Static cursor declarationDECLARE cursor-name CURSOR FOR query-expressionYYYY6Y6
[ORDER BY
{columnspecification | sort-item-specification-number}
[{ASC | DESC}]
[, {column-specification | sort-item-specification-number} [{ASC | DESC}]] ...]
YY9YY7Y7
[{WITH {SHARE | EXCLUSIVE} LOCKYY[Figure][Figure][Figure]
| WITHOUT LOCK [{WAIT | NOWAIT}]}]YY[Figure]Y[Figure]
[WITH ROLLBACK]YY[Figure]Y[Figure]
| NO WAIT}]Y[Figure][Figure][Figure][Figure]
[FOR {UPDATE [OF column-name [, column-name] ...]YYYYY
| READ ONLY}]YY[Figure][Figure][Figure]
Dynamic cursor declarationDECLARE cursor-name CURSOR FOR SQL-statement-identifier-indicating-dynamic-SELECT-statementYYYYY
Dynamic SELECT statement
Format 1
query-expressionYYYY6Y6
[ORDER BY {column-specification|sort-item-
specification-number}
[{ASC | DESC}]
[, {column-specification | sort-item-specification-number}
[{ASC | DESC}]] ...]
YY7YY7Y7
[{WITH {SHARE | EXCLUSIVE} LOCKYY[Figure][Figure][Figure]
| WITHOUT LOCK [{WAIT | NOWAIT}]}]YY[Figure]Y[Figure]
[WITH ROLLBACK]YY[Figure]Y[Figure]
| NO WAIT}]Y[Figure][Figure][Figure][Figure]
[FOR UPDATE]YYYYY
Dynamic SELECT statement
Format 2
SELECT {{column-name
|repetition-column[[subscript]]
|column-name..attribute-name[..attribute-name]..}
[,{{column-name
|repetition-column[[subscript]]
|column-name..attribute-name[..attribute-name]..}]..
|*} FROM LIST list-name
[lock-option]
[Figure][Figure][Figure][Figure][Figure]
ASSIGN LIST statement
Format 1
ASSIGN LIST list-name
FROM ([authorization-identifier.]table-identifier)
[WHERE search-condition]
[WITHOUT LOCK [{WAIT|NOWAIT}]]
[WITH ROLLBACK|NO WAIT]
[Figure][Figure][Figure][Figure][Figure]
ASSIGN LIST statement
Format 2
ASSIGN LIST list-name
FROM list-name-1
[{{AND|OR|AND NOT|ANDNOT}list-name-2
|FOR ALTERLIST]
[Figure][Figure][Figure][Figure][Figure]
DROP LIST statementDROP {LIST list-name|ALL LIST}[Figure][Figure][Figure][Figure][Figure]
OPEN statement Format 1OPEN cursor-name [USING:embeddedvariable
[, embeddedvariable] ...]
YYYYY
OPEN statement Format 2OPEN cursor-name USING DESCRIPTOR [:] SQL-data-area-nameYYYYY
CLOSE statementCLOSE cursor-nameYYYYY
FETCH statement
Format 1
FETCH cursor-name INTO variable [, variable] ...YYYYY
FETCH statement
Format 2
FETCH cursor-name USING DESCRIPTOR [:]SQL-data-area-nameYYYYY
BY variable [ROWS][Figure][Figure][Figure][Figure][Figure]
FETCH statement Format 3FETCH cursor-name INTO array-variable [, array-variable] ...[Figure][Figure][Figure][Figure][Figure]
Single-row SELECT statementSELECT [{ALL | DISTINCT}] (* | selectionexpression
[, selection-expression] ...} INTO variable [, variable] ... FROM table-name [,table-name] ... [WHERE search-condition] [GROUP BY column-specification] [HAVING search-condition]
YYYY[Figure]
[{WITH {SHARE | EXCLUSIVE} LOCKYY[Figure][Figure][Figure]
| WITHOUT LOCK [{WAIT | NOWAIT}]}]YY[Figure]Y[Figure]
[WITH ROLLBACK]YY[Figure]Y[Figure]
| NO WAIT}]Y[Figure][Figure][Figure][Figure]
Insertion value{value-specification | NULL}YYYYY4
INSERT statement
Format 1
INSERT INTO table-name [(columnname [, columnname] ...)]
{VALUES (insertion-value
[, insertionvalue] ...) | query-specification}
YYYYY
[WITH ROLLBACK]YY[Figure]Y[Figure]
INSERT statement
Format 2
INSERT INTO tablename (ROW) {VALUES (row-insertion-value) | query-specification} [WITH ROLLBACK][Figure][Figure][Figure][Figure][Figure]
INSERT statement Format 3

FOR variable
INSERT INTO table-name[(column-name[,column-name]...)]
{VALUES (insertion-value[,insertion-value]...)
|query-specification}

[WITH ROLLBACK]
[Figure][Figure][Figure][Figure][Figure]
INSERT statement Format 4

FOR variable
INSERT INTO table-name(ROW)
{VALUES (row-insertion-value)|query-specification}

[WITH ROLLBACK]
[Figure][Figure][Figure][Figure][Figure]
Update value{value-expression | NULL}YYYYY4
UPDATE statement
Format 1
UPDATE table-name SET column-name=updatevalue
[, columnname=update-value] ... [WHERE {search-condition|
CURRENT OF cursor-name}]
YYYYY
UPDATE table-name SET repetition-column-name
[{subscript|*}] =element-value[,repetition-column-name [{subscript|*}]
=element-value] ... [WHERE{retrieval-condition|
CURRENT OF cursor-name}]
Y[Figure][Figure][Figure][Figure]
UPDATE tablename
ADD repetition-column-name[{subscript|*}] =ARRAY[element-value[,element-value] ...] [,repetition-column-name
[{subscript|*}] =ARRAY[element-value[,element-value] ...) ... [WHERE{retrieval-condition
|CURRENT OF cursor-name}]
Y[Figure][Figure][Figure][Figure]
UPDATE table-name
DELETE repetition-columnname
[{subscript|*}] [,repetition-column-name[
{subscript|*}]... [WHERE{retrieval-condition|
CURRENT OF cursor-name}]
Y[Figure][Figure][Figure][Figure]
[WITH ROLLBACK]YY[Figure]Y[Figure]
UPDATE statement
Format 2
UPDATE table-name
SET ROW=variable
[WHERE {search-condition| CURRENT OF cursor-name}] [WITH ROLLBACK]
[Figure][Figure][Figure][Figure][Figure]
UPDATE statement Format 3

FOR variable
UPDATE table-name
SET column-name=update-value[,column-name=update-value]...
[WHERE search-condition]

[WITH ROLLBACK]
[Figure][Figure][Figure][Figure][Figure]
UPDATE statement Format 4

FOR variable
UPDATE table-name
SET ROW=variable
[WHERE search-condition]

[WITH ROLLBACK]
[Figure][Figure][Figure][Figure][Figure]
DELETE statement Format 1DELETE FROM table-name
[WHERE {search-condition
|CURRENT OF cursor-name}]
YYYYY
[WITH ROLLBACK]YY[Figure]Y[Figure]
DELETE statement Format 2

FOR variable
DELETE FROM table-name

WHERE search-condition
[Figure][Figure][Figure][Figure][Figure]
[WITH ROLLBACK][Figure][Figure][Figure][Figure][Figure]
PREPARE statementPREPARE SQL-statement-identifier FROM{'character-string' | variable}YYYYY
[WITH SQLNAME OPTION]YYY[Figure][Figure]
SQL statements preprocessable by PREPARE statementINSERT,
UPDATE (without cursor),
DELETE (without cursor),
dynamic SELECT statement
YYYYY
PURGE TABLE,
LOCK TABLE,
CALL
Y[Figure][Figure][Figure][Figure]
Definition SQL
ASSIGN LIST,
DROP LIST,
dynamic SELECT statement Format 2
[Figure][Figure][Figure][Figure][Figure]
DESCRIBE OUTPUT statementDESCRIBE [OUTPUT]
SQL-statement-identifier INTO [:] SQL-Data-Area-name [[:] column-name-data-areaname]
YYYYY
DESCRIBE INPUT statementDESCRIBE INPUT
SQL-statement-identifier INTO [:] SQL-data-area-name [[:] column-name-data-area-name]
Y[Figure][Figure][Figure][Figure]
EXECUTE statement
Format 1
EXECUTE SQL-statement-identifier YYYYY
{INTO variable[,variable]... |INTO DESCRIPTOR [:]SQL
-description-area-name}
Y[Figure][Figure][Figure][Figure]
[{USING variable[,variable]... |USING DESCRIPTOR [:]SQL
-description-area-name}]
YYYYY
EXECUTE statement
Format 2
EXECUTE SQL-statement-identifier
{USING array-variable[,array-variable]...
|USING DESCRIPTOR
[:]SQL-descriptor-area-name}
BY variable [ROWS]
[Figure][Figure][Figure][Figure][Figure]
SQL statements executable by EXECUTE statementSQL statements other than dynamic SELECT statement preprocessed by PREPARE statementYYYYY
EXECUTE IMMEDIATE statementEXECUTE IMMEDIATE {'characterstring' | variable}YYY[Figure][Figure]
SQL statements executable by EXECUTE IMMEDIATE statementINSERT, UPDATE (without cursor), DELETE (without cursor)YYY[Figure][Figure]
PURGE TABLE,
LOCK TABLE,
CALL
Y[Figure][Figure][Figure][Figure]
Definition SQL
ASSIGN LIST,
DROP LIST
[Figure][Figure][Figure][Figure][Figure]
PURGE TABLE statementPURGE TABLE table-name [WITH ROLLBACK | NO WAIT]Y[Figure][Figure][Figure][Figure]
LOCK TABLE statementLOCK TABLE tablename
[, tablename] ... [IN {SHARE | EXCLUSIVE} MODE] [WITH ROLLBACK | NO WAIT]
Y[Figure][Figure][Figure][Figure]
UNTIL DISCONNECT[Figure][Figure][Figure][Figure][Figure]
CALL statement10CALL
RD-node-name.authorization-identifier.
routine-identifier(argument-specification)
Y[Figure][Figure][Figure][Figure]
GET DIAGNOSTICS statementGET DIAGNOSTICS...Y8Y8Y8Y8Y8
SET SESSION AUTHORIZATION statementSET SESSION AUTHORIZATION :embedded-variable-1
[{USING|IDENTIFIED BY}
:embedded-variable-2]
[Figure][Figure][Figure][Figure][Figure]
FREE LOCATOR statement

FREE LOCATOR :locator-reference

[,:locator-reference]...
[Figure][Figure][Figure][Figure][Figure]
Assignment statementSET assignment-destination=assignment-value[Figure][Figure][Figure][Figure][Figure]
The letter in each column under the Usable at distributed server column denotes the following:
H: HiRDB
X: XDM/RD
O: ORACLE
R: RDB1 E2
S: SQL/K
Legend:
Y: Can be used.
[Figure]: Cannot be used.
1 The table name formats RD-node-name.authorization-identifier.table-identifier and [authorization-identifier.]table-alias can be used only when the default SQL connection is used as the current SQL connection.
2 The table name format [authorization-identifier.]table-identifier can be used only when an SQL connection to a distributed RD-node is used as the current SQL connection.
3 If a correlation name contains both 1- and 2-byte characters, it may not be processed by a distributed server.
4 The following functions cannot be used:
  • USER literal (SQL/K only)
  • NULL literal (SQL/K only)
  • Comparison operator || (RDB1 E2 only)
  • Comparison operator <> (SQL/K only)
  • Predicate and search condition NOT (SQL/K only)
  • Scalar function (RDB1 E2 and SQL/K only)
  • Query specifications ALL and DISTINCT (SQL/K only)
5 When the LENGTH or SUBSTR function is used on an MCHAR type column, HiRDB processes the data length and location based on the number of characters, while XDM/RD processes based on the number of bytes; this means that the execution results are different. Also, in ORACLE, a character type column may contain both one-byte and two-byte characters, so processing is performed based on the number of bytes when the LENGTH or SUBSTR function is used.
6 For RDB1 E2 or SQL/K, define using an inquiry specification rather than an inquiry equation.
7 When UNION[ALL] is not specified, XDM/RD, RDB1 E2, and SQL/K use a different number for the sort specification item number in the ORDER BY clause from the number used by other systems. In XDM/RD, RDB1 E2, and SQL/K, the number that represents the position of the selection expression of the column to be used as the sort key (the position specified in the SELECT clause) is specified. In other systems, the number that represents the position of the column to be used as the sort key (the position specified in a derived table) is specified.
8 Only errors that occur in the distributed server can be collected. For details about the errors that can occur in a distributed server, see 11.5 Handling distributed server errors.
9 Embedded variables and ? parameters that have repetition structures cannot be used.
10 Procedures that use the PURGE TABLE, COMMIT, or ROLLBACK statement cannot be executed.