sql select

[jmonte407]

I was going through the docs about json... In a SQL select statement there are options to return the results to file "json format is one the supported types"

Do I still need the added step of filetostr("file.json") or can the result be a variable and simply sent back to the client?

[barrymavin]

Jim, when you say "sent back to the client", what client are you referring to?

JSON, XML and HTML output types from SQL SELECT are textual. You normally use these in server pages (.rsp pages) to send the text directly back to the browser. When used this way it is primarily used in WebView sections and is more relevant to the Lianja Cloud Server than Lianja SQL Server as ODBC has no simple way of dealing with textual output like this.

You can use jquery ajax calls with the Lianja Cloud Server to call an .rsp page with arguments and send the raw JSON, XML or HTML back to the browser using one command as Lianja SQL has been extended to handle these output formats directly (no special coding required).

For example here is a simple .rsp page that will output all customers as JSON which can be decoded in your jquery ajax completion function into a javascript object or otherwise given directly to your own javascript UI framework with minimal coding. All output generated by the .rsp page is sent back to the browser. If you look at the HTML5 charting and reporting code in the lianja libraries directory you can see how this is all accomplished.



<%
open database southwind
select * from customers into json stdout
%>


.rsp pages look very similar to (Java Server Pages) .jsp pages and can include many directives such as <%@ include="mylibrary.js"> as well as supporting "codebehind" (like .aspx pages) and inline expressions intermingled in the HTML using <%= expression>.

This is all available in the Lianja Cloud Server (which also has a full Web Client for desktop and mobile that renders and runs the apps you build using the Lianja App Builder). There a many industry standard javascript frameworks included in the Lianja distribution; these are in the library directory.

It is also worth pointing out that .rsp pages have the complete Lianja/VFP cross-platform scripting language available to them so the sky is the limit really when it comes to building data-centric Apps that use them. So you have all the Lianja SQL as well as all the navigational data access commands (NoSQL) that you have in the Lianja App Builder. This engine is common across all of the Lianja products; Lianja App Builder, Lianja SQL Server (write stored procedures in Lianja/VFP), and Lianja Cloud Server.

[jmonte407]

Thanks Barry.. Very Cool!

[jmonte407]

why doesnt this code produce json? it creates a sqlcursor... I've tried SAVE AS ... INTO... TO FILE.... only produces cursors...

clear
lcDSNLess="DRIVER=Lianja ODBC Driver; SERVERNAME=10.0.0.5;USERNAME=?;PASSWORD=?;DATABASE =jimsdatabase"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
?lnConnHandle
if lnConnHandle > 0
*WAIT WINDOW "press key"
a= sqlexec(lnConnHandle,"select * from inventory INTO json inventory.json")
*WAIT WINDOW STR(a)
sqldisconnect(lnConnHandle)
endif

[barrymavin]

It does, on the server where the SQL select is being executed.

[jmonte407]

I'm running the code from the server.. is it saving to the lianja sql install folder? if it is saving.. why does it produce a cursor too?... I'm running the code from vfp9 in a prg file... no file is created in the folder i'm running the query... I even hard coded a folder with it... "c:\temp2\json ... no file found...

[barrymavin]

Hi Jim, check that the connection is successful. Try it interactively in te console to verify the syntax.

[barrymavin]

SQL selects always produce a temporary set of data, then process it depending on the output type. If its not producing the file it may be file permissions. Where is it you want the JSON to go to? Into a fie? Try using the full path in quotes.

[jmonte407]

I'm an adminstrator on this computer.. the folder is set for "everyone" read/write.. connection is successful.. It works if i'm just creating a cursor.. all the records are returned... but outputs such as HTML.. XML... JSON ... doesnt work,,, just create the cursor as this I just used "select * from inventory"...

[barrymavin]

Hi Jim, you can see what the default directory is like this.

select default() as def from sysresultset

[barrymavin]

I am not on my computer at the moment. I will investigate and get back to you when I am.

[jmonte407]

Thanks Barry.... I'm only testing this stuff with VFP9.. I will eventually be using node.js instead of aspx to server my data "in the json format"......

[barrymavin]

Hi Jim, I have fixed that now so it will be available in the next build. If you try it now with SELECT ... INTO HTML myfile.html that should work now.

I anticipate that we will ship Lianja App Builder RC5.3 and Lianja SQL Server RC5 early next week.

[jmonte407]

Thanks Barry... 99% of what we will be retrieving from the server will be json.. One quick question... Why can't the result just be returned as a long string? instead of a file "somefile.json" Filetostr() on big text files will slow it down a little.....

[barrymavin]

It can. Just write a stored procedure that loads it into a VARCHAR and read it on your client.

If you want to just do it from a URI, use the Cloud Server and .rsp pages. Very fast and simple.

[barrymavin]

Ans the cloud server handles OData calls. Just point at a URI and it all goes back as JSON. Images too are handled and can be data bound into a Web Client.

[jmonte407]

I know this... was just hoping something like this could be done "Select * from somefile INTO STRINGJSON" LOL....

[HankFay]

I'm an adminstrator on this computer.. the folder is set for "everyone" read/write.. connection is successful.. It works if i'm just creating a cursor.. all the records are returned... but outputs such as HTML.. XML... JSON ... doesnt work,,, just create the cursor as this I just used "select * from inventory"...

select * from southwind!customers into object myDataObject
myJsonString = json_encode(myDataObject)

By and large, Lianja is made for large numbers of users on a server. It saves memory by having only 1 copy of the runtime in memory, using separate memory spaces (multi-tenant) for each user. Using a memory object, then, rather than a file, is a trade-off between memory available for other uses, and speed. In most cases I don't think it will make much difference in memory, as there aren't that many applications where you have 3,000 users hitting a server: it's just something to keep in mind. And of course, it's all able to be tuned.

hth,

Hank

[barrymavin]

Or as stated here for outputting objects as JSON.
http://www.lianja.com/doc/index.php/PRINT_JSON()



open database southwind
select * from shippers into object shipobj
print_json(shipobj)


In a .rsp page that will send the JSON straight back to the browser so you would typically use this with an AJAX call.

[jmonte407]

Just install the latest of everything... I was going to test the Lianja SQL server to see if the select to json stuff worked... however, nothing seemed to happen... then I looked to see if the sql server was running and it was stopped... I clicked start and got this message "Service state was not set"... I rebooted my computer.. looked to see if the sql server was running.. It was running.. so I clicked stop.. then clicked start again... same message .. "Service state was not set" ...

[barrymavin]

Hi Jim, It will be fixed in RC5.4 -- its just the expiry date on the beta. Will be available within a day or so.

[jmonte407]

Thanks Barry...

[barrymavin]

Jim, make sure you stop the Lianja SQL Server before the install. Maybe thats the issue.

[jmonte407]

I did have it stopped... I tried reinstalling it 3 or 4 times... with the same results..

[jmonte407]

Success... I unistalled it again... deleted the c:\lianja folder.. installed it ... and it seems to be working fine...

Thanks
Jim

[barrymavin]

Thats good.

[jmonte407]

Back to the same issue... nothing works for select * from some file (into json) (save as json) (to file )....

[barrymavin]

Jim, try it at the console in the App Builder.

[barrymavin]

Ah. Sorry. You don't have RC5.4 yet. Hang off a few days.

[jmonte407]

just downloaded everything again.. tried to create json files from select statement in both vfp9 and the lianje command/console window.. no file is ever produced

here is the code again...

clear
lcDSNLess="DRIVER=Lianja ODBC Driver; SERVERNAME=10.0.0.5;USERNAME=?;PASSWORD=?;DATABASE =jimsdatabase"
lnConnHandle=SQLSTRINGCONNECT(lcDSNLess)
?lnConnHandle
if lnConnHandle > 0
a = sqlexec(lnConnHandle,"select * from inventory into JSON C:\TEMP2\stdout.TXT") <-- doesnt work
a = sqlexec(lnConnHandle,"select * from inventory save as JSON stdout.txt") <-- doesnt work
a = sqlexec(lnConnHandle,"select * from inventory to file C:\TEMP2\sdtout.txt") <-- doesnt work


WAIT WINDOW STR(a)

sqldisconnect(lnConnHandle)

ELSE
WAIT WINDOW "no connhandle"

endif

[yvonne.milne]

Hi Jim,

Sorry to hear this is still causing problems. I have Submitted a Ticket to get it looked into again.

Thanks,

Yvonne

[Phjr]

Hi, Sql Server RC6.0 still hangs when restarted ! please find log below
* Lianja SQL Server
* Version 10.0.4
* Compiled on Jun 17 2013 18:21:30
*
* logfile C:\Lianja\sqlserver\log\portserver_007.log
* started at Wed Jul 10 10:38:13 2013
* on PAM-003818 pid 4432
*
RSI: **** Recital/RSI TCP/IP port server activated ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150
RSI: **** getservbyname("db_rsibase","tcp") port=8001 ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150
RSI: **** setsockopt SO_RCVBUF=16384 ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150
RSI: **** setsockopt SO_SNDBUF=16384 ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150
RSI: **** socket buffer sizes - so_rcvbuf=16384, so_sndbuf=16384 ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150
RSI: **** Trial license has expired ****
ident = [tcp_accept]
time = 10:38:13
pid = 00001150

Regards

[barrymavin]

You need to download the RC6 version again and reinstall. That version has expired as stated in the logs.

RSI: **** Trial license has expired ****

Make sure you have stopped it before installing the new version otherwise the installer cannot overwrite a file that is open.

[Phjr]

uninstalled existing Sql Server, installed RC6 Sql Server in brand new directory (c:\LianjaRC6), same result ....
* Lianja SQL Server
* Version 10.0.4
* Compiled on Jun 17 2013 18:21:30
*
* logfile C:\LianjaRC6\sqlserver\log\portserver_001.log
* started at Wed Jul 10 12:07:42 2013
* on PAM-003818 pid 1848
*
RSI: **** Recital/RSI TCP/IP port server activated ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738
RSI: **** getservbyname("db_rsibase","tcp") port=8001 ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738
RSI: **** setsockopt SO_RCVBUF=16384 ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738
RSI: **** setsockopt SO_SNDBUF=16384 ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738
RSI: **** socket buffer sizes - so_rcvbuf=16384, so_sndbuf=16384 ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738
RSI: **** Trial license has expired ****
ident = [tcp_accept]
time = 12:07:42
pid = 00000738

[barrymavin]

Someone in support will look into it and see why thats happening.

[barrymavin]

Submit a ticket and attach a directory listing of c:\lianja and all its subfolders with dates.

Lianja SQL server is normally installed in c:\lianja\sqlserver do you still have that directory on your disk?

[Phjr]

yes, and I normally install everything in c:\lianja. I installed Sql Server in c:\LianjaRC6 to isolate the installation.
Regards

[barrymavin]

Ok but is the \lianja\sqlserver directory still there and does it contain anything?

[Phjr]

It's still there with files and logs for Sql Server. Opened a ticket with requested files.
Regards

[barrymavin]

Yes I saw that. Someone in support will take a look today (US time).

[barrymavin]

Yep it seems the distro did not build properly. We will get that fixed up tomorrow.