OData [examples]

OData-compatible function to create a record

ODATA_CREATE

// OData-compatible functions
odata_read("/southwind/shippers")
odata_read("/southwind/shippers","readresults.txt")
? filetostr("readresults.txt")
 
cCreateString = '{"shipperid":4,"companyname":"Acme Inc.","phone":"(503) 555-1234"}'
odata_create("/southwind/shippers",cCreateString)
odata_read("/southwind/shippers")
 
cUpdateString = '{"phone":"(503) 555-5678", "__olddata":{"shipperid":4}}'
odata_update("/southwind/shippers",cUpdateString)
odata_read("/southwind/shippers")
 
cDeleteString = '{"shipperid":4}'
odata_delete("/southwind/shippers",cDeleteString)
odata_read("/southwind/shippers")

https://www.lianja.com/doc/index.php/ODATA_CREATE()

---

OData-compatible function to delete records

ODATA_DELETE

// All records that match the name/value pairs will be deleted
// Here all the order_details records with an orderid of 10951
// will be deleted
cDeleteString = '{"orderid":10951}'
odata_delete("/southwind/order_details",cDeleteString)
 
// OData-compatible functions
odata_read("/southwind/shippers")
odata_read("/southwind/shippers","readresults.txt")
? filetostr("readresults.txt")
 
cCreateString = '{"shipperid":4,"companyname":"Acme Inc.","phone":"(503) 555-1234"}'
odata_create("/southwind/shippers",cCreateString)
odata_read("/southwind/shippers")
 
cUpdateString = '{"phone":"(503) 555-5678", "__olddata":{"shipperid":4}}'
odata_update("/southwind/shippers",cUpdateString)
odata_read("/southwind/shippers")
 
cDeleteString = '{"shipperid":4}'
odata_delete("/southwind/shippers",cDeleteString)
odata_read("/southwind/shippers")

https://www.lianja.com/doc/index.php/ODATA_DELETE()

---

OData-compatible function to return data

ODATA_READ

// Use URI arguments to select the required records
// Here 5 customers records are returned starting from record 10
odata_read("/southwind/customers?$skip=10&$top=5")
 
// Call a stored procedure 'sp_fetchcustomer' in the southwind database
odata_read("odata?$eval=southwind!sp_fetchcustomer('ALFKI')")
// or
odata_read('odata?$eval=sp_fetchcustomer("ALFKI")&$database=southwind')
 
// Call a function 'fetchCustomer' in the 'mylibrary.prg' function library
// mylibrary.prg should be in the current App or in the library
odata_read('odata?$eval=mylibrary::fetchcustomer("ALFKI")')
 
// Call fetchcustomer.prg located in the myapp App
odata_read('odata?$eval=fetchcustomer("ALFKI")&$app=myapp')
 
// Call a function 'fetchCustomer' in the 'mylibrary.prg' function library
// located in the myapp App
odata_read('odata?$eval=myapplibrary::fetchcustomer("ALFKI")&$app=myapp')
 
// OData-compatible functions
odata_read("/southwind/shippers")
odata_read("/southwind/shippers","readresults.txt")
? filetostr("readresults.txt")
 
cCreateString = '{"shipperid":4,"companyname":"Acme Inc.","phone":"(503) 555-1234"}'
odata_create("/southwind/shippers",cCreateString)
odata_read("/southwind/shippers")
 
cUpdateString = '{"phone":"(503) 555-5678", "__olddata":{"shipperid":4}}'
odata_update("/southwind/shippers",cUpdateString)
odata_read("/southwind/shippers")
 
cDeleteString = '{"shipperid":4}'
odata_delete("/southwind/shippers",cDeleteString)
odata_read("/southwind/shippers")

https://www.lianja.com/doc/index.php/ODATA_READ()

---

OData-compatible function to update records

ODATA_UPDATE

 

// All records that match the name/value pairs in __olddata will be updated
// Here all the order_details records with an orderid of 10951
// will have their discount field set to 6
cUpdateString = '{"discount":6, "__olddata":{"orderid":10951}}'
odata_update("/southwind/order_details",cUpdateString)
 
// OData-compatible functions
odata_read("/southwind/shippers")
odata_read("/southwind/shippers","readresults.txt")
? filetostr("readresults.txt")
 
cCreateString = '{"shipperid":4,"companyname":"Acme Inc.","phone":"(503) 555-1234"}'
odata_create("/southwind/shippers",cCreateString)
odata_read("/southwind/shippers")
 
// Specify a unique name/value pair identifier to update an individual record
cUpdateString = '{"phone":"(503) 555-5678", "__olddata":{"shipperid":4}}'
odata_update("/southwind/shippers",cUpdateString)
odata_read("/southwind/shippers")
 
cDeleteString = '{"shipperid":4}'
odata_delete("/southwind/shippers",cDeleteString)
odata_read("/southwind/shippers")

https://www.lianja.com/doc/index.php/ODATA_UPDATE()

---

// create a new record

var result = Lianja.OData_Create(
       "/odata/southwind/customers",
       { "customerid": "ABCD",
       ...
       }
);
if (!result)
{
       // Error: data cannot be created
}

// read records

Lianja.OData_Read(
    "/odata/yourdatabase/yourtable?$top=1&$skip=20&$rowid", // fetches row 21.
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

// update a record

Lianja.OData_Update(
    "/odata/yourdatabase/yourtable?$rowid=10", // updates record 10
    { "name": "value", "amount":25.67, "date": "20140404", 
      "__olddata" : { "amount":20.00, "date": "20140404" } },
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

// delete a record

Lianja.OData_Delete(
    "/odata/yourdatabase/yourtable?$rowid=10", // deletes record 10
    { "name": "value", "amount":25.67, "date": "20140404" },
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

https://www.lianja.com/doc/index.php/Working_with_JSON_and_JQL

---

When used with the Lianja Cloud Server, the URI is formed as follows:

/odata//

?&&…

e.g.

http://localhost:8001/odata/southwind/customers?$skip=10&$top=5

Or, if the Lianja Server is listening on port 80 or the ISAPI Extension for IIS is installed:

http://localhost/odata/southwind/customers?$skip=10&$top=5

Notes

When using the $sql argument, the URI does not include a table reference:

/odata/?$sql=

e.g.

http://localhost:8001/odata/southwind?$sql=select * from shippers

When using the $eval argument, the URI does not include a database or table reference:

/odata?$eval=

e.g.

http://localhost:8001/odata?$eval=time()

Note: the OData Read and Update services can be enabled/disabled in the Lianja Server Manager Security Settings.

OData Functions

When using the OData Functions from the Lianja App Builder Console or from a Lianja/VFP program (.prg), Lianja/VFP Server Page (.rsp) or JavaScript Server Page (.jssp), the URI is formed as follows:

//

?&&…

e.g.

odata_read("/southwind/customers?$skip=10&$top=5")

Exceptions

When using the $sql argument, the URI does not include a table reference:

/?$sql=

e.g.

odata_read("/southwind?$sql=select * from shippers order by companyname")

When using the $eval argument, the URI does not include a database or table reference:

/?$eval=

e.g.

odata_read("?$eval=time()")

or

odata_read("/odata?$eval=time()")

Value Selector

URIs can include a ‘value selector’ on the table.

e.g.

http://localhost/odata/southwind/customers('A*')

odata_read("/southwind/customers('A*')

The OData server will use heuristics to determine the column that ‘A*’ is referring to in the ‘customers’ table. If the column customersid exists it will be used, if that does not exist and the table is a ‘collection’ i.e. ends with an ‘s’, the ‘s’ will be removed and the column customerid will be looked up.

The column can also be specified, for example to select all customers where the ‘contactname’ starts with ‘A’:

http://localhost/odata/southwind/customers('A*', contactname)

odata_read("/southwind/customers('A*', contactname)")

The $nostrcompare argument can also be used to disable exact matching:

http://localhost/odata/southwind/customers('A', contactname)?$nostrcompare

odata_read("/southwind/customers('A', contactname)?$nostrcompare")

https://www.lianja.com/doc/index.php/OData_URIs

---

JSON [examples]

Create a JSON formatted string from a dynamic array (object)

JSON_ENCODE

myobj = json_decode('{"Name":"Barry", "Company":"Lianja", "Items":["One", "two"]}')
? json_encode(myobj)

{"name":"Barry","company":"Lianja","items":["One","two"]}

https://www.lianja.com/doc/index.php/JSON_ENCODE()

---

Display object in JavaScript Object Notation (JSON)

PRINT_JSON

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

shipobj:{row1:{shipperid:1,companyname:"Speedy Express",phone:"(503) 555-9831"},
	row2:{shipperid:2,companyname:"United Package",phone:"(503) 555-3199"},
	row3:{shipperid:3,companyname:"Federal Shipping",phone:"(503) 555-9931"}}

https://www.lianja.com/doc/index.php/PRINT_JSON()

---

Create a dynamic array (object) from a JSON formatted file

JSON_DECODE_FILE

filename = geturl("http://www.myserver.com/getsomepage.rsp?name=smith&month=10", 30, array(), "myfilename.json")
myobject = json_decode_file(filename)

https://www.lianja.com/doc/index.php/JSON_DECODE_FILE()

---

Create a dynamic array (object) from a JSON formatted string

JSON_DECODE

 

myobj = json_decode('{"Name":"Barry", "Company":"Lianja", "Items":["One", "two"]}')
? myobj
? myobj.name
? myobj.company
? myobj.items[1]
? myobj.items[2]

Object (refcnt=1)
(
    [name] => Barry
    [company] => Lianja
    [items] => Array (refcnt=1)
        (
            [1] = One
            [2] = two
        )
)
Barry
Lianja
One
two

https://www.lianja.com/doc/index.php/JSON_DECODE()

---

https://www.lianja.com/doc/index.php/JSONDATASTRING()

---

JSON data and the JQL (pronounced Jackal) query language for JSON. JQL is the first database independent query language to leverage the complete flexibility of native JSON data types and the full power of SQL.

To model and query data with relationships, and SQL benefits from JSON because it removes the “impedance mismatch” between the data model and the web/mobile application model.

The JQL query language is part of the Lianja database which is embedded into the Lianja Cloud Server.

// create table

create table json_test (id char(10), data json)
create table json_test2 (id char(10), data json)

// insert

insert into json_test values ('A100000000', "{'name':'Barry', 'region':'usa','total':2500}")
insert into json_test values ('A200000000', "{'name':'Chris', 'region':'uk','total':2500}")
insert into json_test values ('A300000000', "{'name':'Yvonne', 'region':'uk','total':2500}")
insert into json_test2 values ('A100000000', "{'name':'Barry', 'region':'usa','total':2500}")
insert into json_test2 values ('A200000000', "{'name':'Chris', 'region':'uk','total':2500}")
insert into json_test2 values ('A300000000', "{'name':'Yvonne', 'region':'uk','total':2500}")

// select

select id, data.name, data.region, data.total from json_test where data.name = 'Barry'

// create index

create index data_name on json_test (data.name)
create index data_name on json_test2 (data.name)
create index data_id on json_test (data.id)
create index data_id on json_test2 (data.id)

// sorting

select id, data.name, data.region, data.total from json_test order by data.name

// primary key (PK) and foreign key (FK) access

select json_test.data.name, json_test2.data.region from json_test inner join json_test2
       on json_test.data.id=json_test2.data.id

// aggregation

select id, data.name, data.region, data.total from json_test group by data.name

// joins

select json_test.data.name, json_test2.data.region from json_test inner join json_test2
       on json_test.data.name=json_test2.data.name

// update

update json_test set data.name='Harry' where data.name='Barry'

// delete

delete from json_test where data.name='Barry'

https://www.lianja.com/doc/index.php/Working_with_JSON_and_JQL

---

Export

select employeeid, lastname, firstname;
  from employees into json emp

https://www.lianja.com/doc/index.php/Importing_and_Exporting_Data

---

JSON and OData

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.

Code:

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):

Code:

// 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:

Code:

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

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

Code:

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.

---

JSON and OData

Q:
My json file looks like this:

Code:

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

Code:

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:

Code:

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.

Code:

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

Code:

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.

Code:

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:

Code:

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:

Code:

select * from categories into object ss

Referencing is the same.

Code:

?ss.row7.categoryname  // referencing a subelement

---

---