What's happening with ODATA?

As evidenced by some other posts on the subject I’ve made recently, I’ve been looking at the HttpApi/REST/ODATA stuff recently.

Whilst I’ve got the basic REST HttpApi stuff working for basic function calls, I’m unclear on what’s happening with regards to CRUD datatable access.

It seems that this is only supported via the REST HttpApi interface in .NET and that, in Delphi, we have to use ODATA instead. Unfortunately, from what I can see on here, ODATA v1 is next to useless as pretty much everything needs v4. A post from five years ago said this was planned for 9.1 but a later one said it was a low priority. I assume v4 still isn’t supported?

It looks like Delphi has been forgotten here. I’m being asked to provide a REST API to our software to allow not only discrete function calls but also CRUD access for data and it looks like I can’t. I could go the ODATA route but is there any point if it’s not going to be compatible with most other systems?

1 Like

Hi,

You are right.

We are reviewing v4 format support.
HttpAPI is more suited for Remoting SDK service calls. To implement a CRUD interface using it one would need to explicitly implement methods for all operations

I’ve been trying to work out what the “correct” or “recommended” way of implementing CRUD operations via REST is.

With .NET you can use HttpApi but with Delphi it’s ODATA. Both appear to do much the same thing so I’m not sure what the differences are or why they’re implemented differently on each platform.

Hi,

both technologies (OData and HttpApi) in Delphi were ported from .NET so both platforms have the same functionality.

Well I was looking at this topic:

Which seems to suggest that you use HttpApi for .NET but OData for Delphi. Are you saying that the HttpApi avenue also works on Delphi?

FWIW, in .NET, we use a combination of RestSchemaDispatcher and HttpApi and it works pretty good.
We use RestSchemaDispatcher to publish datatables through /rest/[Schema]/[DataTable] and
use the basic HTTP-verbs: GET, POST, PUT, DELETE to interact with the datatables.
No other code needed and the businessprocessor logic is honored.
For other api’s like token generation we publish methods through HttpApi.

That’s exactly what I want to do with Delphi but it doesn’t seem possible as it lacks the RestSchemaDispatcher.

Hi,

Can you give to me examples that doesn’t work with ODATA (Delphi) but works with REST (.NET)?

as I see, almost all commands are supported: DataAbstract and new HttpApi

This is where I’m getting confused.

So Delphi has the ODATA dispatcher instead of the REST dispatcher in .NET but basically the two do the same thing? If so, howcome the same functionality was implemented differently between the two platforms?

I’m experimenting with the ODATA now, have managed to retrieve records in JSON format (the clientid header trick from the other topic worked for authentication) but having some strange null conversion errors when trying to do a PUT update right now. I’ll continue to experiment.

Hi,

can you check ODataServer (Delphi).zip sample and related http commands from this post?

Yeah I’m following the same pattern but I can’t work out how the PUT works to update a record - if I omit some columns then it complains they’re required, although this seems to have no correlation with which columns in the database are required and, if I put all the fields into the body then it comes up with a null conversion error.

I think I’ll spin up a small dedicated test app first using the wizard and get that working.

I’m following the example as closely as possible but I can’t get the PUT to work.

GET works fine with this URL:

http://127.0.0.1:8099/odata/dbo_Company(1)?$format=json

This works fine. Company is a single table from my database which has an integer primary key so this returns the columns for the record with ID=1.

If I supply the JSON returned from this as the body to a PUT with the same URL, I get this error:

“Cannot find item “dbo_Company” in collection of type TDAServerDatasetCollection”

If I trap the OnProcessTargetUrl event, I notice both the TargetUrl and TableName have “dbo_Company(1)” in them, is that correct or should it just be the table name? If I remove the (1) from the URL so it’s just:

http://127.0.0.1:8099/odata/dbo_Company?$format=json

Then, predictably, I get:

“The record specified was not found in “dbo_Company”.”

Can you prepare a simple testcase with commands you are using, pls?


it should be like

PUT http://localhost:8099/odata/Customers(’{11111111-1111-1111-1111-111111111111}’)?$format=json

body:

{"d":{
   "__metadata": {
     "uri": "http://localhost:8099/odata/Customers",
     "type": "ODataService.Customers"
   }, 
"Id":"{11111111-1111-1111-1111-111111111111}","Name":"Updated Value","Phone":"(5) 555-4729","Address":"c6be7eec-6a39-48c1-b2c5-5b2c1edbff51","Remarks":"Set on server at 26.10.2017 14:03:37","Discount":0}
}

Yeah that’s exactly what I have, except my table name is different obviously and I’m using an integer primary key rather than a GUID.

It was the dbo_ prefix.

As I’m using MSSQL, the wizard pulls the tables in with the “dbo.” owner prefix on the schema names - removing this sorted it.

Ok, it works in an isolated test project but I get the conversion exception error when I try it in my main app.

Turns out it’s the business processor logic during the update. I’m using strongly-typed classes and it turns out the Old… values appear to be NULL. So I have some logic running that checks if the update is altering the Name column, i.e. “if OldName <> Name then…” and this is throwing an exception.

Is this correct behaviour? In other words, when using OData, will the “old” values in the delta be unavailable?

Hi,

you can omit old values:

  • getting current record:

curl -verbose -X GET “http://localhost:8099/odata/Customers(’{ab793bb2-574c-4080-a2cf-f5a63473a8a7}’)/?$format=json

log
Note: Unnecessary use of -X or --request, GET is already inferred.
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8099 (#0)
> GET /odata/Customers('{ab793bb2-574c-4080-a2cf-f5a63473a8a7}')/?$format=json HTTP/1.1
> Host: localhost:8099
> User-Agent: curl/7.56.0
> Accept: */*
> Referer: rbose
>
< HTTP/1.1 200 OK
< Connection: close
< Content-Type: application/json
< Content-Length: 285
< Date: Tue, 28 Apr 2020 11:05:41 GMT
< Accept-Encoding: gzip, identity
< DataServiceVersion: 1.0;
<
{"d":{"__metadata":{"uri":"http:\/\/localhost:8099\/odata\/Customers('{ab793bb2-574c-4080-a2cf-f5a63473a8a7}')","type":"ODataService.Customers"},"Id":"{ab793bb2-574c-4080-a2cf-f5a63473a8a7}","Name":"Updated Value 10","Phone":"0251-031259","Address":null,"Remarks":null,"Discount":"0"}}
* Closing connection 0
  • updating Name

curl -verbose -X PUT “http://localhost:8099/odata/Customers(’{ab793bb2-574c-4080-a2cf-f5a63473a8a7}’)/?$format=json” -d “{“d”:{”__metadata": {“uri”:“http://localhost:8099/odata
/Customers”,“type”:“ODataService.Customers”},“Name”:“Updated Value 100”}}"

log
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8099 (#0)
> PUT /odata/Customers('{ab793bb2-574c-4080-a2cf-f5a63473a8a7}')/?$format=json HTTP/1.1
> Host: localhost:8099
> User-Agent: curl/7.56.0
> Accept: */*
> Referer: rbose
> Content-Length: 174
> Content-Type: application/x-www-form-urlencoded
>

* upload completely sent off: 128 out of 128 bytes
< HTTP/1.1 204 No Content
< Connection: close
< Content-Type: text/html; charset=ISO-8859-1
< Content-Length: 0
< Date: Tue, 28 Apr 2020 11:05:41 GMT
< Accept-Encoding: gzip, identity
< DataServiceVersion: 1.0;
<
* Closing connection 0
  • checking if change was applied:

curl -verbose -X GET “http://localhost:8099/odata/Customers(’{ab793bb2-574c-4080-a2cf-f5a63473a8a7}’)/?$format=json

log
Note: Unnecessary use of -X or --request, GET is already inferred.
*   Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to localhost (127.0.0.1) port 8099 (#0)
> GET /odata/Customers('{ab793bb2-574c-4080-a2cf-f5a63473a8a7}')/?$format=json HTTP/1.1
> Host: localhost:8099
> User-Agent: curl/7.56.0
> Accept: */*
> Referer: rbose
>
< HTTP/1.1 200 OK
< Connection: close
< Content-Type: application/json
< Content-Length: 286
< Date: Tue, 28 Apr 2020 11:05:41 GMT
< Accept-Encoding: gzip, identity
< DataServiceVersion: 1.0;
<
{"d":{"__metadata":{"uri":"http:\/\/localhost:8099\/odata\/Customers('{ab793bb2-574c-4080-a2cf-f5a63473a8a7}')","type":"ODataService.Customers"},"Id":"{ab793bb2-574c-4080-a2cf-f5a63473a8a7}","Name":"Updated Value 100","Phone":"0251-031259","Address":null,"Remarks":null,"Discount":"0"}}* Closing connection 0

Ok so, when using JSON, the old values aren’t available. Guess that makes sense as, with my regular Delphi client, it’s the TDAMemDataTable that tracks changes in the delta and then sends it, complete with the old values, to the server. Obviously the old values aren’t supplied as part of the JSON payload.

It did occur to me how deletes work as, in this case, the primary key is held in the Old values, OldId in my case, but this value does appear to be present so I can use it for deletes.

It is a bit of a pain as some of my business logic depends on knowing what columns have actually changed, as described above, and I can’t do this if the old values aren’t present.

Deleting works similar:

DELETE http://localhost:8099/odata/Customers('{11111111-1111-1111-1111-111111111111}')?$format=json

if you use TDAJSONDataStreamer, it generates stream with all (new and old) values.

Think I’m getting the hang of it now.

(Just seen your update about the streamer, I’ll try that but the rest of this was written before that)

So basically, when updating via a PUT, the JSON body only needs to contain the values I want to change, so the presence of the value indicates that it’s being updated. I can alter my business logic to cater for that.

One quick question though. Many of my business logic processors update the modified date to the current server time as part of the BeforeProcessChange handler, thus ensuring it’s always modified. If the client didn’t supply this column as part of the JSON then the value isn’t present so I can’t just do “ModifiedDate := Now” in my code. Is there a way I can set/add a column which wasn’t supplied?