[Answers] JSON and OData

[josipradnik]

Q:
My json file looks like this:

{"dbobjects":
  [
    {"name": "Template", "type": "table"},
    {"name": "ADAttributes", "type": "table"}
  ]
}

I am using the code below to iterate through the dynamic array but I get a compile error

jsonDBOs = json_decode_file(filePath)

foreach jsonDBOs.dbobjects as dbo1
  //Do Something                
endfor


**** Lianja error ****
foreach jsonDBOs.dbobjects as dbo1                
^
IN was expected
Called from program   - dbupgrade.prg at line 30
Called from procedure - page1_section2_field1_click at line 4

I tried declaring jsonDBOs as a dynamic array but that didn't work. jsonDBOs.dbobjects is a valid array if I access it manually in the debug console.
A:
Try creating a reference to the array:

adbo = jsonDBOs.dbobjects
foreach adbo as dbo1
  ? dbo1.name
  ? dbo1.type
endfor

an array name or object name should be specified not an expression.

---

Q:
Is there a way I can consume Odata data in the desktop?
I was thinking that geturl() should work, but I am not having any luck with that.
I thought that this might work, but looking in fiddler, there is nothing being sent.

filename = geturl("http://localhost:8001/odata/southwind/customers", 30, array(), "myfilename.json")
if len(filename) = 0
    wait window "no data"
else
        wait window "file has Data"   
endif

No luck at the moment.
Running this

filename = Lianja.odata_read("http://localhost:8001/odata/southwind/customers", "myfilename.json")

I am getting the following.

**** Lianja JavaScript error ****
Traceback (most recent call last):
page1_section1.js at 10
<global>() at 1TypeError: Result of expression 'Lianja.odata_read' [undefined] is not a function.

A:
The OData functions were not available in JavaScript custom sections.
I have now added the OData functions to the Lianja system object for JavaScript/Python/PHP desktop apps (already in web and mobile), so these will be available in the next beta build.

var jsonstring = Lianja.OData_Create(url, jsonstring);
var jsonstring = Lianja.OData_Read(url);
var jsonstring = Lianja.OData_Update(url, jsonstring);
var jsonstring = Lianja.OData_Delete(url, jsonstring);

---

This is an example of manually updating the data in a webview.
This uses the Lianja.Odata_update() method.
You don't need any PHP or ASP.net code, it handle that all for you in a simple function call.
Lianja OdataUpdate - https://youtu.be/etedfN6h3_I

---

Q:
I need to populate an array with data from a json file,but did not want to use subscripts to access the data in the array e.g. array[1,1] but access them as objects just like a table.
A:
Try this approach:

open database southwind
select * from categories into json xxxx
ss=json_decode_file("xxxx.txt")
*?ss   // view all array elements
*?ss.row7.categoryname  // referencing a subelement of dynamic array
for ii=1 to alen(ss)
    num=alltrim(str(ii))
    catid="ss.row"+num+".categoryid"
    catname="ss.row"+num+".categoryname"
    x1=&catid
    x2=&catname
    ? x1
    ? x2
endfor

Result:

1
Beverages
2
Condiments
3
Confections
4
Dairy Products
5
Grains/Cereals
6
Meat/Poultry
7
Produce

If you use SQL select like I did here, the same is accomplished with INTO OBJECT:

select * from categories into object ss

Referencing is the same.

?ss.row7.categoryname  // referencing a subelement

---

---

​All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers

[josipradnik]

Q:
The app has to be an executable, so electron looks perfect.
It is a disconnected app that has no database and needs to be installed all over the globe as just an executable and some support files.
All of the data is coming from already existing web services. So I am decoding json data with getUrl and json_decode.
I will try a temp table/cursor on init

A:
until we get disconnected data (it's in the roadmap, currently for 4.1), that will not work. The cursor never exists on the mobile client: JSON record(s) are sent from the server, and updates are sent back by JSON.

---

Q:
In a canvas section, there are input textbox, combobox and checkbox. These input items table fields (character/numeric/logical) have data source bind to them.
When I use getJson() to pass all the item values to backend.prg, all the data types decode from the Json parameter seems to be character or logical .f. if nothing enter into the textbox.
Is this the correct behaviour?

A:
JSON format is text only
When exchanging data between a browser and a server, the data can only be text.
Logical .f. is not correct JSON format and you should provide some value if nothing is entered in textbox.

A2:
I would also recommend that you use the base64_encode() function to encode the JSON if you are passing it as parameter. In your server side proc you would then use use base64_decode() to decode it.
This prevents any issues with special characters being transferred.

---

Lianja Cloud Server supports OData-compatible data access.
The Server handles ODBC connections as well as HTTP requests using OData URIs. In the following article I will show you how to use Lianja Cloud Server with OData URIs that will allow you to perform CRUD (Create, Read, Update and Delete) operations on your data.
Making OData requests from jQuery and/or the Lianja HTML5 Client in a Web App is extremely straightforward and simple.
Unlike other OData implementations, Lianja Cloud Server OData services does not require any server-side configuration of web services.
http://www.lianja.com/resources/blog...-cloud-server-
You can use Lianja cloud server odata calls to perform CRUD operations on virtual tables too so this functionality is not limited to native Lianja data; MSSQL, MySQL, PostgreSQL, Oracle and others are all supported.
Let me just emphasize again. Unlike other OData implementations, Lianja Cloud Server OData services does not require any server-side configuration of web services.

---

Using OData to query MSSQL is working fine for me.



The example shows how to use it with Lianja SQL Server.
wwwroot should be set to C:\lianja\cloudserver\tenants\public\wwwroot\
Remember also that you have the
odata_create(),
odata_read(),
odata_update() and
odata_delete()
functions that can be used in both programs and rsp pages. These functions provide an OData abstraction above the target database SQL syntax. e.g. you can fetch MSSQL data using $limit (see below).

---

$format=img, if you select an image field from a table then it will be output as an IMG tag with embedded data encoding so that you can insert this into the DOM to fetch and display an image.



By specifying memo fields you can also perform CRUD operations on JSON data.

---

Q:
Is it also possible to use the Lianja oData interface to call a server side function which then returns JSON data?
to allow our non-Lianja clients access to our server side functions.
by including and utilising the LianjaCloudDataServceis.js in our non-lianja clients

A:
Yes. You have several choices.
1. OData calls
2. result = Lianja.evaluate('your_proc( args )'); calls on the client with your_proc residing on the server. ( you can also map client-side JavaScript functions by describing them in exports,conf )
3. Retrieve results of a dynamic .rsp page located in the server.

---

You can also write full rsp pages that return json results fast super fast.

---

Q:
If I have a stored procedure named sp_testfunction.prg in my SouthWind database, which returns the customers table in json format, how would I call the function using OData?

A:
Normally you would use Lianja.evaluate() from an open app, but to evaluate code from the OData URI with no app open you need to create an app that will be used to contain all of your stored procs for fetching JSON encoded data and execute like this.

http://localhost:8001/odata?$eval=fetchcustomer()&$app=yourapp

In order to call server side stored procedures in a database I have now recognized databasename! when using $eval in the 3.3 final release (next build):

// sp_fetchcustomer.prg stored procedure in southwind database
//
// usage: 
//
//    http://localhost:8001/odata?$eval=southwind!sp_fetchcustomer()
//    or
//    http://localhost:8001/odata?$eval=sp_fetchcustomer()&$database=southwind
//
parameter p_custid
if parameters() = 0
    // fetch all customer records
    select * from southwind!customers into cursor mycur
else
    // fetch a specfic customer record
    select * from southwind!customers where customerid = "&p_custid" into cursor mycur
endif
copy to arrayofobjects myobj
// return JSON encoded data to the client
return json_encode(myobj)

Remember that you can test all your stored procs in the "Console" workspace:

odata_read("/odata?$eval=southwind!sp_fetchcustomer('ALFKI')")

If your data handling procs are in a library you can also make calls like this:

odata_read("/odata?$eval=mylibrary::fetchcustomer('ALFKI')")

---

Q:
I created myLibrary.prg containing your fetchCustomer procedure.
When I run odata_read("/odata?$eval=mylibrary::fetchcustomer('ALFKI')") from console it works as expected.
However, if I send

http://localhost:8001/odata?$eval=myLibrary::fetchCustomer(%27ALFKI%27)

from a browser I get a 404 error:
The requested URL /odata?$eval=myLibrary::fetchCustomer(%27ALFKI%27) was not found on this server.
A:

Any odata API calls from the browser will reference a deployed library as opposed to the development version. You need to deploy it first.

For performance reasons, the library is opened up only once and kept open which is why you are seeing this behavior. On a heavily loaded system with many concurrent requests occurring this is required so that the library is not opened and parsed for each odata API call.

---

---

All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...son-and-odata/