Data manipulation

All data manipulation in the server is done using REST technology. That means that we use the VERB of the HTTP request to tell what is going to be requested. These verbs are GET, PUT, POST and DELETE. 

GET read data

PUT updates data

POST creates data

DELETE deletes data

To do each of these operations, you have to have sufficient rights to the table(s) you are manipulating. This means you have to be logged in with a user to do anything with data or you have to change the server settings for "defaultsecurity" to a higher level than the default 8 (No level). This can be done in the server settings in rdrrXaaa.ini for the DataEase application. To login you use the username and password stored in the Users table when logging in using the authentication modules login command at /authentication/login/. The user will get the level stored in the Users table.

All data manipulation takes the query parameter jsonpretty=1 to make the response more human readable. To be a valid query you have to have at lease one valid query field or be a POST (create new data) request, if not a no query error is thrown. This is to avoid getting the first record in the table if you misspell your query or forget a ? before the query string. To get all records form the start of the table simply add a ?youidfield=*  to the url or simply add a noqueryallowed=1 after the dataease query part of the url.

.A valid url the looks like this /command/table/?dataeasequyery&others1=val1&others2=val2 etc.

DataEase query

So what is DataEase query. It is the same as you can use in DataEase for Windows to set a selection filter. So you must remember the "" around text values. You can use all operators like AND, OR, field Between val1 to val2, =, not =, >, >=, < and <=. It also understand the Address2=blank for address with no value. 

Ex. /listdata/Address?Name="B*" and regdate between 10/10/20 to 12/20/20 and Address not = blank 

Sorting

To sort your result ascending or descending you add the field name=in order/field name=in reverse to the other path of the query.

Ex. /listdata/Address?Name="a*"&Name=in order&jsonpretty=1 will return all with a name starting on a in ascending order in human readable json.

Result codes

All calls to server requesting a JSON should always return with a 200 (ok) message and a result as json. I that do not happen, you have got an internal server error (500). The result should always be a object {} containing at lease a "result" key with a value. All other data will come as sub structures in form of key value pairs where the value can be any valid json type.

result string This is always part of a message returned from the server. If all went as it should, the "result" : "ok" is sent.
- ok | all went as planned
- denied | you have no rights
- error | something went wrong. This could be your input values or something else.
error string All "result" : "error" will have a string telling what went wrong. This should be "error" : "what went wrong here"
errorincode string When dql do not compile, a snippet of the dql where the pos is pointed out is returned in this
errorno int When an error was a prism error, the error code from prism can be read from this key.
errorpos int If it was an error in a dql script, this will tell where in the code parsing stopped
message string A descriptive message of what went down. This can be when you got a denied or just what was done on a ok

A list of the most common results

Extra query parameters

astext=1 Will return the output with http header ContentType=text/text instead of text/html or application/json; charset=UTF-8
excludefields= A list of fields that not are going to be returned in the query. The items can have wildcards in them where * is any number of char and ? is exact one. Ex. hidden* will remove all fields starting on hidden and Address? will remove all fields that have Address with one extra character.
Ex. /listdata/Address?Name=B*&i&excludefields=["hidden*","notes"]
fname=in order Set sort ascending on fielname
Ex. /listdata/Address?&noqueryallowed=1&Name=in order will list all addressees sorted on Address.
fname=in reverse Set sort descending on field name
Ex. /listdata/Appointments?Done=no&DoDate=in reverse will list all appointments not done in descending order
getbody=1 return the body code of a page/dql definition as part of the result without compiling it into a bodytree Ex. /definition/dql/0000123/?&getbody=1 will return the body raw body code of the DQL with id 0000123 as part of the dql tdf definiton. The body will be defined in the key "body" in the JSON returned. The list is a JSON array.
getbodytree=1
Will return the page template structure as part of the JSON data. Ex /mydemo/editorder/?OrderNo=000222&json=1&getbodytree=1 will return a structure representing the template used for generating the output of the page
getcode=1 Will return the dql code as part of the JSON data if this is a DQL. Ex /mydemo/editorder/?OrderNo=000222&json=1&getcode=1
getrecordcount=1 Will return the record count of result by the query given as part of the returning JSON as the key 'recordcount' : value.
getdefinition=1 Returns the data definition as part of the JSON data Ex /mydemo/editorder/?OrderNo=000222&json=1&getdefinition=1 will return the tdf of the underlying data
includefields= A list of fields that will ba added to the result. Items can have wildcards in the name like Item* and Address?. The list is a JSON array.
Ex. /listdata/Address?Name=B*&includefields=["Name","*Address*","Zip","EMail"
json=1 Can be used to make a page/dql return the data set as JSON instead of the html page. Ex /mydemo/editorder/?OrderNo=000222&json=1 will make this page show the data set generated when loading the page as JSON code instead of the page merged with this data using LiveText
jsonpretty=1
When a page is returing JSON, you can use this query parameter to make it look human readable. Ex /mydemo/editorder/?OrderNo=000222&json=1&jsonpretty=1 will return the same data as in the last cell, but format it so it is humanly readable.
noqueryallowed=1 Sometimes when simulating DataEase for Windows on the web, we simply want to browse data without query data. Then you can use this setting to allow no ?field=value set in the url.
showhidden=1 Used when hidden tables or fields should be in the resulting JSON. Ex. /definition/listtables/?&showhidden=1 will list all tables in the repository including the hidden system tables.
skipvirtual=1 Will remove any virtual field from the table data before returning data. Ex /listdata/Order/?OrderNo=*&skipvirtual=1 will list all orders in the table but not any field defined as virtual

These can be used on almost any of the different pages to manipulate the data selection and output.