ExecuteSQLResultToJSON ( sqlResult ; fieldSub ; rowSub ; fieldList ; limit )

Converts an ExecuteSQL function result into a JSON object with an array of rows (

Be the first to rate this function Log in to vote

Jeff Norris - Show more from this author

Sample input:

Let (

[
fieldSub = "|#!"; // some unique string not appearing in data w/o ¶
rowSub = "[*>"; // some other unique string not appearing in data w/o ¶
sqlResult = ExecuteSQL ( "[YOUR FANCY SQL QUERY HERE]" ; fieldSub ; rowSub { ; arguments... } )
];

ExecuteSQLResultToJSON ( sqlResult ; fieldSub ; rowSub ; "FieldName1¶FieldName2|JSONNumber¶FieldName3|JSONBoolean" ; 0)

)

Sample output:

{
"data":[
{
"FieldName1":"Data from row 1 column 1 of SQL query",
"FieldName2":743,
"FieldName3":false
},
{
"FieldName1":"Data from row 2 column 1 of SQL query",
"FieldName2":27,
"FieldName3":true
}
],
"records":2
}

===== OR, IF ERROR =====

{
"error":1,
"message":"The error message"
}

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// Mega Magic function by Jeff & Mohamed!
// Relies on provided fieldSub & rowSub which were also used in the ExecuteSQL statement to not include any CRs/LFs!
// fieldSub, rowSub & returnSub (hardcoded here!?!) must all be unique (with, again, no CRs/LFs)
// (FWIW I've just been using fieldSub = "|#!" & rowSub = "[*>") for my stuff.)
// Also: Those sub strings should reliably *NOT* occur in the FM data itself!

// fieldList should be value list which will be used as JSON keys for the SQL result columns!
// to cast a field's data as number or boolean (instead of default string), add "|JSONNumber" or "|JSONBoolean" to field name in fieldList!

// limit value can be supplied to return only first x records from sqlResult (if 0, ignored)

// Will return JSON:
// {
//   "data": [
//     {
//       "[field name 1 from list]": [the sql result for col 1 / row 1],
//       ...
//     },
//     ...
//   ],
//   "records": n count of records in data
// }

// ... OR ... (if a problem)

// {
//   "error": 1,
//   "message": "Error Message"
// }

// First clean the CR's in the data and insert CR's into rows so I have "safe" value-list-friendly rows
Let (
[
returnSub = "{[)"; // unique string not appearing in data (and different from fieldSub & rowSub!) w/o ¶
sqlSafeResult = Substitute ( sqlResult; [ ¶ ; returnSub ] ; [ rowSub ; ¶ ]  ); // here's the "safe" cleaner!
fieldListCount = ValueCount ( fieldList ); // convenience

// "Normalize" (ha ha) the supplied fieldList with pipe-appended JSON types to a stack of names & types here:
fieldListNamesTypes = 
While (
[
counter = 1;
fieldListNames = "";
fieldListTypes = ""
];
counter <= fieldListCount;
[
fieldBits = Substitute ( Trim ( GetValue( fieldList; counter ) ); "|"; ¶);
fieldListNames = fieldListNames & GetValue (fieldBits; 1) & ¶;
fieldListTypes = fieldListTypes &
Let (
typeName = GetValue (fieldBits; 2);
Case(
typeName = "JSONNumber"; 2;
typeName = "JSONBoolean"; 5;
1 // JSONString by default
)
) & ¶;
counter = counter + 1
]; 
fieldListNames & fieldListTypes
)
];

// now do some checks on the result:
Case (

// did the SQL bomb?
sqlSafeResult = "?"; 
JSONSetElement ("{}"; ["error"; 1; JSONNumber] ; ["message"; "SQL has returned '?'."; JSONString] );

// no data returned by SQL ... that's OK!
sqlSafeResult = ""; 
JSONSetElement ("{}"; ["data"; ""; JSONArray] ; ["records"; 0; JSONNumber]);

// final check to make sure fieldList is compatible to columns in SQL result
PatternCount(GetValue(sqlSafeResult; 1); fieldSub) + 1 ≠ fieldListCount ; 
JSONSetElement ("{}"; ["error"; 1; JSONNumber] ; ["message"; "Mismatch of SQL column and field list count!?!"; JSONString] );

// looking good!
// now loop through the rows!
While(
[
counter = 0; // JSON array is zero-indexed!
maxCount = ValueCount( sqlSafeResult );
resultJson = JSONSetElement("{}"; "data"; ""; JSONArray)
];

// if limit supplied, "truncate" json data array!
counter < maxCount and (limit = 0 or counter < limit);

[
resultJson = JSONSetElement(resultJson; "data[" & counter & "]"; 

// now loop through fields to set data object!
While(
[
line = GetValue(sqlSafeResult; counter + 1);
parseLine = Substitute(line; fieldSub; ¶);
listCounter = 1; // FM value lists are one-indexed
queryJson = ""
];

listCounter <= fieldListCount;

[
queryJson = JSONSetElement(queryJson; GetValue(fieldListNamesTypes; listCounter); Substitute(GetValue(parseLine; listCounter); returnSub; ¶); GetValue(fieldListNamesTypes; fieldListCount + listCounter));
listCounter = listCounter + 1
];

queryJson
); 

JSONRaw);
counter = counter + 1
];

// add in record count and return!
Let(
resultJson = JSONSetElement (resultJson; "records"; counter; JSONNumber);
resultJson
)
)
)
)

A different approach to returning structured data (as a JSON object) from FileMaker ExecuteSQL functions.

I do not attempt to dynamically gin up a SQL query.

Rather, this function accepts the *results* from an ExecuteSQL function, along with a value list of field names (& optionally types), and *then* generates the JSON object. Much simpler, IMO, but your mileage may vary, etc.

For the parsing to work, the fieldSub, rowSub & returnSub parameters must (1) all be unique, (2) not contain ¶, and (3) not appear in the data itself. The fieldSub and rowSub must match the fieldSeparator and rowSeparator parameters in the ExecuteSQL function, so I usually just use a Let function and pass these into both functions (see sample input above).

To capture errors, you can check the returned result for "error", e.g.: If [ JSONGetElement ( $result_of_custom_function ; "error" ) ]

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ExecuteSQLResultToJSON ( sqlResult ; fieldSub ; rowSub ; fieldList ; limit )

Comments

Support this website.

Support this website.