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 16
Retrieving the contents of a container field: CAST() function and GetAs() function
You can retrieve file reference information, binary data, or dat
a of a specific file type from a
container field.
1 To retrieve file reference information from a container field, such as the file path to a file, picture,
or QuickTime movie, use the CAST() function with a SELECT statement.
1 If file data or JPEG binary data exists, the SELECT statement with GetAS(field name,
'JPEG') retrieves the data in binary form; otherwise, the SELECT statement with field name
returns NULL.
Example
Use the CAST() function with a SELECT statement to retrieve file reference information.
SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data
In this example, if you:
You can use the SE
LECT statement with the GetAs() function to retrieve the data in binary form
in the following ways:
1 When you use the GetAs() function with the DEFAULT option, you retrieve the master stream
for the container without the need to explicitly define the stream type.
Example
SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data
1 To retrieve an individual stream type from a container, use the GetAs() function with the file’s
type based on how the data was inserted into the container field in FileMaker Pro.
Example
If the data was inserted using the Insert > File command, specify 'FILE' in the GetAs() function.
SELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data
Example
If the data was inserted using the Insert > Picture command, drag and drop, or paste from the
clipboard, specify one of the file types listed in the following table, for example, 'JPEG'.
SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons
1 inserted a file into the container field using FileMaker Pro but stored only a reference to the
file, the SELECT statement retrieves the file reference information as type SQL_VARCHAR.
1 inserted the contents of a file into the container field using FileMaker Pro, the SELECT
statement retrieves the name of the file.
1 imported a file into the container field from another application, the SELECT statement
displays '?' (the file displays as Untitled.dat in FileMaker Pro).