ExecuteSQLToJSON ( tableName ; fieldList ; whereClause )

Returns the result of a simple query as JSON

Be the first to rate this function Log in to vote

Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Sample input:

ExecuteSQLToJSON ( "user" ; "email¶active¶label¶id" ; "WHERE label LIKE '%r%'" )

Sample output:

{
"1" :
{
"active" : "",
"email" : "rdyce1@its.jnj.com",
"id" : "6130949133090751582760057699633805740451913808838159790903",
"label" : "Dyce, Richard"
},
"2" :
{
"active" : "",
"email" : "lfrey@its.jnj.com",
"id" : "5308457655743778943661807436098065035874886682206779462798",
"label" : "Frey, Lukas"
}
}

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

// ExecuteSQLToJSON ( tableName ; fieldList ; whereClause )

Let(
  [
    fieldsForQuery = "\"" & Substitute ( fieldList ; ¶ ; "\", \"" ) & "\"";
    query = "SELECT " & fieldsForQuery & " FROM " & Quote ( tableName ) & whereClause ;
    data = ExecuteSQL ( query ; "|" ; "" )
  ] ;
  While (
    [ i = 0 ; outerList = "" ] ;

    ValueCount ( data ) > i ; 

    [ 
      i = i + 1 ; 
      outerList = List ( outerList ; "[ " & i & " ; " &
        Let (
          [
            processedData = Substitute ( GetValue ( data ; i )  ; "|" ; ¶ )
          ];
          While (
            [ j = 1 ; innerList = "" ] ;
            j <= ValueCount ( fieldList ) ;
            [
              innerList = List ( innerList ; "[ \"" & GetValue ( fieldList ; j ) & "\" ; \"" & GetValue ( processedData ; j ) & "\" ; JSONString ]" );
              j = j + 1
            ] ;
            "JSONSetElement (\"{}\";" & Substitute ( innerList ; ¶ ; ";" ) & ")"
          )
        )
        & " ; JSONObject ]XXX"
      )
    ] ;

    JSONFormatElements ( Evaluate ( "JSONSetElement (\"{}\";" & Substitute ( outerList & "YYY" ; ["XXXYYY" ; ""]; ["XXX" ; " ; "]; ["¶" ; ""] ) & ")" ) )
  )
)

The WHERE clause is manual currently, but can be left empty for smallish sets ;-)

This is a version 0, obviously. Improvements for v1 ;-) :

* Should probably look at Sam Barnum's use of the FileMaker_Fields table to correctly select the JSON field types. For my purposes though, text is mostly fine.

* Maybe look at building the WHERE query using JSON criteria

* Maybe look at providing ID field for enumerating result rows ;-)

* Better handling of ¶ in returned data ;-)

Comments

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

ExecuteSQLToJSON ( tableName ; fieldList ; whereClause )

Comments

Support this website.

Support this website.