Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 8.2   8.3   8.4   8.5   9.0   9.1   9.2   9.3   9.4   9.5    Statistic  

FM.SQL.Execute

Execute the SQL Statement against a FileMaker database.

Parameters

Parameter	Description	Example value
FileName	The target database name. Can be empty to not limit query to one database.	""
SQL Statement	SQL Statement as a Text string
Params	Optional Optional, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement.	123

Result

Returns reference number or error.

Description

Examples

Query only first row:

MBS( "FM.SQL.Execute"; ""; "SELECT * FROM ProductImages FETCH FIRST 1 ROWS ONLY")

SELECT records

MBS("FM.SQL.Execute"; ""; "select * from Contacts")

Select with parameter:

MBS("FM.SQL.Execute"; ""; "select Menge from Boxen where Artikel_Nr=? ORDER BY Menge DESC"; "ABC123")

Query some values:

Set Variable [$sql; Value:MBS( "FM.SQL.Execute"; ""; "select * from Clients")]
If [MBS("IsError")]
    Show Custom Dialog ["SQL error"; $sql]
Else
    Show Custom Dialog ["SQL result"; "got " & MBS( "FM.SQL.RowCount"; $sql ) &" rows with each " & MBS( "FM.SQL.FieldCount"; $sql ) & " …"]
    Set Variable [$r; Value:MBS( "FM.SQL.Release"; $sql )]
End If

Query record as JSON:

Let ( [
fields = MBS( "FM.QueryFieldsForTableName"; "Contacts");
Records = MBS( "FM.SQL.Execute"; ""; "SELECT \"" & substitute(fields; ¶; "\", \"") & "\" FROM \"Contacts\" WHERE \"First\" = ?"; "Joe");

json = MBS( "FM.SQL.JSONRecord"; Records; 0; fields);
r = MBS( "FM.SQL.Release"; Records )]
; json )

See also

• FM.SQL.Field
• FM.SQL.FieldCount
• FM.SQL.FieldType
• FM.SQL.InsertRecords
• FM.SQL.InsertRecordsToSQL
• FM.SQL.JSONRecord
• FM.SQL.JSONRecords
• FM.SQL.Max
• FM.SQL.Min
• FM.SQL.Release
• FM.SQL.RowCount
• FM.SQL.Sum
• FM.SQL.Text
• IsError
• SQL.Execute
• SQL.FieldCount

Example Databases

• SQL in FileMaker/ImageGallery
• SQL in FileMaker/SQL Select Container
• SQL to other databases/SQL Export
• Third Party/FileMaker Snippet Storage

Blog Entries

• FileMaker SQL with Sum, Min, Max and Avg on results
• MBS FileMaker Plugin, version 8.5pr4
• Image Gallery in FileMaker using SQL
• CSV functions in MBS FileMaker Plugin
• FileMaker Custom function to query a value from other table or file
• MBS FileMaker Plugin 7.0 - More than 4400 Functions In One Plugin
• MBS FileMaker Plugin 7.0 - 4400 Funktionen in einem Plugin
• MBS FileMaker Plugin 6.4 for OS X/Windows
• Execute SQL in FileMaker and get back native field types

Release notes

• Version 8.5
  • Fixed FM.SQL.Execute to clear lasterror on success.

Created 18th April 2015, last changed 3th December 2018

FM.SQL.CSV   -   FM.SQL.Field

Feedback: Report problem or ask question.