Hi there,
I would like to not use stored procedures when it comes to delta methods. Also, I want to be able to update multiple tables, so I can’t use the Schema Modeler SQL, because it does not allow SQL scripts (EXECUTE BLOCK).
Can I run code instead of Stored Procedures / SQL / AutoSQL ?
you can create custom delta command :
by other hand, you can create custom service method (like MyUpdateData) which will handle all updates by itself.
The problem with Delta Commands is that they don’t allow updating more than one table at a time.
For example, you cannot define
InsertCommand
as being
EXECUTE BLOCK AS
BEGIN
INSERT INTO TABLE1 (FLD1, FLD2, FLD3) VALUES 1, 2, 3;
INSERT INTO TABLE2 (ID, NAME) VALUES 1, 'JOHN');
END
And the problem with services is that you can’t hook them up into DataTables.
Ex :
If I have a service that needs an
MyService.RegisterEntity(aEntityId, aName, aPassword : UTF8String);
What I can’t do with a service :
I could not use a TMemDataTable with fields
MyServiceTable
EntityIDField
EntityNameField
EntityPasswordField
So that each time I add a record into the table
{ Doing this multiple times in briefcase mode }
MyServiceTable.Insert() // Then setting field values
I could just tell
MyServiceTable.RemoteDataAdapter.ApplyUpdates;
Then the service would be called n times.
What I also can’t do is define server/client business rules for a service that will be applied both on the client and the server when interacting with the TDAMemDataTable.
In resume :
With Delta Command
With Service
With stored procedures
If it was possible to execute scripts with Delta Commands, that would be neat.
you can use this SQL as command only if DAC (= direct access component) can understand this, e.g. if ADO driver is used, then TADOQuery should support this SQL.
I don’t understand this. you can use TMemDataTable on server-side with any field name even they are declared in DB differently. this can be solving via mapping or field aliases like
select a as entityA, ...
yes, it is possible. pls read Business Rules Scripting article.
Note: it uses JavaScript.
I think I might have asked the question a little bit vaguely.
My goal is to simplify the API of the server
I have these tables :
ENTITY (ID)
PERSON (ID, BIRTH, DEATH, GENDER) (FK on ENTITY)
ORGANISATION (ID, CONSTITUTIONDATE) (FK on ENTITY)
LOCATOR (ID, ENTITYID, DATEACTIVE, DATEINACTIVE) (FK on ENTITY)
ADDRESSLOCATOR (ID, ADDRESSID, ADDRESSTYPEID) (FK on LOCATOR, FK on ADDRESS, FK on ADDRESSTYPE)
ADDRESS (ID, LINES, COUNTRY)
Inheritance
--------------------
PERSON is a ENTITY
ORGANISATION is a ENTITY
ADDRESSLOCATOR is a LOCATOR
Each time I want to add a ADDRESSLOCATOR, I also need to add a corresponding LOCATOR. ( It’s a table-per-class hierarchy). (like so Tech Journal: Image)
Similary, each time I want to add a PERSON or ORGANISATION, I have to create the corresponding ENTITY.
For now, the only way I got this to work, is to add, on the client, DAMemDataTables for each of these tables, and define the Master-Child relationships. These DAMemDataTables lie in a DataModule. My form will simply call the Insert / Post / Delete / FieldByName methods to interact with these DAMemDataTables.
To enable the client to add a new PERSON, I have to
1. DAEntityTable.Insert;
2. DAEntityTable.FieldByName ...
3. DAEntityTable.Post
4. DAPersonTable.Insert;
5. DAPersonTable.FieldByName ...
6. DAPersonTable.Post;
7. RDA.ApplyUpdates
------------ CLIENT/SERVER Boundary --------------
8. Server receives updates and apply them
But this is complicated for the client. I would prefer to handle that logic in the server, by having a single “Condensed” table. If I define a table in SchemaModeler like so
// TABLE FULLPERSONTABLE
SELECT ENTITY.ID, PERSON.BIRTH, PERSON.DEATH, PERSON.GENDER
FROM ENTITY
INNER JOIN PERSON ON PERSON.ID = ENTITY.ID
Then, on the INSERT statement somehow updating two tables :
INSERT INTO ENTITY (ID) VALUES (:ID);
INSERT INTO PERSON(ID, BIRTH, DEATH, GENDER) VALUES(:ID, :BIRTH, :DEATH, :GENDER);
Then, the client using this “Interface” would only have to :
1. DAFullPersonTable.Insert;
2. DAFullPersonTable.FieldByName ...
3. DAFullPersonTable.Post;
4. RDA.ApplyUpdate
--------- CLIENT / SERVER Boundary ----------
5. Server handles request
6. Add record in ENTITY table
7. Add record in PERSON table
When you are saying :
Scripts are supported by FireDAC’s TFDScript, but not TFDQuery for example. Is it possible to use this ?
I would really prefer avoiding stored procedures altogether.
In this case at hand, when inserting a record into FullPersonTable, I would have to insert a record in both ENTITY and PERSON table. But that can only happen
So,
yes, it is possible. you can create descendant of uDAFireDACDriver where TDAEFireDACQuery.CreateDataset, TDAEFireDACConnection.CreateCustomConnection and TDAEFireDACDriver.GetConnectionClass should be overridden and return TFDScript , TDAMyEFireDACQuery and TDAEMyFireDACConnection accordingly.
probably you need to override also TDAEFireDACQuery.DoExecute is syntax of TFDScript differ from TFDQuery.
I have attached an example for SDAC driver uDASDACDriverEx.zip (766 Bytes), your modification can be done in similar way.
it can be solved on other way, as I already said in How to call DataService from custom business processor class topic.