[Lazarus] TSQLQuery & ApplyUpdates

[Joost van der Sluis]

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:
> 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

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


> ConstQuery.ApplyUpdates;
> end;
> 
> I am using Updatesql because when I tried it first it said my Updatesql 
> was invalid.

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