Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 9.3   9.4   9.5   10.0   10.1   10.2   10.3   10.4   10.5   10.6    Statistic    FMM    Blog  

FM.SQL.Execute

Execute the SQL Statement against a FileMaker database.

Component Version macOS Windows Linux Server FileMaker iOS SDK
FM FMSQL 5.1 Yes Yes Yes Yes Yes
MBS( "FM.SQL.Execute"; FileName; SQL Statement { ; Params } )   More

Parameters

Parameter Description Example Flags
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, pass here parameters. One parameter to this function for each parameter you want to pass to the SQL statement. 123 Optional

Result

Returns reference number or error.

Description

Execute the SQL Statement against a FileMaker database.
This function requires FileMaker 11 or newer. Please use FM.ExecuteSQL on FileMaker 10 or older.
Returns a SQL Reference, so you can use functions like FM.SQL.RowCount, FM.SQL.FieldCount, FM.SQL.Field and FM.SQL.FieldType to query result.
Don't forget to use FM.SQL.Release to release the result from memory when you are done with it.

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 )

Make a JSON query in one Let statment:

Let (
    [
        $sdat = "10/1/2019 00:00";
        $edat = "10/10/2019 00:00";
        sku = "'PARbh50','PARgy50'";
        sql1 = MBS("FM.SQL.Execute"; ""; "Select docnum,linetotal,db from \"MyTable\" where ItemCode in (" & sku & ") and DocDate between ? and ?"; $sdat; $edat);
        json = MBS( "FM.SQL.JSONRecords"; sql1; "DocNum¶LineTotal¶DB"; 1);
        r = MBS( "FM.SQL.Release"; sql1 )
    ] ; json
)

Query related records as JSON:

# get related teams
Set Variable [ $r ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT \"UUID\", \"ID\", \"ID_Department\", \"Team.Name\", \"MemberCount\" FROM \"Teams\" WHERE ID_Department=?"; Department::ID) ]
If [ MBS("IsError") ]
    Show Custom Dialog [ "SQL error" ; $r ]
Else
    # fill as json in field
    Set Field [ Department::JSON_Department ; MBS( "FM.SQL.JSONRecords"; $r; "UUID¶ID¶ID_Department¶Team.Name¶MemberCount") ]
    Set Variable [ $e ; Value: MBS( "FM.SQL.Release"; $r ) ]
End If

Query two JSON arrays, one for group names and ID list:

Set Variable [ $sql ; Value: MBS("FM.SQL.Execute"; ""; "SELECT DISTINCT \"ID\", \"Group\" FROM \"Contacts\" WHERE Length(\"Group\") > 0") ]
If [ MBS("IsError") ]
    Show Custom Dialog [ "Error" ; $sql ]
Else
    Set Variable [ $IDs ; Value: MBS("FM.SQL.JSONColumn"; $SQL; 0) ]
    Set Variable [ $Groups ; Value: MBS("FM.SQL.JSONColumn"; $SQL; 1) ]
    Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $SQL) ]
    Show Custom Dialog [ "Values" ; $IDs & ¶ & $Groups ]
End If

See also

Example Databases

Blog Entries

FileMaker Magazin

Release notes

Created 18th April 2015, last changed 7th October 2020


FM.SQL.CSV   -   FM.SQL.Field

Feedback: Report problem or ask question.



Links
MBS Xojo PDF Plugins

Start Chat