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"

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. Please see the DBISAM Architecture topic for more information.

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"

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"

Please see the In-Memory Tables topic for more information.

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'

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.

To streamline the above, you can use the TDBISAMEngine QuotedSQLStr method to properly format and escape any embedded single quotes or non-printable characters in a string constant. Please see the Executing SQL Queries topic for more information.

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)

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:

|
+
-
*
/
<>
<
>
.
;
,
=
<=
>=
(
)
[
]
#