Using SQLQuery and AfterPost

[HatForCat]

Hi, I am using an SQLQuery and the "AfterScroll is working fine for what I need from it.

I am using the following to insert records and it does what I need for an Insert. But, I notice there is an AfterInsert, AfterPost etc within SQLQuery but I cannot figure out how to use them.

I'd like to grab the new auto-inc value ("recID") for the Inserted record. I assume the AfterPost event would be a good spot to grab that, but, cannot find how to trigger that event.

Code: [Select]

  SQLQuery1.Close;
  sqlquery1.SQL.clear;
  SQLQuery1.SQL.add ('INSERT INTO tblCust (zAccount)');
  SQLQuery1.SQL.add ('VALUES ("'+Edit1.Text+'");');
  SQLQuery1.ExecSQL;
  SQLTransaction1.CommitRetaining;

Could someone please give me a few code pointers or some place to look for information in using these events. The above code does not trigger any events. Well, none that I can make work.

Thanks.

[rvk]

The AfterPost triggers after.... well... a Post
You're not using Insert/Post but ExecSQL which doesn't trigger AfterPost.

If you want to use AfterPost you should do something like this:

Code: Pascal  [Select][+][-]

1. SQLQuery1.Close;
2. SQLQuery1.SQL.Text := 'SELECT * FROM tblCust';
3.     // ^---- maybe you already had this set to a SELECT,
4.     //        then you don't need to do Close/Open
5. SQLQuery1.Open;
6. SQLQuery1.Insert;
7. SQLQuery1.FieldByName('zAccount').asValue := Edit1.Text;
8. SQLQuery1.Post; // <-- now AfterPost is triggered
9. SQLTransaction1.CommitRetaining;

[kapibara]

Also ApplyUpdates before CommitRetaining?

Code: Pascal  [Select][+][-]

1. SQLQuery1.Post; // <-- now AfterPost is triggered
2. SQLQuery1.ApplyUpdates;
3. SQLTransaction1.CommitRetaining;

[rvk]

Also ApplyUpdates before CommitRetaining?

I assumed ApplyUpdates would be done manually in AfterPost because OP also wanted to get the auto-inc value. But it's good to point out that it has to be done. For retrieving the aut-inc value in AfterPosr, the ApplyUpdates also needs to be in AfterPost because only with ApplyUpdates the communication with the server is done.

But of course, again, the auto-inc value could also be retrieved in the section where you call .Post and .ApplyUpdates like in the code shown here.

[HatForCat]

You're not using Insert/Post but ExecSQL which doesn't trigger AfterPost.

Ahh, OK, thank you very much. I had not realized I could mix the SQL.Text and the .Insert and .Post methods. I assumed they were mutually exclusive and could not see how to make them work. So damned simple now that I see your code.

Thanks so much for the code, I seem to understand stuff better in code than in Reference manual format.

[HatForCat]

Also ApplyUpdates before CommitRetaining?

Thank you for that too. Interestingly - or not, when I tried doing that I got an error message about "zero records updated."

Drove me crazy for half an hour trying things and searching online. In desperation I added "SQLQuery1.Open;" in front of your code and the problem went away.

It seems the Query is automatically being closed after Post and Delete.

[kapibara]

Just wanted to make sure ApplyUpdates was called somewhere. Read rvk's message one more time. If you assign the auto-inc value in the AfterPost event you need to call ApplyUpdates there too. Have to admit I don't know why it must be done this way.

Post and Delete won't close the query. But it will switch from Edit state to Browsing state. Closing the connection or transaction closes all associated queries. Calling Commit instead of CommitRetaining closes the transaction and all its queries. But the connection stays open.

It seems the Query is automatically being closed after Post and Delete.

[mangakissa]

The most imported thing is: which database engine are you using?
All the solutions above doesn't get the primary key into the application.