ODBC and JDBC Guide

40 FileMaker ODBC and JDBC Guide
Additional examples:
Notes from the examples
A column is a reference to a field in the FileMaker database file (the field can contain many distinct values).
The asterisk (*) wildcard character is shorthand for “everything”. For the example SELECT * FROM
Salespeople
, the result is all the columns in the Salespeople table. For the example SELECT
DISTINCT * FROM Salespeople
, the result is all the unique rows in the Salespeople table (no
duplicates).
1 FileMaker does not store data for empty strings, so the following queries always return no records:
SELECT * FROM test WHERE c =’’
SELECT * FROM test WHERE c <>’’
1 If you use SELECT with binary data, you must use the GetAs() function to specify the stream to return.
See the following section
“Retrieving the contents of a container field: CAST() function and GetAs()
function,” for more information.
Retrieving the contents of a container field: CAST() function and GetAs() function
You can retrieve binary data, file reference information, or data of a specific file type from a container field.
To retrieve binary data, use a standard SELECT statement. For example:
SELECT Company_Brochures FROM Sales_Data
If file or JPEG data exists, the SELECT statement retrieves the data in binary form; otherwise, the SELECT
statement returns <null>.
Using Sample SQL
text constant
SELECT 'CatDog' FROM Salespeople
numeric constant
SELECT 999 FROM Salespeople
date constant
SELECT DATE '2011-06-05' FROM Salespeople
time constant
SELECT TIME '02:49:03' FROM Salespeople
timestamp constant
SELECT TIMESTAMP '2011-06-05 02:49:03' FROM Salespeople
text column
SELECT Company_Name FROM Sales_Data
SELECT DISTINCT Company_Name FROM Sales_Data
numeric column
SELECT Amount FROM Sales_Data
SELECT DISTINCT Amount FROM Sales_Data
date column
SELECT Date_Sold FROM Sales_Data
SELECT DISTINCT Date_Sold FROM Sales_Data
time column
SELECT Time_Sold FROM Sales_Data
SELECT DISTINCT Time_Sold FROM Sales_Data
timestamp column
SELECT Timestamp_Sold FROM Sales_Data
SELECT DISTINCT Timestamp_Sold FROM Sales_Data
BLOB
a
column
a. A BLOB is a FileMaker database file container field.
SELECT Company_Brochures FROM Sales_Data
SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data
Wildcard *
SELECT * FROM Salespeople
SELECT DISTINCT * FROM Salespeople