How to generate nested Json objects from MySql to resultSet using xBasic?

[Maroine81]

Nested Json objects for the Northwind database fro example like this:

{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste"
"City": "Berlin", 
"Country": "Germany",
"orders":[INDENT][
{"OrderID": 10643, "OrderDate": "1995-09-25 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 6}, 
{"OrderID": 10692, "OrderDate": "1995-11-03 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 4}, 
{"OrderID": 10702, "OrderDate": "1995-11-13 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 4}, 
{"OrderID": 10835, "OrderDate": "1996-02-15 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 1}, 
{"OrderID": 10952, "OrderDate": "1996-04-15 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 1}, 
{"OrderID": 11011, "OrderDate": "1996-05-09 00:00:00.000000", "CustomerID": "ALFKI", "EmployeeID": 3}
],  
[/INDENT]
}

I obtained the above json definition directly from Mysql database by using the json_object function available in the latest version of MySQl (5.7), you can try it yourself if you're interested:

select json_object(
'CustomerID', customers.`customerid`
, 'CompanyName',customers.`companyName`
, 'City', customers.`City`
, 'Country', customers.`Country`
, 'orders', (select cast(concat('[',
 GROUP_CONCAT(
 json_object(
'OrderID',orderID, 'CustomerID', `CustomerID`, 'EmployeeID', EmployeeID, 'OrderDate', OrderDate)),
 ']') 
 as JSON) from orders where customers.customerID = orders.customerID)
 ) as json_definition from customers where customers.customerID = "ALFKI";

The challenge is that I'm unable to obtain the resultset and to convert it to string so that I can parse the json definition.
The errors I ger are highlighted in red.

function json_def as v (e as p)[INDENT]'debug(1)

dim cn as sql::Connection
cn.Open("::Name::NorthwindMySQL57")
cn.PortableSQLEnabled = .T.

dim args as sql::Arguments 
args.Set("whatCustomer","ALFKI")

dim SQLSelect as c
'SQLSelect is in one single line
SQLSelect ="select json_object('CustomerID', customers.`customerid`, 'CompanyName',customers.`companyName`, 'City', customers.`City`, 'Country', customers.`Country`, 'orders', (select cast(concat('[',GROUP_CONCAT(json_object('OrderID',orderID, 'CustomerID', `CustomerID`, 'EmployeeID', EmployeeID, 'OrderDate', OrderDate)),']') as JSON) from orders where customers.customerID = orders.customerID)) as json_definition from customers where customers.customerID = :whatCustomer

cn.Execute(SQLSelect, args) 
cn.ResultSet.nextRow()
[COLOR=#ff0000]'ERROR: Variable does not contain a valid result set while getting result set next row.'[/COLOR]

dim rs as sql::ResultSet
rs=cn.ResultSet

dim definitionJson as c
definitionJson = rs.ToString()
[COLOR=#ff0000]'ERROR OCCURRED: Database API specific error while converting ResultSet to String Unexpected MySql data type BINARY 245 for colomn json_definition in 'CA5SQLDataTypeInfoMySQL::GetData[/COLOR]

definitionJson = "[" + stritran(definitionJson,crlf(),"," + crlf()) + "]"

cn.Close()

'The goal is to parse the JSON definition so that I would insert it somewhere else
dim p as p
p = json_parse(definitionJson)
dim i as n 
dim count as n 
count = p.size()

'more code here
[/INDENT]

end function

I'm hoping that someone would be kind enough to test this code & provide some help on why this is occuring! It's been 3 days and I can't figure it out.

I'll appreciate it
Thank you

[Davidk]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Would this help?

function json_def as c (e as p)
debug(1)

dim ops as p
ops.connectionString = "::Name::northwind"

ops.sql = <<%txt%
{sql: 'select CustomerId, CompanyName, City, Country from customers where customerid = :whatCustomer', name: 'customer' }
	{sql: 'select OrderId, CustomerId, EmployeeId, OrderDate from orders', name: 'orders', parentKey: 'customerid', key: 'customerId'}
%txt%

dim args as sql::Arguments
args.set("whatCustomer","ALFKI")

dim jsonObj as p
jsonObj = a5_sql_nested_query_to_json_document(ops,args)
dim jsonData as c
jsonData = jsonObj.data

end function

This will produce a nested JSON structure. But... I'm not sure it can be parsed into an XBasic pointer variable. What do you want to do with the JSON?

[Maroine81]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Thank you David

You're right! I'm not sure it can be parsed into an xbasic pointer variable either. There's got to be another way to accomplish what I need to do.

I'm trying to allow users to dynamically generate forms.

Check out "Understanding custom controls" in the video library specifically video 3 to have an idea about what I'd like to do.

I thought getting the table definition directly as Json would be easier but I guess I thought wrong. Maybe it's be better to obtain the form definition directly from the database tables:

Parent Table FIELDS that store form fields (F_ID, fieldName, controlType, fieldLabel, required, allowMultipleSelections)
child Table FIELDVALUES that store values for checkboxes radiobuttons and dropdowns (FV_ID, F_ID, choice, value)

I get to generate the form when the control type is textbox by querying only the FIELDS table but when it comes to checkboxes radiobuttons I get confused about how to get there.

[Michael1954]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Get in touch with Alpha, Dan has built a form builder. They are Beta testing it at the moment. It supports parent-child relationships. It looks super cool.

[Maroine81]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Thanks for the sales pitch Michael! I'm building the form builder according to specific requirements one line code at a time. So far I was able to implement the textbox checkbox and radio button controls dynamically from a parent child relationships tables in a SaaS framework, and it looks hyper super cool too. I like a good challenge and enjoy the satisfaction of overcoming difficult milestones.

[Michael1954]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Not really a sales pitch it is code written in Alpha. The whole app is built in Alpha.

https://www.youtube.com/watch?v=kAXt...ature=youtu.be

I guess it will be available in the next release.

[Maroine81]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Oh my bad. The form builder app does look interesting for developers to quickly design forms for their users although I think it's somewhat complicated for the not so savvy end users to design their forms themselves. I might be wrong, will have end users test use it when it becomes available & see how it goes.

Thanks for sharing.

[Michael1954]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

I know Alpha are putting a lot of effort into making this as simple as possible. They see this as "computer savvy" user level. So a manager needs a picture of problems in a house. He builds an "app" to capture this data. It is meant to be quick to build quick to use. Some apps will be discarded, some will be developed into house apps after positive feedback.

[Michael1954]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Sorry, referring to the original question I have always wondered if you could use the datacache to achieve your objective.

[Maroine81]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Sorry, referring to the original question I have always wondered if you could use the datacache to achieve your objective.

If I was allowing users to design their own forms using their mobile devices, I'd seriously consider data cache, but since the form building is only done online with a computer in a web portal, no data cache is needed.

Too bad the json_object function in MySQL didn't work well with xbasic to generate the nested JSON objects, It would have been a straight forward solution. The wprk around process was to first get the parent data then get the children data using toPropertyArray() function. After that, associate the children data with the parent data using *array_append() function. Then use json_generate() to get the json definition.

[Michael1954]

Re: How to generate nested Json objects from MySql to resultSet using xBasic?

Here is a video from Selwyn.

http://www.ajaxvideotutorials.com/V1...sidedata_4.swf