We help IT Professionals succeed at work.

Working with StartTransaction ???

syloux
syloux asked
on

How can I use correctly StartTransaction ?

I have this :
( Query1.CachedUpdate := True )

  Database1.Open ;
  Database1.StartTransaction ;

  With Query1, Sql Do
    Begin
      Close ;
      Clear ;
      Add('INSERT INTO Test') ;
      Add('VALUES (:Username, :LongName)') ;
      ParamByName('Username').AsString := 'Toto' ;
      ParamByName('Longname').AsString := 'Grand Toto' ;
      ExecSql ;
    End ;

  Query1.ApplyUpdates ;
  Database1.Commit ;    
  Query1.CommitUpdates ;

But when I run it, I have and error messsage on Query1.ApplyUpdates :
"Dataset not in edit or insert mode."

What can I do ?
Thanks

Comment
Watch Question

OK, for one things you don't actually have to use cached updates with insert statements, the following sequence would be fine.

try
  db1.StartTransaction;

  with qry1, sql do
  begin
    ... //as yours
    ExecSQL;
  end;
  db1.Commit;
except
  db1.Rollback;
end;

The only reason I can think of for the error you are recieving would be if you had cached updates set to true on your query but hadn't actually associated an updateSQL object with it to apply the updates with.

Now that wouldn't be the case... would it?
CERTIFIED EXPERT

Commented:
Hi,
I'm not sure but try this:

Database1.Open ;

  With Query1, Sql Do
  Begin
    Close ;
    Clear ;
    Add('INSERT INTO Test') ;
    Add('VALUES (:Username, :LongName)') ;
    ParamByName('Username').AsString := 'Toto' ;
    ParamByName('Longname').AsString := 'Grand Toto' ;
    ExecSql ;
  end;

  if Query1.UpdatesPending then
    Database1.ApplyUpdates([Query1]);
End ;

Regards, Geo
CERTIFIED EXPERT
Top Expert 2004

Commented:
i guess cached updates are only usefull with an select-query,

you get the error because you don't call query.edit or query.insert,
which may cause a dataset not open error, because you can't open a insert query

if you want the capability of cached updates, then use a select-query,
open the query,
insert your new records with the insert method,
supply your fieldvalues with query.fielsbyname().as.. := whatever
post the insert and
start again with a new record,
if you are ready then you do a applyupdate

meikl ;-)
CERTIFIED EXPERT

Commented:
Hi,
Using my solution you don't need to think what the query does (select, insert, etc.) because you check for pending updates first.
The second thing is that TDatabase.ApplyUpdates does all the transaction stuff internally (starts a transaction, commits or rolls back and clears the updates cache) for you.

Regards, Geo

Explore More ContentExplore courses, solutions, and other research materials related to this topic.