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 MySQL 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

EXTRACT[RECORD] (channel,KEY=)

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

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

TRUNCATE TABLE table-name
If text fields are configured: TRUNCATE TABLE 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 sort-information DESC Get the first record.
Extract the key information from the record.

LOCK (channel)

SELECT 1 FROM table-name 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 WHERE 1=2

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

SELECT 1 FROM text-table-name WHERE 1=2

PKY( )

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

PREAD[RECORD] (channel)

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 DESC

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 table-name TO new-table-name

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
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 MySQL 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 MySQL Reference, version 8.9.1. Open this page in the PDF