QueryToJSON Helper

The QueryToJSON Helper file contains a function for converting database query result arrays to the JSON format.

Loading this Helper

This helper is loaded using the following code:

rigLoadHelper "querytojson"

The function:

rigQueryToJSON(pResult, pFieldnames)

Parameters

• pResult: is a database query result array
• pFieldnames: (optional) is an array containing the field names of the database table

Example:

# MODEL DATABASE QUERY
put mymodelFetchAllData("recipe") into tQuery
put tQuery["resultarray"] into tResult
put tQuery["fieldnames"] into tFieldNames

# CONVERT ARRAY TO JSON
put rigQueryToJSON(tResult, tFieldNames) into gData["jsonData"]

Conversion example:

Input: database table

Output: JSON

{
"queryToJSON":[
{
"ID":"1",
"Name":"Banana Bread",
"Directions":"How to make banana bread."
},
{
"ID":"2",
"Name":"Pancakes",
"Directions":"How to make pancakes."
}
]
}

To reference the JSON object use the term "queryToJSON". To reference individual items of the JSON records use the table field names. If the "fieldnames" parameter is empty, item names are "field1", "field2", "field3" ... etc. Example using the Jquery library:

rigJQopen
rigJQeventOpen "'#go'", "'click'"
put "getjsontest/getjsondata" into tAjax["url"]
put "'json'" into tAjax["dataType"]

put rigJQvarR("jsObj, data;") & return into tSuccess
put rigJQsetR("'#resultData'", "append", "'<p>' + element.myTabeleFieldName + '</p>'") into tCallback
put rigJQeachOpenR("jsObj.queryToJSON", , tCallback) && rigJQeachCloseR() after tSuccess

put tSuccess into tAjax["success"]

rigJQajax tAjax

rigJQeventClose
rigJQclose

put the result into gData["JQscript"]

In the example above we perform an Ajax request. We store the retrieved data in a variable called "jsObj" and reference the JSON object with "jsObj.queryToJSON". We iterate over the JSON object using "element.myTabeleFieldName" for an item "myTabeleFieldName" in each JSON record "element" where "myTabeleFieldName" is the name of a database tabel field and "element" is an argument of the JQuery .each function (rigJQeachOpenR).