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 35
FileMaker system objects
FileMaker Pro database files include the following system objects that you can access using SQL
queries.
FileMaker system tables
Every FileMaker Pro database file includes two system tables: FileMaker_Tables and
FileMaker_Fields. For ODBC applications, these tables are included in the information returned
by the catalog function SQLTables. For JDBC applications, these tables are included in the
information returned by the DatabaseMetaData method getTables. The tables can also be used in
ExecuteSQL functions.
FileMaker_Tables
The FileMaker_Tables table contains information about the database t
ables defined in the
FileMaker Pro file.
The FileMaker_Tables table includes a
row for each table occurrence in the relationships graph
with the following columns:
1 TableName - The name of the table occurrence.
1 TableId - The unique ID for the table occurrence.
1 BaseTableName - The name of the base table from which the table occurrence was created.
1 BaseFileName - The FileMaker Pro filename for the database file that contains the base table.
1 ModCount - The total number of times changes to this table’s definition have been committed.
Example
SELECT TableName FROM FileMaker_Tables WHERE TableName LIKE 'Sales%'
FileMaker_Fields table
The FileMaker_Fields table contains information about the fields
defined in the FileMaker Pro file.
The FileMaker_Fields table include
s the following columns:
1 TableName - The name of the table that contains the field.
1 FieldName - The name of the field.
1 FieldType - The SQL data type of the field.
1 FieldId - The unique ID for the field.
1 FieldClass - One of three values: Summary, for summary fields; Calculated, for calculated
results; or Normal.
1 FieldReps - The number of repetitions of the field.
1 ModCount - The total number of times changes to this table’s definition have been committed.
Example
SELECT * FROM FileMaker_Fields WHERE TableName='Sales'