Difference between revisions of "OData URIs"
Yvonne.milne (Talk | contribs) (→See Also) |
Yvonne.milne (Talk | contribs) |
||
| Line 131: | Line 131: | ||
[[Category:OData Functions]] | [[Category:OData Functions]] | ||
[[Category:Row Level Security]] | [[Category:Row Level Security]] | ||
| + | [[Category:Lianja Cloud Server]] | ||
Revision as of 11:04, 12 October 2020
Contents
See Also
OData Operators, ODATA_CREATE(), ODATA_DELETE(), ODATA_READ(), ODATA_UPDATE(), Working with JSON and JQL
OData URIs
Lianja Cloud Server
When used with the Lianja Cloud Server, the URI is formed as follows:
<server>/odata/<database>/<table>?<arg1>&<arg2>&<arg3>...
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:
<server>/odata/<database>?$sql=<sql statement>
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:
<server>/odata?$eval=<function-call>
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:
/<database>/<table>?<arg1>&<arg2>&<arg3>...
e.g.
odata_read("/southwind/customers?$skip=10&$top=5")
Exceptions
When using the $sql argument, the URI does not include a table reference:
/<database>?$sql=<sql statement>
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=<function-call>
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")
Arguments
The following URI arguments are available:
| Argument | Description | Example |
|---|---|---|
| $app | Used in conjunction with $eval to specify the app in which the function is located. | $eval=fetchcustomer("ALFKI")&$app=myapp |
| $args | Creates public variables containing the specified values. The first value is assigned to a public variable named m_arg1, the second to m_arg2 and so on. This is particularly used for virtual tables based on local stored procedures. | $args=ALFKI,4 |
| $callback | Specify a callback function that will wrap the data returned. This is used with JSONP calls to bypass SOP issues in the browser |
$callback=your_javascript_function |
| $count | Don't return any data just tell me how many records would have been selected | $count |
| $database | Used in conjunction with $eval to specify the database in which the stored procedure is located. | $eval=sp_fetchcustomer("ALFKI")&$database=southwind |
| $eval | Return the result from a call to a function or stored procedure. The $app or $database arguments can be included to specify the location of the function or stored procedure. | $eval=time() $eval=southwind!sp_fetchcustomer('ALFKI') $eval=sp_fetchcustomer("ALFKI")&$database=southwind $eval=fetchcustomer("ALFKI")&$app=myapp $eval=mylibrary::fetchcustomer("ALFKI") |
| $filter | Selects only those records that match the specified filter | $filter=contactname eq 'A' and amount gt 0 |
| $format | The output format | $format=ado $format=attachment $format=csv $format=excel $format=html $format=img $format=json $format=jsonarray $format=jsongrid |
| $metadata | Returns metadata describing the columns in the table; name, type, width and decimals. | $metadata |
| $nocount | Don't return the __count member which details the total number of rows that match the query. This improves performance on Virtual Tables |
$nocount |
| $nostrcompare | Disable exact matching. Comparisons are carried out up to the length of the left-hand expression, e.g. 'A' = 'ALFKI'. | $nostrcompare |
| $orderby | Specify a column or expression that the data should be ordered by | $orderby=contactname |
| $rowfilter | Selects only those records that match the specified row filter | $rowfilter=country eq 'USA' |
| $rowid | Add the unique rowid into the output ( __rowid ) which can be used to update data later | $rowid |
| $select | Selects only the specified columns (or expressions) | $select=customerid,contactname |
| $skip | Skips a number of selected records | $skip=50 |
| $sql | Evaluate a SQL SELECT statement directly and return the results. | $sql=select * from customers |
| $top | Selects the specified number of records | $top=10 |
