Nested Json objects for the Northwind database fro example like this:
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:
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.
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
Code:
{ "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] }
Code:
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 errors I ger are highlighted in red.
Code:
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'll appreciate it
Thank you
Comment