Developer’s Guide

8-4 Developer’s Guide
SQL supported by the FileMaker JDBC Driver
The FileMaker JDBC Driver provides support for certain SQL statements,
a RecordID pseudo column, a ModID pseudo column, DbOpen and
DbClose pseudo procedures, character escaping, and FileMaker data type
mapping to JDBC SQL and Java data types.
The following is a list of the SQL statements and definitions that are
supported by the FileMaker JDBC Driver.
Note Items within square brackets [ ] are optional and a vertical
bar | means “or.” An ellipsis (…) indicates that the preceding part of the
statement can be repeated any number of times. Periods and a comma
(.,..) indicate that the preceding part of the statement can be repeated any
number of times with the individual occurrences separated by commas.
The final occurrence should not be followed by a comma.
To update a specific repeating field or field in a portal, add a period
and the number of the row to the end of the field name and enclose
the field name in double quotation marks. For example, to update the
third repetition of the Telephone field for a record in the
Employees.fp5 database, specify the following:
UPDATE “Employees.fp5” SET “Telephone.3”=’[phone]’ WHERE
recordid=4
Property Description
escape A string containing the characters to be escaped in table name, field
name, and layout name SQL identifiers. The driver will escape all
identifiers returned via any method in the DatabaseMetaData class.
This will allow RAD tools that don't support spaces and periods in
SQL identifiers to work with any FileMaker Pro database. The
driver will automatically escape all identifiers for you. See “Using
a character escape” on page 8-7 for more information.
fetchsize This property allows you to set the number of records that are
retrieved by the driver at any one given time. This is important for
result sets (such as a result set of 20000 records) that are too large
to retrieve all at once without causing memory constraints and
performance problems.
user The user name for the connection
password The password for the connection
SQL statement Definition
SELECT statement SELECT { { * | field_name .,.. } [ , RECORDID [ ,
MODID ] ] }
FROM database_name
[ LAYOUT layout_name ]
[ WHERE { predicate [ { { AND | OR } predicate }
... ] } ]
[ ORDER BY { field_name [ASC | DESC] } .,.. ]
Where predicate equals
{ field_name { = | <> | > | >= | < | <= | LIKE } { value
| ? } } | {field_name IS NULL} | {RECORDID =
{value | ?}}
INSERT statement INSERT INTO database_name [ LAYOUT
layout_name ] ( field_name .,.. )
VALUES ( { value | NULL | ? } .,.. )
UPDATE statement UPDATE database_name [ LAYOUT layout_name ]
SET { field_name = { value | NULL | ? } } .,..
[ WHERE { predicate [ { { AND | OR } predicate }
... ] } ]
Where predicate equals
{ field_name { = | <> | > | >= | < | <= | LIKE } { value
| ? } } | {field_name IS NULL} | {RECORDID =
{value | ?} [ AND MODID = { value | ? } ] }
DELETE statement DELETE FROM database_name
[ WHERE { predicate [ { { AND | OR } predicate }
... ] } ]
Where predicate equals
{ field_name { = | <> | > | >= | < | <= | LIKE } { value
| ? } } | {field_name IS NULL} | {RECORDID =
{value | ?} }
CALL stored
procedure (a script)
statement
{ CALL script_name ( database_name [ , { layout_name
| password } ] ) }
Where the outermost curly brackets { } are part of the
CALL statement syntax.
SQL statement Definition