ApplyUpdate in designtime

[marquessbr]

if a component TZQuery created at designtime and another component TZQuery (QRY from function listed below) created at runtime, they have your property CachedUpdates set up to true, if I insert a register via QRY (QRY cahedupdates's=true) at runtime, call "Free" for QRY and call ApplyUpdates for TZQuery component´s will save the register inserted via QRY in my database?

function PreencheCamposNaTabela(Clause, Tabela, GenID: string; Campos: array of string; Chave: string;
Var Valores: array of variant; IdResult: integer): string;
var
Qry : TZQuery;
cgo, ccs,
cvl, chv, gni: string;
i: integer;
begin
gni := IntToStr(DMZ.GetIncrementValue(AnsiUpperCase(GenID), 1));
chv := '(' + AnsiUpperCase(Chave) + ' = ' + gni;
if AnsiUpperCase(Clause) = 'INSERT INTO' then
begin
ccs := AnsiUpperCase(Clause) + ' ' + AnsiUpperCase(Tabela) + ' ';
for i := 0 to High(Campos) do
cgo := cgo + IIF(Campos <> '', ', ' + Campos, '');
cgo := ccs + '(' + AnsiUpperCase(Chave) + ' ' + cgo + ') VALUES (';
for i := 0 to High(Valores) do
cvl := cvl + IIF(VarToStr(Valores) <> '', ', ' + QuotedStr(VarToStr(Valores)), 'NULL');
cgo := cgo + QuotedStr(gni) + cvl + ')';
end;

if AnsiUpperCase(Clause) = 'UPDATE' then
begin
ccs := AnsiUpperCase(Clause) + ' ' + AnsiUpperCase(Tabela) + ' SET ';
for i := 0 to High(Campos) do
cgo := cgo + IIF(Campos <> '', ', ' + Campos + ' = ' + QuotedStr(VarToStr(Valores)), '');
cgo := ccs + chv + cgo + cvl + ')';
end;

Qry := TZQuery.Create(DMZ.zHalley);
try
with Qry do
begin
Connection := DMZ.zHalley;
SQL.Add(cgo);
ExecSQL;
Connection.Commit;
end;
finally
Qry.Free;
end;
if IdResult = -1 then
Result := gni
else
Result := VarToStr(Valores[IdResult]);

end;

[mdaems]

Hi,

As far as I understand your question : yes, the updates should be saved to the database in both cases.
I suppose in your case it is not saved? Can you please demonstrate an easy example of your problem? The creation of the statement in your example above is not very clear for somebody who does not know your application.

Mark

[marquessbr]

Ok, here is that optmized function, that work like above

function FillFLDSInaTable(Clause, aTable, GenID, Key: string; aFields: array of string;
Var Values: array of variant; IdResult, IdKey: integer): string;
var
Qry : TZQuery; // ZQuery created at runtime
aSQL, sSQL,
cgo, cwr, ccs, cvl, gni: string;
tpc: char;
i: integer;
begin
// aFields - array of string with list of names of native flds to selct, insert or update (s, i, u) aTable

// the where clause is
cwr := ' WHERE ' + aFields[IdKey] + ' = ' + Key;

for i := 0 to High(aFields) do
cgo := cgo + IIF(aFields <> '', IIF(i = 0, aFields, ', ' + aFields), '');

// create the list of flds for write the s, i: 'SELECT [cgo] FROM [aTable] WHERE [cwr]

sSQL := 'SELECT ' + cgo + ' FROM ' + AnsiUpperCase(aTable) + cwr;

if AnsiUpperCase(Clause) = 'INSERT INTO' then
begin
gni := IntToStr(DMZ.GetIncrementValue(AnsiUpperCase(GenID), 1));
Values[IdKey] := gni;

ccs := AnsiUpperCase(Clause) + ' ' + AnsiUpperCase(aTable) + ' ';

aSQL := ccs + '(' + cgo + ') VALUES (';
for i := 0 to High(Values) do
cvl := cvl + IIF(VarToStr(Values) <> '', IIF(i = 0, QuotedStr(VarToStr(Values)),
', ' + QuotedStr(VarToStr(Values))), 'NULL');
aSQL := aSQL + cvl + ')';
tpc := 'I';
end;

// First I call FillFLDSInaTable('SELECT', aTable, GenID, Key, aFields, Values, IdResult, IdKey) to
// find the part's typed for the user in a edit
//if no Part's, I recall the function with Clause = 'INSERT' or 'UPDATE' to add the new part informed for the user

if AnsiUpperCase(Clause) = 'UPDATE' then
begin
ccs := AnsiUpperCase(Clause) + ' ' + AnsiUpperCase(aTable) + ' SET ';
for i := 0 to High(aFields) do
cvl := cvl + IIF(aFields <> '', IIF(i = 0, aFields + ' = ' + QuotedStr(VarToStr(Values)),
', ' + aFields + ' = ' + QuotedStr(VarToStr(Values[i]))), '');
aSQL := ccs + cvl + cwr;
tpc := 'U';

// aSQL will is like 'UPDATE aTable SET (aTbFld01 = Values[0], aTbFld0n = Values[n]) ...


end;

if AnsiUpperCase(Clause) = 'SELECT' then
begin
aSQL := sSQL;
tpc := 'S';
end;

Result := 'NEW';

// **************************************************************************************** //
// here I need to know: I have a componente TZquery ZQxxx with (CachedUpdates = True), but
// is created at design time with your SQL's property pointing to the same table
// past for aTable variable above, but the TZQuery (QRY) in this function, is created
// at runtime, get aSQL variable, process do I need, change the Values variable with is a
// pointer with a list of values with I need of this implicit part's and is destroyed above.
// So if I Cal the ZQxxx.ApplyUpdates will save the Insertion ou Update made fo QRY in my
// Database?






Qry := TZQuery.Create(DMZ.zHalley);
try
with Qry do
begin
Connection := DMZ.zHalley;
SQL.Add(aSQL);
case tpc of
'S': begin
Open;
if FieldByName(aFields[IdResult]).AsString <> '' then
begin
Result := FieldByName(aFields[IdResult]).AsString;
for i := 0 to FieldCount - 1 do
Values[i] := Fields[i].AsVariant;
end;
end;
'I', 'U': begin
ExecSQL;
for i := 0 to High(Values) do
Values[i] := Values[i];
Result := VarToStr(Values[IdResult]);
end;
end;
end;
finally
Qry.Free;
end;
end;

[pol]

Hi,

Either you can set QRY.CachedUpdates to false after creating it, or call ApplyUpdates after ExecSQL, that should work. Calling ApplyUpdates for an other TZQuery will only apply the updates of that.

HTH
Rüdiger

[marquessbr]

Ok I try to that and at first time work, later I retry to use this.
Thanks!