Update Joined Table

Relativity Server
1

I am at my wits end :-(.

I have a datatable in a schema whose sql statement is a join. I would want to modify each of the tables independently as can be done via delphis update object, etc. I have tried all settings and the only way it can be update is if a [target table] is specified the statements tab for the data table. Doing this will not suffice as i can no longer modify each of the tables in the join. Using an updateable view is ruled out as it requires that a simple sql join has to be handled in the DB.

Therefore, Is it possible to update a joined table from the client using relativity server.

Specs:
– Win7
– XE2
– DA 6.0.53.935
– Firebird 2.5

Kind regards.

2

You can add business processor component and define custom command for update http://wiki.remobjects.com/wiki/Commands_Pane_(Schema_Modeler) . You can read more about business processor in our wiki http://wiki.remobjects.com/wiki/Business_Processors_(.NET_and_Delphi) , http://wiki.remobjects.com/wiki/The_Business_Processor_in_Depth_(Delphi) custom command are described in “ProcessorOptions” section.

3

I am using Relativity Server at the back end and cannot/dont know how to add a business processor. I have defined custom commands for update and even assigned the command to the datatables updatecommandname, but no dice.

I would really like to know if this is possible.

Kind regards.

4

Hello.

Using commands you can define your own statement and this statement will be used when datatable is updating. To use commands please do the next steps:

  1. Open Schema file in Schema Modeler.
  2. Create new command using New Command item on Commands panel. Create new statement for it using NewStatement item. Set Connection property. Set SQL statement for it, for example:

UPDATE [Orders]
SET
[OrderDate] = :OrderDate,
[OrderStatus] = :OrderStatus,
[CustomerId] = :CustomerId,
[EmployeeId] = :EmployeeId
WHERE
([Id] = :OLD_Id)

Save the statement. Recreate parameters for it.
3. Choose DataTable you need. Choose created update command in UpdateCommandName property for DataTable. Please note, that all parameters, presented in update command statement should be defined in SELECT statement for data table.
4. Save changes in schema.
5. On the client side you may work with dataset as usually. For example, to update datatable you may use:
fDataModule.DataAdapter.Update(newDataset,new string[1] {“TableName”});

Doing the steps above you will allow to work not with whole resultset but with separate data table in this resultset.

Hope this helps.

5

Hi Andreyt,

I tried it exactly as you have said, but with 1 difference. Do you think that is the issue

*** I didn’t put the table name in square [] brackets. ***
In all the various permutations i did, the error was always ----
… table name not found …

I have since abandoned using Relativity server and am coding my own server, since I also use Hydra. Will try a test case and report back on it.

Regards.

6

Hello.

Sorry for the belated response.

*** I didn’t put the table name in square brackets. ***
It shouldn’t be the cause of the problem. This exception message appears when client tries to use a datatable that is not presented in the Schema. For example, if client tries to update data table “Orders”
fDataModule.DataAdapter.Update(newDataset,new string[1] {“Orders”});
but server Schema doesn’t contain such a table or it’s name not “Orders”, but for example “Orders1”, then appropriate exception message will be shown. Another case, if you put the table name in square brackets on the client side. For example:
fDataModule.DataAdapter.Update(newDataset,new string[1] {“[Orders]”});
In this case you need to put the table name without brackets.
Check these situations please.

If it isn’t the cause of the problem please send us stacktrace and small testcase with the problem to investigate it in details.

Hope this helps.