Auto Refresh Server Calculated Column

Data Abstract
1

Hi,

I have an MS SQL Server computed column, called REQUEST_NUMBER. In other words, it is calculated on the server. How do I get it to automatically refresh on the client, when I insert a new row? I have it checked as Server Calculated and Read Only, in the Schema Modeler. Log Changes = false. The value of the IDENTITY column refreshes; however, REQUEST_NUMBER does not. If I set Server Autorefresh = true, I get an error: “Cannot find a table field called ‘REQUEST_NUMBER’ in the mappings collection.” I get a list of columns, with their corresponding values, and that column is not listed. If I reload the row, it displays correctly.

Thanks,

David

1 Like
2

Hello,
You should just set ServerAutoRefresh to true for this field.
The following articles will be helpful for you:

http://wiki.remobjects.com/wiki/Advanced_options_for_controlling_data_updates_(Delphi)
http://wiki.remobjects.com/wiki/Updates_(Data_Abstract)

If you want to refresh this field manually on the server side please use TDABusinessProcessor component:

procedure TDataService.DABusinessProcessor1BeforeProcessChange(
  Sender: TDABusinessProcessor; aChangeType: TDAChangeType;
  aChange: TDADeltaChange; var ProcessChange: Boolean);
begin
  if (aChangeType = ctInsert) then
    aChange.NewValueByName['Phone'] := '';
end;

procedure TDataService.DABusinessProcessor1AfterProcessChange(
  Sender: TDABusinessProcessor; aChange: TDADeltaChange; Processed: Boolean;
  var CanRemoveFromDelta: Boolean);
begin
  if Processed then
    CanRemoveFromDelta := False;
end;
3

Hi,

How can I resolve the error that I get about the column not in the mappings collection, when I set ServerAutoRefresh = true?

Some notes:
I am using RelativityServer
ServerCalculated = true
ruoOnPost = true
I tried ReadOnly = false and = true.
I tried LogChanges = false and = true.

I assume that ServerAutoRefresh causes the column to not appear in the Delta.

4

Hello,
Can you send us your testcase? We will check it and send you fixed.
Also add ddl for your database objects.

5

Hi,

I submitted a test case, last week. I just wanted to make sure that it was received.

Thanks,

David

6

Hi,

Vladimir answered you the 14 of October, just in case I re-sent it.

7

Hi,

I got a test case back that uses RS, yesterday. I see that it uses a TDASpiderMonkeyScriptProvider component. Is this a requirement for Server Autorefresh? If I set the TDAMemDataTable.ScriptingProvider to the TDASpiderMonkeyScriptProvider and the TDASpiderMonkeyScriptProvider.ScriptableComponent to the TDAMemDataTable, when I call TDAMemDataTable.Insert, I get an access violation.

I installed RemObjects+Data+Abstract+for+Delphi±+7.0.71.1097.exe. I removed all of the default values from the schema and updated the TDAMemDataTable.Fields. I still get the access violation and I only get the access violation, if I use TDASpiderMonkeyScriptProvider.

Is there a property that is needs to be set for TDASpiderMonkeyScriptProvider to work correctly? May TDASpiderMonkeyScriptProvider have an issue with MS SQL specific datatypes, like DATE or BIT? I changed all of my SMALLMONEY types to MONEY. I am populating the TDAMemDataTable with DA SQL. However, I do not get an access violation until I call Insert.

Thanks,

David

8

I have the same issues when using server auto refresh.
Also get AV when using ScriptProvider.
I’m using Delphi XE2, UniDac provider with firebird 2.5.2.
Where I can find example how to use this two features?

Thanks,
Dejan

9

Just to clarify for everyone, I am using:

Delphi XE4
MS SQL Server 2012 Express Edition
MS SQL Server Native Client
Relativity Server
I am populating TDAMemDataTable with a SQL statement, using TableRequestInfoV6.
The TDAMemDataTable contains LookupFields.

I received two, test cases back: one uses a custom DA server, the other one uses RS. Both test cases seem to work okay. I just removed the ScriptingProvider removed and Server Autorefresh worked. I tried with ruoOnPost true and false, and it worked.

In my application, if I use the ScriptingProvider, I get an access violation. If I call TDAMemData.RefreshFromServer, the values are refreshed.

I did not notice any property difference between my components and the test case components, but I am going to go through them, one-by-one. The column that I want to Autorefresh is checked: Log Changes, Read Only, Server Autorefresh, which matches the schema that I got back in working the test case. However, my app consistently produces a conflict dialog that the specified column is not in the mapping collection.

10

Server Calculated Column shouldn’t have LogChanges

11

Hello,
Can you send us a small testcase which reproduces AV? We will test it.

12

Hi,

I went back to my original test case to add the ScriptingProvider. It did not reproduce the access violation. I did some more work with that test case. I discovered that my AutoRefresh column has to be selected as LogChanges or I get an error that the column is not in the delta. I can set it to Server AutoRefresh = true, Log Changes = true, and Read Only = true; and it works. In both cases, I did not get the access violation with the scripting provider. This column is a DATETIME column that is set by the server, with a default constraint, and is not edited by the client.

I went back to my main application and copied the same column settings for my autorefresh column. I get a “Details for Change…” dialog, with an error: “Cannot find a table field called ‘REQUEST_NUMBER’ in the mappings collection.” Request # is an MS SQL Server computed column. It is a VARCHAR column that is computed with a user defined function.

I am giving these extra details because both columns were set to the same Schema Modeler Flags of: Server AutoRefresh, LogChanges, and ReadOnly. Those properties were imported from the Schema into the TDAMemDataTables. They are different data types, but have exactly the same Flags.

The TDAMemDataTable for my main application returns 82 columns. The only change that causes the “mappings collection” error is changing REQUEST_NUMBER from Server AutoRefresh = false to Server AutoRefresh = true. I have noticed that if a TDAMemDataTable contains a LookupField and the LookupSource is not active, I will get an error. I have also had issues with DA and MS SQL Server specific data types, such as DATE and SMALLMONEY.

Two more differences between the test case and the actual app. 1) The test case operates directly against the table and the app uses an updateable view. However, I believe that I had the same problem in my app, with using the table. 2) The test case just opens the TDAMemDataTable and the app uses a DA SQL with TableRequestInfoV6.

If test cases can work with Server AutoRefresh and ScriptingProvider, it makes me wonder if the problem is not cause by something else in the schema, but may be set off by the Server AutoRefresh on this particular column.

I am going to keep digging into this and trying to fix or reproduce the errors. However, I may not be looking in the right place, if it is something else in the schema. I am not really sure where to look or what may cause problems for DA.

Thanks,

David

13

Hello,
Please try to update mapping after changing ServerAutoRefresh.
Please look the example with using ServerAutoRefresh:
ServerAutoRefresh.zip (169.9 KB)

14

Hi,

That technique seems to work. However, I still have to check “Log Changes” or I get an error that the field cannot be found in the mappings collection. “Log Changes” doesn’t apply to any column that is Read Only or Server Calculated; however, I can check it.

Just to clarify for anyone else experiencing this problem:

  1. Check the Schema Modeler flags for the corresponding fields: Server AutoRefresh and Log Changes. It does not appear to work if you do not check Log Changes, whether the column should be updateable or not.

  2. Still in the Schema Modeler, go to Table Fields, and click “Update Fields.”

  3. Right click on the TDAMemDataTable and click “Retrieve DataTable Schema.”

At this point, Server AutoRefresh columns should refresh without error.

Thanks,

David

I have to make an addendum to my post. It works if Log Changes = true and Server Calculated = false. If either Log Changes = false or Server Calculated = true, I get an error that the field cannot be found in the mappings collection. Perhaps, I just do not understand how DA uses Server Calculated and what it expects.

15

ServerCalculated fields is used when you want pass value that isn’t present in DB to client. this value can be set in DataStreamer events ( OnBeforeFieldValueSerialization, OnWriteFieldValue, OnWriteFieldValueEx). it is very similar if you have sql like

SELECT fld, 0 as ServerCalculated FROM table

of course, this field isn’t present in DB so it should have LogChanges = False

16

Hi,

If I understand, correctly, ServerCalculated means that it is calculated by RS or a DA server, not by SQL Server.

Thanks,

David

17

Yes, you are right.