What's happening with ODATA?

Data Abstract
#9

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.

#10

Hi,

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

#11

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.

#12

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”.”

#13

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}
}
#14

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.

#15

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.

#16

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?

#17

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
HttpAPI + DA
#18

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.

#19

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.

#20

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?

#21
  • you can set Date/Time in a trigger of DB server.
  • you can do it manually in DAService events like OnBeforeProcessDeltas
  • create BP.OnBeforeProcessChange event and assign Now to required field. this event should be assigned to specific BP in the DAService.OnBusinessProcessorAutoCreated event
#22

Ok thanks.

Regards the streamer, I’m not entirely sure how these work. I just use a Bin2DataStreamer for all my services and it works fine so I left it alone. As this is a binary protocol, I presumed it was only used for communication from my Delphi client and that any “text based” client such as XML/JSON/REST/ODATA/etc would use something else.

Like I said, I’m not really sure how streamers work or how they affect things. I tried swapping the Bin2 for a JSON one as an experiment but functionality was identical for the ODATA calls.

#23

This is basically what I’m doing now - I have design-time BP components with OnBeforeProcessChange handlers. In these I update modified date values and also sometimes change other values in response to user changes. The problem is that, if these values weren’t supplied as part of the JSON body in the PUT call then the corresponding strongly-typed class properties don’t work, so I can’t just do “ModifiedDate := Now” or whatever.

Inside these handlers, how can I change these values?

#24

try to use DAService.OnBeforeProcessDeltas event.
here you can add a new field to delta and set it’s value in related delta changes

#25

Ok I understand what you mean but, if I’m honest, I really dislike that.

My current solution is nice and elegant. Within each BP’s BeforeProcessChange handler I can not only do validation but also set or change any field value before the change is committed to the database, including fields not set by the client, all using the robust, strongly-typed class properties.

Giving that up in favour of messing about with the deltas in the service handler just to get ODATA working isn’t something I really want to do to be honest.

#26

another solution: you can modify user’s request in HTTPServer.OnCustomResponseEvent event and add missing ModifiedDate field to request body and pass request to OData dispatcher.

see more at Using OnCustomResponseEvent in a ROSDK Server snippet.

#27

I’ve actually just tried something else which seems to work.

In the BP’s OnBeforeProcessDelta, checking if the field is present and, if not, adding it, like this:

if (aDelta.IndexOfLoggedField(‘Modified’) < 0) then
aDelta.AddFieldName(‘Modified’,datDateTime);

I can then use the existing strongly-typed class properties in the BeforeProcessChange handler as before.

It seems to work, is there any downside or problem with this approach?

#28

not yet. it should work as expected