Monkeybread Software - MBS FileMaker Plugin: FM.SQL.JSONRecord

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

Returns a row as JSON object.

Component	Version	macOS	Windows	Server	FileMaker Cloud	FileMaker iOS SDK
FM FMSQL JSON	8.1	Yes	Yes	Yes	Yes	Yes

MBS( "FM.SQL.JSONRecord"; SQLref; Row; FieldNames { ; Flags } ) More

Parameters

Parameter Description Example value

SQLref The reference number returned by FM.SQL.Execute function. $SQLRef

Row The row you like to get. 1

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

Parameter	Description	Example value
SQLref	The reference number returned by FM.SQL.Execute function.	$SQLRef
Row	The row you like to get.	1
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

Returns a row as JSON object.
You provide list of field names, which should be in same order as fields in SQL result.
Optionally we can return SQL dates, times and timestamps as SQL format.
Containers are returned as Base64 encoded data.

Examples

Query JSON record:

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

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 )

Blog Entries

Release notes

Version 9.0
- Fixed problem with lost time value in FM.SQL.JSONRecord function result.
Version 8.1
- Added FM.SQL.JSONRecord and FM.SQL.JSONRecords functions.

Created 4th March 2018, last changed 8th April 2019

FM.SQL.InsertRecordsToSQL - FM.SQL.JSONRecords

Feedback: Report problem or ask question.