Ok, I am trying to understand how to use tsqlquery to update my data (using mysql.) It doesn't work, but I think I am on the right idea. I have never used this paraterized sql before, so bear with me.

This is what I have got:

I Have a TsqlQuerry, lets say its name is MyQuery.
So MyQuery.UpdateSQL :=

Update MyTable set MyCol = :MyCol
Where MyIdx = :MyIdx

Now in

procedure TDatabaseForm.MyQueryAfterPost(DataSet: TDataSet);
begin
MyQuery.Params.CreateParam(ftString, 'MyIdx', ptinput);
MyQuery.Params.ParamByName('MyIdx').AsString := 'Idx';
MyQuery.Params.CreateParam(ftString, 'MyCol', ptinput);
MyQuery.Params.ParamByName('MyCol').AsString := 'Test Value';
ConstQuery.ApplyUpdates;
end;

I am using Updatesql because when I tried it first it said my Updatesql was invalid.
I am assuming that I will have to do something like this with Insertsql & deletesql too.

Thx in advance, Terry

If you can read dutch, read this:
http://wiki.freepascal.org/index.php?title=SqlDBHowto/nl#Hoe_stuurt_SqlDB_de_wijzigingen_naar_de_Database-server.3F

But I'll give some clues.

Op dinsdag 28-10-2008 om 06:33 uur [tijdzone -0500], schreef Terry A. Haimann:

Update MyTable set MyCol = :MyCol

Where MyIdx = :MyIdx

Looks fine.

Now in procedure TDatabaseForm.MyQueryAfterPost(DataSet: TDataSet); begin

The following you don't need.

MyQuery.Params.CreateParam(ftString, 'MyIdx', ptinput); MyQuery.Params.ParamByName('MyIdx').AsString := 'Idx'; MyQuery.Params.CreateParam(ftString, 'MyCol', ptinput); MyQuery.Params.ParamByName('MyCol').AsString := 'Test Value';

You don't need the above, because ApplyUpdates assigns the right values to the query. (Param-name should be the same name as the field's name. If you add _old to the param-name, you can use the values of the field before the change.)

Update MyTable set MyCol = :MyCol Where MyIdx = :MyIdx_old

What was the exact message? Most probably it coudn't find any fields for the where-clause. And that's probably because you use upWhereKey as UpdateMode, and there's no primary key defined for your table. Try to change it to upWhereAll.

I am assuming that I will have to do something like this with Insertsql & deletesql too.

Yes, but if you have just a 'simple select' statement, you don't need to. Use the auto-generated onces. And, if you have a DB which uses transactions, don't forget to commit the transaction.

Joost

Close. Changing it to UpWhereAll didn't work, but changing it to UpWhereChanged did work. I spose I should post the results of this little excercise on the Forum, maybe get it added to the Wiki somehow.

I have gotten the Update and Insert to work, but not the delete. It appears that to execute the delete, a AfterDelete procedure needs added and the applyupdates in the AfterUpdate does nothing is a delete case. I have tried using both
delete from mytable where myidx = :myidx
and
delete from mytable where myidx =:myidx_old

And I have been getting the following error:
Project raised exception class 'EDatabaseError' table: No delete query specified and failed to generate one (No field for inclusion in where statement found.)

The Insert or the update doesn't work at all unless I have UpdateMode set to UpdateWhereChanged.