Im using SM V 6, dont like V7 but if that options can be added to V 7 is ok for me.
Hi Donald,
hm, its interesting for what scenarios you need that?
And to say the truth, at the moment I don’t see the way how to do that without impact on all plaforms of the product.
In contrast to statements for schema tables, where we can specify TargetTable and thus use autoSQL option for generation univocal SQL statement, statements of the schema commands doesn’t have TargetTable and even if they had it - there are at least 3 different SQL possible (Update, Delete and Insert).
Also there could be a problem with the parameters which provides values for delta commands. At the moment, when you are creating any delta-command then its SQL with the proper parameters are automatically generated. You can see them and you can change them, give the new unique names for example. But in case when you’ll try to have that autogenerated you need to know the list of available parameters with their names to provide values for them properly in the runtime.
So for adding AutoSQL option to the schema commands we need to introduce there at least 2 additional properties “TargetTable” and “CommandKind” (with available values: Insert, Update, Delete, Custom ) and some kind of “name convention” for parameters to provide values in a proper way.
IMHO all this looks quite overcomplicated especially in comparison to current way when SQL for delta commands automatically generated without any hand-work.
But if you have a scenario where such efforts will be justified then we could to try to do something here
Thanks!
Alex,
When you use commands at server side, you need it defined at Schema.
I use a lot of commands at server side. Is the only reason i find to generate commands, if i will not use at server side i just dont generate and let DA manage update, inserts and deletes.
When i generate by hand, if i change the table estructure, and by example, i add a field, i have to touch by hand the associated commands.
As I understand you have to add only one property to point if is a AutoSql or Sql hand coded, but i think that can be assumed rigth now from the actual values.
Rigth now theres a hidden property to associate commands and datatables inside DA, that is the association used with the filter and allow us to see ONLY the command associated to a table, rigth? Then in some place already exists a TargetTable, or dont?
Off course seems more complicated than the actual way, but need a lot less mantainance in the situation i mentioned. And dont have any impact to users dont using that features, when they generated new sql commands i believe the defaullt way must be autosql.
I hope i explain well, let me know if dont.
Best regards.
Hi Donald,
When i generate by hand, if i change the table estructure, and by example, i add a field, i have to touch by hand the associated commands.
Right, when you are extending the database table with new fields, you need to adjust database stored proceduces which work with that table.
And you will need to update schema table - in order to catch up new database table fields to the schema table.
And also you need to recreate delta-commands for such table to have it in sync.
Rigth now theres a hidden property to associate commands and datatables inside DA, that is the association used with the filter and allow us to see ONLY the command associated to a table, rigth?
No, at the moment there is no strict relation between schema table and schema delta-command. Old DASM just filter related commands by name. Namely by template Insert_%table%> or Update_%table% so on.
Then in some place already exists a TargetTable, or dont?
nope, commands doesn’t have TargetTable, sorry.
Off course seems more complicated than the actual way, but need a lot less mantainance in the situation i mentioned. And dont have any impact to users dont using that features, when they generated new sql commands i believe the defaullt way must be autosql.
Look, even when we will add TargetTable property there to store target database table, and add there property to specify the type of the command (insert, update, delete or custom), there still will be uncertainty in the available parameters, their names, types and size.
At the same time to simplify handling your scenario what do you think about following?
At the moment you can specify what commands should be used for making modifications for your schema table by setting them as the the schema table properties (see pic.1). I could try to add there additional action to refresh delta-commands that binded to given schema table. Thus when you changed schema table you can run this action and it will create (or recreate if already exists) proper delta-commands
I have to paste text, dont know why but dont work quote here (linux + chrome).
“Right, when you are extending the database table with new fields, you need to adjust database stored proceduces which work with that table.
And you will need to update schema table - in order to catch up new database table fields to the schema table.
And also you need to recreate delta-commands for such table to have it in sync.”
Actually not always. for that reason theres AutoSql property. On that tables i dont have to touch Sql code, just update parameters.
“At the moment you can specify what commands should be used for making modifications for your schema table by setting them as the the schema table properties (see pic.1). I could try to add there additional action to refresh delta-commands that binded to given schema table. Thus when you changed schema table you can run this action and it will create (or recreate if already exists) proper delta-commands”
Ok, that can works. I have to admit still not get why you cant use AutoSql on that commands, like you dop with tables and autosql. That is the only thing i ask for.
Best regards.
Oh Donald, wait a bit, I’m sorry but I missed the main point!
stAutoSQL does mean that you want to use very basic autogenerated update SQL based on given database table, right?
But this is the exact same thing as in case when you didn’t set any delta-command for given table!
When you did not set any specific delta-command for given table, then during processing its delta - proper insert/update/delete sql will be automatically generated basing on the structure of the schema table (but using its mapping of course).
So for example you have table Worker with fields id and name. If you didn’t specify any delta-command for it, then insert change will automatically generate and execute SQL like “INSERT Worker(id, name) VALUES (:id, :name)”
Thus we need to use specific delta-commands only when we need to perform custom update query. Otherwise, just do not specify delta-command.
Alex,
In understand i have not need to generate delta commands to wokr on client side
But the scenario is, i have a lot of commands running on server side, with code like this:
lCmd := ServerDataModule.Schema.NewCommand(lConnectionAux,
‘Insert_Tarjetas’, [‘IDTARJETA’, ‘CODIGO’, ‘HABILITADA’,],
[MyGenUUID, Terminal.StrRecibido, ‘Si’, ‘No’]);
And command Insert_Tarjetas is autogenerated inside the schema by hand. What im asking for is this command to be defined as AutoSqland , as you say, l automatically generate and execute when i run this code. Just will need to change the source code to add/remove params.
Best regards.
Hi Donald,
I’m sorry for belated answer.
I think in this scenario, you even don’t need to generate such default delta-commands.
Since this is the Delphi code in subject i’ll pass this issue to my collegue Eugene and ask him to provide you with the source code for method which will generate default delta command for given schema table name.
You will be able to write something like following and thus you will not need to change schema at all
lCmd := ServerDataModule.GetUpdateCommand(lConnectionAux,
'Tarjetas', {Name of the schema table}
cmdTypeInsert, {Type of the update}
['IDTARJETA', 'CODIGO', 'HABILITADA',],
[MyGenUUID, Terminal.StrRecibido, 'Si', 'No']);
Alex,
That is really cool. Thanks for give me a solution.
Best regards.
Why you don’t use autogeneration of delta commands from BusinessProcessor? Simple example of it in the Old\Dynamic SQL sample. In this example, delta is passed into BusinessProcessor.ProcessDelta which autogenerates of delta commands and executes them. Additional events like OnGenerateSQL, BeforeExecuteCommand etc allows to control this process.
Evgeny,
I need it at server side.
BusinessProcessor is a server-side component (see more info at http://wiki.remobjects.com/wiki/Business_Processors ).
How things work: client sends delta(s) with changes to server. DataAbstract Service unpacks delta(s) from binary and passes it into BusinessProcessor (DA Service creates BP automatically in case it isn’t dropped to _impl). depending on BP settings, it can generate delta commads automatically (similar to stAutoSQL) or use existing ones in schema.
as I understand, you need autogenerated commands. it is can be set via BP.ProcessorOptions ( http://wiki.remobjects.com/wiki/TDABusinessProcessor_Class#ProcessorOptions ) property.
The typical usage of autogenerated delta command - call .ApplyUpdate on client-side. it will pack delta changes into binary and calls UpdateData (http://wiki.remobjects.com/wiki/TDataAbstractService_Class#UpdateData ) on server-side.
More complicated example - when changes are already present on server-side, as in the Old\Dynamic SQL sample:
function TDynSQLService.MyUpdateData(const aTableName: AnsiString; const Delta: Binary): Binary;
..
// recreate IDADelta object & column mapping
realDelta.LogicalName:= aTableName;
with Schema.Datasets.DatasetByName(BusinessProcessor.ReferencedDataset),Statements[0] do begin
TargetTable:=aTableName;
ColumnMappings.Clear;
Fields.Clear;
for i:=0 to realdelta.LoggedFieldCount-1 do begin
Fields.Add(realdelta.LoggedFieldNames[i],realdelta.LoggedFieldTypes[i]);
with ColumnMappings.Add do begin
DatasetField:= realdelta.LoggedFieldNames[i];
TableField:= realdelta.LoggedFieldNames[i];
SQLOrigin:= realdelta.LoggedFieldNames[i];
end;
end;
end;
BusinessProcessor.ProcessDelta(conn,realdelta);
..
end;
Evgeny,
The problem here is theres no “client sending delta”. That i need is a Server side command, no client action, just server action.
the code also uses BP.ProcessDelta
procedure TMyService.MyUpdate(
const aTableName: AnsiString;
aChangeType: TDAChangeType;
aParams: array of string;
aOldValues, aNewValues: array of variant);
var
lBP : TDABusinessProcessor;
lD: TDADataset;
lDelta:IDADelta;
ls: TDADeltaChange;
i: Integer;
begin
lBP := TDABusinessProcessor.Create(nil);
lDelta := NewDelta(aTableName);
try
lBP.Schema := ServiceSchema;
lBP.ReferencedDataset := aTableName;
lD:= ServiceSchema.Datasets.DatasetByName(aTableName);
if not assigned(lD) then raise Exception.CreateFmt('%s table isn''t found in schema',[aTableName]);
//recreating fields in delta
for I := 0 to ld.Fields.Count-1 do begin
lDelta.AddFieldName(lD.Fields[i].Name,lD.Fields[i].DataType);
if lD.Fields[i].InPrimaryKey then lDelta.AddKeyFieldName(lD.Fields[i].Name);
end;
ls := TDADeltaChange.Create(lDelta.GetDelta,lDelta.CurrentChangeID,aChangeType);
//assigning values
for I := 0 to High(aParams) do begin
ls.OldValueByName[aParams[i]]:=aOldValues[i];
ls.NewValueByName[aParams[i]]:=aNewValues[i];
end;
lDelta.Add(ls);
lBP.ProcessDelta(Connection, lDelta);
finally
lBP.Free;
lDelta := nil;
end;
end;
usage:
MyUpdate('Customers', ctInsert,
['CustomerID','CompanyName'],
[null,null],
['AAAAA','company']);
Thanks!
Can i suggest a better code to handle updates and no need to pass all the fields, only the fields you plan to update?
procedure TMyService.MyUpdate(
const aTableName: AnsiString;
aChangeType: TDAChangeType;
aParams: array of string;
aOldValues, aNewValues: array of variant);
var
lBP : TDABusinessProcessor;
lD : TDADataset;
lDelta : UDADelta.IDADelta;
ls : TDADeltaChange;
i, j : Integer;
begin
lBP := TDABusinessProcessor.Create(nil);
lDelta := NewDelta(aTableName);
try
lBP.Schema := Schema;
lBP.ReferencedDataset := aTableName;
lD:= lBP.Schema.Datasets.DatasetByName(aTableName);
if not assigned(lD) then
raise Exception.Create(aTableName+' tabla no existe en schema');
//recreating fields in delta
if aChangeType = ctUpdate then
begin
for I := 0 to High(aParams) do
begin
/// 28/11/2014 12:04 G.G. -> Busco campo asi obtengo el tipo
for j := 0 to ld.Fields.Count-1 do
begin
if (lD.Fields[j].Name = aParams[i]) then
begin
lDelta.AddFieldName(lD.Fields[j].Name,lD.Fields[j].DataType);
if lD.Fields[j].InPrimaryKey then lDelta.AddKeyFieldName(lD.Fields[j].Name);
end;
end;
end;
end
else
begin
for I := 0 to ld.Fields.Count-1 do
begin
lDelta.AddFieldName(lD.Fields[i].Name,lD.Fields[i].DataType);
if lD.Fields[i].InPrimaryKey then lDelta.AddKeyFieldName(lD.Fields[i].Name);
end;
end;
ls := TDADeltaChange.Create(lDelta.GetDelta,lDelta.CurrentChangeID,aChangeType);
//assigning values
for I := 0 to High(aParams) do
begin
ls.OldValueByName[aParams[i]]:=aOldValues[i];
ls.NewValueByName[aParams[i]]:=aNewValues[i];
end;
lDelta.Add(ls);
lBP.ProcessDelta(myConnection, lDelta);
except
on e: exception do
begin
RegistrarLogServer('MyUpdate '+e.Message);
end;
end;
lBP.Free;
end;Look at Reduced Delta feature - it allows to send only changed values…
You say is better to fill all the fields, some with nulls values and enable reduced delta?
Reduced delta will not send fields with null values? What do if i want to send a null value to a field then?
Reduced delta allows to skip non-changed fields. it can have sense if your table has a lot of fields (e.g. 20+) but you need to update only 1 field.
in this case, only pk+this field will be sent to server.
if old value is not null, but new is null, this field will be included to change and will be sent to server.
You can try to use reduced delta and detect, suitable it for your project or not.
Thanks, i will try.