SQL Query to JSON

[[email protected]]

Is it possible to return JSON from an sql query?

Here is a sample query.

dim sql as c
sql = <<%txt%
SELECT Make, Model
FROM qryCars
%txt%

dim cn as sql::Connection
cn.open("::Name::Connection1")
dim flag as l
flag = cn.Execute(sql)
dim rs as sql::ResultSet
rs = cn.ResultSet
dim template as c

[Davidk]

Re: SQL Query to JSON

The information is a bit fragmented... so read all 3 areas... and read them carefully as there are little nuggets of info throughout the text.

https://documentation.alphasoftware....x%20Method.xml

https://documentation.alphasoftware....x%20Method.xml

https://documentation.alphasoftware....rsToJSON().xml

[Steve Workings]

Re: SQL Query to JSON

A bit depends on how you're going to use the JSON. You're probably almost there. Basically this:

sqlGet = <<%a%
select.....
%a%

cn.open("::name::myconnection")
cn.execute(sqlGet)
rs = cn.ResultSet
JSON  = rs.ToJSONObjectSyntax()

[RichCPT]

Re: SQL Query to JSON

I'm not sure how this works under Alpha, but later versions of Microsoft SQL can output results as JSON:

https://docs.microsoft.com/en-us/sql...ql-server-2017

[[email protected]]

Re: SQL Query to JSON

Thanks for all you help.

In the ondialoginitialize event my xbasic is now creating this JSON.

[["Pre Op", 9, 8, 10],
["3 Months", 8, 7, 9],
["1 Year", 7, 6, 8],
["2 Years", 6, 5, 7],
["5 Years", 5, 4, 6],
["10 Years", 3, 2, 4]]

How can I make this JSON available to a JS function?

[madtowng]

Re: SQL Query to JSON

I would suggest putting it into a state variable.
This would make it available to js as well as xbasic.

[compuaid]

Re: SQL Query to JSON

You may be able to create a state variable and store it there:

e._state.jsonvalues = jsonstring

[Davidk]

Re: SQL Query to JSON

Use a State variable... or pass it back in e.javascript

e._state.myvar1 = myJSArray

myJSArray = js_escape(myJSArray)
e.javascript = "myJSFunction('" + myJSArray + "');"

on the Client side...

function myJSFunction(myJSArray){

	debugger;
	var myArray1 = JSON.parse("[" + myJSArray + "]");
	
	var myArray2 = {dialog.object}.stateInfo['myvar1'];
	myArray2 = JSON.parse("[" + myArray2 + "]");
	

}

Your not really working with JSON... you're working with a Javascript array. And... your Array isn't formatted the best because your Array of 6 values is sitting in an Array of 1 value. However, it's still workable.

[[email protected]]

Re: SQL Query to JSON

Thanks, I'm nearly there!

I need to pass the JSON into this JS function which draws a google chart with intervals.

If I call the function from a button like this the chart function works.
var x = [["Pre Op", 9, 8, 10],["3 Months", 8, 7, 9],["1 Year", 7, 6, 8],["2 Years", 6, 5, 7],["5 Years", 5, 4, 6],["10 Years", 3, 2, 4]];
drawChart(x);

However if I get the JSON string from a state variable like this, the chart function doesn't work, even though the state variable looks identical if I display it in a textbox using setvalue.
var x = {dialog.object}.stateInfo['jsonvalues'];
drawChart(x);

Somehow I need to get the JSON from the ondialoginitialize event to the drawChart() function which I've pasted below. I don't know why referencing the state variable doesn't work.


function drawChart(a) {
var data = new google.visualization.DataTable();
data.addColumn('string', 'period');
data.addColumn('number', 'values');
data.addColumn({id:'i0', type:'number', role:'interval'});
data.addColumn({id:'i1', type:'number', role:'interval'});


data.addRows(a);


// The intervals data as narrow lines (useful for showing raw source data)
var options_lines = {
title: 'Line intervals, default',
curveType: 'function',
lineWidth: 4,
intervals: { 'style':'bar' },
legend: 'none'
};

var chart_lines = new google.visualization.LineChart(document.getElementById('chart_div'));
chart_lines.draw(data, options_lines);
}

[Davidk]

Re: SQL Query to JSON

You are passing data from the server to the client. This data is passed as a string.

You must parse the string into a Javascript Array before using it.

It works with var x = [[.... because are you building an Array of data.

The code on your button must be...

var x = {dialog.object}.stateInfo['jsonvalues'];
x = JSON.parse(x);
drawChart(x);