sql select

**jmonte407** · 2013-02-17, 08:15

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** · 2013-02-17, 20:39

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.

Code:

<%
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** · 2013-02-18, 13:15

Thanks Barry.. Very Cool!

**jmonte407** · 2013-04-04, 09:14

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** · 2013-04-04, 09:24

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

**jmonte407** · 2013-04-04, 09:32

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** · 2013-04-04, 09:33

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

**barrymavin** · 2013-04-04, 09:38

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** · 2013-04-04, 09:44

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"...