How to Execute TZUpdate???

[IbeDBob]

Hi All,

This is really frustrating. I am trying to use TZUpdate but for the life of me I can't find how to execute or action it.

I have scoured the source code and no exec of any kind. I have tried ExecSQL for the parent query, but does nothing either.

How do I activate an update????

I have searched high and low and I am not a novice at programming, but there seems to be zero in the way of useful tutorials or even help files. This library seems like a good set of tools, but with no help with using them they are useless.

[seawolf]

Hi,
I post an example, hoping you can understand how it works

var
Error: boolean;
Query: TZQuery;
UpdateSQL: TZUpdateSQL;
begin
Error := True;
Query := TZQuery.Create(nil);
Query.Connection := Connection;
// Query.RequestLive := True;
UpdateSQL := TZUpdateSQL.Create(nil);

try
Query.SQL.Text := 'DELETE FROM TABLE';
Query.ExecSQL;
Query.SQL.Text := 'INSERT INTO TABLE VALUES (''A'', 10, ''2004-03-11'')';
Query.ExecSQL;

Query.UpdateObject := UpdateSQL;
UpdateSQL.ModifySQL.Text := ' UPDATE TABLE SET' +
' FLD1 = :FLD1,' +
' FLD2 = :FLD2,' +
' FLD3 = :FLD3'+
' WHERE FLD1 = :OLD_FLD1';

Query.SQL.Text := 'SELECT * FROM TABLE';
// Query.RequestLive := True;
Query.Open;
with Query do
begin
Edit;
Fields[0].Value := Null;
Fields[1].Value := Null;
Fields[2].Value := Null;
Post;
Close;
end;
except
Error := False;
end;
CheckEquals(False, Error, 'Problems with set Null prametrs in SQL');
UpdateSQL.Free;
Query.Free;

[IbeDBob]

Thanks, but it does not update the Table until I Close and Open it again and that moves the cursor off the selected line in the TDBGrid. I need some kind of refresh.

Yes, I am using AutoCommit

I am using SQLite3 if that makes a difference.

Bob

[IbeDBob]

Thanks Seawolf,

I have it fixed. Your code got me 80% of the way, but the Record in the grid losing focus was not good.

May not be the most elegant solution casting another version of the TDBGrid to expose the Row property, but here it is for others...

type
..TTempDBGrid = class(TDBGrid);

procedure TfrmMain.dbgridAccountsCellClick(Column: TColumn);
var
..aRow : Integer;
begin
..aRow:=TTempDBGrid(dbgridMAccounts).Row;
..// Do the Update stuff here
..dbgridAccounts.DataSource.DataSet.MoveBy(aRow);
end;

The Row is saved, then, because the "Refresh will reset the focus to the first record in the TDBGrid, the saved-Row will be used as an offset to reposition the focus.

IMPORTANT: You MUST set the RowSelect:=True for the grid.

[iru]

Gentlefolk,

Using Lazarus 0.9.30, ZEOS 7.0, FB 2.15, WinXP-SP-2.

I am having a problem using the DeleteSQL in a TZQuery-TZUpdateSQL structure.

My code follows the code illustrated at the start of this discussion but nothing is deleted from the database.

Using the SQL monitor I can see a StartTransaction-Commit pair and between them a log entry "Execute prepared, proto: firebird-2.1, msg: Statement 19".

The SQL statement is good. If I save it TZQuery.SQL and use ExecSQL the data is deleted from the database.

If I corrupt the SQL, save it in TZUpdateSQL.DeleteSQL and then execute the code NO SQL error is reported.

Any ideas????? Ian

[seawolf]

Sorry, but I can't understand this statement

If I corrupt the SQL, save it in TZUpdateSQL.DeleteSQL and then execute the code NO SQL error is reported.

Do you try to execute a malformed SQL command? And in this case
Do you receive no messages?

[iru]

Thank you for the response.

Yes, that is correct, with a bad/corrupt SQL statement no error messages/exceptions occur.

Code is as follows (SQL is good):

try
HeatSQL := 'Delete from VENUE_RESULTS where ' +
'((SeasonYear = ' + UnitData.UD.GetCurrent.CurrentSeason + ') and ' +
'(ROUND = ' + UnitData.UD.GetCurrent.CurrentRound.RoundNoString + ') and ' +
'(EVENTNO = ' + UnitData.UD.GetCurrent.CurrentEvent.EventNoString + ') and ' +
'(HEAT = ' + UnitData.UD.GetCurrent.CurrentHeat.HeatNoString + ') and ' +
'(LANE > ' + IntToStr(SpinEditSetLanes.Value) + '))';

tZQuery(UnitData.UD.ZQueryResults).Open;
tZQuery(UnitData.UD.ZQueryResults).Edit;
UnitData.UD.ZUpdateSQLResults.DeleteSQL.Clear;
UnitData.UD.ZUpdateSQLResults.DeleteSQL.Add(HeatSQL);

tZQuery(UnitData.UD.ZQueryResults).Post;
tZQuery(UnitData.UD.ZQueryResults).Close;
DebugDataSet := tZQuery(UnitData.UD.ZQueryResults).Active;
tZQuery(UnitData.UD.ZQueryResults).Open;

UnitData.UD.ZConnection.Commit;
except
on E:Exception do
Begin

end;

I will have a look at the ZEOS code....... Ian.

[iru]

Gentlefolk,

Struggling with this.

In the example given in the earlier sections of this discussion I can understand

Setting the UpdateSQL := 'Update Table Set......'
Setting some fields to Fields[0].Value := Null;

And then performing a Post and a Close.

The Post-Close code can look at the data in the dataset, recognise that fields have changed and then apply the SQL in UpdateSQL to update the database.


With my code which updates DeleteSQL, then performs a Post-Close why should the code invoke the DeleteSQL? There is no changes to the dataset, the DeleteSQL SQL code is aimed at the database. Am I missunderstanding something (more than I usually do)??

Chasing my way through ZEOS code around the post in my code is hard work. No relevant clues yet.

Ian.

[Wild_Pointer]

Hello, iru,

you mean that on post the deleteSQL is invoked? Are you sure you don't have the same sql (HeatSQL) in updateSQL?

I use TZUpdateSQL component very often and *never* observed such a behavior.

using Zeos7 on windows machine. DBMS = PostgreSQL

[iru]

Thank you for the respones,

As far as I can determine the DeleteSQL SQL is not executed. I am trying to find where it is processed in ZEOS but I am not having much luck with that.

The "HEATSQL"/DeleteSQL is certainly different from the ModifySQL(UpdateSQL).

The data structure id DBGrid-DataSource-ZQuery-UpdateSQL (no navigator).
The DBGrid displays the data for a sprint race. Usually 8 rows with each row containing data for that row/lane (Athlete name, club, performance, status, etc).

The program user may look at the DBGrid and decide that the number of rows needs to be adjusted. Click on a button and a small form with two Spinedits are displayed, one for adding rows, one for subtracting rows. Adjust the appropriate SpinEdit, click on the related "save".

The "add" mechanism uses an Append for each additional row which is filled with required default data. Works well.

For the "subtract" mechanism my idea was to use the UpdateSQL.DeleteSQL but as discussed in this chain it (DeleteSQL) does not appear to be invoked. Works OK through ZQuery.SQL-ExecSQL.

A lot of the users of this program will be occassional volunteers with little knowledge of the program and/or computers.
The idea is to keep it simple............

Any ideas or perhaps an example of a working DeleteSQL?

Thanks, Ian.

[Wild_Pointer]

Ian,

see the attachment. I've made an examble of using tzupdatesql component. The query points to ZUpdateSQL via UpdateObject property. I used simple queries for the example just to show the point.

From what you wrote I would bave tables competition, race, competitor. Only competitor is edited by users in the final step (entering names, track numbers, times maybe...).
For that you don't need tzupdatesql object. Simply use zquery.delete (unless you do some additional steps on delete...).

Good luck!

[iru]

Thanks once again,

I have had a look at the .pas and .lfm and can see what you have done.
Most helpful.

Can not compile because of an error in the ide on the TIntegerField.

Known problem in Bugtrack but it looks like I will have to move to a later version of laz 0.9.30.

Groan........

Thanks, Ian.

[iru]

I have changed TIntegerField to TStringField in Unit1.dfm.

Can now see the form in the ide. Will not compile.

I think I have enough info from looking at the .pas and .dfm files.

Will update my code and see what happens.

Thanks once again, Ian.

[iru]

Things are looking better....

I have the following code:

LocalQuery.UpdateObject.DeleteSQL.Clear;
LocalQuery.UpdateObject.DeleteSQL.Add(HeatSQL);

LocalQuery.Delete;
LocalQuery.Edit;
LocalQuery.Post;
LocalQuery.ApplyUpdates;

The .Delete, .Edit and .Post update the ZQuery Dataset.

The .Delete deletes one row (seen on DBGrid (row depends upon the "selected" row)) and changes something in the DataSet so that the .ApplyUpdates invokes the .DeleteSQL and the HeatSQL.

FB database is updated as specified in the HeatSQL.

Just have to refresh the DBgrid display........

Thank you vey much for your interest and for your example code, Ian.

[Wild_Pointer]

Hello iru,

no problem. I'm glad to help.

By the way, You don't need "LocalQuery.ApplyUpdates;" unless you are using CachedUpdates

Good luck!