Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

[Michael1954]

Hi

I know no xbasic and I am trying to use a Json like object of the format

[{"col":1,"row":1,"size_x":2,"size_y":2},{"col":3,"row":7,"size_x":1,"size_y":1}]

I would like to use this in a SQL update statement.

At a later date I need to produce an output from MySQL in the following format.

[
{
col: 1,
row: 1,
size_x: 2,
size_y: 2
},

{
col: 2,
row: 5,
size_x: 1,
size_y: 1
}
];

This I can do using a5_MergeDataIntoTemplate()

So the part I am stuck on is building my update data.

I have seen

txt = <<%txt%
[
{"col":1,"row":1,"size_x":2,"size_y":2},
{"col":3,"row":7,"size_x":1,"size_y":1}
]
%txt%

dim arr[0] as p
a5_JSON_PopulateArray(arr,txt)

in the wiki

Can someone please help with the syntax.

Michael

P.S. I find xbasic daunting!

[Davidk]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Hey Michael,

Need a bit more context.

So the part I am stuck on is building my update data.

Can you expand a bit? What is it you'd like to do.

If you have an array of a json object like...

txt = <<%txt%
[
{"col":1,"row":1,"size_x":2,"size_y":2},
{"col":3,"row":7,"size_x":1,"size_y":1}
]
%txt%

And you want to populate an XBasic array with it...

dim arr[0] as p
a5_JSON_PopulateArray(arr,txt)

This populates the XBasic array with the json data... so in XBasic you could do...

x = arr[1].row

and x would have the value 1

[Michael1954]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Hi David

If I am using drag and drop or gridster it uses the serialise function (Miss spelled as Serialize).
So the above is returning a position and size.
I wish to save 1 in the col field, 1 in the row field, 2 in the size_x field, and 2 in the size_y field
{"col":1,"row":1,"size_x":2,"size_y":2}

I think I am trying to do the reverse of this

delete cn
dim cn as sql::Connection
dim flag as l
cn.open("::Name::MySQL_Bagel")
cn.Execute("SELECT * FROM Home")
rs = cn.ResultSet

This function as you know takes my view called Home and gives me a results set that I can use with a5_MergeDataIntoTemplate()

I assume I can get help from Alpha to pass the array into an update statement as values

dim conn as SQL::Connection
dim upd as SQL::UpdateStatement
dim cs as SQL::CallResult
dim connString as C
dim sql_update as C
connString = "{A5API='Access', FileName='c:\program files\a5v7\mdbfiles\alphasports.mdb'}"
sql_update = "update customer set bill_city = 'Phoenix', bill_postal_code = '08000', bill_state_region = 'AZ' where customer_id = 1"





This example is hard coded and I cannot make the step (LEAP) to using an array.

sql_update = "update Gridster set col = 'help!', row = 'help!', size_x = 'help!', size_y = 'help!' where Grid_id = 1"

Thanks in advance David.

Michael

[Davidk]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Michael,

I wonder if there is a better (read "cleaner") way of doing this... but...

This takes your Array of JSON Objects (2 in your example) and creates a SQL Update statement for each object within a loop.
I think this is what you're after. Change the connection to your own... then run through it with debug and you'll see how it works.

function ProcessJSON as c (e as p)

debug(1)

'here is the array of JSON Objects you say you already have
dim myJSON as c = "[{col:1,row:1,size_x:2,size_y:2},{col:3,row:7,size_x:1,size_y:1}]"
dim arr[0] as p 
a5_JSON_PopulateArray(arr,myJSON)
'Now you have your Array of JSON Object inside an XBasic Array

delete cn
dim cn as sql::Connection
dim flag as l
flag = cn.open("::Name::Test")

if flag then

	dim p as p
        dim replaceArray as p



        'because the data is in an XBasic array, we can use that for a loop for each record update
	for i = 1 to arr.size()

sql_update = <<%txt%
update Gridster set col = p.col, row = p.row, size_x = p.size_x, size_y = p.size_y where Grid_id = 1
%txt%
                'the following creates a pointer array and assigns the current element of our data array to it.  Then we use replace_varibles... to set up the update sql statement.
		replaceArray = arr[i]
		sql_update = replace_variables_in_string(sql_update, replaceArray, "p")
	
		flag = cn.Execute(sql_update)
		if flag then
			'nothing else to do
		else
			'there was an error - close the connection and exit
			cn.Close()
			dim msg as c 
			msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
			msg = js_escape(msg)
			dim jscmd as c 
			jscmd = "alert('" + msg + "');"
			ProcessJSON = jscmd
		end if  
		
	next i		
        cn.close()

else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	ProcessJSON = jscmd
end if

end function

EDIT: corrected sql_update line

[Michael1954]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Thanks David

I will give this a try

Michael

[Michael1954]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Hi David

I am getting an error msg = "Could not execute the query.Line 41 Error reported was: " + cn.CallResult.text "Not currently connected to the database"

flag = cn.Execute(sql_update)

turns to false it was true upto this point.


"update Gridster set col = 1, row = 1, size_x = 2, size_y = 2 where Grid_id = 1"

If I strip out " " the parenthesis I can run the SQL statement at the command line and the update is performed.
Does Alpha need them or should they be removed?

If so how to remove?

Michael

[Davidk]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Yes... sorry... I just copied your line of code in there... should be...

sql_update = <<%txt%
update Gridster set col = p.col, row = p.row, size_x = p.size_x, size_y = p.size_y where Grid_id = 1
%txt%

[Michael1954]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Hi David

Really sorry but I have nearly got it working. I removed a cn.close()

if flag then
'nothing else to do
'removed cn.Close()
else

However the first update works and the second does not.

It appears to be a leading , I remember Selwyn mentioning this in a video.

How would I strip these in xbasic?

Michael

P.S. do you know NienKamper they appear to be just down the road.

http://www.nienkamper.com/ good people.

[Davidk]

Re: Xbasic JSON to and from MySQL using Json_parse() Json_Generate()

Sorry... I usually run the code... didn't this time...

Yes... I forgot we're in a loop... take out that cn.Close... put it after the next i statement... when we're all done.

Move the sql_update = statement to just after the start of the loop... I've corrected the code above.

We need to reset the sql_update variable in order to do the replace.

No... I haven't heard of that company... interesting.