FOR UPDATE clause
The FOR UPDATE
clause locks records for Positioned Updates or Positioned Deletes via SQL cursors. The format is:
FOR UPDATE [OF column_expressions]
column_expressions
is a list of field names in the database table that you intend to update, separated by a comma. column_expressions
is optional, and is ignored.
Return all records in the employee database that have a SALARY
field value of more than $20,000.
SELECT * FROM emp WHERE salary > 20000 FOR UPDATE OF last_name, first_name, salary
When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until you commit the change. Otherwise, the lock is released when you fetch the next record.
Using |
Sample SQL |
text constant |
Copy
|
numeric constant |
Copy
|
date constant |
Copy
|
time constant |
Copy
|
timestamp constant |
Copy
|
text column |
Copy
|
numeric column |
Copy
|
date column |
Copy
|
time column |
Copy
|
timestamp column |
Copy
|
BLOB column |
Copy
Note A BLOB is a FileMaker Pro database file container field. |
Wildcard * |
Copy
|
Notes from the examples
A column
is a reference to a field in the FileMaker Pro 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).
-
FileMaker software does not store data for empty strings, so the following queries always return no records:
CopySELECT * FROM test WHERE c =''
SELECT * FROM test WHERE c <>'' -
If you use
SELECT
with binary data, you must use theGetAs()
function to specify the stream to return. See 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 file reference information, binary data, or data of a specific file type from a container field.
-
To retrieve file reference information from a container field, such as the file path to a file, picture, or video, use the
CAST()
function with aSELECT
statement. -
If file data or JPEG binary data exists, the
SELECT
statement withGetAs(field name, 'JPEG')
retrieves the data in binary form; otherwise, theSELECT
statement with field name returnsNULL
.
Use the C
AST()
function with a SELECT
statement to retrieve file reference information.
SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data
In this example, if you:
-
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 typeSQL_VARCHAR
. -
inserted the contents of a file into the container field using FileMaker Pro, the
SELECT
statement retrieves the name of the file. -
imported a file into the container field from another application, the
SELECT
statement displays '?' (the file displays as Untitled.dat in FileMaker Pro).
You can use the SELECT
statement with the GetAs()
function to retrieve the data in binary form in the following ways:
-
When you use the
GetAs()
function with theDEFAULT
option, you retrieve the default stream for the container without the need to explicitly define the stream type.CopySELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data
-
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.If the data was inserted using the Insert > File command, specify
'FILE'
in theGetAs()
function.CopySELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data
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'
.CopySELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons
File type
Description
'GIFf'
Graphics Interchange Format
'JPEG'
Photographic images
'TIFF'
Raster file format for digital images
'PDF '
Portable Document Format
'PNGf'
Bitmap image format