Developer’s Guide

Using Java and JDBC to deliver your data 9-7
SQL statement examples
The following are some examples of SQL statements, some of which
use RecordID and ModID pseudo columns, and DbOpen and
DbClose pseudo procedures:
SELECT recordid, modid, "last name", "first name", department FROM
"Employees.fp5" WHERE "last name"='smith' AND "first name" = 'joe'
SELECT * FROM "Employees.fp5" WHERE recordid=4
SELECT recordid, modid, * FROM "employees.fp5"
SELECT "last name", "first name", "telephone::phone number" FROM
"employees.fp5" LAYOUT "personal info"
UPDATE "Employees.fp5" SET department='engineering' WHERE
recordid=4 AND modid=2
UPDATE "Employees.fp5" LAYOUT "personal info" SET
"telephone::phone number.2"='555-555-5555' WHERE recordid=4
DELETE FROM "Employees.fp5" WHERE recordid=4
{ CALL DbOpen("inventory.fp5", "some password") }
{ CALL DbClose("inventory.fp5") }
{ CALL FindManagers("employees.fp5") }
{ CALL SortByLastName("employees.fp5", "list view") }
Using a character escape
The FileMaker JDBC Driver supports escaping of lower ASCII
characters in column and table name SQL identifiers. This is useful
if your RAD tool doesn’t support characters such as spaces in
column names or periods in table names. The escape sequence starts
with the dollar symbol ($) and is followed by the two-digit hex value
for the character (such as 2E for a period and 20 for a space).
employees.fp5 => employees$2Efp5
last name => last$20name
SELECT last$20name FROM employees$2Efp5
FileMaker data type mapping to JDBC SQL and Java
data types
The FileMaker JDBC Driver uses the following mappings when
converting FileMaker Pro data types to JDBC SQL types or to Java
data types. (For information about these types, see the JDK 1.1.8
documentation web pages at www.javasoft.com.)
FileMaker Pro support for
Unicode characters
FileMaker Pro only supports the Windows Latin 1 and Macintosh
character sets, which are a subset of Unicode. Therefore, any
character data submitted to FileMaker Pro that contains characters
not present in these character sets (such as certain math symbols) will
not be stored properly in your database. FileMaker Pro inserts a
question mark (?) for any character that it does not recognize.
This FileMaker Pro data type Converts to this JDBC SQL type
TEXT java.sql.Types.LONGVARCHAR
NUMBER java.sql.Types.DOUBLE
DATE java.sql.Types.DATE
TIME java.sql.Types.TIME
CONTAINER java.sql.Types.LONGVARBINARY
This FileMaker Pro data type Converts to this Java data type
TEXT java.lang.String
NUMBER java.lang.Double
DATE java.sql.Date
TIME java.sql.Time
CONTAINER java.awt.Image
Repeating and related fields com.fmi.jdbc.Array