Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 9.0   9.1   9.2   9.3   9.4   9.5   10.0   10.1   10.2   10.3    Statistic  

FM.SQL.JSONRecords

Returns rows as JSON array.

Parameters

Parameter	Description	Example value
SQLref	The reference number returned by FM.SQL.Execute function.	$SQLRef
FieldNames	A list of field names for the JSON.	"Model¶Names"
Flags	Optional The flags for the json creation. Pass 1 to get all values as text. Pass 2 to get all dates, times and timestamps in SQL format.	1+2

Result

Returns JSON text or error.

Description

Examples

Query JSON:

Set Variable [ $sql ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT \"First\", \"Last\", Birthday, \"Photo Container\" FROM Contacts") ]
Set Variable [ $json ; Value: MBS( "FM.SQL.JSONRecords"; $sql; "First¶Last¶Birthday¶Photo"; 2) ]
Show Custom Dialog [ "JSON" ; $json ]
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql ) ]

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

See also

• FM.SQL.Execute
• FM.SQL.JSONRecord
• FM.SQL.Release
• IsError
• SQL.Execute

Blog Entries

• Neues MBS FileMaker Plugin 10.2
• MBS FileMaker Plugin 10.2 - More than 6200 Functions In One Plugin
• Query FileMaker records as JSON
• With JSON in excellent form

FileMaker Magazin

• Ausgabe 3/2018, Seite 34

Release notes

• Version 8.1
  • Added FM.SQL.JSONRecord and FM.SQL.JSONRecords functions.

Created 4th March 2018, last changed 2nd April 2020

FM.SQL.JSONRecord   -   FM.SQL.Max

Feedback: Report problem or ask question.