Generated SQL Code

This section shows the resulting SQL syntax for Thoroughbred functions (Italic typeface). They are presented in alphabetical order.

SQL Syntax for Thoroughbred Functions

CLOSE (channel)

COMMIT
This is performed via a direct OCI call, not as a literal SQL statement.

ERASE

DROP TABLE table-name
If text fields are configured: DROP TABLE text table-name

EXTRACT[RECORD] (channel)

SELECT columns FROM table-name WHERE key-conditions >=
current-key ORDER BY sort-information
Get key from first record or current SELECT
SELECT columns FROM table-name WHERE key-conditions =
current-key ORDER BY sort-information FOR UPDATE NOWAIT

EXTRACT[RECORD] (channel,KEY=)

SELECT columns FROM table-name WHERE key-conditions =
current-key ORDER BY sort-information FOR UPDATE NOWAIT

FKY( )

SELECT columns FROM table-name WHERE key-conditions
ORDER BY sort-information
Get the first record.
Extract the key information from the record.

INITFILE

DELETE FROM table-name
If text fields are configured: DELETE FROM text-table-name

KEY( )

If a record is not extracted
SELECT columns FROM table-name WHERE key >
key of last record read ORDER BY sort-information
Otherwise: Extract the key information from the record.

LKY( )

SELECT columns FROM table-name ORDER BY reverse-sort
Get the first record.
Extract the key information from the record.

LOCK (channel)

LOCK TABLE table-name IN EXCLUSIVE MODE NOWAIT

OPEN (channel,OPT=“LINK|CREATE”)

CREATE TABLE table-name
If text fields are configured: CREATE TABLE text-table-name
LOCK TABLE table-name IN ROW SHARE MODE NOWAIT
If text fields are configured:
LOCK TABLE text-table-name IN ROW SHARE MODE NOWAIT

For sort0:
CREATE UNIQUE INDEX I_table-name_0

For sortn:
CREATE INDEX I_table-name_n

OPEN (channel, OPT=“LINK”)

LOCK TABLE table-name IN ROW SHARE MODE NOWAIT

OPEN (channel, OPT=“LINK|TEXT”)

LOCK TABLE text-table-name IN ROW SHARE MODE NOWAIT

PKY( )

If a record is not extracted
SELECT /*+ INDEX_DESC(table-name) */ columns FROM table-name
WHERE key-conditions ORDER BY reverse-sort
Otherwise: Extract the key information from the record.

PREAD[RECORD] (channel)

If a SELECT is in memory get the next record
Else
SELECT /*+ INDEX_DESC(table-name) */columns FROM table-name
WHERE key-conditions ORDER BY reverse-sort

READ[RECORD] (channel,KEY=)FIND[RECORD](channel,KEY=)

If a SELECT is in memory get the next record
Else
SELECT columns FROM table-name WHERE key-conditions >=
current key ORDER BY sort-information

REMOVE (channel,KEY=)

SELECT columns FROM table-name WHERE key-conditions
FOR UPDATE NOWAIT
DELETE FROM table-name WHERE key-conditions

RENAME

RENAME table-name TO new-table-name

UNLOCK (channel)

LOCK TABLE table-name IN ROW SHARE MODE NOWAIT

WRITE[RECORD] (channel,KEY=)

Get key value from the record data
SELECT columns FROM table-name WHERE key-conditions =
current-key FOR UPDATE NOWAIT
If the record exists:
UPDATE table-name SET column = value,…
Else:
INSERT INTO table-name column … VALUES (value…)
fi
COMMIT
This is performed via a direct OCI call, not as a literal SQL statement.

Multiple Select Statements

The following Basic commands may produce multiple select statements when sequentially accessing tables with multiple key fields defined in the Format:

READ[RECORD]
FIND[RECORD]
PREAD[RECORD]
EXTRACT[RECORD]
KEY( )
PKY( )
FKY( )

Unsupported Basic Directives

There is no equivalent SQL code for the following Basic directives:

ADD
ADDSORT
FILE
REMSORT

These items are not supported when applied to server tables.

From the TS DataServer for Oracle Reference, version 8.9.1. Open this page in the PDF