[Lazarus] TSQLQuery & ApplyUpdates

[Terry A. Haimann]

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.

Joost van der Sluis wrote:
> 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
>
>
>