Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: sql select

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    55

    sql select

    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?

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,784
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    55
    Thanks Barry.. Very Cool!

  4. #4
    Member
    Join Date
    Jun 2012
    Posts
    55
    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

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,784
    It does, on the server where the SQL select is being executed.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Member
    Join Date
    Jun 2012
    Posts
    55
    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...
    Last edited by jmonte407; 2013-04-04 at 09:40.

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,784
    Hi Jim, check that the connection is successful. Try it interactively in te console to verify the syntax.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,784
    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.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Member
    Join Date
    Jun 2012
    Posts
    55
    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"...

  10. #10
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,784
    Hi Jim, you can see what the default directory is like this.

    select default() as def from sysresultset
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

Page 1 of 5 123 ... LastLast

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us