Introduction

DBISAM requires that certain naming conventions be adhered to when executing SQL. The following rules and naming conventions apply to all supported SQL statements in DBISAM.

Table Names

ANSI-standard SQL specifies that each table name must be a single word comprised of alphanumeric characters and the underscore symbol (_). However, DBISAM's SQL is enhanced to support multi-word table names by enclosing them in double quotes ("") or square brackets ([]):

SELECT *
FROM "Customer Data"

DBISAM's SQL also supports full file and path specifications in table references for SQL statements being executed within a local session.  Table references with path or filename extensions must be enclosed in double quotes ("") or square brackets ([]). For example:

SELECT *
FROM "c:\sample\parts"

or

SELECT *
FROM "parts.dat"

Note

It is not recommended that you specify the .dat file name extension in SQL statements for two reasons:

1) First of all, it is possible for the developer to change the default table file extensions for data, index, and BLOB files from the defaults of ".dat", ".idx", and ".blb" to anything that is desired.

2) Using file paths and extensions at all in SQL statements makes the SQL less portable to other database engines or servers.

DBISAM's SQL also supports database name specifications in table references for SQL statements being executed within a remote session.  Table references with database must be enclosed in double quotes ("") or square brackets ([]). For example:

SELECT *
FROM "\Sample Data\parts"

Note

The database name used with remote sessions is not a directory name like it is with local sessions. Instead, it must be a logical database name that matches that of a database defined on the database server that you are accessing with the SQL statement.

To use an in-memory table in an SQL statement within both local and remote sessions, just prefix the table name with the special "Memory" database name:

SELECT *
FROM "\Memory\parts"

Column Names

ANSI-standard SQL specifies that each column name be a single word comprised of alphanumeric characters and the underscore symbol (_).  However, DBISAM's SQL is enhanced to support multi-word column names. Also, DBISAM's SQL supports multi-word column names and column names that duplicate SQL keywords as long as those column names are enclosed in double quotes ("") or square brackets ([]) or prefaced with an SQL table name or table correlation name. For example, the following column name consists of two words:

SELECT E."Emp Id"
FROM employee E

In the next example, the column name is the same as the SQL keyword DATE:

SELECT weblog.[date]
FROM weblog

String Constants

ANSI-standard SQL specifies that string constants be enclosed in single quotes (''), and DBISAM's SQL follows this convention. For example, the following string constant is used in an SQL SELECT WHERE clause:

SELECT *
FROM customer
WHERE Company='ABC Widgets'

Note

String constants can contain any character in the ANSI character set except for the non-printable characters below character 32 (space). For example, if you wish to embed a carriage-return and line feed in a string constant, you would need to use the following syntax:

UPDATE customer SET Notes='ABC Widgets'+
#13+#10+'Located in New York City'

The pound sign can be used with the ordinal value of any ANSI character in order to represent that single character as a constant.

Date, Time, TimeStamp, and Number Constants

DBISAM's SQL uses ANSI/ISO date and number formatting for all date, time, timestamp (date/time), and number constants, which is consistent with ANSI-standard SQL except for missing support for date and time interval constants, which are not supported in DBISAM's SQL currently. The formats are as follows:

All date, time, and timestamp constants must be enclosed in single quotes ('') when specified in an SQL statement. For example:

SELECT *
FROM orders
WHERE (saledate <= '1998-01-23')

Boolean Constants

The boolean constants TRUE and FALSE can be used for specifying a True or False value. These constants are case-insensitive (True=TRUE).  For example:

SELECT *
FROM transfers
WHERE (paid = TRUE) AND NOT (incomplete = FALSE)

Table Correlation Names

Compliant with ANSI-standard SQL, table correlation names can be used in DBISAM's SQL to explicitly associate a column with the table from which it is derived. This is especially useful when multiple columns of the same name appear in the same query, typically in multi-table queries. A table correlation name is defined by following the table reference in the SQL statement with a unique identifier. This identifier, or table correlation name, can then be used to prefix a column name. The base table name is the default implicit correlation name, irrespective of whether the table name is enclosed in double quotes ("") or square brackets ([]). The base table name is defined as the table name for the DBISAM table not including the full path or any file extensions. For example, the base table name for the physical table "c:\temp\customer.dat" is "customer" as show in this example:

SELECT *
FROM "c:\temp\customer.dat"
LEFT OUTER JOIN "c:\temp\orders.dat"
ON (customer.custno = orders.custno)

You may also use the physical file name for the table as a table correlation name, although it's not required nor recommended:

SELECT *
FROM "customer.dat"
LEFT OUTER JOIN "orders.dat"
ON ("customer.dat".custno = "orders.dat".custno)

Note

Table correlation names are case-sensitive in any Kylix version of DBISAM. This is due to the fact that table names under Linux are case-sensitive, and since the default table correlation names are based upon the table names the table correlation names must also be case-sensitive.

And finally, you may use a distinctive token as a correlation name (and prefix all column references with the same correlation name):

SELECT *
FROM "customer" C
LEFT OUTER JOIN "orders" O
ON (C.custno = O.custno)

Column Correlation Names

You can use the AS keyword to assign a correlation name to a column or column expression within a DBISAM SQL SELECT statement, which is compliant with ANSI-standard SQL. Column correlation names can be enclosed in double quotes ("") and can contain embedded spaces. The following example shows how to use the AS keyword to assign a column correlation name:

SELECT
customer.company AS "Company Name",
orders.orderno AS "Order #",
sum(items.qty) AS "Total Qty"
FROM customer LEFT OUTER JOIN orders ON customer.custno=orders.custno
LEFT OUTER JOIN items ON orders.orderno=items.orderno
WHERE customer.company LIKE '%Diver%'
GROUP BY 1,2
ORDER BY 1

You may also optionally exclude the AS keyword and simply specify the column correlation name directly after the column, as shown here:

SELECT
customer.company "Company Name",
orders.orderno "Order #",
sum(items.qty) "Total Qty"
FROM customer LEFT OUTER JOIN orders ON customer.custno=orders.custno
LEFT OUTER JOIN items ON orders.orderno=items.orderno
WHERE customer.company LIKE '%Diver%'
GROUP BY 1,2
ORDER BY 1

Embedded Comments

Per ANSI-standard SQL, comments, or remarks, can be embedded in SQL statements to add clarity or explanation. Text is designated as a comment and not treated as SQL by enclosing it within the beginning /* and ending */ comment symbols. The symbols and comments need not be on the same line:

/*
This is a comment
*/
SELECT SUBSTRING(company FROM 1 FOR 4) AS abbrev
FROM customer

Comments can also be embedded within an SQL statement. This is useful when debugging an SQL statement, such as removing one clause for testing.

SELECT company
FROM customer
/* WHERE (state = 'TX') */
ORDER BY company

Reserved Words

Below is an alphabetical list of words reserved by DBISAM's SQL. Avoid using these reserved words for the names of metadata objects (tables, columns, and indexes). An exception occurs when reserved words are used as names for metadata objects. If a metadata object must have a reserved word as it name, prevent the error by enclosing the name in double-quotes ("") or square brackets ([]) or by prefixing the reference with the table name (in the case of a column name).

ABS

ACOS

ADD

ALL

ALLTRIM

ALTER

AND

AS

ASC

ASCENDING

ASIN

AT

ATAN

ATAN2

AUTOINC

AVG

BETWEEN

BINARY

BIT

BLOB

BLOCK

BOOL

BOOLEAN

BOTH

BY

BYTES

CAST

CEIL

CEILING

CHAR

CHARACTER

CHARCASE

CHARS

COALESCE

COLUMN

COLUMNS

COMMIT

COMPRESS

CONCAT

CONSTRAINT

COS

COT

COUNT

CREATE

CURRENT_DATE

CURRENT_GUID

CURRENT_TIME

CURRENT_TIMESTAMP

DAY

DAYOFWEEK

DAYOFYEAR

DAYSFROMMSECS

DECIMAL

DEFAULT

DEGREES

DELETE

DELIMITER

DESC

DESCENDING

DESCRIPTION

DISTINCT

DROP

DUPBYTE

ELSE

EMPTY

ENCRYPTED

ESCAPE

EXCEPT

EXISTS

EXP

EXPORT

EXTRACT

FALSE

FLOAT

FLOOR

FLUSH

FOR

FORCEINDEXREBUILD

FROM

FULL

GRAPHIC

GROUP

GUID

HAVING

HEADERS

HOUR

HOURSFROMMSECS

IDENT_CURRENT

IDENTITY

IF

IFNULL

IMPORT

IN

INCLUDE

INDEX

INNER

INSERT

INT

INTEGER

INTERSECT

INTERVAL

INTO

IS

JOIN

KEY

LARGEINT

LAST

LASTAUTOINC

LCASE

LEADING

LEFT

LENGTH

LIKE

LOCALE

LOG

LOG10

LONGVARBINARY

LONGVARCHAR

LOWER

LTRIM

MAJOR

MAX

MAXIMUM

MEMO

MIN

MINIMUM

MINOR

MINSFROMMSECS

MINUTE

MOD

MONEY

MONTH

MSECOND

MSECSFROMMSECS

NOBACKUP

NOCASE

NOCHANGE

NOJOINOPTIMIZE

NONE

NOT

NULL

NUMERIC

OCCURS

ON

OPTIMIZE

OR

ORDER

OUTER

PAGE

PI

POS

POSITION

POWER

PRIMARY

RADIANS

RAND

RANGE

REDEFINE

RENAME

REPAIR

REPEAT

REPLACE

RIGHT

ROLLBACK

ROUND

RTRIM

RUNSUM

SECOND

SECSFROMMSECS

SELECT

SET

SIGN

SIN

SIZE

SMALLINT

SPACE

SQRT

START

STDDEV

STOP

SUBSTRING

SUM

TABLE

TAN

TEXT

TEXTOCCURS

TEXTSEARCH

THEN

TIME

TIMESTAMP

TO

TOP

TRAILBYTE

TRAILING

TRANSACTION

TRIM

TRUE

TRUNC

TRUNCATE

UCASE

UNION

UNIQUE

UPDATE

UPGRADE

UPPER

USER

VALUES

VARBINARY

VARBYTES

VARCHAR

VERIFY

VERSION

WEEK

WHERE

WITH

WORD

WORDS

WORK

YEAR

YEARSFROMMSECS

The following are operators used in DBISAM's SQL. Avoid using these characters in the names of metadata objects:

|

+

-

*

/

<>

<

>

.

;

,

=

<=

>=

(

)

[

]

#