SQL Reference
Table Of Contents
- Chapter 1 Introduction
- Chapter 2 Supported standards
- Support for Unicode characters
- SQL statements
- SELECT statement
- SQL clauses
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- UNION operator
- ORDER BY clause
- OFFSET and FETCH FIRST clauses
- FOR UPDATE clause
- DELETE statement
- INSERT statement
- UPDATE statement
- CREATE TABLE statement
- TRUNCATE TABLE statement
- ALTER TABLE statement
- CREATE INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
Chapter 2 | Supported standards 21
1 To define a column as a container field, use BLOB, VARBINARY, or BINARY VARYING for
the field_type.
1 To define a column as a container field that stores data externally, use the EXTERNAL
keyword. The relative_path_string defines the folder where the data is stored
externally, relative to the location of the FileMaker database. This path must be specified as
the base directory in the FileMaker Pro Manage Containers dialog box. You must specify
either SE
CURE for secure storage or OPEN for open storage. If you are using open storage,
the calc_path_string is the folder inside the relative_path_string folder where
container objects are to be stored. The path must use forward slashes (/) in the folder name.
Examples
Using Sample SQL
text column CREATE TABLE T1 (C1 VARCHAR, C2 VARCHAR (50), C3 VARCHAR (1001),
C4 VARCHAR (500276))
text column, NOT N
ULL CREATE TABLE T1NN (C1 VARCHAR NOT NULL, C2 VARCHAR (50) NOT NULL,
C3 VARCHAR (1001) NOT NULL, C4 VARCHAR (500276) NOT NULL)
numeric column CREATE TABLE T2 (C1 DECIMAL, C2 DECIMAL (10,0), C3 DECIMAL (7539,2),
C4 DECIMAL (497925,301))
date column CREATE TABLE T3 (C1 DATE, C2 DATE, C3 DATE, C4 DATE)
time column CREATE TABLE T4 (C1 TIME, C2 TIME, C3 TIME, C4 TIME)
timestamp column CREATE TABLE T5 (C1 TIMESTAMP, C2 TIMESTAMP, C3 TIMESTAMP,
C4 TIMESTAMP)
column for container field CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB)
column for external
sto
rage container field
CREATE TABLE T7 (C1 BLOB EXTERNAL 'Files/MyDatabase/' SECURE)
CREATE TABLE T8 (C1 BLOB EXTERNAL 'Files/MyDatabase/'
OPEN 'Objects')
TRUNCATE TABLE statement
Use the TRUNCATE TABLE statement to quickly delete all records in the specified table, emptying
the table of all data.
TRUNCATE TABLE table_name
You cannot specify a WH
ERE clause with the TRUNCATE TABLE statement. The TRUNCATE
TABLE statement deletes all records.
Only the records in the table specified by tabl
e_name are deleted. Records from any related
tables are not affected.
The TRUNCATE
TABLE statement needs to be able to lock all records in the table in order to delete
the record data. If any record in the table is locked by another user, FileMaker returns the error
code 301 (“Record is in use by another user”).