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)

SELECT 1 FROM table-name WITH (HOLDLOCK, TABLOCKX) WHERE 1=2

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

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

For sort0:
CREATE UNIQUE INDEX I_table-name_0

For sortn:
CREATE INDEX I_table-name_n

OPEN (channel, OPT=“LINK”)

SELECT 1 FROM table-name WITH (HOLDLOCK) WHERE 1=2

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

SELECT 1 FROM table-name WITH (HOLDLOCK) WHERE 1=2

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

A table rename requires an SQL stored procedure.

UNLOCK (channel)

No SQL equivalent. Data will be committed and Basic’s internal lock on the channel will be released.

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 SQL 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 MS SQL Server, version 8.9.1. Open this page in the PDF