HiRDB SQL Executer Outline

HiRDB SQL Executer Overview

HiRDB SQL Executer Menu

Environment Dialog Box

Environment variable

Information displayed as search results

Rules


HiRDB SQL Executer Overview

** Functional overview

HiRDB SQL Executer (GUI version) is an interactive SQL execution program that interactively executes SQL for HiRDB databases.

This program reads SQL from a window or file and displays the execution result in the window.

The GUI version provides an interface that reads SQL and pdsql commands from the input windows and files dedicated to HiRDB SQL Executer, and outputs the execution result to the output windows and files dedicated to HiRDB SQL Executer.

The GUI version also enables you to search user data and browse dictionary information via screen operation without entering SQL.

Windows is the only platform supported.

The program comes in two versions: the SJIS version and the Unicode version. Refer to ”Differences between The SJIS Version and Unicode Version”.

** Executer

Users authorized to connect to HiRDB can execute HiRDB SQL Executer.

However, higher authorization may be required depending on the SQL to be executed.


HiRDB SQL Executer Menu

When the program is successfully connected to HiRDB, the HiRDB SQL Executer Main Window is displayed.

The following windows are displayed in the Main Window.

[SQL Input] Window | Window in which SQLs or pdsql commands are entered.
[Output] Window | Window in which the execution result of SQLs or pdsql commands and the display result of View and Procedure definition information in the Dictionary View Window are displayed.
[Dictionary View] Window | Window in which the dictionary information of the currently connected database are displayed.

When HiRDB SQL Executer is connected, the Main Window also displays the computer name of the connection

destination host (or value specified in PDHOST), PDNAMEPORT, and the Authorization Identifier in its title bar, delimited by colons.

The Main Window contains the following menus:

During SQL execution, or when an input error occurs in the data update function, menu operation is disabled.

[File(F)]

[SQL Input(I)]

Creates and Displays the SQL Input window.

Up to four SQL Input windows can be displayed.

[Execute from File(E)...]

Reads and executes SQL and pdsql commands from the file.

[Connect(C)...]

Connects HiRDB SQL Executer to HiRDB.

Disabled when HiRDB SQL Executer is connected to HiRDB. (*1)

[Disconnect(D)]

Disconnects HiRDB SQL Executer from HiRDB.

A12
Disabled when HiRDB SQL Executer is not connected to HiRDB. (*1)

[Exit(X)]

Exits HiRDB SQL Executer.

[Edit(E)]

[Undo(U)]

Undoes the last edit.

[Redo(R)]

Redoes the last edit.

[Cut(T)]

Deletes the selection and copies it to the clipboard.

[Copy(C)]

Copies the selection to the clipboard.

When you select either the input in the SQL input window or the output in the [Result] tab of the output window, rich text data in the selected range is copied.

When you select the table in the output window, the following operations are performed:

- The character string displayed in the cell is copied. If the copy target is a null value, the character that represents the null value is copied.

- If you select multiple cells, a tab character is used as the column delimiter, and the line feed code (0x0D + 0x0A) is used as the row delimiter.

[Paste(P)]

Pastes the contents of the clipboard to the cursor location.

[Clear(E)]

Erases the selection.

[Select All(A)]

Selects all the contents of the active window.

[Delete All(D)]

Deletes all the contents of the active window.

[Find(F)...]

Displays the Find dialog box.

[Next(N)]

Retrieves the specified character string in the active window.

[Replacement(L)...]

Displays the Replacement dialog box.

This item can be selected only when the SQL input window is active.

[Advanced Operation(V)]

[Upper Case(U)]

Converts single-byte lowercase characters in the selected area to single-byte uppercase characters.

[Lower Case(L)]

Converts single-byte uppercase characters in the selected area to single-byte lowercase characters.

[Display(V)]

[Toolbar]

Switches Toolbar display to Toolbar hide or vice versa.

[Statusbar]

Switches Statusbar display to Statusbar hide or vice versa.

[Dictionary View Window]

Switches Dictionary View Window display to Dictionary View Window hide or vice versa.

[Switch Output]

Switches Output Window display contents.

(Result or Data)

[Operation(C)]

[Execute SQL(E)]

Executes the entered SQLs and pdsql commands.

[SQL History(H)...]

Displays the history of the executed SQLs and pdsql　commands.

[Import(I)...]

Displays the Input Parameters from File dialog box.

[Export(X)...]

Displays the Search Result File Output dialog box .

[Setting(S)]

[Font(F)...]

Sets a font for the active window.

[Environment(V)...]

Displays the Environmental Dialog Box.

[Window(W)]

[Cascade(C)]

Cascades windows.

[Tile Horizontally(H)]

Rearranges windows in the horizontal tiled format.

[Tile Vertically(T)]

Rearranges windows in the vertically tiled format.

[Arrange Icons(A)]

Arranges minimized window icons. (*2)

[Help(H)]

[Help Topic(H)...]

Displays Help.

[Version Information(V)...]

Displays version information.

*1: If the HiRDB server is disconnected (disconnection based on monitoring time), the selection status may

remain the same as in the connected status although the server is no longer connected. To reconnect HiRDB SQL Executer, select [Disconnect] and then select [Connect].

*2: These commands cannot be selected.

The Toolbar items displayed in the Main Window are as follows:

  These items can be moved only on the toolbar in the upper part of the main window.

  In the main menu or the right-click menu in the toolbar display area, these items can also be hidden by hiding the toolbar itself.

During SQL execution or when an input error occurs in the data update function, button operation is disabled.

Items in the [Edit] toolbar

Icon | Name(name displayed when the cursor is positioned) | Description
* | Cut | Deletes the selection and copies it to the clipboard.
* | Copy | Copies the selection to the clipboard.
* | Paste | Pastes the contents of the clipboard to the cursor location
* | Undo | Undoes the last edit.
* | Redo | Redoes the last edit.

Items in the [Search] toolbar

Icon | Name(name displayed when the cursor is positioned) | Description
* | Find | Searches for the character string entered in the left combobox.

Items in the [Operation] toolbar

Icon | Name(name displayed when the cursor is positioned) | Description
 | Connect | Connects HiRDB SQL Executer to HiRDB. Disabled when HiRDB SQL Executer is connected to HiRDB. (*1)
 | Disconnect | Disconnects HiRDB SQL Executer from HiRDB. Disabled when HiRDB SQL Executer is not connected to HiRDB. (*1)
* | Execute SQL | Executes the entered SQL and pdsql commands.
 | Import | Displays the Input Parameters from File dialog box.
 | Export | Displays the Search Result File Output dialog box

Items in the [Display] toolbar

Icon | Name(name displayed when the cursor is positioned) | Description
* | Dictionary View Window | Switches Dictionary View Window display to Dictionary View Window hide or vice versa.
 | SQL History | Displays the history of the executed SQL and pdsql commands.
* | Version Information | Displays version information.


Environmental Dialog Box

Selecting [Environment(V)…] from the [Setting(S)] menu displays the Environmental dialog box.

The Environmental dialog box is shown below:

Item

Contents

[Input Buffer] Text box

Specify the number of characters that can be entered in the SQL Input Window.

Specifiable range: 64000 to 3000000 (*1)

Initial value: 200000

[Result Screen] Text box

Specify the number of rows that can be output to the [Result] tab of the Output Window.

If the number of outputs exceeds the maximum number of rows specified here, the excess rows are deleted starting from the oldest row.

Specifiable range: 1000 to 20000

Initial value: 1000

[Data Screen] Text box

Specify the number of rows that can be output to the [Data] tab of the Output Window.

Data that exceeds the maximum number of rows cannot be output. A message informing that not all the data was output and the total amout of data is displayed in the status bar area. Export data to a file and narrow down extraction conditions as necessary.

Specifiable range: 100 to 20000

Initial value: 1000

[History Storage] Text box

Specify the number of SQL and command histories to be stored.

Specifiable range: 1000 to 2000

Initial value: 1000

[when the Update button operation is successful]

Radio button

If "COMMIT and do not search" is selected, COMMIT is executed, and the table data on the screen is cleared when the Update button is pressed.

If you want to check the update result, execute the search SQL again.

This option is useful if you want to update the table data only once or when you want to update multiple tables.

If "COMMIT and re-execute the search SQL" is selected, COMMIT is executed, and the search SQL that was executed to obtain the data on the screen is executed again when the Update button is pressed.

By default, "COMMIT and re-execute the search SQL" is selected.

[Enter to Execute SQL]

Radio button

When you select “YES”, Executer verifys the existence of end (semicolon) of the statement and executes commands and SQL.

When you select “NO”, Executer begins a new line without checking the end of the statement.

Pressing the F5 key or selecting [Execute SQL(E)] from the [Operation(C)] menu when you select “NO” executes commands and SQL.

[Search result output destination] Check boxes

If "Data window" is selected, the search results appear in the Data tab of the output window.

If "Data window" is not selected, no search results appear in the Data tab. No column names appear as well.

If "Results window" is selected, the search results appear in the Results tab of the output window.

If "Results window" is not selected, no search results appear in the Results tab nor are they stored in an execution result file.

By default, only "Data window" is selected.

*1: In consideration of line feed characters, Executer is designed so that a value greater than the SQL maximum length (2000000) of HiRDB can be specified.

** Notes

This setting is carried over to the next Executer startup.


Environment variable

You can set environment variables to change the default settings of this program.

Environment variables set in the Windows system properties are automatically set at the activation of this program.

You can set the following environment variables:

(a) PDUAPAUTOCOMMIT

Set whether to issue a commit request automatically

after an SQL statement is executed.

ON : Issues a commit request automatically.

OFF: Does not issue a commit request automatically.

The default is ON.

- If you need to change the set values while the program is running,

execute the pdsql command SET AUTO COMMIT.

(b) PDUAPDUMP

Set the display format of the search results.

ON : Displays the search results in hexadecimal.

OFF: Displays the search results as strings.

The default is OFF.

- If you need to change the set values while the program is running,

execute the pdsql command SET DUMPMODE.

(c) PDUAPPTFNULL

Set how the PUTFILE command handles NULL data.

ON : Outputs nothing.

OFF: Outputs an asterisk ("*").

The default is OFF.

- When OFF is set, nothing is output for the following data types:

CHARACTER, VARCHAR, NCHAR, NVARCHAR, MCHAR, MVARCHAR,

BLOB,BINARY

- This environment variable is not effective for the PUTFILE BINARY command.

(d) PDSQLBINARYLIMIT

The command limits the length of the BINARY type data

that can be input and output.

0 : Uses the defined length.

Between 1 and 2147483647 :

Limits the length to the specified value.

The value 0 is assumed if no setting exists.

- If you need to change the set values while the program is running,

execute the pdsql command SET BINARY LIMIT.

(e) PDSQLBLOBLIMIT

The command limits the length of the BLOB type data

that can be input and output.

0 : Uses the defined length.

Between 1 and 2147483647 :

Limits the length to the specified value.

The value 0 is assumed if no setting exists.

- If you need to change the set values while the program is running,

execute the pdsql command SET BLOB LIMIT.

(f) PDSQLCLOBLIMIT

The command limits the length of the CLOB type data

that can be input and output.

0 : Uses the defined length.

Between 1 and 2147483647 :

Limits the length to the specified value.

The value 0 is assumed if no setting exists.

- If you need to change the set values while the program is running,

execute the pdsql command SET CLOB LIMIT.

(g) PDSQL_CHARCODE

This environment variable is valid only for the Windows.

The command sets the program to be activated by the activation program files(PDSQLX.EXE and PDSQLWX.EXE and PDSQLWX8.EXE)

SJIS : Activates the SJIS version (PDSQL.EXE and PDSQLW.EXE and PDSQLW8.EXE).

UNICODE : Activates the Unicode version (PDSQLU.EXE and PDSQLWU.EXE and PDSQLWU8.EXE).

If PDSQL_CHARCODE is not set, set the program to start by referring to PDCLTCNVMODE (environment variable or HIRDB.INI) in the client environment definition.

If the value of PDCLTCNVMODE is a character string that begins with "UCS2":

Start the Unicode version.

If the value of PDCLTCNVMODE is other than the above:

Start the SJIS version.

If neither PDSQL_CHARCODE nor PDCLTCNVMODE has been set, Activates the SJIS version.

(h) PDSQL_SPLITSIZE

Set the size of division acquisition of the

BINARY/BLOB/CLOB type data.

0 : The division acquisition is not done.

Between 4 and 2097152 :

The division acquisition is done by

the specified value(Unit of KB).

- If you need to change the set values while the program is running,

execute the pdsql command SET SPLIT SIZE.

(i) PDSQL_CONVSPACE

Set whether to convert one-byte space into

character codes(without NL (X'0a') and CR (X'0d')) except changing line in character

string of command

ON : The control character code is converted at the

one-byte space.

OFF: The control character code is not converted.

- If you need to change the set values while the program is running,

execute the pdsql command SET CONVSPACE.

(j) PDSQL_EXEC_SEMICOLON_ENTER

Specify the relationship between the semicolon and the execution trigger.

This environment variable is enabled in the following cases:
- in the command line version, it is entered
- a command or SQL statement is entered from a file

YES or 0: After the beginning of an enclosed comment (/*), if a semicolon is immediately followed by a linefeed character#1, the SQL statement is executed.

NO or 1: After the beginning of an enclosed comment (/*), if a semicolon is immediately followed by a linefeed character#1, the SQL statement is not executed.

The semicolon is treated as a character in the enclosed comment.

The system waits for a continuing statement to be entered.
2: If the portion of a line that starts from a semicolon that is not within an enclosed comment and that ends at the end of the line (the linefeed code) is only an enclosed comment, the SQL statement is executed.

At such a time, the enclosed comment that is between the semicolon and the linefeed character is not sent to the server.

If nothing is set, NO is assumed.

In versions 08-02 and earlier, the YES operation was performed.

Example 1: Comparison between when PDSQL_EXEC_SEMICOLON_ENTER is set to 0 and when it is set to 1

(Input)

/*COM11*/SELECT * FROM T01/*COM12;

SELECT * FROM T02/*COM22*/;

If PDSQL_EXEC_SEMICOLON_ENTER is set to 0 (or YES), when entry of the first row is complete, the enclosed

portion below (1) is executed.

When entry of the second row is complete, (2) is executed.

(1)

/*COM11*/SELECT * FROM T01/*COM12

(2)

SELECT * FROM T02/*COM22*/

If PDSQL_EXEC_SEMICOLON_ENTER is set to 1 (or NO), when entry of the second row is complete (a line feed is entered),

the enclosed portion (1) below is executed.

(1)

/*COM11*/SELECT * FROM T01/*COM12;
SELECT * FROM T02/*COM22*/

Example 2: Comparison between when PDSQL_EXEC_SEMICOLON_ENTER is set to 1 and when PDSQL_EXEC_SEMICOLON_ENTER is set to 2

(Input)

/*COM11*/SELECT * FROM T01/*COM12*/;/*COM13*/

/*COM21*/SELECT * FROM T02/*COM22*/;/*COM23*/

/*COM31*/

/*COM41*/SELECT * FROM T04/*COM42*/

/*COM51*/WHERE C1 <200;

If PDSQL_EXEC_SEMICOLON_ENTER is set to 1 (or NO), when entry of the fifth row is complete (a line feed is entered),

the SQL in the enclosed portions (1), (2), and (3) below is executed.

(1) (2)

/*COM11*/SELECT * FROM T01/*COM12*/

/*COM13*/

(2) (3)

/*COM21*/SELECT * FROM T02/*COM22*/

/*COM23*/

(3)

/*COM31*/

/*COM41*/SELECT * FROM T04/*COM42*/

/*COM51*/WHERE C1 <200

If PDSQL_EXEC_SEMICOLON_ENTER is set to 2, when entry of the first row is complete, the enclosed portion (1) below is

executed. When entry of the second row is complete, (2) is executed.

When entry of the fifth row is complete, (3) is executed.

(1)

/*COM11*/SELECT * FROM T01/*COM12*/

/*COM13*/

(2)

/*COM21*/SELECT * FROM T02/*COM22*/

/*COM23*/

(3)

/*COM31*/
/*COM41*/SELECT * FROM T04/*COM42*/
/*COM51*/WHERE C1 <200

In the GUI version or the simplified

GUI version, when data is entered from the SQL input screen, this environment variable is not enabled. The NO operation is always performed.

#1: Either CR (X'0d') + NL (X'0a') or NL (X'0a') is interpreted as a line feed.

(k) PDSQL_REMOVE_LINEFEED_CHARACTER

Set whether to remove the line feed code (NL (X'0a'), CR (X'0d')) from the

character string in the SQL statement or the pdsql command line.

ON: Remove the linefeed character.

OFF: Do not remove the line feed character.

If nothing is set, OFF is assumed.

To change the value during execution of the program,

execute the SET REMOVE LINEFEED CHARACTER pdsql command.

In versions 09-01 and earlier, the NO operation was performed.

In the simplified GUI version, when data is entered from the SQL input screen,

the settings are not enabled. The NO operation is always performed.

For the effects when this setting is ON, see in "Rules".

(l) PDSQL_IDENT_QUOT

In the TABLEINF command and the SEQINF command, set whether to use double quotation

marks (") to enclose the name to be output.
ON: Output the name enclosed by double quotation marks.

OFF: Output the name not enclosed by double quotation marks except when the name includes a lowercase character

If nothing is set, ON is assumed.

In versions 09-01 and earlier, the NO operation was performed.

Note: When the modified version is installed, see the line-mode HiRDB SQL Executer Online Manual (pdsql.txt) for the additions and changes.


Rules

-

This program can be executed only when HiRDB is running.

-

SQL lowercase characters are converted to uppercase characters. When using lowercase characters, enclose them in double quotation marks (").

(Example) INSERT INTO "t1" VALUES(1000);

-

The maximum length of the SQL statement that can be executed is 2000000 bytes.

If the Unicode version , The maximum length of the SQL statement that can be executed is 2000000 characters.

-

The display result of one line or the file output result cannot be retrieved beyond 2097152 bytes.

-

The semicolon, END_PROC ;, END_FUNC ;, END_TYPE ;, or END_TRIG ; written at the end of a line indicates the end of the SQL statement even if they are enclosed in double quotation marks (“) in the SQL statement.

-

If the file/directory name includes a sign or a blank character other than yen sign, enclose it in double quotation marks(“).

-

Processing of a comment in the SQL statement depends on the version of the server to be connected.

-

When the version is 06-01 or earlier, a comment is replaced with one space and the SQL statement is transmitted to the server side. If a comment has no end sign, the comment is replaced until the end of the line.

-

When the version is 06-02 or later, the SQL statement is transmitted to the server side without the comment edited.

-

The interpretation of "--" in the SQL statement depends on the system definition of the server to be connected.

-

If the system definition pd_sql_simple_comment_use is set to "Y", it is interpreted as a simple annotation. Even if there is a semicolon between a simple annotation and a line feed (NL (X'0a')), it is not interpreted as the end of the SQL statement.

If the facility for deleting new line characters is enabled, all line feeds (NL (X'0a') and CR (X'0d')) are deleted before execution.

Accordingly, all entries after a simple annotation are interpreted as annotations.

Likewise, all entries after a simple annotation that are entered in the simple GUI-based SQL entry window are interpreted as annotations.

The GUI-based HiRDB SQL Executer checks if there is a semicolon at the end of the last row before SQL transmission. If all entries are interpreted as an annotation, the error KFPA11106-E is returned as the SQL statement execution result.

-

If the system definition pd_sql_simple_comment_use is not set or not set to "Y", it is interpreted as an operator.

A semicolon (;) that is not enclosed with quotation marks (' or ") is interpreted as the end of the SQL statement.

-

You cannot enter enclosed annotations (/* - */) or simple annotations (--) in the following locations.

(i)

In a pdsql command*1

(ii)

Between CREATE and PROCEDURE in CREATE [PUBLIC] PROCEDURE

(iii)

Between CREATE and FUNCTION in CREATE [PUBLIC] FUNCTION

(iv)

Between CREATE and TYPE in CREATE TYPE

(v)

Between CREATE and TRIGGER in CREATE TRIGGER

(vi)

In the same row as END_PROC;, END_FUNC;, END_TYPE;, or END_TRIG;

*1 You can enter enclosed annotations before a pdsql command (between a line feed (NL (X'0a')) and a pdsql command) or after a pdsql command (between a semicolon and a line feed (NL (X'0a')).

Behavior when enclosed or simple annotations are entered:

If annotations are entered in location (i), a syntax error may occur.

If annotations are entered in either of locations (ii) to (v), the semicolon (;) in the SQL procedure statement is incorrectly interpreted as the end of the SQL statement, and the incorrect SQL statement is sent to the server.

If annotations are entered in location (vi), the program waits for the entry of succeeding lines, as the semicolon (;) is not interpreted as the end of the SQL statement.

-

The following characters can be specified as delimiters of SQL statements and pdsql commands.

Character | SQL statement | pdsql command
Space (X'20') | Y | Y
TAB (X'09') | Y | Y
NL (X'0a')*2 | C | C
CR (X'0d')*2 | C | C
Enclosed annotation | C*3 | N
Simple annotation | C*3 | N
Double-byte space | C*3 | N

Y: Can be specified; C: Can be specified (with conditions); N: Cannot be specified

*2 The behavior depends on whether the facility for deleting new line characters is enabled or not.

If the facility for deleting new line characters is enabled:

All line feeds (NL (X'0a') and CR (X'0d')) in the SQL statement or the pdsql command are deleted before execution.

Accordingly, they are not interpreted as delimiters.

However, the new line character immediately before END_PROC;, END_FUNC;, END_TYPE;, or END_TRIG; is interpreted as a delimiter.

If the facility for deleting new line characters is disabled:

New line characters are interpreted as delimiters.

For example behavior, see SET REMOVE LINEFEED CHARACTER in the section pdsql command.

*3 You cannot enter enclosed annotations, simple annotations, and double-byte spaces as delimiters in the following locations.

- Between CREATE and PROCEDURE in CREATE [PUBLIC] PROCEDURE

- Between CREATE and FUNCTION in CREATE [PUBLIC] FUNCTION

- Between CREATE and TYPE in CREATE TYPE

- Between CREATE and TRIGGER in CREATE TRIGGER

- In the same row as END_PROC;, END_FUNC;, END_TYPE;, or END_TRIG;

-

The following symbols (single-byte character codes) in a pdsql command*4 are interpreted as one meaningful phrase regardless of delimiters appearing before or after the symbols.

Comma (,)

Period (.)

Hyphen or minus sign (-)

Plus sign (+)

Asterisk (*)

Single quotation mark (')

Double quotation mark (")

Left parenthesis (( )

Right parenthesis ( ))

Less than sign (<)

Greater than sign (>)

Equals sign (=)

Circumflex (^)

Exclamation mark (!)

Forward slash (/)

Question mark (?)

Colon (:)

Semicolon (;)

Percent sign (%)

Vertical bar (|)

Left square bracket ([)

Right square bracket (])

Back apostrophe（`）

Left big parenthesis（{）

Right big parenthesis（}）

Dollar ($)

To specify options, you need to enter the delimiters in the defined format.

(Example) PUTFILE .... [-q enclosing-character] .... TO ....

You must not enter a delimiter between the hyphen and q.

You must enter a delimiter between q and the wrap character.

You can enter delimiters before the hyphen or after the wrap character.

*4 This rule does not apply to the following commands, as the entries before the semicolon at the end are regarded as one operand.

- Executing a UNIX command (!)

- Executes a server command (:)

-

If there one or more space characters at the beginning or end of the statement, the consecutive space characters are removed.

-

If you use the default null value setting facility when Executer is connected to XDM/RD E2,

set the default value of the null value in the DEFAULT NULL VALUE OPTION clause of the XDM/RD E2

RD environment definition statement and at the same time set the PDDFLNVAL=USE environment variable.

-

In case of BINARY/BLOB/CLOB type includes in search result, operate as follows.

In SJIS Version SQL Executer, except when the character-code type that HiRDB Server uses is a

shift JIS code, the garble and the column gap are generated including characters other than

ASCII Code.

In Unicode Version SQL Executer, except when the character-code type that HiRDB

Server uses is a Unicode(UTF-8), the garble and the column gap are generated including

characters other than ASCII Code.

Notes on rules:

The behavior described below is the same as the previous versions.

-

If all of the following conditions are satisfied, the error KFPA11106-E occurs when an invalid SQL statement composed only of annotations is executed.

Conditions:

(i) You enter the SQL statement in the GUI- or simple GUI-based SQL entry window.

(ii) You enter a statement composed only of annotations (and the semicolon at the end of the statement), a new line character, and then a statement not including annotations.

(iii) You execute the statements.

Example:

/**/;

SELECT COUNT(*) FROM table001;

....An error occurs, as the first row is executed as one SQL statement.

-

If all of the following conditions are satisfied, an SQL syntax error occurs, as the delimiter at the end of the first statement is ignored and combined with the subsequent statement.

Conditions:

(i) You enter the SQL statement in the GUI- or simple GUI-based SQL entry window.

(ii) The statement does not start with any of the SQL statements or commands in Table 7-1 and Table 8-1, and ends with a semicolon and a new line character.

(iii) The subsequent statement is displayed on the screen.

(iv) You execute the statements.

Example:

TRADE T1 TO T2;

SELECT COUNT(*) FROM table001;

....A syntax error occurs, as the two rows are executed as one SQL statement.

-

If all of the following conditions are satisfied, new line characters are deleted even when the facility for deleting new line characters is disabled.

Conditions:

(i) There is a semicolon before an enclosed annotation to indicate the end of the statement.

(ii) There is only one or more space characters between the semicolon indicating the end of the statement and the enclosed annotation.

(iii) There is only one or more characters listed below in the enclosed annotation.

- Asterisk (*)

- Apostrophe (')

- Double quotation mark (")

- Forward slash (/)

- Space

- Semicolon (;)

(iv) There is only one or more space characters between the enclosed annotation and a new line character.

Example:

SELECT COUNT(*) FROM table001; /*"'*'"*/

SELECT COUNT(*) FROM table002;

.... The new line character between "/*"'*'"*/" and "SELECT."

Note: When the modified version is installed, see the line-mode HiRDB SQL Executer Online Manual (pdsql.txt) for the additions and changes.


How to Use

Connect to HiRDB

Executes SQL and pdsql Commands

Displays History of The Executed SQL and pdsql Commands

Dictionary View Window

Updating table data

Displaying table data in hexadecimal format

Find／Replacement Dialog

SQL That Can Be Executed

pdsql Commands That Can Be Executed

Cancelling a running SQL statement

Troubleshooting

Initialization dialog box

Cautionary notes


Connect to HiRDB

When executed, this program is connected to HiRDB in the following procedure:

** | The SJIS version or the UNICODE version of the program runs depending on the value of the PDCLTCNVMODE environment variable and the PDSQL_CHARCODE environment variable.
** | When PDUSER, PDHOST, and PDNAMEPORT are set in HIRDB.INI or in environment variables PDUSER is used as the authorization identifier and password, PDHOST is used as the host name, and PDNAMEPORT is used as the port number. These three items are used as the default connection settings. If these items are set in both HiRDB.INI and in environment variables, the items set in the environment variables are used as the default connection settings.
** | If the argument below is specified at program startup, the values specified are used as the default connection settings.
 | In normal mode: [-u authorization-identifier[/password]] [-h host-name] [-n port-number] Set the argument in the link destination in the shortcut icon properties. A shortcut for starting the program is found in the start menu.
 | In enhanced security mode: [-u authorization-identifier] [-h host-name] [-n port-number] Set the argument in the link destination in the shortcut icon properties. A shortcut for starting the program is found in the start menu. If the value of the -u option is specified in the authorization identifier/password format, the program terminates with an error.
 | <Example> c:\Program files\hitachi\pdsql\pdsqlw.exe -u hirdb
 | (Note) If you want to display an authorization identifier with double quotation marks ("), enclose it in \"...\".
 |  | c:￥Program files￥hitachi￥pdsql￥pdsqlw.exe -u ￥"root￥"
 |  |  | pdsqlw.exe is the program for the SJIS version.
 |  |  | When activating the Unicode version, change pdsql.exe to pdsqlwu.exe.
 |  |  | When using the activation file, change pdsql.exe to pdsqlwx.exe.
** | If an authorization identifier and a password are set in the default connection settings, a connection to HiRDB is made. When the connection is established, the main window appears.
** | If neither an authorization identifier nor a password are set in the default connection settings, or an attempt to connect fails in such a situation, the CONNECT window appears.

[Authorization Identifier(I)]/[Password(P)]Text box:

The authorization identifier and password specified in the default connection settings is displayed.

If the authorization identifier and password are enclosed in double quotation marks ("), they are case-sensitive.

If the authorization identifier and password are not enclosed in double quotation marks, they are treated as upper case.

You can enter an Authorization Identifier using up to 10 characters, a Password using up to 32 characters.

[PDHOST(H)]/[PDNAMEPORT(N)]Text box:

The PDHOST and PDNAMEPORT specified in the default connection settings is displayed.

The PDHOST and PDNAMEPORT holds a used value(up to five).

When you click [OK], this setting is used as a connection setting.

You can enter a PDHOST using up to 256 characters, and a PDNAMEPORT using up to 10 characters.

[Server type] options:

Select the server type. The server type specified in the default connection settings is displayed.

When you click [OK], this setting is used as a connection setting.

Immediately after installation, the default value is [AIX, HP-UX, Solaris].

[Authorization Identifier and Password is Memorized(R)]Check box：

  If you clear the [Authorization Identifier and Password is Memorized(R)] check box, the Authorization Identifier and Password entered in this window are not carried over to the next connection processing. This check box is selected (default value) immediately after installation.

In enhanced security mode: This item is not selected and cannot be changed.

[OK]Button:

1. In the PDHOST and PDXDSHOST environment variables, set the value that is entered in [Host name].

2. In the PDNAMEPORT and PDXDSPORT environment variables, set the value that is entered in [Port number].

3. In the PDSRVTYPE environment variable, set "WS" if [AIX, HP-UX, Solaris] is selected in [Server type], "PC" if [Linux, Windows] is selected in [Server type], or "VOS3" if [VOS3] is selected in [Server type].

4. Use the values entered in [Authorization identifier] and [Password] to create and set a character string in the PDUSER environment variable. In a mode other than enhanced security mode, if no value is entered in [Password], the same value that is entered in [Authorization identifier] is used as the password to create the character string.

5. Connect to HiRDB.

When the program is successfully connected to HiRDB, the HiRDB SQL Executer Main Window of SJIS or Unicode version is displayed. If the program is not connected successfully, the CONNECT Window is redisplayed after a message is output with the entered information remaining.

[Cancel]Button:

Displays the HiRDB SQL Executer Main Window of SJIS or Unicode version without connecting the program.

If you want to delete the host names and port numbers displayed in a dialog box, delete the pdsqlcon.env file right under the installation directory.

After the connect processing to HiRDB has been succeeded, the HiRDB SQL Executer Main Window shown in the figure below appears.


Executes SQL and pdsql Commands

Entering the SQL and pdsql commands that you want to execute from the SQL Input window and choosing [Execute SQL from the [Operation] menu executes the SQL and pdsql commands. The execution result is displayed in the Execution Result window shown in the figure below.

You can also execute SQL and pdsql commands by creating them in a file in advance and choosing [Execute from File] from the [File] menu.

The [Result] tab of the Output Window displays the search result, the execution result of pdsql commands, and messages.

The [Data] tab displays the immediately preceding search result in a table format with row and column borders.

Information displayed as search results on the [Data] tab of the Output Window:

- The BINARY, BLOB, and CLOB types are not supported. Space is always displayed.

- A blue asterisk (*) is displayed in place of a null value.

- In columns of the SMALLINT, INTEGER, SMALLFLT, FLOAT, INTERVAL YEAR TO SECOND, INTERVAL YEAR TO DAY, or INTERVAL HOUR TO SECOND type, numeric data is set and right-justified in each cell.

- In columns of the DECIMAL type, a character string is set and right-justified in each cell.

- In columns of the CHAR, VARCHAR, NCHAR, NVARCHAR, MCHAR, MVARCHAR, DATE, TIME, or TIMESTAMP type, a character string is set and left-justified in each cell.

- Placing the mouse pointer on the column header displays the column name and the data type in a pop-up window.

- However, they are effective on the output on the [Result] tab.

- SET ARRAY DISPLAY MODE NUM :DATA at all times

- SET DUMPMODE ON :OFF at all times

- The table "Display actions when executing a search" shows the display actions that take place when you execute a search.

Display actions when executing a search

Condition |  | Action
[Search result output destination] in the environment settings dialog box | SET TBLMODE command (the default is ON) | When an error occurs in pre-processing | When 0 data items are found | When an error occurs during the search | When an all-data search completes
[Data screen] selected | ON | The [Result] tab is displayed | The [Result] tab is displayed | The [Result] tab is displayed | The [Data] tab is displayed
 | OFF | The [Result] tab is displayed
[Data screen] not selected | - | The [Result] tab is displayed

Right-clicking the text area on the SQL Input Window displays the following operations menu:

[Undo(U)] | Undoes the last edit.
[Redo(R)] | Redoes the last edit.
[Cut(T)] | Deletes the selection and copies it to the clipboard.
[Copy(C)] | Copies the selection to the clipboard.
[Paste(P)] | Pastes the contents of the clipboard to the cursor location.
[Select All(A)] | Selects all the contents of the SQL Input window.
[Find(F)...] | Displays the Find dialog box.

Right-clicking the text area on the [Result] tab of the Output Window displays the following operations menu:

[Copy(C)] | Copies the selection to the clipboard.
[Select All(A)] | Selects all the contents of the Result tab window.
[Delete All(D)] | Deletes all the contents of the Result tab window.
[Find(F)...] | Displays the Find dialog box.

In the output window, in the [Data] tab, if certain conditions are met, editing the displayed table data and incorporating the edited information into the database is possible.

For details, see "Updating table data ".

Right-clicking the table area on the [Data] tab of the Output Window displays the following operations menu:

[Copy(C)] | Copies the selection to the clipboard. - The character string displayed in the cell is the copy target. If you attempt to copy a null value, the character that represents null is copied. - When multiple cells are selected, a tab character is used as the column delimiter, and the line feed code (0x0D + 0x0A) is used as the row delimiter.
[Select All(A)] | Selects all the contents of the Data tab window.
[Find(F)...] | Displays the Find dialog box.
[Export from Window] | Outputs the data displayed in the window to the file specified in the extended DAT format.(#1)
[Display in hexadecimal] | Displays the value in the selected cell in hexadecimal format.(#3)
[Return to normal display] | Returns the selected cell to normal display.(#3)
[Cancel edit] | Restores the value of the selected cell before it was edited.(#2)
[Set null] | Changes the value of the selected cell to null.(#2)
[Delete] | Deletes the selected row.(#2) The background color of the row to be deleted is displayed in gray. To select the row, select the row header. If the row data has already been edited, the row cannot be deleted. The data in the row to be deleted cannot be edited.
[Add] | Adds a row after the last row.(#2)

#1: For the NULL value, the displayed contents are output.

#2: For details, see "Updating the table data".

#3: For details, see "Displaying table data in hexadecimal format".


Displays History of The Executed SQL and pdsql Commands

Selecting [SQL History(H)…] from the [Operation(C)] menu, clicking the [SQL History] button from the toolbar, or clicking the ”Input History” button from the ?Parameter Input dialog box enables you to display the list of SQL and pdsql commands that are already executed and their ?Parameter values.

You can also load and use the displayed SQL and pdsql commands and ?Parameter values into “SQL Input Window” or “Value Edit Area” of the ?Parameter Input dialog box. The SQL History Window shown in the figure below.

①

** The operations of the SQL History Window

SQL History Window displays SQL and ?Parameter values in a tree format.(*2)

SQL are added to the bottom of the tree in the order they are executed.

The following explains the operations of the SQL History Window.

[Paste Up] Button | Copies the selected SQL or ?Parameter value to “Input SQL Window” or “Value Edit Area” of the ?Parameter Input dialog box.
[Blanket Paste Up] Button | Collectively loads the ?Parameter values of the selected SQL into the ”Demand Parameter List” of the ?Parameter Input dialog box. If the selected SQL is not the same as the SQL to be executed, an error occurs.
[Delete] Button | Deletes the selected SQL from the history.(*3)
[Delete All] Button | Deletes the entire history.(*3)
[Save to File] Button | Saves the history to the file.(*1)
[Close] Button | Closes the SQL History Window.
[History Tree] （① in the above） | Displays SQL and ?Parameter values in a tree format.(*2) SQL are added to the bottom of the tree in the order they are executed.

*1: Saves the history to the file in an executable format.

*2: Line feed characters (0x0d0a) and tab characters included in SQL and ?Parameters are not displayed.

*3: You cannot perform this operation if the ?Parameter Input dialog box is displayed.

** Precautions

- Each time you press the “Tab” key, the control focus moves from the upper left to the lower right.

- The history is retained even if Executer is terminated.

** About the history log

<History log file>

- When Executer is started, the history that was saved when Executor was terminated last is read into the history log file.

- When Executer is terminated normally, the history is output to the history log file.

- The following files are created in the Executer installation directory.

For PDSQLW: _History.log

For PDSQLWU: _HistoryU.log

<Notes>

- As the size and history amount (number of SQL and pdsql commands + number of ?Parameters) of the history log file increases, it may take a long time to start and terminate Executer. If you want to shorten the Executer start/termination time, delete long SQL and pdsql commands and those that contain muliple ?Parameters from the SQL History Window before terminating Executer. Alternatively, delete the history log file before starting Executer.


Dictionary View Window

The Dictionary View Window and Dictionary Details Window display the dictionary information of the connected database and help users enter SQL.

The display content on the Dictionary View Window is shown in the figure below.

Information in the Dictionary View Window is displayed in the following hierarchical format:

Identifiers and the names is output not enclosed by double quotation marks.

Connect Data Base Name(#1)

|-Schema Name(#2)

| |-Table(#4)

| | |-Table identifier

| | | |-Index

| | | |-Index identifier

| | | |-...

| | |-...

| |-View

| | |-View Table identifier

| | |-...

| |-Index(#4)

| | |-Index identifier

| | |-...

| |-Sequence(#4)

| | |-Sequence identifier

| | |-...

| |-Procedure

| |-Routine identifier/ Trigger identifier

| |-...

|-RD Area

| |-RD Area Name

| |-...

|-User(#3)

| |-Identifier

| |-...

|-External Server

|-External Server Name

|-...

#1: If Executer is connected to HiRDB, a fixed character string ([HiRDB]) and version number (VV-RR format) are displayed. If Executer is connected to XDM/RD, a fixed character string ([XDM/RD]) and version number (VV-RR format) are displayed.

#2: The schema of the connection user and a schema with the table that a connection user was given an authorization by GRANT sentence or a view table are displayed. If you select the schema except the connection user, table given an authorization or a view table are displayed.

#3: When a connection user has DBA authority, a user and an outside server are displayed.

#4: Temporary table and Temporary index are not displayed.

When put a cursor on [table name] or [view table name], table comment are displayed.

Selecting an item displays the corresponding information in the Dictionary Details Window.

Double-clicking an item inserts the item name into the cursor position of the SQL Input window.

Right-clicking an item displays the following operations menu:

[Open Data(O)] | Searches the table and outputs the search result to the Output Window.(*2) (The menu becomes selectable when Table Name is selected.)
[Export(X)] | Displays the Search Result File Output dialog box
[Import(I)] | Displays the Input Parameters from File dialog box.
[Definition Source(S)] | Outputs the definition source of the selected object to the Output Window. (The menu becomes selectable when View or Procedure is selected.)
[Object Name Copy(C)] | Inserts the name of the selected object and item names into the cursor position of the SQL Input window.
[Refresh(R)] | Accesses the DB server and loads the latest information of the selected object into the display contents.

(*2)The row order is not guaranteed because sort specification is not applied to the search result.

The item names selected in the Dictionary View Window are displayed in the upper part of the Dictionary Details Window. The difference exists in information displayed with Dictionary Details Window by the item selected with Dictionary View Window. The item names selected in the Dictionary View Window displays the following menu.

Information displayed when [Table identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [Schema Name] [Table Name] [Table ID] [Comment] [Number of Composition Rows] [Total Length] [Create Time] [Number of Index Definition] [Kind of Table Identifier](*1) [Division Information](*1) [RD Area] [External Sever Name] [Table Option Information](*1) | Table definition information. Items are displayed as rows.
Columns | [ID] [Name] [Data Type](*1) [Length] (*1) [Other] (*1) [Comment] | Column information constituting the table.
Views | [Schema Name] [View Table Name] | List information of the view tables referencing the table.
Indexes | [Index ID] [Index Name] [Table Name] [Unique Type] (*2) [Array](*3) [Number of Composition Rows] | List information of the indexes referencing the table. (Same as the items of the INDEXES command) (*2)UNIQUE_TYPE column in the SQL_INDEXES table. (*3)When a repetition column is included: 'M', In other cases: ' '
Procedures | [Routine Name] [Specific Name] [Routine Type] (*4) [Number of Parameters] [Definition Source Size] [Create Time] | List information of the procedures, functions, and triggers referencing the table. (Same as the items of the ROUTINES command) (*4)Procedures or triggers: 'P', Functions: 'F'
Restrictions | [Restriction Information](*1) | List of restriction decision information and reference restriction information referencing the table.

Information displayed when [View Table identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [Schema Name] [Table Name] [Table ID] [Kind of Table Identifier](*1) | View table definition information. Items are displayed as rows.
Columns | [ID] [Name] [Data Type](*1) [Length] (*1) [Other](*1) | Column information constituting the view table.
Table References | [Schema Name] [Table Name] | List information of the table referenced by the view table.

Information displayed when [Index identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [Schema Name] [Table Name] [Index Name] [Index ID] [Number of Composition Rows] [Index Type](*1) [RD Area] [Plugin] [Index Option Information](*1) | Index definition information. Items are displayed as rows.
Composition Row | [Order of Index Definition] [Row ID] [Row Name] [Data Type] [Number of Array] [ASC/DESC](*5) | Row information constituting the index. (*5)Ascending order: ’ASC’, Descending order: ’DESC’

Information displayed when [Sequence identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [Schema Name] [Sequence identifier] [Sequence ID] [PUBLIC USAGE] [Data Type] [Start Value] [Maximum Value] [Minimum Value] [Increment] [Cycle Option] [Log Interval] [RD Area] | Index definition information. Items are displayed as rows.

Information displayed when [Routine identifier/ Trigger identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [Type](*6) [Schema Name] [Procedure] [Specific Name] [Number of Parameters] [Trigger Operation Opportunity](*6-2) [Trigger Opportunity] (*6-2) [Trigger Operation] (*6-2) | Procedure definition information. Items are displayed as rows. (*6)Procedure name: ’procedure’, Function name: ’function’, Trigger name: ’trigger (*6-2)This item is displayed when the selected item is a trigger. For the contents to be output, see the output format of the TABLEINF command.
Parameters | [Order of Specifying Parameter] [INOUT](*7) [Parameter Name] [Data Type](*7-2) [Data Length] | Procedure parameter information. (It is the same as the item of the PRPARAMS command.) (*7)Input: 'IN', Output: 'OUT’ ', Input/output: 'INOUT', Others: 'NONE' (*7-2)DATA_TYPE column of the SQL_ROUTINE_PARAMS table.

Information displayed when [RD Area Name] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [RD Area ID] [RD Area Name] [Server Name] [RD Area Type](*8) [Page Length] [Segment Size] [Number of Initial Allocation Segments] [Number of Increment Segments](*9) [Number of Files] [Number of Storage Tables] [Number of Storage Indexes] | RD area definition and usage information. Items are displayed as rows. (*8)RDAREA_TYPE column of the SQL_RDAREAS table. (*9) '*' is displayed when RD area extension is not specified.
Use | [Type](*10) [Schema Name] [Object Name] [Use Table Name] | Object information allocated to the RD area. （It is the same as the item of the RDAREAINF command.） (*10)Table: 'TABLE', Index: 'INDEX', Long data string: 'BLOB'. When [Type] is INDEX or BLOB, the original table name is displayed in [Use Table Name].

Information displayed when [Authorization Identifier] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [User ID] [DBA Authority](*11) [Schema Definition Authority](*12) [Schema Create Time] [Password Limitation Transgressor Type Code](*13) | Authorization identifier information. Items are displayed as rows. (It is the same as the item of the USERS command.) (*11) DBA_PRIVILEGE column of the SQL_USERS table. (*12) SCHEMA_PRIVILEGE column of the SQL_USERS table. (*13) PASSWORD_TEST column of the SQL_USERS table. If the connected server is not HiRDB 07-02 or later, space is displayed.

Information displayed when [External Server Name] is selected:

Tab displayed | Display items when the tab is selected | Remarks
Properties | [External Server ID] [External Server Name] [BES Name Accesed External Server] [Server Type](*14) [Server Version] [Number of Definition Tables] [User Name] | External server information. Items are displayed as rows. （It is the same as the item of the SERVERS command.） (*14) FOREIGN_SERVER_TYPE column of the SQL_FOREIGN_SERVERS table

Right-clicking an item displays the following operations menu:

[Copy(C)] | Copies the selection to the clipboard.
[Select All(A)] | Selects all the contents of the Grid.

(*1) Please refer to the output form of the TABLEINF command.


Find/Replacement Dialog

Selecting [Find(F)…] from the [Edit(E)] menu displays the modeless Find dialog box. Selecting [Replacement(L)…] from the [Edit(E)] menu displays the modal Replacement dialog box.

The modeless Find dialog box enables you to retrieve character strings in active windows. The modal Replacement dialog box enables you to replace character strings in active windows.

Character strings specified in "Find What” and “Replacement What” do not support regular expression.

The Find Dialog and the Replacement Dialog are shown in the figure below.

Details of the input item of the Window are shown below.

[Find what(N)] Check box:

Type in the character string to be searched. You can type in up to 32767 characters.

[Replacement what(E)] Check box:

Type in a character string to be replaced with the character string you typed in “Find what”.

You can type in up to 32767 characters.

[Match whole word only(W)] Check box:

If you select this check box, Executer searches for, or replaces, the whole words that match the character

string you typed in “Find what”.

[Match complete cell only (O)] Check box:

If you select this check box, Executer searches for only the cells that match the character string you typed in “Find what”.

You cannot select this check box if the SQL Input Window, [Result] tab of Output Window, or Dictionary Tree View Window is an active window.

[Match case(C)] Check box:

If you select this check box, Executer searches for, or replaces, the character string you typed in “Find what” in case-sensitive mode.

[Find Next(F)] Button:

Searches the area below the cursor position of the active window for the character string. When the corresponding character string is found, Executer highlights it.(*1)

[Close] Button:

Closes this dialog box.

[Replacement(R)] Button:

Searches the area below the cursor position of the active window for the character string. When the corresponding character string is found, Executer replaces it with the character string you typed in “Replacement what” and highlights the string.(*1)

[Replacement All(A)] Button:

  Searches the active window and replaces all the corresponding character strings with the character string you typed in “Replacement what”.(*1)

*1: If it cannot find the corresponding character string, Executer outputs the message “The search character string could not be found.” If you subsequently click the Find or Replacement button, the search/replacement processing will start again from the top.


Updating table data

You can execute the search SQL (SELECT statement) to edit the table data on the screen and update the database.

- Required application

- Database to be connected (destination database)

- Data types

- Update method

- Description of update operation control

- Example of update operation

- Checking input values

- Values inserted during the add operation

- Notes


Displaying table data in hexadecimal format

Display search result in hexadecimal format on the [Data] tab screen in the Output window.

(1) Target data types

The types of data that can be displayed in hexadecimal format are listed below.

- Numeric data (INTEGER, SMALLINT, DECIMAL, FLOAT, SMALLFLT)

- Character string data (CHAR, VARCHAR)

- National character data (NCHAR, NVARCHAR)

- Mixed character string data (MCHAR, MVARCHAR)

- Date data (DATE)

- Time data (TIME)

- Time-stamp data (TIMESTAMP)

- Date interval data (INTERVAL YEAR TO DAY, INTERVAL YEAR TO SECOND)

- Time interval data (INTERVAL HOUR TO SECOND)

The types of data that cannot be displayed in hexadecimal format are listed below.

- Row data (ROW)

- Large object data (BLOB, CLOB)

- Binary data (BINARY)

- Abstract data type

(2) Operation procedure

Select cells on the [Data] tab screen, and select [Display in hexadecimal (H)] in the right-click menu to display them in hexadecimal format.

Cells displayed in hexadecimal format have a light blue background.

To exit hexadecimal format display and return to normal format display, select [Return to normal display (N)] in the right-click menu.

When [Return to normal display (N)] is selected in the right-click menu, the items displayed in hexadecimal format return to normal format display.

Details of search result displayed in hexadecimal format:

- Character string format data displayed in hexadecimal format is aligned to the left in the cells.

- Null values are indicated with a blue asterisk (*).

- ROW/BINARY/BLOB/CLOB type data is not supported. Cells are always blank.

(3) Notes about [Display in hexadecimal (H)] and [Return to normal display (N)] in the right-click menu

The ability to change the display using [Display in hexadecimal (H)] or [Return to normal display (N)] in the right-click menu depends on the status of the selected cells. If multiple cells are selected, the operation is applied to each cell depending on its status. The table below shows the validity of the operations for each cell status. "Valid" indicates that the operation is applied to the cell, and "Invalid" indicates that it is not applied.

Validity/invalidity of [Display in hexadecimal (H)] and [Return to normal display (N)] operations in right-click menu

Operation | Status of selected cell
 | Edited | Contains data type that cannot be displayed | Hexadecimal format display | Normal display
[Display in hexadecimal (H)] | Invalid | Invalid | Invalid | Valid
[Return to normal display (N)] | Invalid | Invalid | Valid | Invalid

(4) Notes

(a) Cells cannot be displayed in hexadecimal format if they are in a row with table data that is being edited.

(b) Data cannot be edited in rows that contain cells that are displayed in hexadecimal format.

(c) When [Export display data] is selected in the right-click menu, the cells displayed in hexadecimal format are output to the file in character string format, with the hexadecimal format data enclosed in quotation marks (").

(d) SJIS code values are displayed for CHAR/VARCHAR/NCHAR/NVARCHAR/MCHAR/MVARCHAR type data when using the SJIS version of HiRDB SQL Executer for GUI. For the Unicode version, the Unicode code values are displayed.

(e) The [Paste (P)] operation is not available for cells that are displayed in hexadecimal format.


SQL That Can Be Executed

** Specifying a SQL or pdsql command from the SQL Input window

In the SQL Input window, you can execute only one SQL or pdsql command at a time. Each SQL or pdsql command requires a semicolon (;) at the end of the line. You cannot execute several SQL or pdsql commands at a time.

The length of one line must be 32760 (about 32K) characters or less.

The length of one SQL or pdsql command must also be within the size specified in the Environmental dialog box.

** Specifying SQL or pdsql commands from the file

You can execute several SQL or pdsql commands at a time from the file. You can also write one SQL or pdsql command in several lines. For SQL or pdsql command execution from the file, each SQL or pdsql command requires a semicolon (;) at the end of the line. If a semicolon is missing from the end of the line, the line is assumed to continue to the next line. The length of each line must be less than or equal to 32760 characters (about 32 KB). The length of each SQL or pdsql command must be less than or equal to 2146959360 characters (about 2 GB).

<Example of specifying SQL or pdsql command in the file>

SELECT C1,C2 FROM T1;

SELECT C3,C4

FROM T2;

** SQL that can be executed by this program

For information about SQL syntax, see the manual "HiRDB SQL Reference".

In the XDM/RD E2 connection status, the executable SQL are different.

For more details, see "XDM/RD E2 Connection”.

<Definition SQL>

SQL that can be executed |  | Description
Schema definition | CREATE SCHEMA | Defines a schema.
 | DROP SCHEMA | Drops a schema.
Table definition | ALTER TABLE | Alter table definition.
 | CREATE TABLE | Defines a table.
 | DROP TABLE | Drops a table.
View definition | CREATE [PUBLIC] VIEW | Defines a viewed table.
 | DROP [PUBLIC] VIEW | Drops a viewed table.
Index definition | ALTER INDEX | Alter index definition.
 | CREATE INDEX | Defines an index.
 | DROP INDEX | Drops an index.
Procedure definition | ALTER PROCEDURE | Alters a procedure.
 | CREATE PROCEDURE(#1)(#10) | Defines a procedure.
 | DROP PROCEDURE | Deletes a procedure.
Alias definition | CREATE ALIAS | Defines an alias.
 | DROP ALIAS | Drops an alias.
Altering function | ALTER ROUTINE | Alters a function or procedure.
Function definition | CREATE [PUBLIC] FUNCTION(#2) (#10) | Defines a function.
 | DROP [PUBLIC] FUNCTION | Drops a function.
Index type definition | CREATE INDEX TYPE | Defines an index type.
 | DROP INDEX TYPE | Drops an index type.
User-defined type definition | CREATE TYPE(#3) (#10) | Defines an abstract data type.
 | DROP DATA TYPE | Drops abstract data type, individual. type, or named line type.
Trigger definition | ALTER TRIGGER | Recreates a trigger SQL object.
 | CREATE TRIGGER(#4) (#10) | Defines a trigger.
 | DROP TRIGGER | Drops a trigger.
Security audit target definition | CREATE AUDIT | Defines the audit event to be registered as audit trial and the audit target.
Security audit target exclusion | DROP AUDIT | Excludes the defined audit target.
CONNECT-related security facility definition | CREATE CONNECTION SECURITY | Defines security items related to the CONNECT-related security facility.
CONNECT-related security facility deletion | DROP CONNECTION SECURITY | Deletes security items related to the CONNECT-related security facility.
Sequence definition | CREATE SEQUENCE | Defines a sequence.
 | DROP SEQUENCE | Drops a sequence.
DBA privilege | GRANT DBA | Grants DBA privilege.
 | REVOKE DBA | Revokes DBA privilege.
Schema definition privilege | GRANT SCHEMA | Grants schema definition privilege.
 | REVOKE SCHEMA | Revokes schema definition privilege.
CONNECT privilege | GRANT CONNECT | Grants CONNECT privilege.
 | REVOKE CONNECT | Revokes CONNECT privilege.
Access privilege | GRANT Access privilege | Grants access privilege.
 | REVOKE Access privilege | Revokes access privilege.
RDAREA usage privilege | GRANT RDAREA | Grants RDAREA usage privilege.
 | REVOKE RDAREA | Revokes RDAREA usage privilege.
Memory DB table definition | ALLOCATE MEMORY TABLE | Defines a memory DB table.
 | DEALLOCATE MEMORY TABLE | Drops a memory DB table.
Comment addition | COMMENT | Adds a comment to a table or column.

<Data manipulation SQL>

List creation | ASSIGN LIST | Creates a list.
Procedure call | CALL(#5) | Calls a procedure.
Row deletion | DELETE | Deletes a row.
Dropping list | DROP LIST | Drops a list.
Inserting row | INSERT | Inserts a row.
Deleting all rows | PURGE TABLE | Deletes all rows.
 | TRUNCATE TABLE |
Retrieval | SELECT(#6) | Retrieves table data.
Updating row | UPDATE | Updates table data.

<Control SQL>

Transaction termination | COMMIT | Terminates a transaction.
Table lock | LOCK | Locks a table.
Transaction rollback | ROLLBACK | Rolls back a transaction.
User change | SET SESSION AUTHORIZATION(#7) | Changes the connected user.

<Others>

Getting diagnostic information | GET DIAGNOSTICS(#8) | Gets diagnostic information.
Execute HiRDB operation command or utility command. | CALL COMMAND(#9) | Execute HiRDB operation command or utility command.

#1: When specifying CREATE PROCEDURE, add END_PROC to the end of a line.

(Example)

CREATE PROCEDURE P1() BEGIN INSERT INTO T1 (C1) VALUES(10);

UPDATE T1 SET C1 = 0 WHERE C1 < 10 ; END END_PROC;

#2: When specifying CREATE FUNCTION, add END_FUNC to the end of a line.

(Example)

CREATE FUNCTION FUNC1( NUM1 INTEGER,NUM2 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE SUM1 INTEGER;

SET SUM1 = NUM1 + NUM2;

RETURN SUM1;

END

END_FUNC;

#3: When specifying CREATE TYPE, add END_TYPE to the end of a line.

(Example)

CREATE TYPE TEL( NAME VARCHAR(15),TEL_NO INTEGER )

END_TYPE;

#4: When specifying CREATE TRIGGER, add END_TRIG to the end of a line.

(Example)

CREATE TRIGGER TRIG1

AFTER INSERT ON T0

FOR EACH ROW

INSERT INTO T1 VALUSE(10, “ABC”)

END_TRIG;

#5：If the argument of Procedure called by the CALL statement is an input parameter, you can specify a value expression or “?”. If the argument of Procedure is an output parameter or input/output parameter, specify “?” in the argument.

If “? Parameter Input Dialog” is displayed for the input or input/output parameter after you have entered the CALL statement, specify the corresponding value.

When the executed CALL statement terminates normally after you have entered the corresponding value in “? Parameter Input Dialog”, the result of the output or input/output parameter is displayed.

If the CALL statement terminates with the cursor for the results-set being opened in Procedure, the contents of the results-set are displayed.

(Example)

Procedure：

CREATE PROCEDURE ORDERED_EMPS(IN REGION INTEGER,OUT CNT INTEGER)

DYNAMIC RESULT SETS 2

BEGIN

DECLARE CUR1 CURSOR WITH RETURN

FOR SELECT id_no, name FROM emps_1

WHERE id_no < REGION ORDER BY id_no;

DECLARE CUR2 CURSOR WITH RETURN

FOR SELECT id_no, name FROM emps_2

WHERE id_no < REGION ORDER BY id_no;

OPEN CUR1;

OPEN CUR2;

SET CNT = 2;

END

END_PROC;

CALL Statement:

call ORDERED_EMPS(?,?);

  Execution result of CALL statement:

CNT

------------

2

KFPA12120-I Processing of SQL completed, dynamic result set returned

ID_NO NAME

------------ --------------------------------

3201 Mishima

4001 Shimizu

4201 Yoshida

4501 Iwata

5301 Kawasaki

5401 Matsuda

KFPA12121-I Processing of SQL completed, additional result set returned

ID_NO NAME

------------ --------------------------------

1301 Ibaragi

1901 Kagawa

2501 Matsue

KFPA12100-I No rows satisfying search condition

#6: Only dynamic SELECT statements can be specified. The cursor cannot be specified.

#7: When using the SET SESSION AUTHORIZATION statement, specify

the Authorization identifier directly in embedded variable 1, and Password directly in embedded variable2.

(Example) SET SESSION AUTHORIZATION USER0001 IDENTIFIED BY PASS0002;

#8: When using the GET DIAGNOSTICS statement, specify “?” in the embedded variable.

(Example) GET DIAGNOSTICS ?=NUMBER;

When specifying a condition number, also specify “?”.

If “?Parameter Input Dialog” is displayed after the GET DIAGNOSTICS statement has been executed, specify the corresponding value.

(Example) GET DIAGNOSTICS EXCEPTION ? ?=RETURNED_SQLCODE;

The following shows the condition information item names that can be specified by the GET DIAGNOSTICS statement.

If you specify other condition information item names, SQL Executer returns an error.

<condition information item name>

RETURNED_SQLCODE

ERROR_POSITION

ERROR_SQL_NO

ERROR_SQL

ROUTINE_TYPE

ROUTINE_SCHEMA

ROUTINE_NAME

TRIGGER_SCHEMA (Note1)

TRIGGER_NAME (Note1)

CONSTRAINT_SCHEMA (Note1)

CONSTRAINT_NAME (Note1)

MESSAGE_TEXT

QUERY_NAME

CONDITION_IDENTIFIER (Note1)

Note1: These are the new item names that can be specified in SQL Executer 02-07 and later.

#9: If the embedded variable when using the CALL COMMAND statement is

an input parameter, you can specify a value expression or ?.

If the embedded variable is an output parameter, specify ?.

Clauses where the embedded variable is an input parameter:

- CALL COMMAND

- WITH

- INPUT

- ENVIRONMENT

- SERVER

Clauses where the embedded variable is an output parameter:

- OUTPUT TO

- ERROR TO

- RETURN CODE TO

After executing the CALL COMMAND statement, response requests are

made for the number of input parameters to which ? was specified.

Therefore, you must specify a value that satisfies the response

requests.

After responding to all the input requests, output parameter results

are displayed when the CALL COMMAND statement is executed and

ends normally.

Note that the data type returned by the OUTPUT TO or ERROR TO clause

is BLOB(2G). Therefore, we recommend that you use the SET BLOB LIMIT

command to change the maximum length to an appropriate value and then

execute the statement.

(Example)

CALL COMMAND 'pddef' INPUT ?

OUTPUT TO ? RETURN CODE TO ?

ENVIRONMENT 'PDUSER=SAS1/SAS1';

#10: The GUI versions of SQL Executer 02-06 and earlier required a semicolon or space before the end of a compound statement(END_PROC,END_FUNC,END_TRIG,END_TYPE).

The GUI versions of SQL Executer 02-07 and later require a space or line feed. In these versions, a semicolon is arbitrary.


pdsql Commands That Can Be Executed

  After starting this program, you can enter not only SQL statements but also pdsql commands that simplify input and execution of SQL statements and specify an execution result format.

** pdsql commands that can be specified from this program

  For information about the pdsql commands that can be specified, see ”pdsql Command Usage” and ”pdsql_Command_List”.


pdsql Command Usage

<Syntax explanation>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Symbols Meaning

───────────────────────────────────

[ ] The items enclosed by this symbol can be omitted.

(Example) REPEAT [repetition-count]:

repetition-count can be omitted.

| The items delimited by this symbol can be selected.

{ } Specify one of the multiple symbols enclosed by this symbol.

(Example) SET AUTO COMMIT {ON | OFF}:

ON or OFF can be selected.

::= Indicates that the term on the left side of this symbol is defined as

the term on the right side.

Indicates the default that is assumed

(Underline) by the system when the operand is omitted.

(Example) CHANGE [ HOLD | NOHOLD ]:

NOHOLD is assumed.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

<Explanation of attributes>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Symbols Meaning

───────────────────────────────────

~ Those written after this symbol

indicate the attributes of a user-specified value.

<< >> Indicates the value assumed

when the user does not specify the appropriate item.

< > Indicates the member of a user-specified value.

( ) Indicates the range within

which the user can specify a value.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

<Explanation of members>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

members Meaning

──────────────────────────────────────────────

Alphabetic character Alphabetic (A to Z, a to z) and _ (underscore)

Alphabetic character and symbol Alphabetic (A to Z, a to z) and symbols (#, @, \)

Alphanumeric Alphabetic characters and numeric digits (0 to 9)

Alphanumeric and symbol Alphabetic characters, symbols,
and numeric digits (0 to 9)

Unsigned integer Numeric digits

Hexadecimal Numeric digits and letters (A to F, a to f)

Identifier Alphanumeric string beginning
with an alphabetical character

(For RDAREA, alphanumeric string includes a space.)

Symbol name Alphanumeric symbol string beginning

with an alphabetic symbol

Character string Character string

Path name Symbol names, /, and period (.), '(' and ')'

File name File name complying with the OS rules

File name "file-name", ""file-name"",

"path-name\file-name",

""path-name\file-name""

(If a space, '(', or ')' is included in the file name,

describe it enclosed in double quotations " ".

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


pdsql Command List

<Executable pdsql command from this program>

Item | Command | Outline of function
1 | BLOCK/ITERATE | Iterates a command(#1)
2 | CHANGE | Changes a character string(#1)
3 | COLUMNS | Displays table columns
4 | CONNECT | Connects HiRDB SQL Executer to HiRDB
5 | CONSTRAINTS | Lists the constraint names defined by the specified authorization identifier
6 | DESCRIPTORS | Displays abstract data type attributes
7 | DISCONNECT | DISCONNECTs HiRDB SQL Executer from HiRDB
8 | FILES | Displays RDAREA file information
9 | GETFILE | Input parameters from a file
10 | HELP | Displays help
11 | INDEXCLM | Displays index columns
12 | INDEXES | Displays indexes
13 | ITERATE | Terminates command iteration(#1)
14 | LOBS | Displays LOB RDAREA
15 | PRPARAMS | Displays parameters
16 | PUTFILE | Outputs the results to a file
17 | RDAREAINF | Displays RDAREA information
18 | RDAREAS | Displays RDAREA
19 | REPEAT | Executes a SQL repeatedly
20 | ROUTINES | Displays routines
21 | SEQINF | Displaying ordinal number creator information
22 | SEQUENCES | Displaying a list of ordinal number creators
23 | SERVERS | Lists the foreign servers owned by the specified authorization identifier
24 | SET ANALYSIS MODE | Execution of only SQL pre-processing
25 | SET ARRAY DISPLAY MODE | Switches repetition column information
26 | SET ARRAY LIMIT | Limits the maximum number of elements
27 | SET ARRAY NOVALUE | Sets display characters for elements without repetition column values
28 | SET AUTO COMMIT | Automatically issues COMMIT
29 | SET BINARY LIMIT | Limits the BINARY-type length
30 | SET BLOB LIMIT | Limits the BLOB-type length
31 | SET CHAR LIMIT | Limits the CHAR-type length
32 | SET CLOB LIMIT | Limits the CLOB-type length
33 | SET CMDBUF | Limits the output length of the server command execution result
34 | SET CONVSPACE | Converts control character codes to single-byte spaces
35 | SET DISPLAY | Changes the output format
36 | SET DUMPMODE | Displays the retrieval result in both the hexadecimal format and character format or only in the character format
37 | SET ECHOBACK | Echoes back a command
38 | SET FETCH ARRAY SIZE | Setting of the number of rows to be searched at one FETCH
39 | SET FETCH LIMIT | Restriction of the number of search target rows
40 | SET NCHAR LIMIT | Limits the NCHAR-type output length
41 | SET NULL CHARACTER | Sets the display character for the null value
42 | SET PADDING CHAR | Changes padding characters
43 | SET REMOVE LINEFEED CHARACTER | Deletion of linefeed character
44 | SET RESULT FILEOUT | Outputs the execution result both to the screen and a file
45 | SET SPLIT SIZE | Specify the split acquisition size of the BINARY/BLOB/CLOB type in kilobytes
46 | SET TBLMODE | Displays a table format
47 | SET WHENEVER | Exception control specification(#1)
48 | SINGLE ROW | Single-row SELECT statement
49 | SOURCE | Displays the definition source
50 | STATUS | Displays the operating status
51 | TABLEINF | Displays table information
52 | TABLES | Displays a table list
53 | TRIGGERS | Lists the triggers owned by the specified authorization identifier
54 | TYPES | Displays an abstract data type
55 | USERS | Displays a user list
56 | /* | Comment line(#1)
57 | : | Executes a server command

#1: These commands can be specified only when they are written in and executed from the file

<Notes>

  Some limitations are applied to the XDM/RD E2 connection. For more details, see also ”XDM/RD E2 Connection”.

  When SQL reserved word deletion function is used, there is a command not to be able to execute the function. Refer to “Effect of The SQL Reserved Word Deletion Function” together.


Cancelling a running SQL statement

Cancel the running SQL statement.

(1) Cancelling a running SQL statement

Press the Esc key while the SQL statement is running.

If cancellation succeeds, the dialog box shown below appears.

If cancellation fails, the SQL statement continues to run normally.

After cancellation succeeds, the HiRDB SQL Executer waits for the entry of SQL statements.

(2) SQL statements that cannot be cancelled

The following SQL statements cannot be cancelled even if the Esc key is pressed while the SQL statement is running.

- CREATE

- DROP

- ALTER

- GRANT

- REVOKE

- ALLOCATE

- DEALLOCATE

- COMMENT

- COMMIT

- ROLLBACK

(3) pdsql commands that cannot be cancelled

The following pdsql commands cannot be cancelled even if the Esc key is pressed while the pdsql command is running.

- RDAREAINF

- SEQINF

- TABLEINF

- SOURCE

(4) Note

(a) When cancel the running SQL statement, to roll back a transaction.

(b) When cancel the running SQL statement, KFPX27010-I message and executed results are difference.


Troubleshooting

When a failure occurs in SQL and in the pdsql commands that issue SQL to obtain information from the HiRDB server, use the HiRDB Client's troubleshooting features to investigate causes of the failure.

For more details about the HiRDB Client's troubleshooting features, see UAP Troubleshooting in the HiRDB Manual UAP Developer's Guide.


Initialization dialog box

From the start menu, select HiRDB SQL Executer and then Connection control setting. The initialization dialog box appears.

The initialization dialog box is shown in "Figure 1: Initialization dialog box."

Figure 1: Initialization dialog box

[Normal mode] radio button:

This radio button is selected by default.

If selected, the following character string appears in the Description field.

Normal mode:

We recommend that you select this mode if you updated the previous version and want to continue to use Executer.

[Enhanced security mode] radio button:

This radio button is not selected by default.

If selected, the following character string appears in the Description field.

Enhanced security mode:

We recommend that you select this mode unless you updated the previous version.

- The -u option cannot be used to specify passwords.

- The last used connection information is not kept."

[OK] button:

The connection control setting is configured, and then the program is ended.

[Cancel] button:

The program is ended without the connection control setting being configured.


Cautionary notes

(a) If the connection is disconnected, and if you try to execute an SQL statement without re-establishing the connection, the connection may be re-established automatically depending on the PDAUTOCONNECT client environment variable setting. In this case, the SQL statement is executed.

However, information which should be obtained normally if the connection is manually re-established may not be obtained. To avoid functional limitations, you need to disconnect the connection, re-establish it manually, and then execute the SQL statement.

If the disconnect button is not active, enter SQL (DISCONNECT;) directly into the SQL entry window to disconnect the connection.


Error Message

Meaning of The Message

Message List

Message Text

HiRDB Message


Meaning of The Message

** Format of message output

Messages are output to the “Execution Result Window” and “Message Dialog”.

(Example)

KFPX27000-E xx....xx

KFPX27000-E: Message ID

xx....xx: Message text

** Format of message description

[Message HELP format on HiRDB SQL Executer]

KFPX27nnn-i

Message text

Provides the meaning of the message.

(S): Indicates the action that this program takes for the output message.

(P): Indicates the action that the user takes for the output message.

nnn: Indicates a message number.

i: Indicates a message severity level.

E: Error message(Indicates the trouble that the function doesn't work occurred)

W: Warning message

I: Information message

(Indicates the simple operation situation that doesn't correspond to above-mentioned E and W.)

Q: Response waiting message

(Indicates to wait for the user's response to the output message.)

[Message HELP Format of HiRDB server]

KFPxnnnnn-i

Message-text （y）

Explains the meaning of the message.

(S): Indicates the treatment that HiRDB does after the message is output.

(P): Indicates the treatment that the programmer who receives the message takes.

(O): Indicates the treatment that the operator who receives the message takes.

[Measures] Indicates the treatment that the manager of HiRDB that receives the message takes.

*: "Please contact the maintenance member." used in the message shows the contact to the employee of our company or our business charge post.

x: It is a code in which the management origin of the message is identified.

A: Message of SQL statement

B: Message related to distributed database

C: Peculiar message to Windows NT HiRDB

D: Message of dictionary server

H: Message related to database

I: Message of HiRDB filesystem

J: Message related to JDBC driver

K: Message related to utility (Statistical analysis or state analysis)

L: Message related to utility (Creation or reorganization)

M: Message related to MIB

N: Message related to utility (Optimizing)

O: Message of OS library

R: Message related to utility (Recovery)

S: Message of system manager

T: Message of online reorganization that can be updated

U: Message related to utility (Common)

X: Message related to utility (Environment definition)

Y: Message related to plug-in

Z: Message of client

nnnnn: Indicates the message number.

i: Indicates the importance degree of message.

E: Error message (Indicates the trouble that the function doesn't work occurred)

W: Alert message

(Indicates the warning of usage condition etc. of resource. Or indicates to assume the value and to continue processing though a specified mistake of the command was found.)

I: Information message

(Indicates the simple operation situation that doesn't correspond to above-mentioned E and W.)

Q: Response waiting message

(Indicates to wait for the user's response to the output message.)

y：Indicates the type at output destination of message.

When one message has the type at two or more output destination, the type with the possibility to be output ties by '+' and is described.

It becomes 'L' at the output to a standard output by Pdcat command and the real-time output function.

The KFPJ message is acquired by getMessage() of JDBC driver's SQLException object.

E: Standard error output

S: Standard output

L: Message log file and Windows NT Event log (*1)

R: Error log file

S: SQLCA (SQL Communication area)

D: Message dialog

J: Event database of JP1

*1: In the following cases, it is likely not to be output the message or to be output to the event log of the Windows NT of the host who has a message output former unit.

- At the failure of the unit with a single server or the system manager

- Before it reactivates after the message log server terminates abnormally or before reactivation

- Standby system


Message List

KFPX27001-I

KFPX27010-I

KFPX27011-E

KFPX27012-E

KFPX27013-E

KFPX27014-E

KFPX27015-E

KFPX27016-E

KFPX27017-E

KFPX27018-E

KFPX27021-E

KFPX27032-I

KFPX27033-I

KFPX27040-E

KFPX27041-E

KFPX27042-E

KFPX27043-E

KFPX27045-E

KFPX27047-E

KFPX27050-E

KFPX27051-E

KFPX27052-E

KFPX27053-E

KFPX27054-E

KFPX27055-E

KFPX27056-E

KFPX27057-E

KFPX27058-E

KFPX27059-E

KFPX27060-E

KFPX27062-E

KFPX27063-E

KFPX27064-E

KFPX27065-E

KFPX27066-E

KFPX27067-E

KFPX27068-E

KFPX27069-E

KFPX27070-E

KFPX27072-E

KFPX27073-E

KFPX27074-E

KFPX27075-E

KFPX27076-E

KFPX27077-E

KFPX27078-E

KFPX27079-E

KFPX27080-E

KFPX27086-E

KFPX27087-E

KFPX27090-E

KFPX27091-E

KFPX27092-I

KFPX27093-E

KFPX27094-E

KFPX27097-I

KFPX27098-I

   KFPX27804-I

   KFPX27805-I

   KFPX27806-I

   KFPX27807-I

   KFPX27808-I

   KFPX27809-I

   KFPX27810-I

   KFPX27811-I

   KFPX27821-W

   KFPX27822-W

KFPX27823-I

   KFPX27830-E

   KFPX27831-E

   KFPX27832-E

   KFPX27833-E

   KFPX27834-E

   KFPX27835-E

   KFPX27836-E

   KFPX27841-Q

   KFPX27842-Q

   KFPX27843-Q

   KFPX27844-E

   KFPX27845-E

   KFPX27851-E

   KFPX27852-E

   KFPX27853-E

   KFPX27854-E

   KFPX27861-I

   KFPX27891-E

KFPX27901-I

KFPX27903-W

KFPX27904-I

KFPX27905-E

KFPX27906-E

KFPX27907-E

KFPX27909-E

KFPX27910-E

KFPX27911-E

KFPX27912-E

KFPX27913-E

KFPX27914-E

KFPX27915-E

KFPX27916-E

KFPX27917-E

KFPX27918-E

KFPX27919-E

KFPX27921-E

            KFPX27922-E

KFPX27923-E

KFPX27924-I

KFPX27925-E


Message Text

KFPX27001-I
Processing of SQL completed
Execution of the SQL statement has been completed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27010-I
nnnn rows xxxx
SQL statement processing ended successfully and nnn data items were selected, updated, inserted, or deleted (xxxx).
xxxx : selected: nnn data items were selected.
updated: nnn data items were updated.
inserted: nnn data items were inserted.
deleted: nnn data items were deleted.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27011-E
PDSQLW2.EXE not found
PDSQLW2.EXE was not found.
(S): This application is ended.
(P): Install this program correctly.
KFPX27012-E
CODECONV.DLL not found
CODECONV.DLL was not found.
(S): This application is ended.
(P): Correctly install the code convert run time, and set the environment.
KFPX27013-E
KanjiLink XKP not found
KanjiLink XKP was not found.
(S): This application is ended.
(P): Correctly install KanjiLink XKP run time library, and set the environment.
KFPX27014-E
FILE I/O Error ( XXXXX )
FILE I/O was failed. (XXXXX)
(S): This application is ended.
(P): Execute FILE I/O again after removing the error factor.
XXXXX : Error code
KFPX27015-E
Shared Memory access Error ( XXXXX )
Shared Memory processing was failed. ( XXXXX )
(S): When the problem is occured while processing the start, this application is ended.
In other case, wait for the input of the next SQL or pdsql command.
(P): Secure an enough memory, and start again or re-execute it.
XXXXX : Error code
KFPX27016-E
Error occured on Code Convert Process ( RetCode=XXXXX, SysErr=YYYYY )
Code Convert Run Time Process is wrong. ( code convert error code=XXXXX, error code=YYYYY )
(S): This application is ended.
(P): Review the Code Convert Run Time Process, and reinstall it if necessary.
KFPX27017-E
Invalid character code in Input data ( RetCode=XXXXX )
Invalid character code exists in Input data. ( code convert error code=XXXXX )
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct data and then re-execute.
KFPX27018-E
nnn rows convert is failed, Line no. = XXXXX, YYYYY, ZZZZZ, ...
nnn rows convert has been failed. Line no = XXXXX, YYYYY, ZZZZZ, ... .
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27021-E
More than 2097152 output length
The output length exceeds 2,097,152 bytes.
(S): Waits for the input of the next SQL or pdsql command.
(P): Correct the output length to 2,097,152 bytes or lower and then re-execute.
KFPX27032-I
1 character changed
One character string has been changed.
(S): If there are SQL waiting for execution, executes them as they are.
(P): If there are no SQL waiting for execution, enter the next SQL or pdsql command.
KFPX27033-I
n characters changed
n character string has been changed.
(S): If there are SQL waiting for execution, executes them as they are.
(P): If there are no SQL waiting for execution, enter the next SQL or pdsql command.
KFPX27040-E
Invalid xxxx
xxxx is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify xxxx correctly and then re-execute.
Cause and countermeasure when the variable section xxxx displays the password
when the SET SESSION AUTHORIZATION statement is used to make an attempt to switch to a user
whose password includes lower case letters and is 29 to 30 bytes long:
A client library that cannot connect when a password that includes lowercase letters and is longer than
28 bytes is being referenced.
Install a client library for which this connection problem has been corrected.
KFPX27041-E
Incomplete pdsql command
The command is incomplete.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify command correctly and then re-execute.
KFPX27042-E
More than nn
This value is greater than nn.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify a value less than nn and then re-execute.
KFPX27043-E
Not found before data
The pre-change character string does not exist.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct data and then re-execute.
KFPX27045-E
Number of variables invalid or number of parameters exceeds max parameters
The number of specified invalid variables or parameters exceeds the maximum number of parameters.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the number of valid variables or parameter so that it will not exceed the maximum number of parameters and then re-execute.
KFPX27047-E
Inconvertible data type of input variable
The data type of the entered variable cannot be converted.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the correct variable and then re-execute.
KFPX27050-E
More then 32,000 characters in character string literal
The specified character string exceeds 32,000 bytes.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify a character string of 32,000 bytes or less and then re-execute.
KFPX27051-E
Invalid floating point numeric literal
The specified floating-point numeric literal is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct value and then re-execute.
KFPX27052-E
More then 30 characters in kanji identifier
The kanji identifier exceeds 30 characters.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify 30 kanji characters or less and then re-execute.
KFPX27053-E
More then 255 characters in identifier
The identifier exceeds 255 characters.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify 255 characters or less and then re-execute.
KFPX27054-E
Invalid national character string literal
The specified national character string literal is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct national character string literal and then re-execute.
KFPX27055-E
more then 16,000 characters in national character string literal
The specified national character string literal exceeds 16,000 characters.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify a national character string literal of 16,000 characters or less and then re-execute.
KFPX27056-E
Invalid kanji identifier
The specified kanji identifier is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct kanji identifier and then re-execute.
KFPX27057-E
Numeric literal out of range
The specified numeric literal is out of range.
(S): Waits for the input of the next SQL or pdsql command.
(P): Change the numeric literal to a value within the range and then re-execute.
KFPX27058-E
Invalid numeric literal
The specified numeric literal is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the valid numeric literal and then re-execute.
KFPX27059-E
Input data too large for column
Input data exceeds the column definition length or the maximum number of repetition elements.
Alternatively, input data exceeds the range specified by the SET ARRAY LIMIT, SET BINARY LIMIT, SET BLOB LIMIT, or SET CHAR LIMIT command that changes the area size.
(S): Waits for the input of a SQL or pdsql command.
(P): Specify input data so that it will not exceed the column definition length or area size and then re-execute.
Alternatively, execute a command that changes the area size.
KFPX27060-E
Insufficient memory on PROCESS, size=aa..aa
aa..aa : Size of the area that was to be allocated (in bytes)
A memory shortage occurred on PROCESS.
(S): Waits for the input of the next SQL or pdsql command.
(P): Allocate sufficient memory and then re-execute.
If this memory shortage occurred in the SQL that uses the ? parameter, the parameter area is too large. In this case, change the SQL to a SQL that narrows down target columns or execute a command that changes the area size to reduce the parameter area.
In the retrieval SQL, the maximum length of the string data to be obtained may be too big. If the extracted string contains the BINARY/BLOB/CLOB type, execute the SET SPLIT SIZE command and set split and obtain.
KFPX27062-E
Invalid data type of operand
The data type of an invalid operand was specified.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the data type of the valid operand and then re-execute.
KFPX27063-E
Invalid precision or scale in scalar function
An invalid precision or scale was specified in the scalar function.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct value and then re-execute.
KFPX27064-E
Overflow in scalar function
The specified scalar function is out of range.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct scalar function within the range and then re-execute.
KFPX27065-E
Block/iterate cannot nest
The block statement cannot be specified in the Block/iterate statement.
(S): Waits for the input of the next SQL or pdsql command.
(P): Cancel the block statement specification and then re-execute.
KFPX27066-E
Invalid character expression for data type date
The character expression specified as the date attribute is invalid.
(S): Waits for the input of a SQL or pdsql command.
(P): Specify the correct character expression and then re-execute.
KFPX27067-E
Invalid token of operand
The syntax (token) of the operand is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct syntax (token) and then re-execute.
KFPX27068-E
Not found variable identifier
The specified variable was not found.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct variable and then re-execute.
KFPX27069-E
More than 50 block items, Enter ITERATE command
More than 50 SQL or pdsql command statements were specified in the Block/iterate statement.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the ITERATE command.
KFPX27070-E
Command "<" cannot nest
The "<" command cannot be specified in the read file.
(S): Skips the appropriate statement and reads the next statement.
(P): Eliminate the "<" command from the read file and then re-execute.
KFPX27072-E
Command error, Table not found
The specified table was not found.
(S): Waits for the input of the next SQL or pdsql command.
(P): Remove the cause of the error and then re-execute.
KFPX27073-E
System call error, func=aa..aa, errno=bbb
aa..aa: Name of the system call where the error occurred
bbb: Error number returned by the system call
An error occurred in the system call indicated by aa....aa.
(S): Waits for the input of a SQL or pdsql command.
(P): See the OS reference manual in which errno.h and the appropriate function are written based on the error number (errno: external variable indicating the error status), correct the cause of the error, and then re-executed.
KFPX27074-E
ioctl error, errno=xx
ioctl error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and ioctl are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27075-E
fdopen error, errno=xx
fdopen error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and fdopen are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27076-E
fclose error, errno=xx
fclose error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and fclose are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27077-E
fflush error, errno=xx
fflush error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and fflush are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27078-E
open error, errno=xx
open error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and open are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27079-E
fgets error, errno=xx
fgets error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): See the reference manual in which errno.h and fgets are written based on the error number (errno: external variable indicating the error status), remove the cause of the error, and then re-execute.
KFPX27080-E
Inconvertible data type in comparison predicate
An inconvertible data type was specified in the comparison predicate.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct data type and then re-execute.
KFPX27086-E
fputs error, errno=xx
fputs error. Detailed error code =xx
(S): Waits for the input of the next SQL or pdsql command.
(P): Based on the error number (errno: external variable indicating the error status), see the reference manuals for errno.h and fputs, eliminate the cause of the error, and then re-execute.
KFPX27087-E
Write error, errno=xx.
Write error. Detailed error code =xx.
(S): Waits for the input of the next SQL or pdsql command.
(P): Based on the error number (errno: external variable indicating the error status), see the reference manuals for errno.h and write, eliminate the cause of the error, and then re-execute.
KFPX27090-E
GUI MODE cannot "XXXXX" command
The command indicated by XXXXX cannot be used on GUI.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify this command in a file or specify another command and then re-execute.
KFPX27091-E
Not found ";" after command
A semicolon (;) was not found at the end of the SQL or pdsql command.
(S): Waits for the input of the next SQL or pdsql command.
(P): Add a semicolon (;) and then re-execute.
KFPX27092-I
Processing of SET XXXXX command completed
The SET XXXXX command (pdsql command) has been executed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command:
KFPX27093-E
GUI MODE cannot "?" parameter
The "?" parameter cannot be used on GUI.
(S): Waits for the input of a SQL or pdsql command.
(P): Execute the SQL or pdsql command without using the "?" parameter.
KFPX27094-E
Error occurred on HiRDB. SQLCODE = xx
An error occurred in HiRDB. SQL code = xx
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct data type and then re-execute.
KFPX27097-I
Auto commit ( XXX -> YYY )
The Auto Commit status changed from XXX to YYY.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27098-I
Auto rollback ( XXX -> YYY )
The Auto Rollback status changed from XXX to YYY.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27804-I
Editing not allowed: Table-joining or multiple queries are specified.
Table-joining or multiple queries are specified.
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27805-I
Editing not allowed: This is a system table.
A query of the system table is specified.
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27806-I
Editing not allowed: The number of output items is 256 or greater.
The number of search-result output items is 256 or greater.
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27807-I
Editing not allowed: This is a read-only view table.
A query of a read-only table is specified.
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27808-I
Editing not allowed: A repetition structure is included.
A repetition structure is specified for a search-result output item.
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27809-I
Editing not allowed: No editable items available.
The search-result output items do not contain editable items.
See "Notes" (h) through (j).
This output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27810-I
Editing not allowed: This is a table for which WITHOUT ROLLBACK is specified.
WITHOUT ROLLBACK is specified for the query table or query base table.
Because the update statement that the update function generates and executes cannot be rolled back, this output result cannot be edited.
(S): Waits for the input of the next SQL or pdsql command.
(P): If you want to update a view table or a base table for which WITHOUT ROLLBACK is specified, create your own update statement (UPDATE, DELETE, INSERT) statement and execute it.
If updating is not your objective, ignore this message.
KFPX27811-I
Editing not allowed: The client environment variable PDDBLOG is set to a value other than ALL.
The client environment variable PDDBLOG is set to a value other than ALL.
If an update interrupt or update error occurs with the update function, DB shutdown or DB corruption will occur.
In this condition, you cannot use the update function from the output result screen.
(S): Waits for the input of the next SQL or pdsql command.
(P): If you want to update, close SQL Executer, change the value of the client environment variable
PDDBLOG, and run SQL Executer again.
If updating is not your objective, ignore this message.
KFPX27821-W
This is a warning message that appears when you try to update from this search result screen. A unique index column does not exist in the search result. If the update target cannot be uniquely determined, the update attempt will be handled as an error. For details, see Help.
Because a unique index column does not exist in the search result, updating may not be possible.
If updating is not your objective, ignore this message.
- If updating is your objective
If you want to update data, we strongly recommend that you include a unique index column in the search column.
HiRDB SQL Executer uses the column information in the search results as update conditions.
Therefore, if the search result does not include a unique index column, and multiple rows of data match the update conditions, it is possible that rows that are not being edited will be updated.
In addition, compared to when a unique index column is available, the update process is slower.
Editing this search result is possible. However, if you click the update button and multiple rows of data that match the update conditions are found, an error dialog box will appear, and the update will be invalidated by rolling back.
Example:
SELECT WORKPLACE FROM EMPLOYEE_MASTER WHERE EMPLOYEE_ID='00000003'
In this example, HiRDB SQL Executer executes
UPDATE EMPLOYEE_MASTER SET WORKPLACE='...edited information...' WHERE WORKPLACE='...displayed result...'
You can check the index definition information by using the INDEXES command or the dictionary view window.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27822-W
Updating of multiple rows with the same data value has been suspended.
Update-transaction commitment has been suspended as specified by the user.
In this condition, you can continue editing other rows, but the following issues need to be considered when updating the data:
? When the data of the rows that you continue to edit is updated, the transaction will be completed by either committing or rolling back.
Check whether updating the rows with the same content and the same value is acceptable.
To perform this check, start another instance of Executer, and then search the target table by using the WITHOUT LOCK NOWAIT option.
Regardless of whether the update data value is to be applied or discarded, close the output window. A dialog box with the message "Update transaction is not complete. Update?" appears. To apply the update, click YES. Otherwise click No.
(S): Waits for the input of the next SQL or pdsql command.
(P): Either close the output window, or enter the next SQL or pdsql command.
KFPX27823-I
Editing not allowed: Check the row (red) (orange) where the update error occurred. Updating has been invalidated for rows (yellow) in which errors did not occur. To re-execute the update process, restart it from the search operation.
An update error occurred in one of the rows. The update has been rolled back. Because the updated row on the screen is inconsistent with the data in the database, you cannot edit this result.
To re-execute the update process, restart it from the search operation.
(S): Waits for the input of the next SQL or pdsql command.
(P): Either close the output window, or enter the next SQL or pdsql command.
KFPX27830-E
An uneditable column is selected.
A menu operation [Set null value] has been executed with an uneditable column included in the select range.
Null values cannot be assigned to uneditable columns.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
KFPX27831-E
A column with the NOT NULL attribute is selected.
A menu operation [Set null value] has been executed with a column that has the NOT NULL attribute included in the select range.
Null values cannot be assigned to columns with the NOT NULL attribute.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
KFPX27832-E
The copy-source size exceeds the copy-destination size.
If the content in the clipboard were to be pasted in the area from the upper-left to the lower-right of the selected range, the content size in the clipboard would exceed the number of rows or columns in the table. Contents cannot be pasted if they exceed the size of the table.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
KFPX27833-E
The copy destination contains a deleted row.
If the content in the clipboard were to be pasted in the area from the upper-left to the lower-right of the selected range, a row that is specified to be deleted would be included in the copy destination range. Contents cannot be pasted to rows that are specified to be deleted.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
Cancel the rows that are specified to be deleted, or adjust the copy-destination range so that such rows are not included.
KFPX27834-E
Pasting to multiple selected ranges is not supported.
A paste operation where the content in the clipboard was not a single value and multiple copy destination ranges existed was executed. Pasting to multiple selected ranges is not supported.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, perform either of the following:
- Either change the copy-source information, or change the selected range to one area and then execute the paste operation
- Either perform another operation, or enter an SQL statement or command.
KFPX27835-E
The copy destination is uneditable table data.
A paste operation was executed on a search result that is uneditable.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
KFPX27836-E
The paste destination rows include cells that are displayed in hexadecimal.
Rows that are displayed in hexadecimal cannot be edited.
If the content in the clipboard were to be pasted in the area from the upper-left to the lower-right of the selected range, a row that contains a cell represented in hexadecimal would be overwritten. Rows that are in hexadecimal representation cannot be edited.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
Either clear the hexadecimal representation, or adjust the copy-destination range so that such rows are not included.
KFPX27841-Q
You executed an update that would cause multiple rows in the database to be updated, even though only a single row was edited. Commit the transaction?(Number of edited rows: xxxx Number of rows to be updated: yyyy)
xxxx: The number of rows on the screen that the user edited
yyyy: The number of rows in the database whose values will be changed by the update statement.
You executed an update that would cause multiple rows in the database to be updated, even though only a single row was edited.
(S):Waits for the reply to dialog box.
(P):Select the [Yes] button or the [No] button.
If you click [Yes]: The transaction will be committed and completed.
If you click [No]: The transaction will be rolled back and completed.
KFPX27842-Q
Update data transaction is not complete. Commit the transaction?
[Yes]: The transaction will be committed. (Updating of update completed rows (yellow) will be committed by excluding the rows that resulted in errors (red).)
[No] The transaction will be rolled back. (The update operation, including update-complete rows (yellow), will be invalidated.)
[Cancel]: The operation will be canceled.
An update error occurred in one of the rows. Because [NOT rollback] is specified for the error action, the updated transaction is not yet committed. An attempt was made to execute another SQL statement or to complete the update operation without committing the data update transaction.
(S):Waits for the reply to dialog box.
(P):Select the [Yes] button or the [No] button or the [Cancel] button.
If you click [Yes]: The transaction will be committed and completed, and the operation will continue.
Updating of update-complete rows (yellow) will be committed by excluding the rows that resulted in errors (red).
If you click [No]: The transaction will be rolled back and completed, and the operation will continue.
The update operation, including update-complete rows (yellow) will be invalidated.
If you click [Cancel]: The execution of the other SQL statement or the completion of the update operation will be canceled.
The user must explicitly execute COMMIT or ROLLBACK to complete the transaction.
KFPX27843-Q
Discard the edited data?
You executed an operation that would reset the edited data on the screen. Examples of such an operation include executing another SQL statement and closing the window.
(S):Waits for the reply to dialog box.
(P):Select the [Yes] button or the [No] button.
If you click [Yes]: The edited data will be discarded, and the operation will continue.
If you click [No]: The operation will be canceled.
KFPX27844-E
An update error occurred.
An update error occurred in one or more rows.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
On the [Data] tab, rows with errors are indicated with a red background.
Check the SQL statement and message that are displayed on the [Result] tab, change or undo the editing of the value, and then click [Update] again. If there is a problem in the SQL statement, correct the search SQL statement, and then execute it again.
KFPX27845-E
Because the statement included an update that would cause multiple rows in the database to be updated even though only a single row was edited, the update was invalidated. Check the help information, and take appropriate measures.
The statement included an update that would cause multiple rows in the database to be updated even though only a single row was edited. Because the update target cannot be uniquely determined,an error was issued, and the update was invalidated by rolling back.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
The rows with an orange background on the [Data] tab are the multiple rows that qualify for updating in response to the editing of the single row, which prevent the update target
from being uniquely determined.
If message KFPX27821-W is displayed in the message area, follow the procedure for handling message KFPX27821-W.
If message KFPX27821-W is not displayed, check that the values displayed in the unique key column do not contain null values (the search result displays *****).
If null values exist, the update target cannot be uniquely determined because there are multiple null values in the unique key column, and therefore, updating is not possible.
Change the search column so that the update target can be uniquely determined, and execute the search SQL statement again.
Note that an SQL statement that would cause multiple rows in the database to be updated even though only a single row has been edited can be verified on the result tab.
KFPX27851-E
XXXX was not found. Check the path setting.
A prerequisite file for running HiRDB SQL Executer is missing.
XXXX:= the name of the prerequisite file
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, check that the prerequisite program product is properly installed.
KFPX27852-E
The search result cannot be displayed because there are too many columns.
Too many search columns were specified.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, narrow down the number of search columns, and then execute the SQL statement.
KFPX27853-E
An area for storing the data before the change cannot be allocated. The update function will be suppressed.
Data for the update function could not be stored due to insufficient memory. If this dialog box appears, you cannot edit the data on the screen.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, check that there is enough free memory, close all applications, then and run HiRDB SQL Executer again.
KFPX27854-E
Processing failed because there is too much data.
Unable to perform processing because the amount of applicable data is too large for the [Copy], [Paste], or other data operation.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, narrow down the amount of data, then and execute the operation again.
KFPX27861-I
The SQL statement in progress was canceled.
A cancel request was received while executing an SQL statement.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, enter the next SQL or pdsql command.
KFPX27891-E
The search string was not found.
The search string was not found in the [Search] operation.
(S):Waits for the reply to dialog box.
(P):Select the "OK" button.
After that, if you executed a search that should have returned results, check the search string or options, and then execute the [Search] operation again.
KFPX27901-I
Processing of XXXXX command completed
The XXXXX command (pdsql command) has been executed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27903-W
Invalid environment definition, variable= XXXXX
The environment variable XXXXX is invalid.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
Alternatively, stop the pdsql command, specify the correct value, and then re-execute.
KFPX27904-I
Execute server command, return code = xx
A server command was input.
xx: If this message was output together with the KFPZ02470-E or KFPZ02471-E message, it indicates the return code of the COMMAND EXECUTE statement. In all other cases, this message indicates the execution command return code of the COMMAND EXECUTE statement. In either case, see COMMAND EXECUTE in the manual "HiRDB SQL Reference."
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27905-E
Invalid HiRDB Client Library Installed
An invalid HiRDB client library is installed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Input the next SQL or pdsql command.
The following indicates the prerequisite version of the HiRDB client library for the function.
- Server command execution: Version 06-00 or later
- Display of result collection: Version 07-02 or later Confirm the following and take appropriate action if necessary.
- No old client library remains in the directory indicated by the environment variable PATH.
KFPX27906-E
Number of datas in input file not equal to questions, line = xx
line = xx
The number of ? parameters to be stored does not match the number of questions. Input data line=xx
(S): Processes the next data line.
(P): Correct the data of the appropriate line number in the input data file.
KFPX27907-E
Unable to specify XXXX operand with YYYY operand
The XXXX operand cannot be specified when the YYYY operand is specified.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the correct combination of operands and then re-execute.
KFPX27909-E
Command "QUIT" can be used only under BLOCK/ITERATE or ? paramete input exception
The "QUIT" command can be used only in the BLOCK statement or in ? parameter input.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27910-E
Sum of columns length exceeds 4GB
Retrieval results data length and input/output parameter length have exceeded 4 GB.
(S): Waits for the input of the next SQL or pdsql command.
(P): Change the length of the retrieval results data and input/output parameter equal to or less than 4GB and then re-execute the program.
In the search SQL, the maximum length of the string data to be obtained may be too big. If the extracted string contains the BINARY/BLOB/CLOB type, execute the SET SPLIT SIZE command and set split and obtain.
KFPX27911-E
Invalid data type of retrieval item XX
The data type of the XXth retrieval item is incorrect.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the following SQL or pdsql command:
KFPX27912-E
Invalid data type of "?" parameter XX
The data type of the XXth "?" parameter is incorrect.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the following SQL or pdsql command:
KFPX27913-E
Unicode files include invalid BOMs (Byte Order Mark).
BOMs are in the big endian mode.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify a file with the little endian mode and re-execute the program.
KFPX27914-E
Selected SQL not equal to the SQL to be executed
The selected SQL and the SQL to be executed do not match.
(S): Waits for the input of the "OK" button.
(P): Select the same SQL as the SQL to be executed.
KFPX27915-E
XXXX Error code=YY name=ZZZZ
AAAA
API type=XXXX Detailed error code=YY API name=ZZZZ Error description=AAAA
API type ::= { WIN32API | Method }
An error occurred with the API name.
(S): Suspends processing.
(P): Eliminate the cause of the error and then re-execute.
KFPX27916-E
More than 2000000 input length
Input of the SQL or pdsql command exceeds 2000000 characters.
(S): Suspends processing.
(P): Input a SQL or pdsql command of 2000000 characters or less.
KFPX27917-E
More than 32760 input line length
The input character string in one line exceeds 32760 characters.
(S): Suspends processing.
(P): Edit the input character string so that one line does not
contain more than 32760 characters.
KFPX27918-E
Invalid -u option on security mode
With a security enhancement mode, -u option is unjust.
(S): Exit this program.
(P): Review an argument at the time of the start, and, please carry it out.
KFPX27919-E
HiRDB SQL Executer not installed
There is an error of either next.
- HiRDB SQL Executer not installed
- Installation environment of HiRDB SQL Executer is injustice.
(S):Exits HiRDB SQL Executer.
(P):Reinstall HiRDB SQL Executer.
KFPX27921-E
No data in input data file
The input data file does not contain any data.
(S): Waits for the input of the next SQL or pdsql command.
(P): Specify the input data file that contains the data, and
then retry the operation.
KFPX27922-E
Unable to execute XXXX with YYYY mode
Command or SQL = XXXX Mode name = YYYY
Mode name ::= { analysis }
A command or SQL statement specified in YYYY mode cannot be executed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Check the condition and the command or the combination of SQL.
KFPX27923-E
Cannot XXXX command, YYYY
Command = XXXX Status = YYYY
The specified command in the status indicated by YYYY cannot be executed.
(S): Waits for the input of the next SQL or pdsql command.
(P): Check the condition and the command.
KFPX27924-I
Transaction XXXX by WHENEVER specification
commit or rollback = XXXX
The transaction was completed with the status indicated by XXXX according to the WHENEVER command specification.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.
KFPX27925-E
Violation at two or more rows update(XXXX rows) This information is output for verifying the SQL statement that caused an error. Using the output SQL as is would improperly update the database. Therefore, do not directly execute the output SQL.
XXXX: The number of rows that the SQL statement tried to update
Multiple rows qualified for updating in a single SQL statement.
(S): Waits for the input of the next SQL or pdsql command.
(P): Enter the next SQL or pdsql command.


HiRDB Message

Refer to manual "HiRDB message" of a pertinent version for the HiRDB version.

Refer to manual "HiRDB message" or "XDM E2 message (XDM/RD E2)" for the content of the message.


Input of The Data Which Used The ?Parameter

? Parameter Input

? Parameter Input Dialog

"*" Input Rule


? Parameter Input

？ Parameter Input

** In case of input from “? Parameter Input Dialog”

<Demand of value input>

If you input SQL with ? parameter from SQL Input Window, the program displayed ? Parameter Input Dialog and demand value input.

- The following explains the “Demand Parameter List”of each value contents.

Item

Contents

NO

Indicates the sequential number of the ? parameter in the SQL that the input request is for.

DATA TYPE

The data type returned by the server is displayed as ? parameter information.

Indicates the data type. For details, see "Table A-5. Data Code Value and Displayed Character String" in the description of the TABLEINF command.

Note that you cannot input or output a parameter of the abstract data type.

For a NOT NULL constraint column, this field indicates the NOT NULL qualifier.

If the data type is a repetition column, displays ARRAY[n] (n: repetition count) qualification representation.

For the ? parameter contained in the value expressing IS NULL, input SQLCODE = 0x01.

If the data type has not been set, this field shows "**".

VALUE

Indicates the ?parameter input value.

Immediately after The ? parameter Input dialog box is displayed, the previous value is set in VALUE.

<Input rules>

- For long data type (BLOB, CLOB) and binary type data, you can enter data with a character string constant, or in the hexadecimal-input or file-input format.

- To enter data from a repetition column, use ARRAY[] shown below.

CREATE TABLE T(C INTEGER ARRAY[3]);

INSERT INTO T VALUES(?);

(Display the “? Parameter Input Dialog”)

ARRAY[100,200,300] ...Input from Value edir area.

The formats can be specified in parentheses are "value input", "hexadecimal input", and "*".

** Inputting "Execute from file" in the GUI version

<Request for value input>

If you input SQL in which ? is specified, the following message is displayed in the search results window.

DATA ( 1) ? : DECIMAL(29,10) ARRAY[4] : VALUE(NULL)

↑ ↑ ↑

(1) (2) (3)

(1) Indicates the sequential number of the ? parameter in the SQL that the input request is for.

(2) Data type of the ? parameter

Indicates the data type.

If the type includes length or precision and scale, the value is enclosed in parentheses.

For a NOT NULL constraint column, this field indicates the NOT NULL qualifier. For a repeating column, this field indicates the ARRAY[n] (n is the repeat frequency) qualifier.

If the data type has not been set, this field shows "**".

(3) Indicates the setting if * was input.

If the data type is the character string type ([M | N][VAR]CHAR), large object data type (BLOB), BINARY type or CLOB type, this field displays a maximum of 16 bytes and truncates the following bytes.

If the data type is the large object data type (BLOB) or BINARY type, this field displays a value in hexadecimal.

(However, if the data length is 0 bytes in the large object data type (BLOB) or BINARY type, this field displays a double quotation mark (").

For a repeating column, this field displays only data of the first element.

<Input rules>

- No request is made for input to the ? parameter. Therefore, within the file, you must specify an input value after the SQL that specifies the ? parameter.

- To end input of the parameter values, input a semicolon at the end of the line to indicate a line-feed.

- For the large object data type (BLOB), BINARY type or CLOB type, you can input using a character string literal, the "hexadecimal input" format or the "file input" format.

- Empty sending causes an error.

- For input to a repeating column, use ARRAY[] as shown below.

CREATE TABLE T(C INTEGER ARRAY[3]);

INSERT INTO T VALUES(?);

ARRAY[100,200,300];

The formats you can specify within parentheses are "value input," "hexadecimal input" and "*".

- To abandon input of a parameter value and cancel the SQL that specifies the ? parameter, input QUIT.

** Value input format

The following explains the value input format of ?parameter.

- "Value input" format

- "Hexadecimal input" format

- ”FILE file-name” format

- "*" format

<"Value input" format>

There are several formats for inputting a value to the ? parameter:

- Integer literal

- Decimal literal

- Floating-point numeric literal

- Character string literal

- National character string literal

- Mixed character string literal

- NULL

For details on the above values, see the manual HiRDB SQL Reference.

[Notes]

- Represent the real part (integer part and fraction part) and exponent part of an integer literal, decimal literal, or floating-point numeric literal with up to 38 digits.

- If data is entered beyond the storable range (e.g., input of a fractional part for the integer type) when the data type is an integer numeric type (INTEGER, SMALLINT) or a fixed-point number type (DECIMAL, INTERVAL YEAR TO DAY, INTERVAL HOUR TO SECOND), an error occurs.

<"Hexadecimal input" format>

"Hexadecimal input" means a character string that begins with "0x" (or "0X") and is followed by several hexadecimal characters.

[Notes]

- This format cannot be used for the floating-point type (FLOAT, REAL).

[Example]

- If the number of input digits is odd, 0 is added to the beginning of data for processing.

Example） | Input: | 0x100
 | Processing Value: | 0x0100

- If input exceeds the defined length of each type, an error occurs.

Example） | Target data type: | CHAR(4) |
 | Input: | 0x413132333435 | ...An error occurs because input represents 6 bytes.

- If the data type is an integer numeric type (INTEGER, SMALLINT), input data (hexadecimal representation) is converted to decimal data and set.

Example） | Target data type: | INTEGER
 | Input: | 0x00001000
 | Setting: | 4096

- If the data type is a fixed-point type (DECIMAL,INTERVAL YEAR TO DAY,INTERVAL HOUR TO SECOND), input data is set as consecutive byte values.

The input format is a packed decimal format. Write input data tailored to the defined length.

Example） | Target data type: | DECIMAL(10,4) | …The defined length is 6 bytes.
 | Input: | 0x01234567891C |
 | Setting: | 123456.7891 |

- If the data type is other than the integer numeric type and fixed-point type, input data is set as consecutive byte values.

Example） | Target data type: | VARCHAR(16)
 | Input: | 0x41313233
 | Setting: | 'A123'

<”FILE file-name” format>

This format specifies input from the specified file.

[Notes]

- Consecutive bytes that make up the file contents (binary including control characters) become the input data.

- Specify "file-name" in an absolute pathname.

- If "file-name" includes a character other than the backslash (\) or a space character, enclose it in double quotation marks (").

(Example)

FILE "C:\TEMP\DA DC.TXT"

- If the specified file does not exist, an error occurs.

- This format is valid only for character string types ([M | N][VAR]CHAR), TIME type, DATE type, TIMESTAMP type, large object data type (BLOB, CLOB), and BINARY type.

<"*" format>

For details, see “"*" Input Rule”.


? Parameter Input Dialog

？ Parameter Input Dialog

If you executed SQL contains ? parameter on “SQL Input window”, the ? parameter Input dialog box is displayed.

If you set in input variables and push the “Execute“ button, SQL is executed.

** ？ Parameter Input Dialog

(**3)

(**2)

(**1)

- [Demand Parameter List] (**1 in the above)

Displays the demanded ?Parameter in a list format(#1). Selecting a row where you want to enter (edit) a value displays the current value (VALUE) in Value Edit Area.” A row where a value was changed is displayed in yellow. A row where an input value is erroneous is displayed in red. Immediately after The ? parameter Input dialog box is displayed, the previous value is set in VALUE. If the previous value does not exist, the default value is set in VALUE. For details on the previous value, see Appendix D, “*” Input Rules.”

- [Value Edit Area] (**2 in the above)

You can enter up to 2,000,000 characters of data.

This area is used to change the values in the row selected in “Demand Parameter List. “

Line feed characters (0x0d0a) and tab characters are also included as input values(#2).

Do not add a semicolon at the end of a value. For other input rules, see “? Parameter Input".

- [Error Display Area] (**3 in the above)

If an inut value is erroneous, the error parameter number and error message are displayed in this area.

Display of button | Outline of function of button
[Renew] | Reflects the “Value Edit Area“ value in “Demand Parameter List“ and displays the row in yellow.
[File] | Enables you to select the file you want to specify as an input value from the File Reference dialog box. The selected file is displayed in “Value Edit Area“ in the “FILE file-name“ format.
[Execute] | Accepts the values displayed in “Demand Parameter List“ as ?Parameter input data. If the SQL cannot be executed because an input value is erroneous, you must reedit the input value. The row whose the inupt value is erroneous is displayed in red. The contents of this error are displayed in “Error Display Area“.
[Cancel] | Cancels the SQL.
[History] | Displays the “SQL History“ window.

#1: The line feed characters (0x0d0a) and tab characters included in the ?Parameter value are not displayed.

#2: You can enter tab characters as values by pressing “Ctrl + Tab”.

Others:

- Each time you press the “Tab“ key, the control focus moves from the upper left to the lower right.

- During an attempt to reedit an erroneous input value, if you reedit an input value of a row other than the error row (red row), you may be unable to execute the SQL because the input value is considered erroneous. In this situation, click the “Cancel“ button to cancel the SQL and then reexecute the SQL.

** File Reference dialog

If you push the “File“ button, the dialog is displayed in the figure below.


"*" Input Rule

“*” Input Rule

** SQL input from the ? parameter input dialog box

<Input when executing from a file in the line mode version or GUI version>

  The previously entered value is used as the value displayed in the input request message only if all the following conditions are satisfied:

・The same SQL as the previous SQL is executed.

・The command that changes the transfer area size between the previous SQL (SET ARRAY LIMIT, SET BINARY LIMIT,SET BLOB LIMIT,SET CHAR LIMIT,SET NCHAR LIMIT) are not executed. In the following cases, however, the previously entered value (appropriate data) is invalid even if the same SQL as the previous SQL is executed (the value becomes undefined):

   ‐ Implicit conversion between numeric values is used.

  ‐ An error (error beginning with KFPX) occurred in SQL Executer.

  ‐ An invalid data type error occurs.

  ‐ The data entered as the retrieval condition value is longer than the defined length.

  In other cases, use the default shown in section 1.3 according to the data type definition.

<Input from the ? Parameter input dialog box>

  If you have specified an asterisk (*) as the input value and the same SQL as the SQL to be executed is in the SQL history (the latest one if there are multiple SQL), the input value used for that SQL becomes the input value. If the same SQL as the SQL to be executed does not exist in the SQL history, the default value shown the input value is displayed in below.

<default value>

Data type definition |  | Value
(When the NOT NULL constraint is missing) |  | NULL
With NOT NULL constraint |  |
 | INTEGER | 0
 | SMALLINT | 0
 | DECIMAL | 0
 | FLOAT | 0
 | SMALLFLT | 0
 | CHARACTER(n) | n-byte space
 | VARCHAR(n) | 1-byte space
 | NCHAR(n) | n-character space
 | NVARCHAR(n) | 1-character space
 | MCHAR(n) | n-byte space
 | MVARCHAR(n) | 1-byte space
 | DATE | 0001-01-01
 | TIME | 00:00:00
 | INTERVAL YEAR TO SECOND | 0 year 0 month 0 day 0 hour 0 minute 0 second
 | INTERVAL YEAR TO DAY | 0 year 0 month 0 day
 | INTERVAL HOUR TO SECOND | 0 hour 0 minute 0 second
 | BLOB(n) | Data whose length is 0
 | TIMESTAMP | 0001-01-01 00:00:00
 | BINARY(n) | Data whose length is 0
 | CLOB(n) | Data whose length is 0

** In case of the input of using GETFILE command

   In any cases, the default value became the input value is displayed in below.

Data type definition |  | Value
(When the NOT NULL constraint is missing) |  | NULL
With NOT NULL constraint |  |
 | INTEGER | 0
 | SMALLINT | 0
 | DECIMAL | 0
 | FLOAT | 0
 | SMALLFLT | 0
 | CHARACTER(n) | n-byte space
 | VARCHAR(n) | 1-byte space
 | NCHAR(n) | n-character space
 | NVARCHAR(n) | 1-character space
 | MCHAR(n) | n-byte space
 | MVARCHAR(n) | 1-byte space
 | DATE | 0001-01-01
 | TIME | 00:00:00
 | INTERVAL YEAR TO SECOND | 0 year 0 month 0 day 0 hour 0 minute 0 second
 | INTERVAL YEAR TO DAY | 0 year 0 month 0 day
 | INTERVAL HOUR TO SECOND | 0 hour 0 minute 0 second
 | BLOB(n) | Data whose length is 0
 | TIMESTAMP | 0001-01-01 00:00:00
 | BINARY(n) | Data whose length is 0
 | CLOB(n) | Data whose length is 0


XDM/RD E2 Connection

For details about the XDM/RD E2 data types and SQL description formats that can be used when you connect to XDM/RD E2, see Basic Items in the manual, HiRDB XDM/RD E2 Connection Features.

** Notes on Data Types

<INTERVAL YEAR TO SECOND type or BOOLEAN type>

・You cannot retrieval columns with these data types.

・You cannot use the ? parameters.

<CHAR type or MCHAR type>

・If you enter data that includes 2-byte characters into the ? parameter, XDM/RD E2 will execute character code conversion and extend the data length, resulting sometimes in an SQL error. In such cases, specify NONE for the SET PADDING CHAR command.

** Notes on the pdsql Commands

For the list of pdsql commands, please refer to “pdsql Command List”.

<Unusable commands>

- You cannot use the ": (Executes a server command)" command.

<Dictionary-retrieval-related commands>

- To use dictionary-retrieval-related commands, you need to define the "HiRDB compatible dictionary review".For details about the "HiRDB compatible dictionary review", see User's Manual for XDM E2 System XDM/RD E2, Operations.

Dictionary-retrieval-related commands are as follows:

- COLUMNS

- CONSTRAINTS(*1)

DESCRIPTORS

- FILES

- INDEXCLM

- INDEXES

- LOBS

- PRPARAMS

- RDAREAINF(*1)

- RDAREAS

- ROUTINES

- SERVERS

- SOURCE

- TABLEINF

- TABLES(*1)

- TRIGGERS(*1)

- TYPES(*1)

- USERS

*1: Assume that you use a schema name that contains nine or more characters. When the result of command execution displays the character string containing the schema name, it does not display the portion that exceeds the column width.

** Executable SQLs and End Specification for SQL Statements

SQL executable list for the XDM/RD E2 connection.

For details about the SQL syntax, see XDM E2 System XDM/RD E2 SQL References.

[Definition-related SQL]

Executable SQL |  | Description
Schema definition | CREATE SCHEMA | Defines a schema.
 | DROP SCHEMA | Drops the schema.
Table definition | CREATE TABLE | Defines a table.
 | DROP TABLE | Drops the table.
Table definition change | ALTER TABLE | Alter the table definition.
View definition | CREATE VIEW | Defines a view table.
 | DROP VIEW | Drops the view table.
Index definition | CREATE INDEX | Defines an index.
 | DROP INDEX | Drops the index.
Procedure definition | CREATE PROCEDURE(*1) | Defines procedure.
 | DROP PROCEDURE | Drops the procedure.
Procedure change | ALTER PROCEDURE | Alter the procedure.
Alias definition | CREATE ALIAS | Defines an alias.
 | DROP ALIAS | Drops the alias.
Function re-creation | ALTER FUNCTION | Alter a function.
Function definition | CREATE FUNCTION(*2) | Defines a function.
 | DROP FUNCTION | Drops the function.
Type definition | CREATE TYPE(*3) | Defines a type.
User-defined type deletion | DROP TYPE | Drops the abstract data type, individual type, and named-row type.
Trigger re-creation | ALTER TRIGGER | Alter a trigger SQL object.
Trigger definition | CREATE TRIGGER(*4) | Defines a trigger.
Trigger deletion | DROP TRRIGER | Drops the trigger.
Sequence generator definition change | ALTER SEQUENCE | Alter the sequence generator.
Sequence generator definition | CREATE SEQUENCE | Defines a sequence generator.
 | DROP SEQUENCE | Drops the sequence generator.
RD-node support definition | CREATE NONLOCAL TABLE | Defines support for the RD node and table.
 | DROP NONLOCAL TABLE | Drops the definitions supporting the RD-node and table.
Role definition | CREATE ROLE | Defines a role.
 | DROP ROLE | Drops the role.
User attribute change | ALTER USER | Alter user attributes.
Privilege definition Subsystem privilege | GRANT DBA | Grants the DBA privilege.
 | REVOKE DBA | Revokes the DBA privilege.
 | GRANT CONNECT | Grants the CONNECT privilege.
 | REVOKE CONNECT | Revokes the CONNECT privilege.
Privilege definition Scheme definition privilege | GRANT SCHEMA | Grants the DBA privilege.
 | REVOKE SCHEMA | Revokes the schema definition privilege.
Privilege definition Access privilege | GRANT access privilege | Grants the access privilege.
 | REVOKE access privilege | Revokes the access privilege.
Privilege definition RDAREA usage privilege | GRANT RDAREA | Grants the RDAREA usage privilege.
 | REVOKE RDAREA | Revokes the RDAREA usage privilege.
Privilege definition User-defined type privilege | GRANT user-defined type privilege | Grants the user-defined type privilege.
 | REVOKE user-defined type privilege | Revokes the user-defined type privilege.
Privilege definition Sequence generator privilege | GRANT sequence generator privilege | Grants the sequence generator privilege.
 | REVOKE sequence generator privilege | Revokes the sequence generator Privilege.
Privilege definition Role usage privilege | GRANT role usage privilege | Grants the role usage privilege.
 | REVOKE role usage privilege | Revokes the role usage privilege.
Comments addition | COMMENT | Adds comments to tables and columns.

[Operation-related SQL]

Executable SQL |  | Description
Row deletion | DELETE | Deletes rows.
Row insertion | INSERT | Inserts rows.
Deleting all rows | PURGE TABLE | Deletes all rows.
Retrieval | SELECT(*6) | Retrieve the table for data.
Row updating | UPDATE | Updates data in a table.

[Control-related SQL]

Executable SQL |  | Description
Transaction termination | COMMIT | Terminates the transaction.
Table lock | LOCK | Locks the table.
Transaction cancel | ROLLBACK | Cancels the transaction.

[SQL control statement]

Executable SQL |  | Description
Calling procedure | CALL(*5) | Calls procedure.

*1: When specifying CREATE PROCEDURE, add END_PROC to the end of a line.

(Example)

CREATE PROCEDURE P1()

BEGIN

INSERT INTO T1 (C1) VALUES(10) ;

UPDATE T1 SET C1 = 0 WHERE C1 < 10 ;

END

END_PROC;

*2: When specifying CREATE FUNCTION, add END_FUNC to the end of a line.

(Example)

CREATE FUNCTION FUNC1( NUM1 INTEGER,NUM2 INTEGER )

RETURNS INTEGER

BEGIN

DECLARE SUM1 INTEGER;

SET SUM1 = NUM1 + NUM2;

RETURN SUM1;

END

END_FUNC;

*3: When specifying CREATE TYPE, add END_TYPE to the end of a line.

(Example)

CREATE TYPE TEL( NAME VARCHAR(15),TEL_NO INTEGER )

END_TYPE;

*4: When specifying CREATE TRIGGER, add END_TRIG to the end of a line.

(Example)

CREATE TRIGGER TRIG1

AFTER INSERT ON T0

FOR EACH ROW

INSERT INTO T1 VALUSE(10, “ABC”)

END_TRIG;

*5: If the procedure called by the CALL statement has parameters, specify as many ?'s as the number of parameters in the procedure argument.

After the CALL statement is executed, requests for responses are made as many times as the number specified.

Specify the corresponding values.

(Example)

  Procedure:

    CREATE PROCEDURE BOUNUS_CNTS(IN VAL INTEGER,OUT POINT INTEGER)

      BEGIN

        SET POINT = VAL + 100;

      END

    END_PROC;

  Example of Call Statement execution：

    call BOUNUS_CNTS(?,?);

     DATA ( 1) ? : INTEGER : VALUE(NULL)

    ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+

    5000;

     POINT

    ------------

     5100

*6: You can specify only dynamic SELECT statement. You cannot set the cursor specification.

End specification of SQL statement>

  If there is semicolon (;) at the end of one line, it processes assuming to be SQL statement so much. If there is no semicolon, the line is treated assuming that it continues to the following line.

(Example)

CREATE TABLE T1(C1 INT, (Continue)

C2 CHAR(10), (Continue)

C3 CHAR(10))； (End of SQL statement)

・The length of one line should be within 65530 characters.


Differences between The SJIS Version and Unicode Version

** Prerequisites

You can use the SJIS version of the program only when the codes of character data passed to and from HiRDB Client are in SJIS or ASCII.

You can use the Unicode version of the program only when the codes of character data passed to and from HiRDB Client are in Unicode (UCS2).

The code of character data passed to and from the HiRDB Client is determined by the character codes of the HiRDB server and the value of environment variable PDCLTCNVMODE.

For more details, see the HiRDB manual, UAP Developer's Guide or HiRDB User's Guide, UCS2 Support in HiRDB Client.

** Usable Character Sets

You can use the character sets supported by the operating system (OS) with the coding method for either Shift JIS, ASCII or UCS2.

The SJIS version uses Shift JIS for the coding method if the Windows Codepage 932 character set is available.

The SJIS version uses ASCII for the coding method if the Windows Codepage 932 character set is unavailable.

You can also use external characters by registering them in the OS.

The Unicode version uses UCS2 for the coding method, making a wider range of characters available than that of the SJIS version. However, the Windows command prompt supports only Windows Codepage 932 as the means of displaying Japanese, and therefore, in the line mode version, you cannot display characters that are not included in the Windows Codepage 932 character set.

In the GUI version, you can display all Unicode characters supported by the OS.

*: Windows Codepage 932 character set is “JIS X 0201:1997 (Latin character, Katakana)

+ JIS X 0208:1997 + NEC special characters(Row 13) + NEC selection of IBM extensions(Rows 89 to 92)

+ IBM extensions(Rows 115 to 119)”.

** Input Text Files

In this document, following files are referred to as input text files.

- files specified in the Run from File menu of the GUI version of the program.

- files specified in the pdsql command "<" of the line mode version.

- files that re-direct standard inputs in the line mode version.

- files specified in the GETFILE command of the line mode version (the GUI version does not have the GETFILE command).

- files specified by "FILE Filename" for the character string type data in the ? parameter in the line mode version.

In the SJIS version, you need to use the Shift JIS or ASCII format for input text files.

In the Unicode version, you need to use the UCS2 format for input text files.

You need to use the little endian mode for the byte order.

The BOM (Byte Order Mark) that can be added to the top of a file is 0xFFFE.

The BOM is not necessarily required.

Use of the big endian BOM (0xFEFF) will result in an error.

** Output Text Files

In this document, the following files are referred to as output text files.

- files specified in the pdsql command ">" of the line mode version (the GUI version does not have the ">" command).

- output files in the FIX and DAT formats for the PUTFILE command of the GUI and line mode versions.

In the SJIS version, output text files take the Shift JIS or ASCII format.

In the Unicode version, output text files take the UCS2 format.

You need to use the little endian mode for the byte order.

The BOM (Byte Order Mark = 0xFFFE) is added to the top of a file.

** Notes

- You cannot use the Unicode version of the program in Windows Me.

- In the line mode version of the program with the Unicode version, when you re-direct the display contents for the command prompt to a file, the file will take the Shift JIS or ASCII format, not the UCS2 format.

- As for the character-string constant (character string that encloses it with single quotation) specified excluding SQL sentence (pdsql command etc.), the character-code becomes 32,000 bytes by SJIS at the SJIS version program, and the character-code becomes 32,000 bytes by Unicode(UTF-8) at the Unicode version program. Therefore, in case of the Unicode version program, it is likely to become an error even if the character-code is Unicode(UCS2) and within 32,000 bytes.


Effect of The SQL Reserved Word Deletion Function

Among the functions provided by the pdsql command and SQL Executer, there are some functions that issue SQL statements that use reserved words that the HiRDB server's SQL reserved word deletion function can delete.

In the table below, the commands/functions that you can no longer use due to the syntax error that occurs when you delete a reserved word.

Reserved word | Command/function that is no longer available
CASE | TABLEINF , TRIGGERS
ELSE | TABLEINF , TRIGGERS
END | TABLEINF , TRIGGERS
FREE | (SET SPLIT SIZE splits and obtains the large object data)
NOWAIT | COLUMNS , CONSTRAINTS , DESCRIPTORS , FILES , INDEXCLM , INDEXES , LOBS , PRPARAMS , RDAREAINF , RDAREAS , ROUTINES , SEQINF , SEQUENCES , SERVERS , SOURCE , TABLEINF , TABLES , TRIGGERS , TYPES , USERS
SUBSTR | COLUMNS , CONSTRAINTS , DESCRIPTORS , INDEXCLM , LOBS , PRPARAMS , ROUTINES , SEQUENCES , SERVERS , TABLEINF , TABLES , TRIGGERS , TYPES
THEN | TABLEINF , TRIGGERS
VALUE | COLUMNS , DESCRIPTORS , INDEXCLM , INDEXES , PRPARAMS , RDAREAS , SEQINF , SEQUENCES , TABLEINF , USERS
WHEN | TABLEINF , TRIGGERS


Example database

The basic configuration of the example database in this manual is as shown below.

Table name:GOODS

GOODS_CODE | GOODS_NAME | MAKER_CODE | MAKER_NAME | PRICE
CHAR(13) | VARCHAR(64) | CHAR(4) | VARCHAR(32) | DECIMAL(7,2)
F89S0000101BL | Blouse[Blue] | F89S | Ammy's | 35.00
F89S0000101YL | Blouse[Yellow] | F89S | Ammy's | 35.00
F52X0000101YL | Blouse[Yellow] | F52X | Laser | 32.00
F89S0000201WH | Polo shirt[White] | F89S | Ammy's | 36.40
F89S0000202RD | Polo shirt[Red] | F89S | Ammy's | 36.40
F89X0000203PU | Polo shirt[Purple] | F89X | Ammy's | 37.20
00000000204BK | Polo shirt[Black] | 0000 | Private's | 39.00
F89S0000302WH | Skirt[White] | F89S | Ammy's | 51.10
F89S0000353GR | Skirt[Green] | F89S | Ammy's | 47.60
F89X0000353GR | Skirt[Green] | F89X | Laser | 45.20
F89S0000353RD | Skirt[Red] | F89S | Ammy's | 47.60
F89S0000411BL | Sweater[Blue] | F89S | Ammy's | 84.00
F89S0000412RD | Sweater[Red] | F89S | Ammy's | 84.00
00000000413WH | Sweater[White] | 0000 | Private's | 68.00
F89S0000591WH | Socks[White] | F89S | Ammy's | 2.50
F89S0000591BL | Socks[Blue] | F89S | Ammy's | 2.50
F89S0000591RD | Socks[Red] | F89S | Ammy's | 2.50
00000000591BK | Socks[Black] | 0000 | Private's | 2.00

CREATE TABLE GOODS

(GOODS_CODE CHAR(13)

,GOODS_NAME VARCHAR(64)

,MAKER_CODE CHAR(4)

,MAKER_NAME VARCHAR(32)

,PRICE DECIMAL(7,2)

);

CREATE UNIQUE INDEX GOODS_UKEY ON GOODS(GOODS_CODE);

CREATE INDEX GOODS_OKEY1 ON GOODS(MAKER_CODE);

COMMENT ON COLUMN GOODS.GOODS_CODE IS 'the unique value of 13 digits ';

COMMENT ON COLUMN GOODS.MAKER_CODE IS 'an external key to maker information';

Table name:SHOP

SHOP_CODE | SHOP_NAME | ADDR | TELEPHONE
CHAR(6) | VARCHAR(40) | VARCHAR(64) | CHAR(16)
000001 | Ginza 1 cyoume | Cyuouku Ginza 1 , Tokyo | 03-xxxx-1234
000002 | Nanba Parks | Namihayaku Nanba 2, Osaka | 06-xxxx-1234
000003 | Tenjin | Cyuouku Tenjin 2, Fukuoka | 092-xxx-1234
000004 | Meieki | Nakamuraku Meieki 1, Nagoya, Aichi | 052-xxx-1234

TELEPHONE | KIND | OPEN_TIME | CLOSE_TIME
CHAR(16) | CHAR(8) | TIME | TIME
03-xxxx-1234 | DIRECT | 10:00:00 | 20:30:00
06-xxxx-1234 | FC | 10:00:00 | 20:00:00
092-xxx-1234 | OWNER | 09:30:00 | 19:00:00
052-xxx-1234 | DIRECT | 10:00:00 | 20:00:00

CREATE TABLE SHOP

(SHOP_CODE CHAR(6)

,SHOP_NAME VARCHAR(40)

,ADDR VARCHAR(64)

,TELEPHONE CHAR(16)

,"KIND" CHAR(8)

,"OPEN_TIME" TIME

,"CLOSE_TIME" TIME

);

CREATE UNIQUE INDEX SHOP_UKEY ON SHOP(SHOP_CODE);

Table name:STOCK

SHOP_CODE | GOODS_CODE | GOODS_NAME | SQUANTITY
CHAR(6) | CHAR(13) | VARCHAR(64) | SMALLINT
000001 | F89S0000101WH | Blouse[White] | 61
000001 | F52X0000101WH | Blouse[White] | 24
000001 | F89S0000101BL | Blouse[Blue] | 62
000001 | F89S0000101YL | Blouse[Yellow] | 15
000001 | F52X0000101YL | Blouse[Yellow] | 0
000001 | F89S0000201WH | Polo shirt[White] | 29
000001 | F89S0000202RD | Polo shirt[Red] | 67
000001 | F89X0000203PU | Polo shirt[Purple] | 34
000001 | 00000000204BK | Polo shirt[Black] | 20
000001 | F89S0000302WH | Skirt[White] | 65
000001 | F89S0000353GR | Skirt[Green] | 42
000001 | F89X0000353GR | Skirt[Green] | 14
000001 | F89S0000353RD | Skirt[Red] | 18
000001 | F89S0000411BL | Sweater[Blue] | 12
000001 | F89S0000412RD | Sweater[Red] | 22
000001 | 00000000413WH | Sweater[White] | 18
000001 | F89S0000591WH | Socks[White] | 280
000001 | F89S0000591BL | Socks[Blue] | 90
000001 | F89S0000591RD | Socks[Red] | 300
000001 | 00000000591BK | Socks[Black] | 164
000002 | F89S0000101WH | Blouse[White] | 17
000002 | F52X0000101WH | Blouse[White] | 38
000002 | F89S0000101BL | Blouse[Blue] | 21
000002 | F89S0000101YL | Blouse[Yellow] | 20
000002 | F52X0000101YL | Blouse[Yellow] | 27
000002 | F89S0000201WH | Polo shirt[White] | 30
000002 | F89S0000202RD | Polo shirt[Red] | 24
000002 | F89X0000203PU | Polo shirt[Purple] | 5
000002 | F89S0000302WH | Skirt[White] | 14
000002 | F89S0000353GR | Skirt[Green] | 34
000002 | F89X0000353GR | Skirt[Green] | 9
000002 | F89S0000353RD | Skirt[Red] | 20
000002 | F89S0000411BL | Sweater[Blue] | 18
000002 | F89S0000412RD | Sweater[Red] | 18
000002 | F89S0000591WH | Socks[White] | 350
000002 | F89S0000591RD | Socks[Red] | 48
.... | .... | .... | ....

CREATE TABLE STOCK

(SHOP_CODE CHAR(6)

,GOODS_CODE CHAR(13)

,GOODS_NAME VARCHAR(64)

,SQUANTITY SMALLINT

);

CREATE UNIQUE INDEX STOCK_UKEY ON STOCK(SHOP_CODE,GOODS_CODE);


BLOCK/ITERATE

[Function]

The BLOCK/ITERATE command repeatedly executes the SQL or pdsql command specified between BLOCK and ITERATE Command.

[Format 1]: Simple iteration

BLOCK

SQL or pdsql command

ITERATE [repetition-count]

Operands

** repetition-count ~<unsigned integer>（1 to 2147483647）<<1>>

Specifies the number of times the specified SQL or pdsql command is to be executed.

[Format 2]: Repetition using a variable

BLOCK variable-name = initial-value TO end-value [ BY incremental-value ] [ LENGTH length ] [ HEX ]

SQL or pdsql command

ITERATE

Operands

** variable-name ~<identifier>（1 to 8 characters）

Specifies the name used in the specified SQL or pdsql command as a variable. The value of the variable changes within the range from the initial value to the end value.

Naming convention:

- Must begin with a one-byte alphabetic character.

- Must consist of one-byte alphanumeric characters.

- Must not exceed 8 bytes.

- Must be case-sensitive.

- Must differ from a reserved word.

** initial-value ~<unsigned integer> (-2147483648 to 2147483647)

Specifies the initial value of the variable.

** end-value ~<unsigned integer> (-2147483648 to 2147483647)

Specifies the end value of the variable.

** incrimental-value ~<unsigned integer>

When the initial value is less than the end value (1 to 2147483647) <<1>>

When the initial value is more than the end value (-2147483648 to –1) <<-1>>

Specifies the incremental value of the variable.

** length ~<unsigned integer> (1 to 255) <<number of digits>>

Specify this operand when converting the number of digits of the variable to be replaced.

（Example）When LENGTH 3 is specified, the variable is replaced as 1->001, 2->002.

** HEX << decimal notation>>

Specify this operand when representing the variable to be replaced in hexadecimal notation.

(Example) The variable is replaced as 11->A, 20->14.

[Rules]

(1) It becomes the input wait state of the BLOCK/ITERATE statement when accept the input of the BLOCK statement.

In the input wait state of the BLOCK/ITERATE statement, SQL statement or pdsql command is not executed.

Please input ITERATE or QUIT to make normal input wait state from the input wait state of the BLOCK/ITERATE statement.

(2) In the input wait state of the BLOCK/ITERATE statement, Up to 50 SQL or pdsql command statements can be specified.

If input more than 51 statements, KFPX27069-E error is outputted.

(3) When accept the input ITERATE, the SQL or pdsql command in the input wait state of the BLOCK/ITERATE statement is execute repeatedly.

When input SQL or pdsql commands were more than 51 statements, between 1 and 50 statement is execute repeatedly.

After, becomes to normal input wait state.

(4) When accept the input QUIT, the SQL or pdsql command in the input wait state of the BLOCK/ITERATE statement is canceled and not executed.

Becomes to normal input wait state.

(5) When an error except KFPX27069-E occurs in the input wait state of the BLOCK/ITERATE statement, the inputted SQL or pdsql command is canceled and not executed.

Becomes to normal input wait state.

(6) If the same character string, as variable-name exists in a SQL or pdsql command, it is replaced by the value of the variable.

(7) The BLOCK statement cannot be nested (the BLOCK statement cannot be respecified in BLOCK/ITERATE).

[Notes]

(1) The number of times the specified SQL or pdsql command is executed is "end value - initial value + 1".

(2) Take sufficient care for a variable name in the phrase delimited by a space or symbol because it is replaced.

(See Example 2 below.)

(3) When appoint the SQL including the ? parameter in the input wait state of the BLOCK/ITERATE statement, ? parameter cannot appoint the data for the parameter just after SQL.

Just after ITERATE, please appoint the data of the number of times unit to repeat BLOCK and ITERATE.

BLOCK X=1 TO 50;

INSERT INTO T1 VALUES(?);

ITERATE;

1;

2;

3;

:

49;

50;

[Examples]

(1) The following example inserts data from 1 to 50 with the INSERT statement.

BLOCK X=1 TO 50;

INSERT INTO T1 (C1) VALUES(X);

ITERATE;

The execution result of this example is equivalent to the following execution result:

INSERT INTO T1 (C1) VALUES(1)

INSERT INTO T1 (C1) VALUES(2)

..

INSERT INTO T1 (C1) VALUES(50)

(2) The following example deletes T005DATA from table T001DATA.

BLOCK AX=1 TO 5 LENGTH 3;

DROP TABLE TAXDATA;

ITERATE;

The execution result of this example is equivalent to the following execution result:

DROP TABLE T001DATA;

DROP TABLE T002DATA;

..

DROP TABLE T005DATA;


CHANGE

[Function]

The CHANGE command changes the character string in the immediately preceding SQL and executes the SQL.

[Format]

CHANGE character-string-1 character-string-2 [ { HOLD | NOHOLD } ][ { DISPLAY | NODISPLAY } ]

Operands

** character-string-1 ~<character string> (1 to 255 characters)

Specifies a pre-change character string.

** character-string-2 ~<character string> (1 to 255 characters)

Specifies a post-change character string.

** HOLD | NOHOLD

HOLD Does not execute the SQL after the character string is changed.

NOHOLD Executes the SQL after the character string is changed.

** DISPLAY | NODISPLAY

DISPLAY Displays the SQL before and after the character string is changed.

NODISPLAY Does not display the SQL before and after the character string is changed.

[Notes]

(1) The SQL length cannot exceed 2 megabytes after the character string is changed.

(2) Enclose character-string-1 and character-string-2 in an apostrophe ('). To use an apostrophe as a character, specify two apostrophes continuously.

(Example) If the character string is A'BC, specify 'A''BC'.

(3) Character strings are case-sensitive.

[Examples]

Table (T2) is retrieved after table (T1) is retrieved.

SELECT * FROM USER1.T1;

The following execution result is displayed.

CHANGE 'T1' 'T2';

The following SQL is executed.

SELECT * FROM USER1.T2;


COLUMNS

[Function]

The COLUMNS command displays specified table columns.

[Format]

COLUMNS [[ RD-node-name [.] ] authorization-identifier [.] ] table-identifier [+COMMENT][+LIKE]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the table.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** table-identifier ~<table identifier> (1 to 30 characters)

Specifies the table identifier used to display column information.

** [+COMMENT]

Outputs the column comment.

If this operand is not specified, no column comment is output.

** [+LIKE]

Displays a list of columns in partially-matched tables by including special characters in the table identifier,.

If multiple tables match, the TABLE_NAME column is output, and the data is sorted in ascending order by using the TABLE_NAME column as the first key and the COLMNN_ID column as the second key.

The special characters include the underscore (_) and the percent sign (%), which have the following meanings:

_ (underscore) Any one character

% (percent sign) A character string of any length (0 or more characters)

When using the special character %, enclose the table identifier with quotation marks.

If this operand is not specified, a list of the table columns that exactly match the table identifier is displayed.

In versions 09-01 or earlier, the list of the columns of partially matched tables is always displayed.

[Example]

The following example displays the columns of table ZAIKO.

COLUMNS ZAIKO;

[Notes]

(1) When a string that starts with a plus sign (+)is specified as the operand,

if the character string following the plus sign is not COMMENT or LIKE, an error occurs.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | COLUMN_ID | 9 | Numeric | Column ID(#1)
2 | COLUMN_NAME | 30 | Character | Column name
3 | DATA_TYPE | 39 | Character | Data type(#2)
4 | DATA_LENGTH | 11 | Character | Data length(#3)
5 | MAX_ELM | 7 | Numeric | Number of repetition columns(#4)
6 | COMMENT | 255 | Character | Column comment(#5)
7 | TABLE_NAME | 30 | Character | Table identifier(#1)(#6)

#1: The results are sorted by this row in ascending order.

#2: If the data type is abstract data type, the data type identifier is displayed.

#3: If the data type is abstract data type, the data length is not displayed.

#4: The number of repetition columns is displayed only in HiRDB Version 5.0 05-03 or later.

#5: Output when the +COMMENT option is specified.

#6: Output when the +LIKE option is specified and multiple tables are matched.


/* (Annotation)

[Funcition]

The /* (Annotation) command makes the line a comment line.

[Format]

/* Annotation [ */ ]

[Notes]

-

This command targets the case where all of the one line is annotated.


CONNECT

[Function]

The CONNECT command connects HiRDB SQL Executer to HiRDB.

[Format]

CONNECT [authorization-identifier [/password] [host-name/port-number] ]

Operands

** authorization-identifier ~<authorization-identifier> (1 to 8 characters)

Specifies the authorization identifier used to connect HiRDB SQL Executer to HiRDB.

** password ~<password> (1 to 30 characters)

Specifies the password corresponding to the authorization identifier.

** host-name ~<host name> (1 to 255 characters)

Specifies the host name of the HiRDB system to be connected.

** port-number ~<unsigned integer> (1 to 2147483647)

Specifies the port number of the HiRDB system to be connected.

[Notes]

(1) If the operands are omitted, the CONNECT window appears after the CONNECT command has been entered.

The authorization identifier and password to be entered are converted to upper case.

When using lower case, enclose it in double quotation marks (").

(2) To connect HiRDB SQL Executer to HiRDB with another authorization identifier after the entering the CONNECT command, DISCONNECT it from HiRDB once with the DISCONNECT command and then specify the CONNECT command.

(3) If password is omitted, HiRDB SQL Executer is connected to HiRDB on the assumption that no password is specified.

[Reference]

DISCONNECT


CONSTRAINTS

[Function]

The CONSTRAINTS command lists the constraint names defined by the specified authorization identifier.

[Format]

CONSTRAINTS [ { authorization-identifier | ALL } ]

Operands

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specify the authorization identifier of the user who defines constraint names.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays all the defined constraints.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | CONSTRAINT_NAME | 39 | Character | Constraint name(#1)(#2)
2 | TABLE_NAME | 30 | Character | The table name of the table where the constraint name is defined
3 | TYPE | 4 | Character | Constraint type

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.


DESCRIPTORS

[Function]

The DESCRIPTORS command displays abstract data type attributes.

[Format]

DESCRIPTORS [[ RD-node-name [.] ] authorization-identifier [.] ] abstract-data-type-identifier

or DESCS

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the table.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** abstract-data-type-identifier ~<abstract data type identifier> (1 to 30 characters)

Specifies the abstract data type identifier used to display attribute information.

[Notes]

(1) This command can be used only in HiRDB Version 5.0 or later.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | POSITION | 8 | Numeric | Attribute ID(#1)
2 | OBJECT_NAME | 30 | Character | Attribute name
3 | DATA_TYPE | 39 | Character | Data type(#2)(#3)
4 | DATA_LENGTH | 11 | Character | Data length(#4)

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#3: If the data type is abstract data type, the data type identifier is displayed.

#4: If the data type is abstract data type, the data length is not displayed.


DISCONNECT

[Function]

The DISCONNECT command DISCONNECTs HiRDB SQL Executer from HiRDB after COMMIT has been issued.

[Format]

DISCONNECT

[Notes]

(1) HiRDB SQL Executer does not terminate even if it is DISCONNECTed by the DISCONNECT command.

To reconnect HiRDB SQL Executer to HiRDB, execute the CONNECT command. To exit HiRDB SQL Executer, from the [File] menu, choose [Exit].

(2) The DISCONNECT command issues COMMIT before DISCONNECTing HiRDB SQL Executer from HiRDB.

For this reason, to roll back a transaction, you must issue ROLLBACK in advance.

[Reference]

CONNECT


! ( Executes a DOS command )

ＤＯＳコマンドの実行

[機能]

  ! の後に指定したDOSコマンドを実行します。

[形式]

  ! DOSコマンド


EXIT

[Function]

COMMIT & DISCONNECTを発行後、本プログラムを終了します。

[Format]

EXIT

[Notes]

(1) 本プログラムの終了前に、COMMIT & DISCONNECTを発行します。そのため、トランザクションを無効にする場合、事前にROLLBACKを発行しておく必要があります。

(2) HiRDBに接続していない状態でEXITを投入した場合、本プログラムの終了だけを行います。

[Reference]

DISCONNECT


< ( Enters the SQL and dsql commands from a file )

< ( ファイルからのＳＱＬ、pdsqlコマンド入力 )

[機能]

  ファイルからＳＱＬ、pdsqlコマンドを読み込んで実行します。

[形式]

  < ファイル名

  <<

オペランド

● ファイル名 ～＜ファイル名＞

  実行するＳＱＬ、pdsqlコマンドを格納しているファイルのファイル名を指定します。

[規則]

(1) 指定したファイル中にEXITコマンドがあると、そこで入力を中断します。

  中断した続きから読み込んで実行する場合は、<< を入力してください。

(2) 読み込むファイル中に"<"コマンドを指定することはできません。

[参照]

  >


> ( Outputs to a file )

> ( 入力ＳＱＬ、pdsqlコマンドのファイル出力 )

[機能]

  入力したＳＱＬ、pdsqlコマンドをファイルに出力します。

[形式]

  > ファイル名

オペランド

● ファイル名 ～＜ファイル名＞

  入力したＳＱＬ、pdsqlコマンドを出力するファイル名を指定します。

[規則]

(1) ＳＱＬ、pdsqlコマンドの出力を終了するには再び > を入力してください。

[留意事項]

(1) コマンドの格納の終了のために最後に入力した > もファイルに格納されているので、コマンドを保存したファイルを入力に使用する場合は、> を削除してから使用してください。

(2) 読み込むファイル中に"<"コマンドを指定することはできません。

[参照]

  <


FILES

[Function]

The FILES command displays RDAREA structure files.

[Format]

FILES [[ RD-node-name [.] ] { RD-area-name | ALL } ]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** RD-area-name ~<identifier> (1 to 30 characters)

Specifies the name of the RD area where structure files are to be displayed.

** ALL

Displays all the defined structure files.

[Example]

The following example displays the structure files of RDAREA PDBUSER01.

FILES PDBUSER01;

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | RDAREA_ID | 12 | Character | RDAREA ID(#1)
2 | RDAREA_NAME | 30 | Character | RDAREA name
3 | SERVER | 8 | Character | Server name
4 | PHYSICAL_FILE_ID | 16 | Character | File ID(#1)
5 | PHYSICAL_FILE_NAME | 167 | Character | File name
6 | INITIAL_SIZE | 12 | Character | Number of initially allocated segments

#1: The results are sorted by the following rows.

- RDAREA_ID (ascending order)

- PHYSICAL_FILE_ID (ascending order)


GETFILE

[Funciton]

GETFILE command gets data corresponding to the ? parameter specified in the SQL statement from a file.

[Format]

GETFILE [COMMIT commit-interval]

[-k LOB-file-type [source-directory]]

[-mf file-name-for-the-message-output-location]

[-int] [-errstop]

[-cs {utf-8 | utf-16le}]

FROM input-data-file-name SQL-statement

Operands

** COMMIT commit-interval ~<unsigned integer> (0 to 2147483647)

Specify the interval at which COMMIT should be issued with a numeric value.

If a value other than 0 is specified, COMMIT is issued when the end of file is reached regardless of the AUTO COMMIT mode.

If 0 is specified, COMMIT is not issued until the end of file is reached.

Whether or not to issue COMMIT when the end of file is reached depends on the AUTO COMMIT mode.

If this operand is omitted, 0 is assumed.

** | [-k LOB-file-type [source-directory]]
 | When inputting column data of the BLOB/CLOB data column, you must specify the file type and source directory.
 | LOB-file-type: f
 | f: Reads data from files that have been created for each item of LOB data.
 | source-directory:
 | Specify a directory that contains an LOB data file.
 | If a relative path is specified for an LOB data file, the directory is referenced.
 | If the path is omitted, the same location as the input data file is assumed.
** | [-mf file-name-for-the-message-output-location]
 | Specify the file name of the file to which the message is output.
 | If the file name contains non-existent path, an error will occur when the message is output.
 | If a file already exists, the message is added to that file.
 | If the specified file name is same as the input data file name, an error occurs.
 | If this operand is specified, the message is output to both the window and file.
** | [-int]
 | If the input data is a decimal point value for a column of INTEGER/SMALLINT data, the value after the decimal point will be truncated.
 | If this operand is not specified, the operation is assumed to be an invalid data input error.
** | [-errstop]
 | If an invalid input data error or SQL execution error occurs, processing will be suspended.
 | If this operand is not specified, input data will continue processing until the end, even if an error occurs.
** | [-cs {utf-8 | utf-16le}] ~< -cs utf-16le >
 | Specify the character encoding for the input data file.
 | utf-8: UTF-8 character encoding is used for input.
 | utf-16le: UTF-16 (little-endian) character encoding is used for input.
 | This operand is valid only for the Unicode Version.
 | If this operand is specified on the SJIS Version, an error occurs.
 | If this operand is not specified, UTF-16 (little-endian) character encoding is used for input.
 | If this operand specification differs from the BOM in the input data file, the KFPX27913-E error occurs.
** | input-data-file-name ~<file-name>
 | Specifies the name of the file in which the data corresponding to the ? parameter is to be stored.
** | SQL-statement
 | - The following SQL statements can be specified. - INSERT statement - DELETE statement - UPDATE statement
 | - Specify ? parameters corresponding to the columns to which the data is inputted from the file.
 | - For details on ?parameter, see "?parameter Input" and “"*" Input Rule".
 | - For information about SQL syntax, see the manual HiRDB SQL Reference.

[Notes]

- he number of ? parameters dose not correspond the number of columns in the file, an error occurs and the next row is processed.

- 08-00 and later versions support large files exceeding 2GB.

This enables input from an input file even if its size is larger than 2GB.

- The maximum allowed length for GETFILE command is the length specified by an environment variable PDSQLBLOBLIMIT, PDSQLBINARYLIMIT or PDSQLCLOBLIMIT, or a pdsql command SET ARRAY LIMIT, SET BINARY LIMIT, SET BLOB LIMIT, SET CLOB LIMIT, SET CHAR LIMIT, SET FETCH LIMIT or SET NCHAR LIMIT. (If data exceeding the maximum allowed length is entered, message:KFPX27059-E is output.)

[Restrictions]

- "FILE file-name" value input formats for the ? parameter cannot be used. For details on "FILE file-name", see "Input of The Data Which Used The ?Parameter".

- Do not include non-printable characters (ASCII code 0x00-0x1F such as a null character) in the data.

[Input formats]

- Put data corresponding to the ? parameters into a row of the file. (Add a line feed character to the end of each line).

- Put a comma(,) between two columns data as column delimiter.

- One row consists of up to 2,097,152 bytes.

Table: Data input format for each data type

Data type | Column data input format(#1)
INTERVAL YEAR TO SECOND | - Specify in numeric value [-]yyyymmddhhmmss.[nnnnnn] format. - For minus, attach a symbol "-".
INTERVAL YEAR TO DAY | - Specify in numeric value [-]yyyymmdd. format. - For minus, attach a symbol "-".
INTERVAL HOUR TO SECOND | - Specify in numeric value[-]hhmmss. format. - For minus, attach a symbol "-".
DATE | - Specify in character string "yyyy-mm-dd" format.(#4)
TIME | - Specify in character string "hh:mm:ss" format. (#4)
TIMESTAMP | - Specify in character string "yyyy-mm-dd hh:mm:ss[.nnnnnn]" format.
BINARY MVARCHAR MCHAR NVARCHAR NCHAR VARCHAR CHAR | - Specify a character enclosed with double quotations (") - To include double quotations in the data, describe two consecutive double quotations.
FLOAT SMALLFLT DECIMAL INTEGER SMALLINT | - Specify a numeric value with characters. - For minus, attach a symbol "-". - Specify a decimal number in "integer.decimal" format. - Specify a floating point number in "mantissa + 'e' + exponent" format.
BLOB CLOB | (#3)

#1: When inputting a null value for a data type, the character string NULL, an asterisk, or a space can be specified.

If an asterisk or space is specified, one of the values described in "*" input rules will be stored.

#3: Depending on whether the -k option is specified, the data input will differ:

When -k option is not specified:

A value for the column data will be specified as the value for the ? parameter.

When the -k option is specified:

A value stored in the file indicated by the column data will be specified as the value for the ? parameter.

#4: When there is DATE type or no decimal designation of the TIME type, description without the double quote (") is possible.

[Example]

CREATE TABLE T1(C1 INT, C2 CHAR(10);

GETFILE FROM \tmp\data INSERT INTO T1 VALUES(?, ?);

Contents of the file \tmp\data

1000,"AAAAAAAAAA"

2000,"BBBBBBBBBB"

3000,NULL


HELP

[Function]

The HELP command displays help.

[Format]

HELP [ { pdsql command-name | pdsql } ]

or ?

Operands

** pdsqlcommand-name ~<identifier> (1 to 30 characters)

The explanation of the pdsql command is displayed.

** pdsql

The explanation of pdsql is displayed.

[Notes]

(1) When the operand is omitted, the command list is displayed.


INDEXCLM

[Function]

The INDEXCLM command displays columns of the specified index.

[Format]

INDEXCLM [[ RD-node-name [.] ] authorization-identifier [.] ] index-identifier

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the specified index.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** index-identifier ~<index identifier> (1 to 30 characters)

- Specifies the index identifier used to display column information.

- If the index is a cluster key, enclose it in a double quotation mark (").

Example: INDEXCLM "(CLUSTER0000131196)";

[Example]

The following example displays columns of index I1.

INDEXCLM I1;

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | INDEX_ORDER | 12 | Numeric | Order in which indexes are defined(#1)
2 | COLUMN_ID | 9 | Numeric | Column ID
3 | COLUMN_NAME | 30 | Character | Column name
4 | DATA_TYPE | 39 | Character | Data type(#2)
5 | DATA_LENGTH | 11 | Character | Data length
6 | MAX_ELM | 7 | Numeric | Number of repetition columns(#3)
7 | ASC_DESC | 8 | Character | ASC/DESC

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#3: The number of repetition columns is displayed only in HiRDB Version 5.0 05-03 or later.


INDEXES

[Function]

The INDEXES command displays the indexes of the user possessing the specified authorization identifier.

[Format]

INDEXES [[ RD-node-name [.] ] { authorization-identifier | ALL } ]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier>（1 to 8 characters）

Specifies the authorization identifier of the user possessing the indexes.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays all the defined indexes.

[Example]

The following example displays the indexes of the user whose authorization identifier is USER1.

INDEXES USER1;

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | INDEX_NAME | 39 | Character | Index name(#1)(#2)
2 | INDEX_ID | 12 | Numeric | Index ID
3 | TABLE_NAME | 30 | Character | Table identifier
4 | UNIQUE_TYPE | 11 | Character | Unique type
5 | ARRAY | 5 | Character | Repetition column (M)(#3)
6 | COLUMN | 6 | Numeric | Number of index structure columns

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#3: The number of repetition columns is displayed only in HiRDB Version 5.0 05-03 or later.


ITERATE

[Function]

The ITERATE command terminates SQL or pdsql command specification between BLOCK and ITERATE.

[Reference]

For details on this command, see BLOCK.


LOBS

[Function]

The LOBS command displays LOB RDAREA.

[Format]

LOBS [ RD-node-name ]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | RDAREA_NAME | 30 | Character | RDAREA name(#1)
2 | COLUMN_NAME | 70 | Character | Column name(#2)
3 | DIV_NO | 6 | Numeric | Order in which partitioning conditions are specified
4 | DATA_LENGTH | 11 | Character | Data length

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 92.


PRPARAMS

[Function]

The PRPARAMS command displays parameters for the specified procedure.

[Format]

PRPARAMS [[ RD-node-name [.] ] authorization-identifier [.] ] procedure-name

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier>（1 to 8 characters）

Specifies the authorization identifier of the user possessing the specified procedure.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** prpcedure-name ~<identifier>（1 to 30 characters）

Specifies the procedure name used to display parameter information.

[Example]

The following example displays parameters for procedure P1.

PRPARAMS P1;

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | NO | 6 | Numeric | Order in which parameters are specified(#1)
2 | MODE | 5 | Character | Parameter I/O mode
3 | PARAMETER_NAME | 30 | Character | Parameter name
4 | DATA_TYPE | 24 | Character | Data type
5 | DATA_LENGTH | 11 | Character | Data length

#1: The results are sorted by this row in ascending order.


PUTFILE

[Function]

The PUTFILE command outputs the retrieval result to a file.
- | DAT format for pdload
- | Extend DAT format for pdload
- | Binary format for pdload
- | Fixed-size data format for pdload

[Format]

PUTFILE | [ADD][COL_NAME]
 | [{DAT | EXTDAT | BINARY | FIX | SEPARATOR_TAB | BIN_PDRORG}]
 | [QOUT_OFF] [{LF | CRLF}] [SUP]
 | [DATE_TO_CHAR] [TIME_TO_CHAR] [TIMESTAMP_TO_CHAR]
 | [-q enclosing-character]
 | [-k LOB-file-type [storage-directory]]
 | [-s separator-character]
 | [-m message-output-interval]
 | [-mf message-output-file-name]
 | [-cs {utf-8 | utf-16be | utf-16le}]
 | TO file-name SELECT-statement

Operands

** | ADD
 | If the specified file already exists, data is added to the file.
 | The format check for the existing file is not performed.
 | If the specified file already exists and this operand is not specified, the file is overwritten (the existing information is erased and replaced).
** | COL_NAME
 | Outputs the extracted column name to the first row when the retrieval result is output in DAT format or extended DAT format.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG is specified for the output format, an error occurs.
** | {DAT | EXTDAT | BINARY | FIX | SEPARATOR_TAB | BIN_PDRORG} <<DAT>>
 | - | DAT
 |  | Specify DAT if you want to output the retrieval result in DAT format (column delimiter: Comma).
 |  | Output files in this format can be used by input data files in DAT format in the database creation utility (pdload).
 | - | EXTDAT
 |  | Specify EXTDAT if you want to output the retrieval result in extended DAT format (column delimiter: Comma).
 |  | Output files in this format can be used by as input data files in extended DAT format in the database creation utility (pdload).
 | - | BINARY
 |  | Specify this operand when you output the retrieval result in BINARY format (for FIX table) for pdload.
 |  | Output files in this format can be used by as input data files in BINARY format (for FIX table) in the database creation utility (pdload).
 | - | FIX
 |  | Specify this operand when you output the retrieval result in fixed-length data format for pdload.
 |  | Output files in this format can be used by as input data files in fixed-length data format in the database creation utility (pdload).
 | - | SEPARATOR_TAB
 |  | Specify this operand when you output the retrieval result in DAT format for pdload (column delimiter: Tab).
 |  | Output files in this format can be used by input data files in DAT format in the database creation utility (pdload).
 | - | BIN_PDRORG
 |  | Outputs the retrieval result where each line begins with column length, column data offset in the binary format for pdload.
 |  | Output files in this format can be used by as input data files in binary format (for non-FIX table) in the database creation utility (pdload), but it can’t be used by as input data files in binary format (for FIX table) in the database creation utility.
** | QUOT_OFF
 | Specify QUOT_OFF if you do not want the output data to be enclosed in double quotations when the retrieval result is output in DAT or extended DAT format.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG is specified for the output format, an error occurs.
 | The target data types are:
 | - | CHAR
 | - | VARCHAR
 | - | MCHAR
 | - | MVARCHAR
 | - | NCHAR
 | - | NVARCHAR
 | - | BINARY
 | If this operand is omitted, data of above data types is output enclosed in double quotations.
** | {LF | CRLF} <<CRLF>>
 | Specify how to output the line feed to indicate the end of each retrieval row when the retrieval result is output in DAT format, extended DAT format or fixed-length data format.
 | Specify how to output the line feed to indicate the end of each retrieval row when the retrieval result is output in DAT format, extended DAT format or fixed-length data format.
 | LF |  | ：Outputs 0x0A as line feed code.
 | CRLF |  | ：Outputs 0x0D and 0x0A as line feed code (carriage return and line feed).
** | SUP
 | Specify SUP if you do not want consecutive spaces to be output after the column data when the retrieval result (column data of CHAR, NCHAR or MCHAR type) is output in DAT format or extended DAT format.
 | If this operand is specified when BINARY, FIX or BIN_PDRORG is specified for the output format, an error occurs.
 | Using this operand can reduce the amount of output data.
 | Where the meaning of “space” differs depending on the data type:
 | CHAR type, MCHAR type |  |  | ：Single-byte space
 | NCHAR type |  |  | ：Double-byte space
 | Output example：
 | Data type |  |  |  | ：MCHAR(32) | 32 characters in definition length
 | Inserted data |  |  |  | ：” Naka-Ku Yokohama “ | 20 characters data (3 rear one-byte space characters)
 | When the SUP operand is not specified |  |  |  | ：” Naka-Ku Yokohama “ | 15 rear one-byte space characters
 | When the SUP operand is specified |  |  |  | ：” Naka-Ku Yokohama” | A rear one-byte space doesn’t exist.
** | [DATE_TO_CHAR] [TIME_TO_CHAR] [TIMESTAMP_TO_CHAR]
 | Specify this operand to enclose the output data with double quotations when the retrieval result is output in DAT or extended DAT format.
 | If this operand is specified when BINARY, FIX or BIN_PDRORG is specified for the output format, an error occurs.
 | If DATE_TO_CHAR is specified, the target is DATE type data.
 | If TIME_TO_CHAR is specified, the target is TIME type data.
 | If TIMESTAMP_TO_CHAR is specified, the target is TIMESTAMP type data.
 | If this operand is omitted, data of the above data types is output without being enclosed in double quotations.
** | [-q enclosing-character] 0 to 9, a to z, A to Z, or 1-byte characters
 | Specify an enclosing character when outputting search results in DAT format or extended DAT format.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG has been specified as the output format, an error will occur.
 | If this operand is specified when the QUOT_OFF operand has been specified, an error will occur.
 | The enclosing character must be enclosed by single quotation marks (').
 | If a single quotation mark is specified as the enclosing character, add another single quotation mark.
 | If the backslash mark (\) is specified as the enclosing character, add another backslash mark.
 | If multiple characters are specified as enclosing characters, only the first character will be valid. The latter characters will be disregarded.
 | If a character that is also used as a separator is specified, an error will occur.
 | If nothing is specified, a double quotation mark (") will be assumed.
 | Examples:
 | When a percent sign (%) is specified as the enclosing character: -q '%'
 | When a single quotation mark (') is specified as the enclosing character: -q ''''
** | [-k LOB-file-type [storage-directory]]
 | When outputting column data of the BLOB/CLOB data column to a file that is not an output data file, you must specify the format and output location.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG has been specified as the output format, an error will occur.
 | LOB file-type:{f | c}
 | f: A file is created for each item of LOB data. The file name will made up of the LOB column name and a serial number.
 | c: A file is created containing multiple items of LOB data from the same LOB column. The file name is LOB-column-name.
 | If the LOB column name contains a lower-case character, the column name must be enclosed by parentheses (()).
 | If the same file name already exists, the file will be overwritten.
 | storage-directory:
 | Specify the directory to which the LOB data file is output.
 | If non-existent directory is specified, an error will occur when the file is output.
 | If nothing is specified, the same location as the output data file will be used.
** | [-s separator-character] 0 to 9, a to z, A to Z, single-byte characters, or tab
 | When outputting search results in DAT format or extended DAT format, a line separator must be specified.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG has been specified as the output format, an error will occur.
 | The separator character must be enclosed by single quotation marks (').
 | If a single quotation mark is specified as the separator character, add another single quotation mark.
 | If the backslash mark (\) is specified as the separator character, add another backslash mark.
 | To specify a tab as the separator character, specify '\t'.
 | If multiple separator characters are specified, only the first character is used as the separator character. The latter characters will be disregarded.
 | If the specified character is same as the enclosing character, an error will occur.
 | If nothing is specified, a comma is assumed.
** | [-m message-output-interval] Integer without signs (100 to 100000000)
 | Specify the output interval for historical messages.
 | The output interval is the number of output rows.
 | If nothing is specified, historical messages will not be output.
** | [-mf message-output-file-name]
 | Specify a file name to which historical messages are output.
 | If a file name that does not exist is specified, an error will occur when historical messages are output.
 | If a specified file name already exists, the message is added to that file.
 | If this operand is specified when the -m operand has not been specified, an error will occur.
 | If the specified file name is the same as the output data file, an error will occur.
 | If this operand is specified, historical messages are output to both window and file.
** | [-cs {utf-8 | utf-16be | utf-16le}]
 | Specify the character encoding for the output data file.
 | utf-8: UTF-8 character encoding is used for output.
 | utf-16be: UTF-16 (big-endian) character encoding is used for output.
 | utf-16le: UTF-16 (little-endian) character encoding is used for output.
 | This operand is valid only for the Unicode Version.
 | If this operand is specified on the SJIS Version, an error occurs.
 | If this operand is specified when BINARY, FIX, or BIN_PDRORG has been specified as the output format, an error will occur.
 | If this operand is not specified, UTF-16 (little-endian) character encoding is used for output.
 | The value that I cannot express outputs '#'.
 | If the column type is BINARY, BLOB and CLOB, this operand is not effect.
** | File-name ~<file name>
 | Specifies the name of the file in which the retrieval result is to be stored.
 | When include the pass which does not exist in a file name, it becomes the error in the output.
 | If a path name is specified, the retrieval result is output to the specified directory.
 | If no path name is specified, the retrieval result is output to the current directory.
** | SELECT-statement
 | For information about the syntax of the SELECT statement, see the manual HiRDB SQL Reference.

[Notes]

(1) In 02-04 or later, the program supports large file sizes over 2 GB, enabling retrieval results exceeding 2GB to be output to a file.
(2) The PUTFILE command outputs the retrieval results that are limited as specified by the following environment variables and the pdsql commands.

environment variables:

- PDSQLBLOBLIMIT

- PDSQLBINARYLIMIT

- PDSQLCLOBLIMIT

pdsql commands:

- SET ARRAY LIMIT

- SET BINARY LIMIT

- SET BLOB LIMIT

- SET CLOB LIMIT

- SET CHAR LIMIT

- SET FETCH LIMIT

- SET NCHAR LIMIT

[Output format]

**

<DAT format for pdload><extend DAT format for pdload>

**

<BINARY format for pdload>

** | <BINARY format (for non-FIX table) for pdload>
 | - | This format is the format in which the database reorganization utility (pdrorg) unloads a non-FIX table. For more details about output formats, see HiRDB Command References.
 | - | You cannot use the database creation utility pdload to load the data of the file that is output using this data format into the FIX table.

**

<Fixed-length data format for pdload>


PUTFILE(BINARY)

<BINARY format for pdload(for FIX table)>

-

The format is for the case where FIX table is unloaded with the pdrorg command.

For more details about output formats, see HiRDB Command References.

-

When you use the database creation utility pdload to load a file that is output in this data format, specify the null value in the null value and function information file for the output value above.

Note that, in this case, if real data exists that has the same value as the output value of the null value, the data will also be loaded as the null value.

-

If ROW is specified as the selection expression in the SELECT statement, binary data is output.

When using row data obtained by ROW to migrate data, the endian and character code for the servers must be matched.

-

Table A-2 indicates the output values for when the data is a null value.

Note that if a search result contains a data type that is not listed in the table, an error will occur.

Table A-2 : Output value for when the data is a null value (binary (FIX table compatible) format)

Data type | Output value (when null value)
INTERVAL YEAR TO SECOND | 00000000000000.
INTERVAL YEAR TO DAY | 00000000.
INTERVAL HOUR TO SECOND | 000000.
DATE | 0001-01-01
TIME | (#2)
TIMESTAMP | 0001-01-01 00:00:00
BINARY | Data whose length is 0 byte
BLOB | Data whose length is 0 byte
MVARCHAR | 1-byte space
MCHAR | Space
NVARCHAR | 1-character space
NCHAR | Space
VARCHAR | 1-byte space
CHARACTER | Space
FLOAT | 0
SMALLFLT | 0
DECIMAL | 0
INTEGER | 0
SMALLINT | 0

#2: Depending on the server to which you connect,

output value will differ.

When connecting to HiRDB: 00:00:00

When connecting to XDM/RD E2:

- 8 bytes of empty space (If a digit grouping separator is

included, 9 bytes and the separator)


PUTFILE(DAT)

<DAT format for pdload><extend DAT format for pdload>
- | One row data is output for each retrieved row. (Line feed code is attached to the end of each row.)
- | Each column data is separated by a column delimiter.
- | One row consists of up to 2,097,152 bytes.
- | Data output to a file can be used as input data to pdload.
- | If the retrieved character data or BINARY type data includes unprintable characters (ASCII code 0x00-0x1F, such as a null character), the output differs depending on the output format.
 | DAT format | :Unprintable characters are converted to spaces and output.
 | Extended DAT format | :Unprintable characters are output without being converted.
- | If the retrieved character data or BINARY type data includes a character identical to the enclosing character (double quotation), the output differs depending on the output format.
 | DAT format | :The character is output without being converted.
 | Extended DAT format | :The character is output twice consecutively.
- | Since the enclosing character does not exist when QUOT_OFF is specified, the characters are output without being converted either in the DAT format and extend DAT format.
 | QOutput example when QUOT_OFF operand is not specified:
 | Data to store | :famous "Suzuki"
 | DAT format | :"famous "Suzuki""
 | Extended DAT format | :"famous ""Suzuki"""
- | Table A-1 shows the different formats for data output.
 | Note that if the search results contain a data type that is not listed in the table, an error will occur.

Table A-1 : Data output format for each data type

Data type | Column data output format | Null value
INTERVAL YEAR TO SECOND | - Column data is output in the [-]yyyymmddhhmmss. [nnnnnn] format (numerical value format). - A minus sign (-) is added to column data. - The fractional part depends on the data definition length. | (#1)
INTERVAL YEAR TO DAY | - Column data is output in the [-]yyyymmdd. format(numerical value format). - A minus sign (-) is added to column data. | (#1)
INTERVAL HOUR TO SECOND | - Column data is output in the [-]hhmmss.[nnnnnn] format (numerical value format). - A minus sign (-) is added to column data. - The fractional part depends on the data definition length. | (#1)
DATE | - Column data is output in the yyyy-mm-dd format (character string format). - If DATE_TO_CHAR operand is specified, data is output enclosed with double quotations. | (#1)
TIME | - Column data is output in the hh:mm:ss[.nnnnnn] format (character string format).(#4) - If TIME_TO_CHAR operand is specified, data is output enclosed with double quotations. - The fractional part depends on the data definition length. | (#5)
TIMESTAMP | - Column data is output in the yyyy-mm-dd hh:mm:ss[.nnnnnn] format (character string format). - If TIMESTAMP_TO_CHAR operand is specified, data is output enclosed with double quotations. - The fractional part depends on the data definition length. | (#1)
BINARY | Column data is output, enclosed in double quotation marks (").(#3) If the byte length is 0, column data is not output. | Not output
BLOB CLOB | (#6) | (#6)
MVARCHAR MCHAR NVARCHAR NCHAR VARCHAR CHARACTER | Column data is output, enclosed in double quotation marks (").(#3) If the byte length is 0, column data is not output. | Not output
FLOAT SMALLFLT DECIMAL INTEGER SMALLINT | - A numerical value is output as a decimal number character string. - A minus sign (-) is added to column data. - "Integer.decimal" is output as a decimal point. - The exponent (e) of mantissa is output as a floating point. | (#1)

#1: If ON is set in the PDUAPPTFNULL environment variable, no column data

is output.

If OFF is set in the PDUAPPTFNULL environment variable, * is output.

If the PDUAPPTFNULL environment variable is not set, * is output.

#3: If the QUOT_OFF operand is specified, the data is output without

enclosed characters.

#4: The data output differs by connecting server.

When connecting to HiRDB:

The data is output without enclosed characters.

To output data with enclosing characters, the TIME_TO_CHAR operand

must be specified.

When connecting to XDM/RD E2:

The data is output with enclosed characters.

To output data without enclosing characters, specify the

QUOT_OFF operand.

#5: Depending on the server to which you connect, the null value output

will differ.

When connecting to HiRDB: See #1).

When connecting to XDM/RD E2: Nothing is output.

#6: The data output will differ depending on whether the -k option is

specified:

When the -k option is not specified:

- If the data length is 0 bytes, no data is output.

- If the data length is at least 1 byte, a null value is output.

For details about how null values are output, see #1).

When the -k option is specified:

- A value other than a null value, a file name of an LOB data

file, or a file name of a LOB column data file is output.

- If the data is a null value, nothing is output.


PUTFILE(FIX)

<Fixed-length data format>
- | Data is output for each of retrieved lines (a line feed character is added to the end of each line).
- | There is no space between column data and column data.
- | One line cannot exceed 2,097,152 bytes.
- | Data output to files can be used as input data for pdload.
- | If ROW is specified as the selection expression in the SELECT statement, data is output as character data (CHAR type).
 | When using row data obtained by ROW to migrate data, the endian and character code for the servers must be matched.
- | Table A-3 indicates the output area size, and Table A-4 lists output data formats.
 | Note that if a search result contains a data type that is not listed in the table, an error will occur.

Table A-3 : Output area length (Fixed-length data format)

Data type | Output area length[bytes] (#1)
INTERVAL YEAR TO SECOND | 16+ scale
INTERVAL YEAR TO DAY | 10
INTERVAL HOUR TO SECOND | 8+ scale
DATE | 10
TIME | 8(If the scale greater than 0, 9+scale)
TIMESTAMP | 19(If the scale greater than 0, 20+scale)
BINARY | Maximum number of stored bytes.(#2)
BLOB | Maximum number of stored bytes.(#2)
MVARCHAR | Maximum number of stored bytes.(#2)
MCHAR | Number of stored bytes.(#2)
NVARCHAR | Maximum number of stored characters.*2(#2)
NCHAR | Number of stored characters.*2(#2)
VARCHAR | Maximum number of stored bytes.(#2)
CHARACTER | Number of stored bytes.(#2)
FLOAT | 23
SMALLFLT | 23
DECIMAL | precision+2
INTEGER | 11
SMALLINT | 6

#1: This table is not valid at the Unicode Version.

#2: To reduce the size, use the SET xxxx LIMIT command.

Table A-4 : Output data format (Fixed-length data format)

Data type

Data output format(#1)

INTERVAL YEAR TO SECOND

- Column data is output in the [-]yyyymmddhhmmss.[nnnnnn] format.

- A minus sign (-) is added to column data.

- The fractional part depends on the data definition length.

INTERVAL YEAR TO DAY

- Column data is output in the [-]yyyymmdd. format.

- A minus sign (-) is added to column data.

INTERVAL HOUR TO SECOND

- Column data is output in the [-]hhmmss.[nnnnnn] format.

- A minus sign (-) is added to column data.

- The fractional part depends on the data definition length.

DATE

Column data is output in the yyyy-mm-dd format.

TIME

- Column data is output in the hh:mm:ss[.nnnnnn] format.

- The fractional part depends on the data definition length.

TIMESTAMP

- Column data is output in the yyyy-mm-dd hh:mm:ss[.nnnnnn] format.

- The fractional part depends on the data definition length.

BINARY

BLOB

MVARCHAR

MCHAR

NVARCHAR

NCHAR

VARCHAR

CHARACTER

- Data is output in character format.

- Undisplayable characters (e.g., null characters (ASCII codes 0x00 to 0x1F)) in retrieved character data are converted to a colon (:) and output.

- If column data is a variable-length character string shorter than the defined length, it is padded with space characters (0x20) by the defined length.

FLOAT

SMALLFLT

DECIMAL

INTEGER

SMALLINT

- A right-align numerical value is output as a decimal number character string.

- A minus sign (-) is added to column data.

- "Integer.decimal" is output as a decimal point.

- The exponent (e) of mantissa is output as a floating point.

#1: If the data is a null value, spaces (0x20) equal to the output area size for the row are output.


RDAREAINF

[Function]

The RDAREAINF command displays information for the specified RDAREA.

[Format]

RDAREAINF [ RDAREA-name ]

Operands

** RDAREA-name ~<indentifier> (1 to 30 characters)

Specifies the RDAREA whose information you want to display.

[Output example]

TYPE USER NAME USED_TABLE

-------- ---------- ------------ ----------

aaaa bbbb cccc dddd

aaaa : Type of stored data (TABLE, INDEX, BLOB, SEQUENCE)

bbbb : Creator (authorization identifier)

cccc : Name

Table identifier when aaaa is TABLE,

Index identifier when aaaa is INDEX,

Column name when aaaa is BLOB,

Sequence identifier when aaaa is SEQUENCE

dddd : Original table identifier when aaaa is INDEX or BLOB


RDAREAS

[Function]

The RDAREAS command displays RDAREAS.

[Format]

RDAREAS [ RD-node-name ]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

[Output example]

RDAREA_ID RDAREA_NAME SERVER TYPE PAGE_SIZE ~

--------- ------------------------------ -------- ---- --------- ~

aaaa bb....bb cccccccc de fffff ~

~ SEGMENT_SIZE INITIAL_SIZE FILE_COUNT N_TABLE N_INDEX N_SEQ

~ ------------ ------------ ---------- ------- ------- ------

~ ggggg hhhhh iiiii jjjjj kkkkk lllll

aaaa : RDAREA ID(#1)

bb....bb : RDAREA name

cccccccc : Server name

d : RDAREA type(#3)

e : Attribute of RDAREA for temporary table(#3)(#4)

fffff : Page length

ggggg : Segment size

hhhhh : Number of initially allocated segments

iiiii : Number of files

jjjjj : Number of stored tables

kkkkk : Number of stored indexes

lllll : Number of stored sequences(#2)

#1: The results are sorted by this row in ascending order.

#2: This column is displayed when HiRDB V8 08-05 or later is connected.

#3: For details about the meaning of the displayed content, see "Appendix G. Data Dictionary Table Retrieval" and "Table G-3 SQL_RDAREAS table contents" in "HiRDB UAP Development Guide".

#4: When HiRDB V9 09-02 or earlier is connected, nothing is output.


REPEAT

[Function]

The REPEAT command re-executes the immediately preceding SQL.

[Format]

REPEAT [ repetition-count ] [ { DISPLAY | NODISPLAY } ]

Operands

** repetition-count ~<unsigned integer> (1 to 2147483647) <<1>>

Specifies the number of times the immediately preceding SQL is executed.

** DISPLAY | NODISPLAY

DISPLAY: Displays the SQL to be re-executed.

NODISPLAY: Does not display the SQL to be re-executed.

[Notes]

(1) This command cannot re-execute control SQLs such as COMMIT and ROLLBACK.

(2) If the RDAREAINF, SEQINF, SOURCE, or TABLEINF command to issue SQL was immediately executed before, the KFPX27923-E error occurs.

[Example]

The following example inserts the same data into table T1 by 100 lines with the INSERT statement.

INSERT INTO T1 (C1,C2) VALUES(1000,2000);

REPEAT 99;


ROUTINES

[Function]

The ROUTINES command displays the routines possessed by the user of the specified authorization identifier.

[Format]

ROUTINES [[ RD-node-name [.] ] { authorization-identifier | ALL } ]

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identier ~<authorization identier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the routines.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays all the defined routines.

[Example]

The following example displays the routines of the user whose authorization identifier is USER1.　　　ROUTINES P1;

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | ROUTINE_NAME | 39 | Character | Routine name(#1)(#2)
2 | SPECIFIC_NAME | 30 | Character | Specific name
3 | TYPE | 4 | Character | Routine type P : Procedure F : Function
4 | N_PARAM | 7 | Numeric | Number of parameters
5 | OBJECT_SIZE | 12 | Numeric | Defined source size (unit: bytes)(#3)
6 | CREATE_TIME | 14 | Character | Routine creation time(#4)

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#3: For the procedure for external routine operation or registry operation, the defined source size is 0.

#4: For the SQL procedure statement, it indicates SQL analysis time;

for the external routine, it indicates routine definition creation time.


SEQINF

[Function]

  Displays information about a specified ordinal number creator.

[Format]

  SEQINF [authorization-identifier[.]]

ordinal-number-creator-identifier

Operands

** authorization-identifier Authorization identifier (1 to 8 characters)

Specify the authorization identifier for the user that has the ordinal

number creator.

If the authorization identifier is not specified, an authorization

identifier for CONNECT is assumed.

** ordinal-number-creator-identifier Identifier (1 to 30 characters)

Specify the ordinal number creator whose information you want to

display.

[Notes]

(1) If unidentifiable data is acquired for the output items, either "?Unknown" is displayed or no value

is output for the item.

[Output format]

Sequence Schema : bb....bb .... Authorization identifier

Sequence Name : cc....cc .... Ordinal number creator identifier

Sequence ID : dddddddddd(dd....dd)

.... Ordinal number creator ID

The value is displayed as a hexadecimal

number (decimal number).

Public Usage : e .... Indicates whether PUBLIC USAGE is specified.

(Y: specified; Space: not specified)

Data Type : ff....ff .... Data type

Start Value : gg....gg .... Start value (Space: no start value is specified)

Maximum Value : hh....hh .... Maximum value (Space: no maximum value is specified)

Minimum Value : ii....ii .... Minimum value (Space: no minimum value is specified)

Increment : jj....jj .... Increment value (Space: no increment value is specified)

Cycle Option : k .... Cycle option

(Y:CYCLE; N:NO CYCLE)

Log Interval : ll....ll .... Log output interval

RDAREA Name : mm....mm .... RD area name for the storage location of

the ordinal number creator

Depending on the specification of the PDSQL_IDENT_QUOT environment variable, the following names vary when they are output:

- Sequence Schema

- Sequence Name

- RDAREA Name

If the PDSQL_IDENT_QUOT environment variable is set to ON:

The name is output enclosed by double quotation marks.

If the PDSQL_IDENT_QUOT environment variable is set to OFF:

The name is output not enclosed by double quotation marks, except when the name includes a lowercase character.

If the PDSQL_IDENT_QUOT environment variable is not set:

The name is output enclosed by double quotation marks.


SEQUENCES

[Function]

  Displays a list of ordinal number creators.

[Format]

  SEQUENCES [ { authorization-identifier | ALL } ]

Operands

** authorization-identifier Authorization identifier (1 to 8 characters)

Specify the authorization identifier for the user that has the ordinal

number creator.

If the authorization identifier is not specified, an authorization

identifier for CONNECT is assumed.

A list of ordinal numbers owned by the specified user is displayed.

** ALL

A list of ordinal numbers owned by all the users is displayed.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | SEQUENCE_SCHEMA | 15 | Character | Authorization identifier (#1)(#2)
2 | SEQUENCE_NAME | 30 | Character | Ordinal number creator identifier (#1)
3 | PUBLIC_USAGE | 1 | Character | Indicates whether PUBLIC USAGE is specified (Y: specified; Space: unspecified)
4 | CREATE_TIME | 14 | Character | Time needed to create ordinal numbers

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the

default dictionary settings, the number of characters displayed

will be 30.


: ( Execution of server command )

[Function]

The command specified after the : (colon) is executed on the server side.

[Format]

: Command

Operands

** Command

The command executed on the server side is specified.

[Notes]

- | HiRDB Assist-Server is necessary for the HiRDB server that executes the command.
 | Refer to the following manual for details:
 |  | Manual "HiRDB SQL reference: COMMAND EXECUTE sentence"
 |  | Manual "HiRDB UAP developer’s guide"
- | This function can be used by the following versions:
 |  | HiRDB Version 6 06-00 or a later version

[Reference]

SET CMDBUF


SERVERS

[Function]

SERVERS command lists the foreign servers owned by the specified authorization identifier.

[Format]

SERVERS [ { authorization-identifier | ALL } ]

Operands

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specify the authorization identifier of the user who owns foreign servers.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays all the defined routines.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | SERVER_ID | 12 | Numeric | Foreign server ID(#1)
2 | SERVER_NAME | 39 | Character | Foreign server name(#2)
3 | BES | 8 | Character | BES name to access the foreign server
4 | TYPE | 30 | Character | Server type
5 | VERSION | 30 | Character | Server version
6 | N_TABLE | 12 | Numeric | The number of tables defined on the foreign server
7 | MAPPING_USER_ID | 10 | Character | User name on the foreign server(#3)

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#3: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 30.


SET ANALYSIS MODE

[Function]

This command executes only the SQL pre-processing.

This is used to acquire the access path without changing the data.

[Format]

SET ANALYSIS MODE｛ ON | OFF ｝

Operands

** ON | OFF

ON: Performs only the pre-processing of the entered SQL statement.

OFF: Performs both the pre-processing and execution of the entered SQL statement.

[Notes]

(1) The following shows the SQL statements for which this command is enabled:

- ASSIGN LIST

- DELETE

- DROP LIST

- INSERT

- PURGE TABLE

- SELECT

- UPDATE

- LOCK

COMMIT and ROLLBACK are executed even if this mode is ON.

When this mode is ON, if SQL statements other than the above is executed, the KFPX27922-E error occurs.

(2) The following shows the dictionary search commands for which this command is enabled:

COLUMNS, CONSTRAINTS, DESCRIPTORS, FILES, INDEXCLM, INDEXES, LOBS,

PRPARAMS, RDAREAS, ROUTINES, SEQUENCES, SERVERS, TABLES, TRIGGERS,

TYPES, USERS

If the RDAREAINF, SEQINF, SOURCE, and TABLEINF dictionary search commands, which are not included in the above, are used, the pre-processing and execution are performed even if the mode is ON.


SET ARRAY DISPLAY MODE

[Function]

The SET ARRAY DISPLAY MODE command switches the output contents of repetition column information.

[Format]

SET ARRAY DISPLAY MODE { DATA | NUM }

Operands

** DATA| NUM

DATA ： Displays the data stored as the retrieval result.

NUM ： Displays the number of data stored as the retrieval result.

[Notes]

(1) The number of stored data includes NULL value data.

(2) This command is valid only when the SQL statement is executed with the retrieval result display format set to the table format.

If the PUTFILE command is executed, "stored data" is always output.

(3) If the number of stored data is greater than the number of elements specified in the SET ARRAY LIMIT command, the number of elements specified in the SET ARRAY LIMIT command is returned.

[Example]

CREATE TABLE T ( ID CHAR(11), V SMALLINT ARRAY[5], C DECIMAL(10,5));

INSERT INTO T VALUES('A19Y3946122',ARRAY[4,7,1,6,5],+100.35);

INSERT INTO T VALUES('D25Y7788792',ARRAY[9,NULL,NULL,3,2],+120.02);

INSERT INTO T VALUES('J03Y9750756',ARRAY[1,6,3],+99.56);

SET ARRAY DISPLAY MODE NUM;

SELECT ID,V,C FROM T ORDER BY ID; ... Displays the number of stored data.

SET ARRAY DISPLAY MODE DATA;

SELECT ID,V,C FROM T ORDER BY ID; ... Displays stored data.

[Output example]

The following shows examples of repetition column information output when the retrieval SQL is executed using the table in Example.

- When SET ARRAY DISPLAY MODE DATA is executed

ID V[0] V[1] V[2] V[3] V[4] C

----------- -------- -------- -------- -------- -------- ------------

A19Y3946122 4 7 1 6 5 00100.35000

D25Y7788792 9 ****** ****** 3 2 00120.02000

J03Y9750756 1 6 3 ****** ****** 00099.56000

･･･

・When SET ARRAY DISPLAY MODE NUM is executed

ID NUMELM(V) C

----------- --------- ------------

A33Y3946122 5 00100.35000

D25Y7788792 5 00120.02000

J03Y9750756 3 00099.56000


SET ARRAY LIMIT

[Function]

The SET ARRAY LIMIT command limits the maximum number of elements.

[Format]

SET ARRAY LIMIT maximum-number-of-elements

Operands

** maximum-number-of-elements ~<unsigned integer> ({0 | 2 to 30000})

Specifies the maximum number of elements limited in repetition columns.

Specify 0 when no limit exists for the maximum number of elements in repetition columns (uses the maximum number of elements set in the column definition).

[Example]

The following example retrieves columns where 50 are defined as the repetition count only by 10 elements, starting from the beginning of the table.

CREATE TABLE T1(C1 INTEGER ARRAY[50]);

...

SET ARRAY LIMIT 10;

SELECT C1 FROM T1;


SET ARRAY NOVALUE

[Function]

Sets characters to display elements with no values in repetition columns.

[Format]

SET ARRAY NOVALUE 'Character'

Operands

** 'Character' ～<one character> <<'＊'>>

Sets a character to be displayed when elements in the repetition columns have no value.

[Notes]

(1) Settings are valid when retrieval results are set to be displayed in the table-format.

(2) The settings do not apply to the PUTFILE command.

(3) You can use single-byte alphanumeric characters and symbols.


SET AUTO COMMIT

[Function]

The SET AUTO COMMIT command automatically issues COMMIT when SQL execution terminates normally.

[Format]

SET AUTO COMMIT { ON | OFF }

Operands

** ON | OFF

ON : Automatically issues COMMIT when SQL execution terminates normally.

OFF : Does not automatically issue COMMIT when SQL execution terminates normally.

[Notes]

(1) If execution of a SQL designed to automatically issue COMMIT terminates normally, this SQL automatically issues COMMIT despite the fact that OFF is specified.

[Example]

The following example automatically issues COMMIT:

SET AUTO COMMIT ON;

INSERT INTO T1 (C1,C2) VALUES(1000,2000);

The following example does not automatically issue COMMIT:

SET AUTO COMMIT OFF;

INSERT INTO T1 (C1,C2) VALUES(1000,2000);

COMMIT;


SET BINARY LIMIT

[Function]

The SET BINARY LIMIT command limits the length of the BINARY-type transfer area.

[Format]

SET BINARY LIMIT length

Operands

** length ~<unsigned integer> (0 to 2147483647)

Specifies the length of the BINARY-type transfer area.

If you do not limit the length of the BINARY-type transfer area, specify 0.

If 0 is specified, the length of the BINARY-type transfer area complies with the defined column length.

[Notes]

(1) You can use the environment variable PDSQLBINARYLIMIT to set limitations at the activation of this program. You can use the STATUS command to check the status.

[Example]

The following example retrieves BINARY-type column whose defined length is 2147483647 bytes only by 100 bytes.

CREATE TABLE T1(C1 BINARY(2147483647));

...

SET BINARY LIMIT 100;

SELECT C1 FROM T1;


SET BLOB LIMIT

[Function]

The SET BLOB LIMIT command limits the length of the BLOB-type transfer area.

[Format]

SET BLOB LIMIT length

Operands

** length ~<unsigned integer> (0 to 2147483647)

Specifies the length of the BLOB-type transfer area.

If you do not limit the length of the BLOB -type transfer area, specify 0.

If 0 is specified, the length of the BLOB-type transfer area complies with the defined column length.

[Notes]

(1) You can use the environment variable PDSQLBLOBLIMIT to set limitations at the activation of this program. You can use the STATUS command to check the status.

[Example]

The following example retrieves BLOB-type column whose defined length is 2 gigabytes only by 100 bytes.

CREATE TABLE T1(C1 BLOB(2G);

...

SET BLOB LIMIT 100;

SELECT C1 FROM T1;


SET CHAR LIMIT

[Function]

The SET CHAR LIMIT command limits the length of the CHAR/VARCHAR/MCHAR/MVARCHAR-type transfer area.

- CHAR

- VARCHAR

- MCHAR

- MVARCHAR

[Format]

SET CHAR LIMIT length

Operands

** length ~<unsigned integer> (0 to 32000)

Specifies the length of the CHAR/VARCHAR/MCHAR/MVARCHAR-type transfer area.

If you do not limit the length of the CHAR/VARCHAR/MCHAR/MVARCHAR-type transfer area, specify 0.

If 0 is specified, the length of the CHAR/VARCHAR/MCHAR/MVARCHAR-type transfer area complies with the defined column length.

[Example]

The following example retrieves CHAR-type column whose defined length is 30000 bytes only by 100 bytes.

CREATE TABLE T1(C1 CHAR(30000));

...

SET CHAR LIMIT 100;

SELECT C1 FROM T1;

[Notes]

(1) This command is also valid for dictionary information display commands (COLUMNS, DESCRIPTORS, FILES, INDEXCLM, INDEXES, LOBS, PRPARAMS, RDAREAS, ROUTINES, TABLES, TYPES, USERS).


SET CLOB LIMIT

[Function]

Limits the length of the data transfer area for the CLOB type data.

[Format]

SET CLOB LIMIT length

Operands

** length ~<unsigned integer> (0 to 2147483647)

Specifies the length of the CLOB type data.

When you do not limit the length of the CLOB type data (use the length defined for columns), specify 0.

[Notes]

(1) You can use the environment variable PDSQLCLOBLIMIT to set limitations at the activation of this program.

You can use the STATUS command to check the status.

[Example]

Retrieval results only 100 bytes in columns with the CLOB type data length of 2GB.

CREATE TABLE T1(C1 CLOB(2G) IN ULOB1);

...

SET CLOB LIMIT 100;

SELECT C1 FROM T1;


SET CMDBUF

[Function]

The SET CMDBUF command changes the length of the area used to receive the execution result of the commands executed by the server.

[Format]

SET CMDBUF length

Operands

** length ~<unsigned integer> (1 to 2147483647)

Specifies the length of the area used to receive the execution result of the commands executed by the server.

The initial value is 30,000 bytes.


SET CONVSPACE

[Function]

If control characters (0x01-0x1F) other than a line feed exist in a character string of an SQL statement or pdsql command, SET CONVSPACE command converts the control character codes to singe-byte spaces.

[Format]

SET CONVSPACE { ON | OFF }

Operands

** ON | OFF <<OFF>>

ON : | Converts control character codes to single-byte spaces.
OFF : | Does not convert control character codes.

[Environment variable]

** PDSQL_CONVSPACE = ON | OFF

ON : | Converts control character codes to single-byte spaces.
OFF : | Does not convert control character codes.

  If not specified, OFF is assumed.

[notes]

(1)

You can use the STATUS command to check the status.


SET DISPLAY

[Function]

SET DISPLAY command changes the output format.

[Format]

SET DISPLAY [{ ALL｜OFF }] [{ PROMPT｜NOPROMPT }]

Operands

** { ALL｜OFF } <<ALL>>

ALL : Outputs the retrieval results and messages.

OFF : Outputs messages only.

** { PROMPT｜NOPROMPT } <<PROMPT>>

PROMPT : Displays a command input prompt.

NOPROMPT : Does not display a command input prompt.

[Environment variable]

** PDSQL_DISPLAY = ALL | OFF

ALL : Outputs retrieval results and messages.

OFF : Outputs messages only.

If not specified, ALL is assumed.

** PDSQL_DISPLAYNOLINE = YES | NO

YES : Does not display a command input prompt.

NO : Displays a command input prompt.

If not specified, NO is assumed.

[Notes]

(1) You can use the STATUS command to check the status.


SET DUMPMODE

[Function]

The SET DUMPMODE command displays the retrieval result in both the hexadecimal format and character format or only in the character format.

[Format]

[ SET ] DUMPMODE { ON | OFF }

Operands

** ON | OFF

ON :

When the retrieval result is displayed in the table format (TBLMODE is ON), it is displayed in the hexadecimal format.

When the retrieval result is displayed in the list format (TBLMODE is OFF), it is displayed in both the hexadecimal format and character format.

OFF :

The retrieval result is displayed in the character format.

[Environment variable]

** PDUAPDUMP = { ON | OFF } | { YES | NO }

ON or YES :

When the retrieval result is displayed in the table format (TBLMODE is ON), it is displayed in the hexadecimal format.

When the retrieval result is displayed in the list format (TBLMODE is OFF), it is displayed it both in the hexadecimal format and character format.

OFF or NO :

The retrieval result is displayed in the character format.

If this operand is not specified, OFF is assumed.


SET ECHOBACK

[Function]

The SET ECHOBACK command echoes back the entered command character string.

[Format]

SET ECHOBACK { ON | OFF }

Operands

** ON | OFF

ON： Echoes back the entered command.

OFF：Does not echo back the entered command.


SET FETCH ARRAY SIZE

[Function]

This command specifies the number of rows to be searched at one FETCH by using the array FETCH function.

[Format]

SET FETCH ARRAY SIZE (Number of rows)

Operands

** (Number of rows) <Unsigned integer>(0, 2 to 32767)<<0>>

- Specify the number of rows to be searched at one FETCH.

- To return to the normal FETCH operation (one item is searched at one FETCH), specify 0.

[Notes]

(1) The following are the dictionary search commands that enable this command:

COLUMNS, CONSTRAINTS, DESCRIPTORS, FILES, INDEXCLM, INDEXES, LOBS,

PRPARAMS, RDAREAINF, RDAREAS, ROUTINES, SEQINF, SEQUENCES, SERVERS,

TABLES, TRIGGERS, TYPES, USERS

When the SOURCE and TABLEINF dictionary search commands, which are not included in the above, are used, rows are not collectively acquired, even if the collective acquisition of multiple rows is specified. One row is acquired at a time.


SET FETCH LIMIT

[Function]

This command restricts the number of rows to be searched (the number of rows for which FETCH is executed).

[Format]

SET FETCH LIMIT Number of search target rows

Operands

** Number of search target rows ~<unsigned integer> (0 to 2147483647)
- Specify the number of rows to be searched (the number of rows for which FETCH is executed).

- To not restrict the number of rows to be searched (the number of rows for which FETCH is executed), specify 0.

[Example]

Restrict the number of search target rows to 10.:

SET FETCH LIMIT 10;

SELECT * FROM T1 ORDER BY C1 ASC;

[Notes]

(1) The following shows the dictionary search commands that enable this command:

COLUMNS, CONSTRAINTS, DESCRIPTORS, FILES, INDEXCLM, INDEXES, LOBS,

PRPARAMS, RDAREAS, ROUTINES, SEQUENCES, SERVERS, TABLES, TRIGGERS,

TYPES, USERS

When the RDAREAINF, SEQINF, SOURCE, and TABLEINF dictionary search commands, which are not included in the above, are used, all the results are output regardless of the number of search target rows specified.

(2) The result set of the CALL statement outputs all the results regardless of the number of search target rows specified.


SET NCHAR LIMIT

[Function]

The SET NCHAR LIMIT command limits the length of the NCHAR/NVARCHAR-type transfer area.

[Format]

SET NCHAR LIMIT length

Operands

** length ~<unsigned integer> (0 to 16000)

Specifies the length of the NCHAR/NVARCHAR-type transfer area.

If you do not limit the length of the NCHAR/NVARCHAR-type transfer area, specify 0.

If 0 is specified, the length of the NCHAR/NVARCHAR-type transfer area complies with the defined column length.

[Example]

The following example retrieves NCHAR-type column whose defined length is 15000 characters only by 100 characters.

CREATE TABLE T1(C1 NCHAR(15000));

...

SET NCHAR LIMIT 100;

SELECT C1 FROM T1;

[Notes]

(1) This command is also valid for dictionary information display commands (COLUMNS, DESCRIPTORS, FILES, INDEXCLM, INDEXES, LOBS, PRPARAMS, RDAREAS, ROUTINES, TABLES, TYPES, USERS).


SET NULL CHARACTER

[Function]

Sets characters to display the null value in the table-format display feature for the retrieval results.

[Format]

SET NULL CHAR〔ACTER〕 'Character'

Operands

** 'Character' ~<one character> <<'＊'>>

Sets a character to be displayed for the null value.

[Notes]

(1) Settings are valid when retrieval results are set to be displayed in the table format.

(2) The settings do not apply to the PUTFILE command.

(3) You can use single-byte alphanumeric characters and symbols.


SET PADDING CHAR

[Function]

Specifies, for the input ? parameters of the CHAR and MCHAR types, whether to add padding characters after the input data.

[Format]

  SET PADDING CHAR { SPACE | NONE }

Operands

** SPACE | NONE

SPACE : Pads space characters after the input data so that the data length becomes the same as that of the defined length, and uses the data as the ? parameter value.

NONE : Uses the data with the same length as that of the input data as the ? parameter value.

[Notes]

(1) At activation, the program selects the SPACE operand.

(2) When XDM/RD E2 is connected, if data including 2-byte characters are input in the input ? parameter with the fixed length (the CHAR or MCHAR type), XDM/RD E2 will execute character code conversion and extend the data length, resulting in an SQL error.

You can avoid this problem by specifying the NONE operand.

This problem does not occur with the NCHAR type data because the data length does not change after the character code conversion.

(3) When you input SQL statements having input ? parameters, the program obtains the input ? parameter information, such as data type and definition length, from the server (HiRDB or XDM/RD E2).

The program displays the ? parameter information obtained from the server in a message requesting the input of the ? parameter value. Data types of the input ? parameter created by the program are as follows:

Data type of the ? parameter information obtained from the server |  | CHAR | MCHAR
Data type of the input ? parameter value created by the program | When the SPACE operand is specified | CHAR | MCHAR
 | When the NONE operand is specified | VARCHAR | MVARCHAR


SET REMOVE LINEFEED CHARACTER

[Function]

This command removes the line-feed code (0x0A, 0x0D) if it is included in the SQL statement or in the character string of the pdsql command.

[Format]

SET REMOVE LINEFEED CHARACTER { ON | OFF }

Operands

** ON | OFF <<OFF>>

ON: Removes the line-feed code.

OFF: Does not remove the line-feed code.

Environment variable

** PDSQL_REMOVE_LINEFEED_CHARACTER = ON | OFF

ON: Removes the line-feed code.

OFF: Does not remove the line-feed code.

If nothing is set, OFF is assumed.

[Notes]

(1) The status can be checked by using the STATUS command.

(2) In versions 09-01 and earlier, the ON operation was performed.

(3) In the simplified GUI version, if the command is entered from the SQL input screen, the setting is not enabled. The ON operation is always performed.

(4) For the effects when this setting is ON, see (i) in "Rules".

Examples

(Input)

SELECT * FROM T123

WHERE C1=10 AND C2 like 'solu

tion%';

- If PDSQL_REMOVE_LINEFEED_CHARACTER is set to ON:

Because the line-feed code is deleted, a delimiter needs to be entered separately from the line feed.

The line-feed code is deleted from the value of the constant.

The constant that does not include the linefeed character can be written across multiple rows.

(SQL to execute)

テキスト ボックス: SELECT * FROM T123WHERE C1=10 AND C2 like 'solution%'

- If PDSQL_REMOVE_LINEFEED_CHARACTER is set to OFF:

Because the line-feed code is not deleted, the character is used as the word delimiter.

A constant that includes the line-feed code can be written.

A constant that does not include the line-feed code cannot be written across multiple rows.

(SQL to execute)

テキスト ボックス: SELECT * FROM T123 WHERE C1=10 AND C2 like 'solu tion%'


SET RESULT FILEOUT

[Function]

SET RESULT FILEOUT command outputs the execution result both to the screen and a file.

[Format]

SET RESULT FILEOUT { ON [file-name] | OFF }

operands

** ON [file-name]

Outputs the execution result to the specified file.

If the file name is omitted, the previously specified file name is used.

If this command is executed for the first time after the program is started, the file name is mandatory.

** OFF

Terminates the output of the execution result to a file.

[Notes]

- The execution result of the line mode HELP command is not output to a file.

- The executed SQL statement and pdsql command are not output. If you want to output them, use SET ECHOBACK command together.

- If an existing file is specified, the execution result is added to the file.


SET SPLIT SIZE

[Function]

The size to which the BINARY/BLOB/CLOB type does the split acquisition is specified.

To use this command, the retrieval output can be done by evading memory shortage when the data stored in the BINARY/BLOB/CLOB type is retrieved.

[Format]

SET SPLIT SIZE SPLIT_ACQUISITION_SIZE

Operands

** SPLIT_ACQUISITION_SIZE ~<unsigned integer> (0,4 to 2097152) <<0>>

Specify the split acquisition size of the BINARY/BLOB/CLOB type in kilobytes.

If you do not want to perform split acquisition, specify 0.

[Notes]

- | This command will be effective only when the connected server and client are HiRDB V7 07-01 or a later version.
- | Astandard, specify a value so that there will be the fewest number of split acquisition times and t s he memory shortage will not occur, taking into account the distribution of the real data lengths.
 | Example of obtaining the standard value when the distribution of real data lengths is unknown:
 |  | SELECT AVG(LENGTH(blob1)) FROM table1 WHERE int1<100;
 |  |  | ･･･ | (Under the same condition as for Retrieval SQL,) obtain the average value of the real data lengths of the longest data type (blob1) included in the acquired row data.
- | The split acquisition size can be specified in advance by environment variable PDSQL_SPLITSIZE at program start time.
 | The specified value can be checked by STATUS command.
- | If split acquisition specification is performed by this command, the communication with HiRDB and output processing is also split. Therefore, the performance may be degraded depending on the number of split acquisition times.
 | If you can determine that the maximum real data length will not cause memory shortage, try to execute retrieval by specifying the maximum data length for each of SET BINARY LIMIT, SET BLOB LIMIT and SET CLOB LIMIT commands.
- | This command supports retrieval by the SELECT statement. However, it does not support the CALL statement.
- | Even if BINARY/BLOB/CLOB type data is retrieved when the output format of the retrieval result is specified as the list format, the retrieval data is not displayed.

[Use example]

- | If two columns (int1 AS INTEGER and blob1 AS BLOB (2G)) are retrieved and output to a file, the maximum real data length is 700MB and the average real data length is 100MB File output command:
 | File output command:
 |  |  | PUTFILE BIN_PDRORG TO C:\TEMP\T1.DAT SELECT “int1”,”blob1” FROM T1;
 | Flow of memory shortage evasion:
 |  | ** | Execute the command with nothing specified => Memory as large as the defined length (2G + 4) is required.
 |  |  | If memory shortage occurs, specify the maximum real data length for the maximum length of BLOB type data.
 |  |  | SET BLOB LIMIT 734003200;
 |  | ** | (Execute the command) => Memory as large as the length specified for BLOB LIMIT (700M + 4) is required.
 |  |  | If memory shortage still occurs, specify the average real data length for the split acquisition size.
 |  |  | SET SPLIT SIZE 104857600;
 |  | ** | (Execute the command) => Memory as large as the size specified for SPLIT SIZE (100M + 4) is required.
 |  |  | If memory shortage still occurs, specify half of the split acquisition size.
 |  |  | SET SPLIT SIZE 52428800;
 |  | ** | If memory shortage still occurs, evade by reducing the split acquisition size value.


SET TBLMODE

[Function]

The SET TBLMODE command displays the retrieval result in the table format or in the list format.

[Format]

SET TBLMODE { ON | OFF }

Operands

** ON | OFF

ON： Displays the retrieval result in the table format (horizontal display of columns).

OFF：Displays the retrieval result in the list format (vertical display of columns).

[Notes]

- Null values are represented by ********.


SET WHENEVER

[Function]

This command specifies the processing that HiRDB SQL Executer should take after executing an SQL statement.

[Format]

SET WHENEVER

{ SQLERROR | SQLWARNING | NOT FOUND }

{ EXIT return value [ COMMIT | ROLLBACK ]

| CONTINUE [ COMMIT | ROLLBACK | NONE ] }

return value::= { integer constant | SQLCODE}

Operands

** { SQLERROR | SQLWARNING | NOT FOUND }

SQLERROR: Specifies the processing to be performed when the SQL statement is not executed normally due to an error caused by the user or with HiRDB.

SQLWARNING: Specifies the processing to be performed when the SQL statement was executed normally but a status was detected that the user is to be warned of.

NOT FOUND: Specifies the processing to be performed when no result is found after all the tables are searched.

** {EXIT return value [COMMIT|ROLLBACK]

|CONTINUE [COMMIT|ROLLBACK|NONE]}

EXIT: Exit HiRDB SQL Executer with the return value specified by the user.

If COMMIT or ROLLBACK is not specified, COMMIT is assumed.

CONTINUE: Continues the execution of HiRDB SQL Executer.

If COMMIT, ROLLBACK, or NONE is not specified, NONE is assumed.

COMMIT: Commits the current transaction.

ROLLBACK: Rolls back the current transaction.

NONE: For SQLERROR, commit or rollback is not performed.

For SQLWARNING or NOT FOUND, when the AUTO COMMIT mode is ON, the current transaction is committed.

** Return value ::= {integer constant|SQLCODE}

Integer constant: The specified value (in the range from -32768 to -1) is the return value.

SQLCODE: SQLCODE is the return value.

[Notes]

(1) The setting status at the time of startup is the same as when the following command is executed.

SET WHENEVER SQLERROR CONTINUE NONE;

SET WHENEVER SQLWARNING CONTINUE NONE;

SET WHENEVER NOT FOUND CONTINUE NONE;

(2) If the SQL is normally executed, the transaction is resolved depending on the AUTO COMMIT mode.

(3) If the exception control specification specified for SQLWARNING is in use, if an SQLWARNING occurs during the search operation, the display of the search result stops at the rows where the SQLWARNING occurred.

"Search" in this case indicates the SELECT statement, CALL statement, and GET DIAGNOSTICS statement.

(4) This command is not applied to an SQL statement that is internally issued in HiRDB SQL Executer processing.


SINGLE ROW

[Function]

Executes a single-row SELECT statement.

[Format]

SINGLE ROW SELECT statement

Operands

** SELECT statement

- Specify a single-row SELECT statement.

- For details about the grammar for a single-row SELECT statement,

see the HiRDB SQL Reference.

[Notes]

(1) Do not specify the INTO clause.

(2) If the result has more than two rows, an error (KFPA11449-E) occurs.


SOURCE

[Function]

SOURCE command displays the definition source.

[Format]

SOURCE { VIEW | PROCEDURE | FUNCTION | TRIGGER | ROUTINE }

[authorization-identifier.] Definition-source-name

Operands

** { VIEW | PROCEDURE | FUNCTION | TRIGGER | ROUTINE }

Specify the type of the definition source.

VIEW : Viewed table

PROCEDURE : Procedure

FUNCTION : Function

TRIGGER : Trigger

ROUTINE : Function, procedure, trigger

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specify the authorization identifier of the user who owns the definition source.

If the authorization identifier is omitted, the authorization identifier in CONNECT command is assumed.

** DEFINITION_SOURCE_NAME

Specify the definition source name for which information is to be displayed.

[Notes]

(1) Even if the specified definition source name is defined, if the definition source stored in the data dictionary table is NULL, an asterisk (*) is displayed.

(2) If ROUTINE is specified for an operand, the definition sources are retrieved in the order of procedures, functions and triggers. Therefore, if procedures, functions and triggers are specified with the same name, multiple sources are displayed.

(3) In HiRDB, because the definition sources of the functions, procedures and triggers are stored in the BLOB type, operation will be as follows:

In SJIS SQL Executer, if a character other than ASCII code is included when the character code classification used by the HiRDB server is other than Shift JIS code, the character is garbled.

In Unicode SQL Executer, if a character other than ASCII code is included when the character code classification used by the HiRDB server is other than Unicode (UTF-8), the character is garbled.


STATUS

[Function]

The STATUS command displays the operating status of this program.

[Format]

{ STATUS | ST } [ ALL ]

Operands

** ALL

The program displays all statuses, regardless of the settings for the SET command.

[Output format]

PROGRAM NAME | : | This program name
PP-No. | : | Product ID
VERSION | : | Version of Executer
SERVER VERSION | : | Version of the connected HiRDB server
CONNECT USER-ID | : | Authorization identifier connected to the HiRDB server
FETCH LIMIT | : | Setting value for SET FETCH LIMIT
FETCH ARRAY SIZE | : | Setting value for SET FETCH ARRAY SIZE
DUMP FORMAT OUTPUT MODE | : | Setting value for SET DUMPMODE
TABLE FORMAT DISPLAY MODE | : | Setting value for SET TBLMODE
ARRAY DISPLAY MODE | : | Setting value for SET ARRAY DISPLAY MODE
ECHOBACK | : | Setting value for SET ECHOBACK
ANALYSIS MODE | : | Setting value for SET ANALYSIS MODE
AUTO COMMIT MODE | : | Setting value for SET AUTO COMMIT
BLOB LIMIT LENGTH | : | Setting value for SET BLOB LIMIT
BINARY LIMIT LENGTH | : | Setting value for SET BINARY LIMIT
CLOB LIMIT LENGTH | : | Setting value for SET CLOB LIMIT
CHARACTER LIMIT LENGTH | : | Setting value for SET CHAR LIMIT
NCHAR LIMIT LENGTH | : | Setting value for SET NCHAR LIMIT
ARRAY LIMIT | : | Setting value for SET ARRAY LIMIT
SERVER COMMAND BUFFER | : | Setting value for SET CMDBUF
NULL CHARACTER | : | Setting value for SET NULL CHARACTER
ARRAY NOVALUE | : | Setting value for SET ARRAY NOVALUE
PADDING CHAR | : | Setting value for SET PADDING CHAR
RESULT FILEOUT | : | Setting value for SET RESULT FILEOUT
SPLIT SIZE | : | Setting value for SET SPLIT SIZE
DISPLAY MODE | : | Setting value for SET DISPLAY
CONVSPACE | : | Setting value for SET CONVSPACE
REMOVE LINEFEED CHARACTER | : | Setting value for SET REMOVE LINEFEED CHARACTER
WHENEVER SQLERROR | : | Setting value for SET WHENEVER SQLERROR
WHENEVER SQLWARNING | : | Setting value for SET WHENEVER SQLWARNING
WHENEVER NOT FOUND | : | Setting value for SET WHENEVER NOT FOUND

[Notes]

- If ALL operand is not specified, information other than the following information is not displayed by default.

VERSION :

SERVER VERSION :

CONNECT USER-ID :

FETCH LIMIT :

DUMP FORMAT OUTPUT MODE :

AUTO COMMIT MODE :


TABLEINF

[Function]

   The TABLEINF command displays information for the specified table.

[Format]

TABLEINF [[ RD-node-name [.] ] [ authorization-identifier [.]] table-identifier

Operands

** RD-node-name ~<RD-node-name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the table.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** table-identifier

Specifies the table identifier used to display table information.

[Notes]

(1) In XDM/RD 10-01, If this command is executed for the table in which reference constraints or triggers are defined, an SQL statement error occurs and processing terminates because no HiRDB dictionary viewed table exists.

(2) If unidentifiable data is acquired for the output items, either "?Unknown" or "?????" is displayed, or no value is outputfor the item.

[Output format]

Table Type : aa....aa

Table Schema : bb....bb

Table Name : cc....cc

Table ID : dddddddddd(dd....dd)

Column :

eeeee ff....ff gg....gg [hh....hh]

Divide Info : ii....ii(#9)(#11)

RDAREA Name : kk....kk(#9)

Server Name : MM....MM(#10)

Option : ll....ll(#9)(#10)

Check : LL....LL(#9)(#11)(#13)

Foreign Key : NN....NN(#9)(#11)

----------------------------------------------------------------------(#2)

Index Information

Index Type : mm....mm

Index Name : nn....nn

Index ID : oooooooooo(oo....oo)

Column :

ppppp qq....qq rrrr

RDAREA Name : ss....ss

Option : tt....tt

----------------------------------------------------------------------(#8)(#11)

Trigger Information

EEEEE

Trigger Name : FF....FF.GG....GG(HH....HH)

Action Time : II....II

Event : JJ....JJ

Action Type : KK....KK

----------------------------------------------------------------------(#3)

View Information

uuuuu vv....vv.ww....ww

----------------------------------------------------------------------(#4)(#12)

Routine Information

xxxxx yy....yy.zz....zz(AA....AA)

yy....yy.OO....OO(PP....PP)(#11)

yy....yy.QQ....QQ(RR....RR)(#11)

----------------------------------------------------------------------(#5)

Base Table Information

BBBBB CC....CC.DD....DD

aa....aa : Table identifier type. Any one of the following table identifier types is output:

TABLE

[SHARE](#11) FIX TABLE

FOREIGN TABLE

[PUBLIC](#11) VIEW

[PUBLIC](#11) READ ONLY VIEW

bb....bb : Schema name

cc....cc : Table identifier name

dddddddddd(dd....dd) : Table ID. Displays the value in hexadecimal (decimal) notation.

eeeee ~ hh....hh : Component column information. Displayed repeatedly as many times as the number of columns.

eeeee : Column ID

ff....ff : Column name

gg....gg : Data type (#6)

hh....hh : Column attribute information. The following contents are output.

{NULL | NOT NULL} : NULL/NOT NULL attribute

[WITH DEFAULT] : WITH DEFAULT attribute

[RECOVERY {ALL | PARTIAL | NO }] : Column recovery restriction attribute

[IN ((RDAREA-name)[,(RDAREA-name)]...)]

: RDAREA name for LOB column

[ALLOCATE (attribute-name IN (RDAREA-name)

[,attribute-name IN (RDAREA-name)]...)]

: Specification attribute for RDAREA for LOB in abstract data type definition

[ARRAY[number-of-repetition-columns]] : Repetition column attribute (#11)

[SYSTEM GENERATED] : SYSTEM GENERATED attribute。(#11)

[PLUGIN (plug-in-option)] : Plug-in specification attribute (#11)

[DEFAULT [default-value]] : DEFAULT attribute。(#11)

[UPDATE ONLY FROM NULL] : Updatable column attribute (#11)

[NO SPLIT] : No-split option attribute (#11)

ii....ii : Split information. Any one of the following is output: (#11)

NON DIVISION

KEY RANGE

PARTITIONED BY (column-name)

PARTITIONED BY MULTIDIM (matrix-partitioning-table-information)

[FIX] HASH (hash-function-name) BY (column-name)[,( column-name)]...

kk....kk : RDAREA information for table

MM....MM : Foreign server name

ll....ll : Table option information

If the table identifier type is TABLE or FIX TABLE,

the following contents are output.

PCTFREE＝((percentage-of-unused-space),( percentage-of-free-pages-in-segment)) :

Usage ratio information

{LOCK ROW｜LOCK PAGE} : Unit of locked resources

[SUPPRESS DECIMAL] : Abbreviated data storage specification information

[WITHOUT ROLLBACK] : Row locking information (#11)

{{SEGMENT | UNIT} REUSE ｛〔number-of-segments〕｜{NO | NOUSE}｝} :

Free space reuse information (#11)

[INSERT ONLY [WHILE

｛date-interval-data｜labeled-duration｝ BY column-name]] :

Falsification prevented table attribute (#11)

If the table identifier type is FOREIGN TABLE, information is output in the following syntax:

OPTIONS(SCHEMA 'schema-name-on-foreign-server',

TABLE 'table-name-on-foreign-server')

LL....LL : Constraint decision information. Displayed repeatedly as many times as the number of constraint decisions.

This information is output in the following syntax. (#11)(#13)

CONSTRAINT constraint-name CHECK(check-constraint-retrieval-condition)

NN....NN : Reference constraint information. Displayed repeatedly as much as the number of reference constraints.

Output it by the following syntaxes. (#11)

CONSTRAINT constraint-name FOREIGN KEY(table-column-name-with-foreign-key)

REFERENCES table-name-of-the-table-to-be-referenced

ON DELETE ON_DELETE trigger-name

ON UPDATE ON_UPDATE trigger-name

mm....mm ~ tt....tt : Index information

Displayed repeatedly as many times as the number of indexes.

mm....mm : Index type. Any one of the following is output:

[UNIQUE] INDEX

[UNIQUE] CLUSTER KEY

UNIQUE PRIMARY [CLUSTER] KEY

nn....nn : Index name

oooooooooo(oo....oo) : Index ID。

The value is displayed in hexadecimal (decimal) notation.

ppppp ~ rrrr : Component column information. Displayed repeatedly as many times as the number of columns.

ppppp : Column ID

qq....qq : Column name

rrrr : Ascending/descending order information {ASC | DESC}

ss....ss : RDAREA name for index

tt....tt : Index option information. The following contents are output.

PCTFREE＝((percentage-of-unused-space)) : Usage ratio information

EEEEE ~ KK....KK : Usage ratio information (#11)

Displayed repeatedly as many times as the number of triggers.

EEEEE : Display serial number

FF....FF : Schema name to which trigger belongs

GG....GG : Trigger name

HH....HH : Specific name (#7)

II....II : Trigger action event｛BEFORE｜AFTER｝

JJ....JJ : Trigger event

｛INSERT｜DELETE｜UPDATE〔OF column-name〔，column-name〕…〕｝

KK....KK : Trigger action

〔｛FOR EACH ROW｜FOR EACH STATEMENT｝〕

uuuuu ~ ww....ww : View information. Displayed repeatedly as many times as the number of views

uuuuu : Display serial number

vv....vv : Schema name

ww....ww : View name

xxxxx ~ AA....AA : Routine information (#12)

Displayed repeatedly as many times as the number of routines.

xxxxx : Display serial number

yy....yy : Schema name

zz....zz : Routine name

AA....AA : Specific name (#7)

OO....OO : Trigger name (#11)

PP....PP : Specific name (#7)(#11)

QQ....QQ : Constraint name (#11)

RR....RR : Specific name (#7)(#11)

BBBBB ~ DD....DD : Table information of reference destination

Displayed repeatedly as many times as the number of reference destination tables.

BBBBB : Display serial number

CC....CC : Schema name to which the table belongs

DD....DD : Table name

#2: Displays the index information that references the table identifier specified by the command.

#3: Displays the view information that references the table identifier specified by the command.

#4: Displays the routine information that references the table identifier specified by the command.

#5: Displays the table information that is referenced by the table identifier specified by the command.

#6: The defined data code values and displayed character strings are listed in the table below:

Table A-5 : Data Code Value and Displayed Character String

Data code value | Displayed data type name | Notes on length
96 or 97 | INTERVAL YEAR TO SECOND | Displayed in "(precision, scale)".
100 or 101 | INTERVAL YEAR TO DAY | Displayed in "(precision, scale)".
110 or 111 | INTERVAL HOUR TO SECOND | Displayed in "(precision, scale)".
112 or 113 | DATE | Not displayed.
120 or 121 | TIME | Displayed in "(scale)". If the scale is 0, not displayed.
124 or 125 | TIMESTAMP | Displayed in "(scale)". If the scale is 0, not displayed.
131 | Abstract data type name | Not displayed.
144 or 145 | BINARY | Maximum number of stored bytes.
146 or 147 | BLOB | Maximum number of stored bytes.
152 or 153 | CLOB | Maximum number of stored bytes.
160 or 161 | MVARCHAR | Maximum number of stored bytes.
164 or 165 | MCHAR | Number of stored bytes.
176 or 177 | NVARCHAR | Maximum number of stored characters.
180 or 181 | NCHAR | Number of stored characters.
192 or 193 | VARCHAR | Maximum number of stored bytes.
196 or 197 | CHAR | Number of stored bytes.
224 or 225 | FLOAT | Not displayed.
226 or 227 | SMALLFLT | Not displayed.
228 or 229 | DECIMAL | Displayed in "(precision, scale)".
240 or 241 | INTEGER | Not displayed.
244 or 245 | SMALLINT | Not displayed.

Note that, in SQL Executer 02-06 or earlier, the display contents are limited as follows:

- For the TIMESTAMP type data, the scale is not displayed.

- For the abstract data type, a dot (.) is displayed.

#7: For the specific name, refer to the manual "HiRDB UAP Development Guide."

#8: Displays the trigger information that references the table identifier specified by the command.

#9: Displayed when the table identifier type is TABLE or FIX TABLE.

#10: Displayed when the table identifier type is FOREIGN TABLE.

#11: Not output in SQL Executer 02-06 or earlier versions.

#12: If the routine name is the specific name of the trigger, the trigger name is output.

If the trigger name is the specific name of the reference constraint, the constraint name is output.

In XDM/RD 10-01, if the above information exists, this information is not output because no HiRDB dictionary viewed table exists.

#13: In HiRDB, because the definition source is stored in BLOB type, operation will be as follows:

In SJIS SQL Executer, if a character other than ASCII code is included when the character code classification used by the HiRDB server is other than Shift JIS code, the character is garbled.

In Unicode version SQL Executer, if a character other than ASCII code is included when the character code classification used by the HiRDB server is other than Unicode (UTF-8), the character is garbled.

#14: Output in SQL Executer 08-01 and later.

#15: Output in SQL Executer 09-02 and later.

#16: The following names are output enclosed by double quotation marks:

- Restriction name output in the restriction condition information

- Column name output in the reference restriction information

#17: Output in SQL Executer 09-03 and later.


TABLES

[Function]

The TABLES command displays a list of the tables possessed by the user of the specified authorization identifier.

[Format]

TABLES [[ RD-node-name [.] ] { authorization-identifier | ALL } ] [+COMMENT]

Operands

** RD-node-name ~<RD node name> (1 to 30 characters)

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the table.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays all the defined tables.

** [+COMMENT]

This operand outputs the table comments.

If this operand is not specified, no table comment is output.

[Notes]

(1) If a character string that starts with a plus sign (+) is specified as the operand, and the character

string that follows the plus sign is not COMMENT, an error occurs.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | TABLE_SCHEMA | 12 | Character | Authorization identifier(#1)(#2)
2 | TABLE_NAME | 30 | Character | Table identifier
3 | N_COLS | 6 | Numeric | Number of composition columns
4 | N_INDEX | 7 | Numeric | Number of index definitions
5 | CREATE_TIME | 14 | Character | Table creation time
6 | COMMENT | 255 | Character | Table comment(#3)

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 24.

#3: Output when the +COMMENT option is specified.


TRIGGERS

[Function]

TRIGGERS command lists the triggers owned by the specified authorization identifier.

[Format]

TRIGGERS [ { authorization-identifier | ALL } ]

Operands

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specify the authorization identifier of the user who owns the triggers.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

Displays the triggers of all the defined users.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | TRIGGER_NAME | 39 | Character | Trigger name (authorization-identifier.trigger-name)(#1)
2 | SPECIFIC_NAME | 30 | Character | Specific name of the trigger action procedure
3 | TABLE_NAME | 30 | Character | Name of the table in which the trigger is defined(#2)
4 | ACT | 3 | Character | Trigger action time(#2)
5 | EVT | 3 | Character | Trigger event type
6 | TYP | 3 | Character | Trigger action unit
7 | VAL | 3 | Character | Trigger/index enable flag
8 | CREATE_TIME | 16 | Character | Trigger creation time(#2)

#1: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.

#2: The results are sorted by the following rows.

- TABLE_NAME (ascending order)

- ACT (ascending order)

- CREATE_TIME (ascending order)


TYPES

[Function]

The TABLEINF command displays information for the specified table.

[Format]

TYPES [[ RD-node-name [.] ] { authorization-identifier | ALL } ]

Operands

** RD-node-name ~<RD-node-name>（1 to 30 characters）

Specifies the RD node name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user possessing the table.

If authorization-identifier is omitted, the authorization identifier in the CONNECT command is assumed.

** ALL

All user's defined abstract data types are displayed.

[Notes]

(1) This command can be used since HiRDB Version 5.0.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | TYPE_ID | 12 | Character | Abstract data type ID(#1)
2 | TYPE_NAME | 39 | Character | Abstract data type name(#2)
3 | N_ATTR | 6 | Numeric | Number of composition attributes

#1: The results are sorted by this row in ascending order.

#2: If the setting to use mixed character data is specified in the default dictionary settings, the number of characters displayed will be 61.


USERS

[Function]

The USERS command displays a user list.

[Format]

USERS [[ RD-node-name[.] ] { authorization-identifier | ALL } ]

Operands

** RD-node-name ~<RD-node-name>（1 to 30 characters）

Specifies the RD-node-name when the remote database is accessed.

RD-node-name can be specified only when the remote database is HiRDB.

If RD-node-name is omitted, the local database is accessed.

** authorization-identifier ~<authorization identifier> (1 to 8 characters)

Specifies the authorization identifier of the user to be displayed.

If authorization-identifier is omitted, ALL is assumed.

** ALL

Displays all users.

[Output contents]

# | Column name | Number of characters | Type | Contents
1 | USER_ID | 30 | Character | User ID(#1)
2 | DBA | 3 | Character | DBA privilege
3 | SCHEMA | 6 | Character | Schema definition privilege
4 | CREATE_TIME | 14 | Character | Schema creation time
5 | TEST | 4 | Character | Password restriction violator type code (if unchecked, space)(#2)

#1: The results are sorted by this row in ascending order.

#2: Output when connected to HiRDB V7 07-02 or a later version.


Command Display

The Command Display dialog box is displayed when the [Command display (V)] button is clicked in the Input Parameters from File dialog box, or when the [Command display (V)] button is clicked in the Search result File Output dialog box.

The Command Display dialog box is shown below.

Command confirmation:

Confirm the command.

To execute the command, click the [Execute (E)] button.

To make a revision, click the [Cancel] button.

Details of the input items in the window are described below.

- [Execute (X)] button

Execute the displayed command.

- [Cancel] button

Return to the Search result File Output screen or Input Parameters from File screen from which this dialog box was opened.


Input Parameters from File

If you go to the menu and select [Control (C)] followed by [Import (I)...], or if you click the [Import] button in the toolbar or select [Import] in the right-click menu in the Dictionary View window, the Input Parameters from File dialog box is displayed.

When a table is selected in the Dictionary View window, an INSERT statement that controls the dynamic parameter data for all target columns is placed in [SQL input (S):].

The Input Parameters from File dialog box is shown below.

Details of the input items in the window are described below.

[SQL input (S):] text area: (Maximum 2,000,000 bytes)

Define the SQL statement that controls the dynamic parameter data. The following SQL statements can be specified:

- INSERT statement

- DELETE statement

- UPDATE statement

Specify the dynamic parameters for the columns in which to input data from the file. For details about dynamic parameters, refer to Dynamic parameter input and "*" input rules. For information about SQL statement syntax, refer to the HiRDB SQL Reference Manual.

[Input from history (H)] button

Displays the SQLHistory dialog box.

SQL statements from previously executed SQL can be imported into [SQL input (S):] for use.

[Decimal point data (INTEGER/SMALLINT)]

Specify how to handle data with decimal points in the input data file. The target data type is INTEGER/SMALLINT.

[Error when data has decimal point (N)]:

For columns of INTEGER/SMALLINT type data, data following a decimal point is handled as an error.

[Drop data after decimal point (C)]:

For columns of INTEGER/SMALLINT type data, data following a decimal point is rounded down.

[Use LOB input file (U)] check box

Check the box when using a LOB input file to input data to dynamic parameters for BLOB/CLOB type data.

Unchecked:

The corresponding column data values for columns of CLOB/BLOB type data are ignored. Null values are set as corresponding values when inputting dynamic parameter data. A LOB input file is not referenced.

Checked:

A LOB input file is referenced when inputting dynamic parameter data for columns of CLOB/BLOB type data.

Specify the format of the LOB input file that is input for LOB columns.

[LOB input file (L)]: Select this option when there is a file for each column and row.

[File reference directory (G):] text area: (Maximum 1,024 bytes)

Specify the directory that contains the LOB input file in the input data file, when the name of the LOB input file defined in the input data file is the relative path.

When directory is specified: The specified directory name is combined with the name of the LOB input file in the input data file to form the absolute pathname.

When no directory is specified: The directory containing the input data file is used as the directory name, and it is combined with the name of the LOB input file in the input data file to form the absolute pathname.

If a directory is specified but the absolute path is included in the name of the LOB input file described in the input data file, the specified directory is not referenced.

[Browse (W)...] button

The directory selection dialog box is displayed, and you can specify the name of the directory containing the LOB input file by selecting it.

[Character encoding]

Specify the character encoding for the input data file. Data is input using the specified character encoding. When using the SJIS version, this option cannot be selected. If this operand specification differs from the BOM in the input data file, the KFPX27913-E error occurs.

[UTF-8 (M)]: UTF-8 character encoding is used for input.

[UTF-16 [little-endian] (Z)]: UTF-16 (little-endian) character encoding is used for input.

[Input data file name (I):] text area: (Maximum 1,024 bytes)

Specify the name of the input data file containing the data for dynamic parameters.

[Browse (R)...] button

The file selection dialog box is displayed, and you can specify the name of the input data file containing the dynamic parameter data by selecting it.

[Advanced settings (T)...] button

The Advanced Settings dialog box for Input Parameters from File is displayed.

[Command display (V)] button

The Command Display dialog box for Input Parameters from File is displayed.

The settings are displayed in command format.

[Execute (X)] button

The specified additional/updated SQL statement is executed, and the dynamic parameters are input to the data from the input data file.

[Cancel] button

The settings are canceled, and the Input Parameters from File dialog box closes.


Input Parameter from File: Advanced Settings

The Advanced Settings dialog box is displayed when the [Advanced settings (T)] button is clicked in the Input Parameter from File dialog box.

The Advanced Settings dialog box is shown below.

Details of the input items in the window are described below.

[Message output destination]
 | Specify the output destination for messages.
 |  | [Window (W)]: Messages are output to the screen for display.
 |  | [Window and file (F)]: Messages are output to both the screen and a file.
 | [Message output file name (M):] text box
 |  | Specify the name of the file in which to output the messages. If a non-existent path is included in the file name, an error occurs at output. If a file already exists at the specified location, the output data is appended to the file. If the specified file name matches that of the input data file, an error occurs. (Maximum 1,024 bytes)
 | [Browse (R)...] button
 |  | The file selection dialog box is displayed, and you can specify the name of the message output file by selecting it.
[COMMIT interval (O)] check box
 | Select the [COMMIT interval (O)] check box to specify a numerical value for the interval at which COMMIT is issued. If a value other than 0 is specified, COMMIT is issued when the end of the file is reached, regardless of the AUTO COMMIT mode setting. If 0 is specified, COMMIT is not issued until the end of the file. However, the AUTO COMMIT mode determines whether or not COMMIT is issued when the end of the file is reached.
 |  | Not checked: 0 is assumed.
 |  | Checked: Specify a numerical value for the interval at which COMMIT is issued. A value ranging from 0 to 2,147,483,647 can be specified.
[Stop processing when error occurs (P)] check box
 | Specify whether to interrupt processing when input data is abnormal or an SQL execution error occurs.
 |  | Not checked: The subsequent data is processed when an error occurs.
 |  | Checked: Processing stops when an error occurs.
[OK] button
 | The settings are saved, and the Advanced Settings dialog box closes.
[Cancel] button
 | The settings are cancelled, and the Advanced Settings dialog box closes.


Search Result File Output

If you go to the menu and select [Control (C)] followed by [Export(X)...], click the [Export] button in the toolbar, or select the [Export] button in the right-click menu in the Dictionary View window, the Search Result File Output dialog box is displayed.

When a table is selected in the Dictionary View window, an SQL statement to search the table is placed in [SQL input(S):]

The Search Result File Output dialog box is shown below.

Details of the input items in the window are described below.

[SQLinput(S):]text area: (Maximum 2,000,000 bytes)

Define the SELECT statement.

For information about the SELECT statement syntax, refer to the HiRDB SQL Reference.

[Input from history (Ctr+H)…]button:

Displays the SQL history dialog box.

SQL statements from previously executed SQLs can be imported into [SQL input(S):] for use.

[Output data format]

Specify the format of the search result file output.

[DAT format(D)]:

Specify when the data format to output the search result is in DAT format.

An output file in this format can be used as an input data file in DAT format for the database load utility (pdload).

[Use extension functionality(E)]checkbox

Select the [Use extension functionality (E)] checkbox to output the search result in extended DAT format.

When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected.

Unchecked: Search result is output in DAT format.

Checked: Search result is output in extended DAT format.

An output file in this format can be used as an input data file in DAT format for the database load utility (pdload).

[Separator character(P):]text area :(Maximum 2 bytes)

The search result output data in DAT format or extended DAT format is separated by the specified delimiter.

To specify the single quotation (‘) as the separator character, consecutively write single quotation marks.

To specify the yen (\) as the separator character, consecutively write the yen symbol.

To specify a tab as the separator character, write ‘\t’.

If two or more characters are specified as the separator character, only the first character is valid. The second and later characters are invalid.

If the specified character matches that of the enclosure characters, an error occurs.

The available characters are [0-9], [a-z], [A-Z], a single-byte symbol, or a tab.

When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected.

A comma (,) is specified by default.

[Enclosure characters(Q):]text area:(Maximum 2 byte)

When the search result is output in DAT format or extended DAT format, the output data is enclosed by the specified enclosure characters.

To specify the single quotation (‘) as the enclosure character, consecutively write single quotation marks.

To specify the yen (\) as the enclosure character, consecutively write the yen symbol.

If two or more characters are specified as the enclosure character, only the first character is valid. The second and later characters are invalid.

If the specified character matches that of the separator characters, an error occurs.

The available characters are [0-9], [a-z], [A-Z] or a single-byte symbol.

When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected.

A double quotation mark (“) is specified by default.

[fixed-size data format(F)]:

Specify when the data format to output the search result is in fixed-size data format.

An output file in this format can be used as an input data file in fixed-size data format for the database load utility (pbload).

[binary(for FIX table) format(B)]:

Specify this when the data format to output the search result is in binary (for FIX table) format.

An output file in this format can be used as an input data file in binary (for FIX table) format for the database load utility (pbload).

[binary(for non-FIX table) format(N)]:

Specify this when the data format to output the search result is in binary (for non-FIX table) format.

An output file in this format can be used as an input data file in binary (for non-FIX table) format for the database load utility (pbload).

However, it cannot be used as an input file in binary format for a FIX table.

[File output mode]

Specify the data format to output the search result.

The format of the existing file is not checked.

[Creation mode(C)]:If the file already exists, the file is overwritten (the existing information is deleted and new information is written).

[Addition mode (A)]:If the specified file already exists, new information is added to the file.

[Output of LOB input file (U)]checkbox

Select the [Output of LOB input file (U)] checkbox to output the column data in the BLOB/CLOB data type to a file other than the search result file.

When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected.

Unchecked:

If data length for the BLOB/CLOB data type is zero, nothing is output.

If the data length is one or more bytes, a null value is output to the search result file.

Checked:

For data other than a null value, the name of the LOB data file or the LOB column data file is output to the search result file.

If the data is a null value, nothing is output.

[Create LOB input file(L)]:

A file is created for each LOB data.

The file name to be created is output as [LO column name + search serial number].

[Create LOB input file by column (O)]:

A file is created to contain the multiple LOB data in one LOB column.

The file name to be created is output as the target LOB column name.

When the LOB column name includes lowercase characters, the LOB column name is enclosed with brackets (()).

If the same file exists, the file is overwritten.

[Storage directory(G)]text area

Specify the output destination of the output LOB input file or the columned LOB input file.

If a non-existent directory is specified, an error occurs when the data is output.

If this is omitted, the same location as the output data file is assumed.

(Maximum 1,024 bytes)

[Browse(W)…]button

This button displays the directory browse dialog box for users to select the name of the directory for storing the LOB input file or the LOB input file by column.

[Character encoding]

Specify the character encoding of the data to be output when the search result is output in DAT format or extended DAT format.

For the SJIS version, this operation is unavailable.

[UTF-8(M)]: UTF-8 character encoding is used for output.

[UTF-16[big-endian](Y)]: UTF-16 (big-endian) character encoding is used for output.

[UTF-16[little-endian](Z)]: UTF-16 (little-endian) character encoding is used for output.

A value that cannot be displayed in these character codes is output as “#”.

This operand, if specified, does not affect the data output from the BINARY, BLOB, and CLOB columns.

[Search result file name(I):]text area:(Maximum 1,024 bytes)

Specify the name of a file where the search result is output.

If the file name includes a non-existent path, an error occurs when the data is output.

With the [File output mode] specified, the file is overwritten (the existing information is deleted and new information is written) or new information is added to the file.

[Browse(R)…]button

This button displays the file browse dialog box for users to select the search result file.

[Advanced settings(T)…]button

The advanced setting dialog box for the search result file output is displayed.

[Command Display (V)…]button

The command view dialog box for the search result file output is displayed.

[Execute(X)]button

The specified SQL statement is executed, and the results are output to the file.

[Cancel]button

The settings are canceled, and the Search Result File Output dialog box closes.


Search result File Output: Advanced Settings

The Advanced Settings dialog box is displayed when the [Advanced settings (T)] button is clicked in the Search result File Output dialog box.

The Advanced Settings dialog box is shown below.

Details of the input items in the window are described below.

[Progress message output interval (V)] check box
 | Select the [Progress message output interval (V)] check box to specify the output interval for progress messages.
 |  | Unchecked: Progress messages are not output.
 |  | Checked: Specify a numerical value for the interval at which to output progress messages. A value ranging from 100 to 100,000,000 can be specified. (Maximum 6 bytes)
 |  |  | [Message output destination]
 |  |  |  | Specify the output destination for progress messages.
 |  |  |  | [Window (W)]: Messages are output to the screen for display.
 |  |  |  | [Window and file (F)]: Messages are output to both the screen and a file.
 |  |  |  | [Message output file name (M):] text box
 |  |  |  |  | Specify the name of the file in which to output the progress messages. If a non-existent path is included in the file name, an error occurs at output. If a file already exists at the specified location, the output data is appended to the file. An error occurs when this operand is specified without specifying the -m operand. If the specified file name matches that of the output data file, an error occurs. (Maximum 1,024 bytes)
 |  |  |  | [Browse (R)...] button
 |  |  |  |  | The file selection dialog box is displayed, and you can specify the name of the progress message output file by selecting it.
[Linefeed code]
 | Specify the linefeed code that is appended to each row of the search result when they are output in DAT format, extended DAT format, or fixed-size data format. When binary (for FIX table) format or binary (for non-FIX table) format is specified for the output format, these options are unchecked and cannot be selected.
 |  | [CRLF (C)]: Linefeed code is output using 0x0D and 0x0A.
 |  | [LF (L)]: Linefeed code is output using 0x0A.
[Output column names (O)] check box
 | The extracted column names are output in the first row when the search result are output in DAT format or extended DAT format. When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected.
 |  | Unchecked: The extracted column names are not output in the first row.
 |  | Checked: The extracted column names are output in the first row.
[Remove trailing whitespace (B)] check box
 | Select the [Remove trailing whitespace (B)] check box to prevent whitespace from being appended to column data, when CHAR, NCHAR, and MCHAR type column data is output in the search result in DAT format or extended DAT format. When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, this option is unchecked and cannot be selected. You can reduce the volume of data that is output by using this operand.
 |  | Unchecked: Whitespace at the end of column data is output when CHAR, NCHAR, and MCHAR type column data is output.
 |  | Checked: Whitespace at the end of column data is not output when CHAR, NCHAR, and MCHAR type column data is output.
 | The whitespace referred to here varies depending on the data type. CHAR type, MCHAR type: Single-byte whitespace NCHAR type: Double-byte whitespace
[Enclosure characters]
 | When fixed-size data format, binary (for FIX table) format, or binary (for non-FIX table) format is specified for the output format, these options are unchecked and cannot be selected.
 |  | [Use (U)]: Select this option to enclose the specified type of output data with enclosure characters, when the search results are output in DAT format or extended DAT format.
 |  | [Do not use (N)]: Select this option to not enclose the output data with enclosure characters, when the search result are output in DAT format or extended DAT format.
 | [Data type]
 |  | [DATE type (A)] check box
 |  |  | Select this option to enclose the output data with enclosure characters, when the search results are output in DAT format or extended DAT format. The target data type is DATE.
 |  |  |  | Unchecked: The targeted data type is output without enclosure characters before and after the output data.
 |  |  |  | Checked: The targeted data type is output with enclosure characters before and after the output data.
 |  | [TIME type (T)] check box
 |  |  | Select this option to enclose the output data with enclosure characters, when the search results are output in DAT format or extended DAT format. The target data type is TIME when connected to HiRDB.
 |  |  |  | Unchecked: The targeted data type is output without enclosure characters before and after the output data.
 |  |  |  | Checked: The targeted data type is output with enclosure characters before and after the output data.
 |  | [TIMESTAMP type (S)] check box
 |  |  | Select this option to enclose the output data with enclosure characters, when the search results are output in DAT format or extended DAT format. The target data type is TIMESTAMP.
 |  |  |  | Unchecked: The targeted data type is output without enclosure characters before and after the output data.
 |  |  |  | Checked: The targeted data type is output with enclosure characters before and after the output data.
[OK] button
 | The settings are saved, and the Advanced Settings dialog box closes.
[Cancel] button
 | The settings are canceled, and the Advanced Settings dialog box closes.


Updating table data(Control)

Description of update operation control

(1)

(3)

(4)

(2)

(1) [Operation when an error occurs:] combo box

When the update target exists, the operation is available.

[No ROLLBACK]

ROLLBACK is not performed while the transaction continues.

Select this option when update is successfully performed by correcting, for example, the invalid edited data.

Note that every time the [Update] button is clicked, the update SQL is executed while the transaction continues, and this keeps using the HiRDB resources such as the line lock or the system log. Therefore, if this situation is left unattended for a long time, the following failures might occur:

- When the business application is in operation, this application is waiting for the row lock to be released, causing the response delay or SQL execution timeout to occur.

- Because the system log cannot be unloaded, the system log is used up, causing HiRDB to go down.

Immediately correct the data with the error, and click the [update] button or close the output window to exit the transaction.

[ROLLBACK] (default)

ROLLBACK is performed. The table data on the screen remains undeleted, but the table data cannot be edited.

Select this option when correcting the edited data is not able to solve the error.

(2) [Update] button

When the update target exists, the operation is available.

An update SQL is generated based on the search data and the edited data, and execute the SQL.

Execute multiple update SQLs. If an error occurs in any of the SQLs, perform the operation selected in [Operation when an error occurs:].

If an error, such as invalid data, occurs and the data cannot be successfully updated, the background color of the line where the data was not able to be updated is displayed in red. The SQL statement and the error message are output to the [Result] tab. Check the [Result] tab.

If multiple rows are to be updated on the database for one edited row, the background color of these rows are displayed in orange. The relevant SQL statement and the KFPX27925-E are output to the [Result] tab.

If multiple rows are to be updated, the selected operation in [Operation when an error occurs:] is ignored and the data remain on the window, but rollback is performed. After that the table data cannot be edited. To edit the table data, you need to execute the search SQL again.

If the update target is not multiple rows and no error occurs, the operation specified in the environment setting option, [Operation with the [Update] button is successful:], is performed.

(3) Message area

When executing a search SQL, if data is edited or the [Update] button is clicked, an information or warning message about the update functionality is displayed.

If there is no message, the area is not displayed.

If there is a message, clicking the F1 key while focusing on the area displays the help of the message.

(4) Data area

You can edit the data based on the search result.

If you want to directly change the data value in a cell, select the cell and click the same cell, and you enter to the edit mode.

To cancel the edit, press the [Esc] key. The edit mode exits.

To complete the editing, enter the end-of-line key or move to another cell.

At this time, check if the value corresponding to the data type was entered.

For details about the check details, see "Input value check ".

If the value is determined to be correct, the font of the edited cell changes to bold and the background color of the edited row changes to yellow, and then the edit mode exits.

To set a null value, select the cell and then select the [Set null value] from the right-click menu.

To delete the row data, select the target row header and then select [Delete] from the right-click menu.

To add a new row data, select [Add] from the right-click menu.

For details about values to input, see "Value to input at the time of addition ".

The following table shows the status when clicking the edit, delete, add, or update button and the display type.

Status and expression of the table data on the screen

# | Status | Row header | Background color | Display column
1 | Unedited row |  | White | No bold
2 | Row including a cell with its value changed | UPD | Yellow | Bold
3 | Added row by [Add] | INS | Yellow | Bold
4 | Deleted row by [Delete] | DEL | Light gray | No bold
5 | Row where [Update] (UPDATE/INSERT) was successful |  | Yellow | No bold
6 | Row where [Update] (DELETE) was successful |  | Gray | No bold
7 | Row where [Update] (UPDATE) failed | UPD | Red | Bold
8 | Row where [Update] (INSERT) failed | INS | Red | Bold
9 | Row where [Update] (DELETE) failed | DEL | Red | No bold
10 | Row where multiple values were updated by [Update] (UPDATE) | UPD | Orange | Bold
11 | Row where multiple values were updated by [Update] (DELETE) | DEL | Orange | No bold
12 | Row including a cell in the hexadecimal format |  | Light blue | No bold

Status of table data on the screen and menu operation

# | Status of selected line (*2) | [Cancel edit] | [Set null value] | [Delete] (*1) | [Paste]
1 | Unedited row | N/A | Yes | Yes | Yes
2 | Row including a cell with its value changed | Yes | Yes | No | Yes
3 | Added row by [Add] | Yes (*1) | Yes | No | Yes
4 | Deleted row by [Delete] | Yes (*1) | No | Yes: N/A | No
5 | Row where [Update] (UPDATE/INSERT) was successful | N/A | Yes | Yes | Yes
6 | Row where [Update] (DELETE) was successful | N/A | No | No | No
7 | Row where [Update] (UPDATE) failed | Yes | Yes | No | Yes
8 | Row where [Update] (INSERT) failed | Yes (*1) | Yes | No | Yes
9 | Row where [Update] (DELETE) failed | Yes (*1) | No | Yes: N/A | Yes
10 | Row where multiple values were updated by [Update] (UPDATE) | No | No | No | No
11 | Row where multiple values were updated by [Update] (DELETE) | No | No | No | No
12 | Row including a cell in the hexadecimal format | No | No | No | No

*1: The operation is available only when a row is selected

*2: In an operation when an error occurs, after [Rollback] is selected and [Update] is clicked, if any row (in red) with an error exists, the operation above is unavailable.


Updating table data(Input Check)

- Checking input values

If the data is edited, the edited value is checked to make sure that it is an "allowable literal" and within the "allowable range."

For the meaning of literals, see the HiRDB SQL Reference manual.

When entering a character string literal, you do not need to enclose it with quotation marks (').

Allowable literals and ranges

Column data type | Allowable literal | Allowable range
INTEGER | Integer literal Decimal literal | The following range represented by integers: -2147483648 to 2147483647
SMALLINT | Integer literal Decimal literal | The following range represented by integers: -32768 to 32767
FLOAT | Integer literal Decimal literal Floating-point literal | The following range represented by double-precision floating point numbers: -1.7 ´ 10308 to +1.7 ´ 10308
SMALLFLT | Integer literal Decimal literal Floating-point literal | The following range represented by single-precision floating point numbers: -3.4 ´ 1038 to +3.4 ´ 1038
DECIMAL(m,n) | Integer literal Decimal literal | The range of 1 £ m £ 38, 0 £ n £ 38, and n £ m, and in the defined digits m: precision; n: scaling
CHAR (n) VARCHAR (n) MCHAR (n) MVARCHAR (n) | Character string literal | SJIS: The number of bytes needed to represent the input character string in SJIS £ n Unicode: The number of bytes needed to represent the input character string in Unicode £ n ´ 2 n: defined length
NCHAR (n) NVARCHAR (n) | Character string literal | SJIS: The number of bytes needed to represent the input character string in SJIS £ n ´ 2 Unicode: The number of bytes needed to represent the input character string in Unicode £ n ´ 2 n: defined length
DATE | Character string literal | Valid date in the YYYY-MM-DD format YYYY: 0001 to 9999 MM: 01 to 12 DD: 01 to 31
TIME | Character string literal | In the hh:mm:ss format hh: 00 to 23 mm: 00 to 59 ss: 00 to 59
TIMESTAMP [(p)] | Character string literal | In the YYYY-MM-DD hh:mm:ss [.nnnnnn] format YYYY: 0001 to 9999 MM: 01 to 12 DD: 01 to 31 hh: 00 to 23 mm: 00 to 59 ss: 00 to 59 nnnnnn: Fractional seconds in 0 to 6 digits (n: 0 to 9) The date must be valid.
INTERVAL YEAR TO DAY | Integer literal Decimal literal | The following range represented by integers: -99991199 to 99991199
INTERVAL HOUR TO SECOND | Integer literal Decimal literal | The following range represented by integers: -995959 to 995959

If the entered value is not the allowable literal or within the allowable range, an exclamation mark (!) appears at the beginning of the line being edited, and the following message appears in the status bar.

"The input value is invalid. (aa....aa)"

aa....aa: data type

Example:

"F89S0000101BL" is in the GOODS_CODE column, and you wrongly enter "ab" in the PRICE column (data type: DECIMAL(7,2)), and then press the Enter key. The exclamation mark (!) appears at the left side of the row. (The cell is still in edit mode.)

When you place the mouse cursor on the exclamation mark, the message appears.

Enter the value of the correct data type, and then press the Enter key. The exclamation mark disappears.


Updating table data(Method)

Update method

Clicking the [Update] button triggers the following procedure to be performed.

1. Refers to rows one by one from the first row to the last row in the table, and repeats the creation and execution of an SQL statement (processing of the steps 2 through 4 below).

2. Determines whether to edit the row based on whether data before update exists and the background color of the row and the font of each cell.

3. If the row is determined to be edited, an update SQL statement is built based on the elements described in 3-1 through 3-3 below.

3-1 The following table shows the relationship between the type of an SQL statement to generate and the status of the row.

Types of SQL statement to generate

# | SQL statement | Data before update | Background color or the row | Font of the cell
1 | UPDATE statement | Yes | Yellow | Bold
2 | DELETE statement | Yes | Light gray | Not bold
3 | INSERT statement | No | Yellow | Bold

If the status of the row is other than the above, no SQL statement is generated and the steps before the step 4 are not performed.

3-2 If an SQL statement to generate is an UPDATE or INSERT statement, an element for the update target column is created.

For an UPDATE statement, the column including the edited data only is the update target.

For an INSERT statement, the displayed column that can be edited is the update target. (*1)

3-3 If an SQL statement to generate is an UPDATE or DELETE statement, an element of the update condition is created.

The update conditions vary depending on whether or not the search result includes the column making up the unique key.

3-3-1 When the search result includes the column making up the unique key:

The column making up the unique key is used as the condition specification.

If multiple indexes having the unique key exist, the indexes are sorted by the index name in an ascending order and then the first index is used.

3-3-2 For cases other than the above

Among the items output as the search result, the column that can be edited is used as a condition specification. (*2)

Although the SQL specification allows the BINARY column to be used as a search condition, displaying and editing data is not allowed, so this column is not included in a search condition.

4. Executes the built update SQL statement.

If the update was successful but multiple rows are the update target in the database for one edited row, rollback is performed and the processing ends with the KFPX27845-E dialog message displayed.

At this time, if an update SQL with an error already exists, the KFPX27844-E and KFPX27845-E messages are displayed in the same dialog box.

5. Performs the termination processing of the update.

5-1. If an error occurs, the KFPX27844-E dialog message is displayed and the operation selected in [Operation when an error occurs:] is performed.

5-2. If the situation does not apply to 5-1 above, incorporate the edits into the database.

Notes:

1. Lock control is not applied. If another UAP performs an update operation to the same table being edited, an inconsistency occurs between the data in the database and the update target data, and this might cause data corruption.

To prevent data from being updated by another UAP, the following method can be used:

(1) Before executing the search SQL statement, set the AUTO COMMIT mode to OFF.

(2) Specify WITH EXCLUSIVE LOCK for the search SQL and execute the search.

When using this mode, note the following:

- If a data update by an update SQL (manual input) is also used, manually perform COMMIT or ROLLBACK before executing the search processing to resolve the transaction.

When the search result is edited and updated by the [Update] button, the transaction is resolved by COMMIT at the time the update processing is successfully completed regardless whether the AUTO COMMIT mode is specified.

If an error occurs, the operation specified in [Operation when an error occurs:] is performed.

Note that the transaction might be resolved by ROLLBACK depending on the error.

- After the search is performed, the lock control has been applied to the target rows.

With this situation left unchanged, accesses from other applications are restricted, so no response or an SQL error (lock timeout) might occur in other application.

2. In the status before the [Update] button is clicked, if multiple rows with the same update condition exist and the two or more these rows are edited, an SQL statement execution error occurs in the second row onwards.

*1) If the search result includes the elements of the repeat column, the INSERT statement fails.

*2) As shown in the example below, if the search result includes the elements of a repeat column, these elements are used for specifying the condition. Therefore, if the repeat column itself is null, an error might occur because no update target exists.

Example: SELECT C1,C2[1],C2[2],C2[3],C3 FROM T1;

The following shows the example of edited contents and an update SQL developed when the [Update] button is clicked.

For details about the definition and data value of the table and index, see "Database for examples".

Example 1. A case where one row is updated on the screen while two or more rows are to be updated in the database

Search SQL:

SELECT GOODS_NAME,PRICE FROM GOODS WHERE MAKER_NAME='Laser';

On the screen (before editing):

GOODS_NAME | PRICE
Blouse[White] | 00035.00
Blouse[Yellow] | 00032.00
Skirt[Green] | 00045.20

On the screen (before editing):

GOODS_NAME | PRICE
Blouse[White] | 00034.50
Blouse[Yellow] | 00032.00
Skirt[Green] | 00045.20

↓The [Update] button is clicked.

Update SQL:

UPDATE GOODS SET PRICE=00034.50 WHERE GOODS_NAME=? AND PRICE=?;

First parameter value: ’Blouse[White]’

Second parameter value: 00035.00

=> Information without "Laser" in the MAKER_NAME column is also the update target, so multiple rows might be updated.

In this example, by including the index key column (GOODS_CODE column) having the unique attribute in the SQL statement for the search, the update row can be identified and therefore the data is correctly updated.

If no index key column having the unique attribute exists in the table, when all the displayed search columns are set as the search condition, the updated row must be able to be identified.

For the table in the example, update is possible by including the MAKER_NAME column in the search column.


- Values inserted during the add operation

When Add is selected from the menu, a row is added, and null values are inserted in editable columns.

- Notes

(a) We recommend that you set the client environment variable PDSWAITTIME to 0 and then run the HiRDB SQL Executer if you use the facility for updating table data.

If PDSWAITTIME is not 0, the connection may be lost depending on the setting or default. In this case, the database cannot be updated.

(b) The table is not internally locked.

To prevent other users from updating the data values, execute the LOCK TABLE statement before executing the search SQL statement.

(c) You cannot enter control character codes and any external character values that cannot be represented.

(d) Your screen may flicker when you are editing large object data.

To edit large object data, copy it into a text editor, edit it as needed, and then paste the values into the cells.

(e) Unless the requirements presented in Required application and Database to be connected (destination database) are met, you cannot edit the table data on the screen even if you execute the search SQL.

You cannot edit the table data on the screen as well if you connect to the database in the following case or if an error occurs during the process of getting information for the update facility.

(e-1) The client environment variable PDDBLOG is not set to ALL.

(f) You cannot edit the table data on the screen if you execute a search SQL that meets any of the following conditions:

(f-1) A table join with a different table

(f-2) Multiple query specifications (for example, using a UNION or EXPECT clause, or subqueries)

(f-3) A query to a system table (MASTER schema)

(f-4) Number of items output as search results being 256 or more

(f-5) A query to a read-only viewed table

(f-6) A query to a table with a WITHOUT ROLLBACK specification or a viewed table for which the base table is a table with a WITHOUT ROLLBACK specification

(f-7) Repetition column specified as an item to be output as search results

When the above search SQL is executed, any of the following messages may appear in the Data tab:

(f-1) or (f-2): "KFPX27804-I Not editable: Table join or multiple query specifications"

(f-3): "KFPX27805-I Not editable: System table"

(f-4): "KFPX27806-I Not editable: Number of output items 256 or more"

(f-5): "KFPX27807-I Not editable: Read-only viewed table"

(f-6): "KFPX27810-I Not editable: Table with a WITHOUT ROLLBACK specification"

(f-7): "KFPX27808-I Not editable: Repetition structure contained."

(g) If you use the "[AS] column name" syntax to name select expressions, the table data is updated by using the specified column name. Thus, the data may be corrupted if there are multiple columns with the same name.

You must not edit the table data on the screen when you execute the search SQL by using "[AS] column name."

(h) Unless you specify the column or an "*" as the item to be output as search results, you cannot edit the column data on the screen. (If you want to edit the column data, you must not include the "[AS] column name" in the search SQL statement.)

When you try to edit the column data, the following message is displayed in the status bar:

"You cannot edit data. (The data type or select expression is not supported.)"

(i) If you specify any of the following data types as the item to be output as search results, you cannot edit the column data on the screen.

Abstract data type, ROW, BINARY, or BLOB

When you try to edit the column data, the following message is displayed in the status bar:

"You cannot edit data. (The data type or select expression is not supported.)"

(j) If the items output as search results consist only of the columns that are not editable as described in (h) and (i) above, you cannot edit the table data on the screen. The following message is displayed in the Data tab:

"KFPX27809-I Not editable: There are no editable items."

(k) The update is executed as described below.

The table data is referenced in each of the rows from first to last. When a row with a yellow or gray background is found, it is included in the SQL statement, and then the SQL statement is executed.

If there are multiple rows with the same update conditions, and if two or more such rows are edited before the Update button is pressed, an error occurs when the SQL statement is executed against the second or subsequent row.

Column to be updated: Only the column for which data is edited is updated.

If a row is added, the editable column on the screen is updated.

If search results contain repetition column elements, an error occurs during the process of adding a row.

Update condition specifications

- If the column comprising the unique key is included in the items output as search results:

The column comprising the unique key is included in the condition specification.

If there are multiple indexes with unique keys, the index that comes first in ascending order is used.

If the column comprising the unique key has a null value, multiple rows may be updated.

If the column comprising the unique key has a value other than null, the row cannot be added or changed by using the same value as other rows.

- If the column comprising the unique key is not included in the items output as search results:

Of the items output as search results, the editable column is included in the condition specification.

Although SQL allows the use of the BINARY column as a search condition, the BINARY column is not included in the condition specification, as the data cannot be displayed nor edited.

As shown in the example below, the repetition column elements are included in the condition specification if the repetition column elements are included in search results. Thus, an error may occur when the repetition column itself is null.

Example: SELECT C1, C2[1], C2[2], C2[3], C3 FROM T1;

Timing of executing the update SQL:

When the Update button is pressed, the update SQL is executed.

If multiple rows in the database are to be updated, an error message appears indicating that multiple rows are to be updated.

(l) No (redo) facility is available for restoring the updated values or edited cell values to the original values.

However, edited cell values can be reset to the values at the time of searching only if the update is not yet executed.

Example: The cell in the first row and second column in the SELECT execution result table contains "Shiga."

You edit the cell in the first row and second column: The cell contains "Kusatsu."

You move the cell: (The edit is committed.)

You edit the cell in the first row and second column: The cell contains "Ootsu."

You move the cell: (The edit is committed.)

By selecting Undo edit from the menu, you can reset the value in the cell in the first row and second column to "Shiga."

However, you cannot reset the value to "Kusatsu."

(The update is executed.)

You can no longer reset the value to "Shiga."

(m) If you edit values by selecting Paste, the behavior is as described below.

(m-1) If the data to be copied (source data) contains delimiters (such as tab characters and new line characters), a tab character is interpreted as the end of a column, and a new line character is interpreted as the end of a row.

(m-2) No input values are checked.

As a character string pasted in the SMALLINT, INTEGER, SMALLFLT, FLOAT, DECIMAL, INTERVAL YEAR TO DAY, or INTERVAL HOUR TO SECOND data type column is included in the SQL statement as it is when the Update button is pressed, it may be interpreted as a select expression. In this case, the update is executed.

(m-3) If the source data contains delimiters (such as tab characters and new line characters), the behavior depends on the selection of the destination table to which the data is to be pasted.

- If only one range is selected as the destination, the number of columns and rows in the range is checked. If it is a multiple or multiples of the number of columns and rows of the source data, the source data is pasted in the destination repeatedly.

- If two or more ranges are selected as the destination, and if the source data contains only one value and no delimiters, the value is pasted in the selected cells. If the source data contains delimiters, and if two or more ranges are selected as the destination, the informational dialog box saying "Pasting in multiple selected ranges is not supported." appears.

- The cells in the destination are checked to make sure that the source data can be set in the range from the upper left to the lower right. If the source data cannot be set in this range, an informational dialog box saying "The source data contains more cells than the destination." appears.

- If the number of columns to be copied is one, the number of rows in the destination is checked, and if the number of rows is one, the source data is pasted in the destination columns repeatedly.

- If the number of rows to be copied is one, the number of columns in the destination is checked, and if the number of columns is one, the source data is pasted in the destination rows repeatedly.

- If none of the above is the case, the source data is pasted in the destination according to the number of columns and rows of the source data.

Example 1: The source data has three columns and five rows, and the table control has 10 columns and 10 rows.

If the cell at the eighth column and sixth row is selected as the destination, the source data is pasted in the cells from the eighth column and sixth row to the 10th column and 10th row.

If the cell at the eighth column and seventh row is selected as the destination, the informational dialog box is displayed.

If the cells in six columns and 10 rows starting from the second column and first row are selected as the destination, the source data is pasted in the cells from the second column and first row to the seventh column and 10th row.

If the cells in three columns and seven rows starting from the second column and first row are selected as the destination, the source data is pasted in the cells from the second column and first row to the fourth column and fifth row.

Example 2: The source data has one column and four rows, and the table control has 10 columns and 10 rows.

If the cell at the eighth column and seventh row is selected as the destination, the source data is pasted in the cells from the eighth column and seventh row to the eighth column and 10th row.

If the cells in three columns and one row starting from the eighth column and seventh row are selected as the destination, the source data is pasted in the cells from the eighth column and seventh row to the 10th column and 10th row.

If the cells in three columns and two rows starting from the eighth column and seventh row are selected as the destination, the source data is pasted in the cells from the eighth column and seventh row to the eighth column and 10th row.

If the cells in three columns and one row starting from the eighth column and eighth row are selected as the destination, the informational dialog box is displayed.

(n) If the data is found invalid during the process of checking input values, the behavior is as described below.

The menus, toolbar buttons, and right-click menus are disabled.

If you lock the computer and then unlock it, all key operations except the ESC (escape) key are disabled.

(o) In version 08-05 or earlier, the settings configured in the font settings window were applied to the output window. However, now the font style settings (italic, bold, strikethrough, and underline) are ignored, as the editing facility is supported.

(p) You cannot edit the data in a line that contains the cell displayed in hex format.

If you select multiple cells, and if you then select Set null from the right-click menu, no null values are entered in the cells of the line that contains a cell displayed in hex format. If you select multiple lines, and if you then select Delete from the right-click menu, the line that contains the cell displayed in hex format is not deleted.


Updating table data(Operation)

Example of update operation

The following describes the operation procedure by showing an example.

This example searches the products (GOODS) table, changes the target product name (GOODS_NAME) and the unit price (PRICE), and adds or deletes the target row.

(a) Enter the products (GOODS) table search SQL statement and execute it.

The search result is displayed in the [Data] tab.

(b) Change the table data.

Selecting the cell to edit and clicking the same cell enters the cell editing mode.

This example changes the [Socks[Black] in the GOODS_NAME column (in the same row as [00000000591BK] in the GOOGS_CODE column) to [Socks[NewBlack]], and [00032.00] in the PRICE column (in the same row as [F52X0000101YL] in the GOODS_CODE column) to [00032.80].

In the same procedure as the above, edit the PRICE column with the target value that is in the same row as [F52X0000101YL] in the GOODS_CODE.

(c) Add a row.

Select [Add] from the right-click menu.

Add a row to the last row.

(d) Delete a row.

This example deletes the row including [Sweater[Red]] in the GOODS_NAME column (in the same row as [F89S0000412RD] in the GOODS_CODE column).

Select the header of the target row.

Select [Delete] from the right-click menu.

(e) When the editing is completed, click the [Update] button.

Clicking the [Update] button executes one SQL statement (UPDATE, DELETE, or INSERT statement) at a time for the data in the edited row to be incorporated into the database.

To display the contents in the database again after the updated data is incorporated, re-execute the search SQL statement.


Updating table data(Requirement)

- Required application

To use this facility, the following application must be installed on the same machine.

- HiRDB client version 08-04 or later

- Database to be connected (destination database)

This facility can be used for the following destination database.

- HiRDB server version 08-04 or later

- Data types

The following HiRDB data types are editable.

- Numeric data (INTEGER, SMALLINT, DECIMAL, FLOAT, and SMALLFLT)

- Character data (CHAR and VARCHAR)

- National character data (NCHAR and NVARCHAR)

- Mixed character string data (MCHAR and MVARCHAR)

- Date data (DATE)

- Time data (TIME)

- Timestamp data (TIMESTAMP)

- Date interval data (INTERVAL YEAR TO DAY)

- Time interval data (INTERVAL HOUR TO SECOND)

The following HiRDB data types are not editable.

- Row data (ROW)

- Large object data (BLOB)

- Binary data (BINARY)

- Abstract data type


Information displayed as search results

The search results described here include the following information:

- Search results for the SELECT statement

- Output parameters for the CALL statement or GET DIAGNOSTICS statement

- Results-set for the CALL statement

Search results will be displayed according to the specified display format.

The display format will be either hexadecimal or character format.

The default setting is character format.

The display format can be changed by using the SET DUMPMODE command or the environment variable PDUAPDUMP.

Table 1 indicates the display area size of the column for each data type,

and table 2 lists the data display formats.

Table 1 : Display area size (character format)

Data type | Display area size (column) (see #1)
ROW | Total number of bytes for all the columns
INTERVAL YEAR TO SECOND | 16+scale
INTERVAL YEAR TO DAY | 10
INTERVAL HOUR TO SECOND | 8+scale
DATE | 10
TIME | 8(If the scale greater than 0, 9+scale)
TIMESTAMP | 19(If the scale greater than 0, 20+scale)
BINARY | Maximum number of stored bytes.(#2)
BLOB | Maximum number of stored bytes.(#2)
CLOB | Maximum number of stored bytes.(#2)
MVARCHAR | Maximum number of stored bytes.(#2)
MCHAR | Number of stored bytes.(#2)
NVARCHAR | Maximum number of stored characters.*2(#2)
NCHAR | Number of stored characters.*2(#2)
VARCHAR | Maximum number of stored bytes.(#2)
CHAR | Number of stored bytes.(#2)
FLOAT | 23
SMALLFLT | 23
DECIMAL | precision+2
INTEGER | 12
SMALLINT | 6

#1: If the display area size is smaller than the length of the

column name, the length of the column name will be applied.

#2: You can use the SET xxxx LIMIT command to reduce the size.

Table 2 : Data display format for each data type

Data type | Column data display format (see #1)
ROW | - Data is output in character format. - If the data contains an invalid character (ASCII code 0x00 to 0x1F, such as a null character), the invalid character is displayed as a colon (:). - If the data length is 0 bytes, no data is output.
INTERVAL YEAR TO SECOND | - A right-align numerical value is output in [-]yyyymmddhhmmss.[nnnnnn] format. - A minus sign (-) is added to column data. - The fractional part depends on the data definition length.
INTERVAL YEAR TO DAY | - A right-align numerical value is output in [-]yyyymmdd. format. - A minus sign (-) is added to column data.
INTERVAL HOUR TO SECOND | - A right-align numerical value is output in [-]hhmmss.[nnnnnn] format. - A minus sign (-) is added to column data. - The fractional part depends on the data definition length.
DATE | - Column data is output in the yyyy-mm-dd format (character string format).
TIME | - Column data is output in the hh:mm:ss[.nnnnnn] format (character string format). - The fractional part depends on the data definition length.
TIMESTAMP | - Column data is output in the yyyy-mm-dd hh:mm:ss[.nnnnnn] format (character string format). - The fractional part depends on the data definition length.
BINARY BLOB CLOB MVARCHAR MCHAR NVARCHAR NCHAR VARCHAR CHAR | - Data is output in character format. - If the data contains an invalid character (ASCII code 0x00 to 0x1F, such as a null character), the invalid character is displayed as a colon (:). - If the data length is 0 bytes, no data is output.
FLOAT SMALLFLT DECIMAL INTEGER SMALLINT | - A right-align numerical value is output as a decimal number character string. - A minus sign (-) is added to column data. - "Integer.decimal" is output as a decimal point. - The exponent (e) of mantissa is output as a floating point.

#1: Excluding ROW data, if the data is a null value, a null value

(the initial value is an asterisk (0x2A)) equal to the size of

the display area is displayed.