FOR UPDATE clause

The FOR UPDATE clause locks records for Positioned Updates or Positioned Deletes via SQL cursors. The format is:

Copy
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.

Example

Return all records in the employee database that have a SALARY field value of more than $20,000.

Copy
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.

Examples

Using

Sample SQL

text constant

Copy
SELECT 'CatDog' FROM Salespeople

numeric constant

Copy
SELECT 999 FROM Salespeople

date constant

Copy
SELECT DATE '2021-06-05' FROM Salespeople

time constant

Copy
SELECT TIME '02:49:03' FROM Salespeople

timestamp constant

Copy
SELECT TIMESTAMP '2021-06-05 02:49:03' FROM Salespeople

text column

Copy
SELECT Company_Name FROM Sales_Data
SELECT DISTINCT Company_Name FROM Sales_Data

numeric column

Copy
SELECT Amount FROM Sales_Data
SELECT DISTINCT Amount FROM Sales_Data

date column

Copy
SELECT Date_Sold FROM Sales_Data
SELECT DISTINCT Date_Sold FROM Sales_Data

time column

Copy
SELECT Time_Sold FROM Sales_Data
SELECT DISTINCT Time_Sold FROM Sales_Data

timestamp column

Copy
SELECT Timestamp_Sold FROM Sales_Data
SELECT DISTINCT Timestamp_Sold FROM Sales_Data

BLOB column

Copy
SELECT Company_Brochures FROM Sales_Data
SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data

Note  A BLOB is a FileMaker Pro database file container field.

Wildcard *

Copy
SELECT * FROM Salespeople
SELECT DISTINCT * FROM Salespeople

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).

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 a SELECT statement.

  • 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.

Copy
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 type SQL_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 the DEFAULT option, you retrieve the default stream for the container without the need to explicitly define the stream type.

    Example

    Copy
    SELECT 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.

    Examples

    If the data was inserted using the Insert > File command, specify 'FILE' in the GetAs() function.

    Copy
    SELECT 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'.

    Copy
    SELECT 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