Show Results of Xbasic SQL Query in UX

[DoronF]

Re: Show Results of Xbasic SQL Query in UX

Hi Jinx,

I actually developed a Query and Report Engine that generates a whole SQL statement and much more in Visual FoxPro Engine. I started that actually 15 years ago and your process reminded me how I started. Good luck with your work...

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com
MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
Convert Ms Access to Web
Custom Software Development
Alpha Five Development
No Interest Loans Application
MS SQL Remote DBA

[bowenjohn]

Re: Show Results of Xbasic SQL Query in UX

Sigh, this is why a list control would be so nice if it could be made to render dynamic columns.

A couple of years ago I started to build something like this for my work but got side tracked and picked it up yesterday. I ran into the same problem with the List Control rendering dynamic columns. I am working on trying to solve this, I will let you know if I find a solution.

BTW, this is a great thread between you and GGandhi.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

very nice.

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

Thanks Gandhi,

I was busy with some other work the last day or so but I got back to it today a little. The HTML rendering is the only slow part now. Everything else seems to be nice and speedy. I also would like to figure out how to make my table columns resizable.

Anyway, here's a preview of what I've got.

http://screencast.com/t/M75SBdtnnUdm

I added an output to excel so that they can send the long queries out that way. It's funny that building a huge Excel file is faster than rendering the HTML

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

okay here I have shown two videos

ajax callback to grid done entirely thru alpha five
http://screencast.com/t/jekFBjXqBEy0

alpha five to emit the json data and the angularjs to pick up and process the data ( done cross site HTTP)
http://screencast.com/t/zBeB8q9YV9Zr

hope you like them
good luck with your project.
edit:
here is the screencast for the angular_itemsCallback.a5w that is in the server which emits the json object array for angularjs to pick up.
http://screencast.com/t/Qt9i0FsZt

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

Ok, I used obj.length as you suggested. I also commented out the cnt portion of the code. It didn't make too much difference on the server side but it's definitely a better solution. The biggest difference was the first for loop that you reminded me to take out.

Anyway, now it seems like the html rendering is the slow point. Maybe I'll try rendering the Inner html a row at a time similar to what MSSQL server does to keep the user entertained....LOL

I think I might have to figure out how to paginate the data.

Anyone have any ideas on the HTML rendering?

Sigh, this is why a list control would be so nice if it could be made to render dynamic columns.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

okay when i get home tonight i will show how i did with your sample you gave in the first post.
if you want me to work a large data send it thru pm. i will download and process that and post back to via pm.

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

you are still looping thru after the parsing so there is speed break.
show a partial resultset and I can understand better to see any improvement can be done. or may be you are right to start with anyway.

I'm only looping through the columns, not the rows so it's not like I'm going through the whole result set. After it get's to the client side, yes, I'm looping through in JavaScript to build the HTML. In Post #5 above, you will find a result of my Xbasic code. I can give you another one if you want. Maybe I'm not understanding what you want.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

you don't need the count, obj.length will be sufficient.

result = "["+strtran(result, crlf(),","+"]" will be sufficient for that portion of the code. where you are creating an array.
rstoJSONOject in version 11 is not right, in my opinion. you need to rework it.
once the object is built you don't parse it in xbasic do it in javascript.
again you don't need column count that can be done itself.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

you are still looping thru after the parsing so there is speed break.
show a partial resultset and I can understand better to see any improvement can be done. or may be you are right to start with anyway.

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

Inefficient. I need to return ALL ROWS that the query returns. Looping through the result set (rs.data(1) etc.) is even slower than what I'm doing above. That's why I'm doing this in the first place. For speed.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

counterintuitive!
you have the resultset and the data is available as data(1) or data("name")
you are converting to an object and parsing in xbasic?!?

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

So, that portion of the code looks like this now:

(commented for clarity of why I'm doing stuff)

result = rs.ToJSONObjectSyntax()
'Get the count to let users know how many rows affected
cnt = w_count(result,chr(13))
'add a bracket to the front of the result for proper formatting.
results = "[" + result
'change the crlf delimited list to comma delimited.  Otherwise javascript doesn't like the line breaks
results = crlf_to_comma(results)
'escape the javascript senistive stuff in the results like single quotes that may be in the sql stuff returned  WHY DOESNT .ToJSONObjectSyntax() DO THIS??!!
results = js_escape(results)
'the rest of the stuff below shouldn't take much time as it's just building a string for javascript code
results = "var data = '" + results
results = results + "]';" + crlf()+ "var obj = JSON.parse(data);"
if columns[1] <> "" then
clmcnt = columns.SIZE()
	for i = 1 to clmcnt
		inhtml = inhtml + "<th>" + columns[i] + "</th>"
	next
inhtml = inhtml + "</tr>'"
results = results  + crlf() + "var inhtml = " + inhtml + crlf() + "for (i = 0; i <"+cnt+"; i++){" + crlf() + "inhtml = inhtml + '<tr><td>' + i +'</td>"
	for i = 1 to clmcnt
		results = results + "<td>' + obj[i]."+columns[i]+"+'</td>"
	next
results = results + "</tr>'" + ";" + crlf() + "}"+ crlf() + "inhtml = inhtml + '</tr></table>';" + crlf() + "$('RESULTS').innerHTML= inhtml;"
end if

a.h
why you need to parse the object in the xbasic?
can you show what is returned ( even truncated in the middle)?

The very first code I pasted is what the xbasic returns to the browser.

[GGandhi]

Re: Show Results of Xbasic SQL Query in UX

a.h
why you need to parse the object in the xbasic?
can you show what is returned ( even truncated in the middle)?

[-Jinx-]

Re: Show Results of Xbasic SQL Query in UX

It basically returns a JSON string. BASICALLY. It still needs to be formatted correctly by changing the crlf's to commas. I need cnt but you're right that I didn't need:

for i = 1 to cnt
results = results + word(result,i,chr(13)) + crlf()
next

That was a left over artifact that needed to be cleaned up by changing the previous line to:

results = "[" + results

So that saves some time. I'm working on adding a timer to the JavaScript portion right now to see the time it takes to execute the server side code vs the time it takes to execute the client side code.