Board index » delphi » Cached Updates and TQuery

Cached Updates and TQuery

Can you use cached updates with an SQL statement that does does not return
a result set (ie: you need to use execSQL instead of open with the TQuery
component). If (as seems to be the case) you can't then what's the best way
around this problem.

(When I do try I get the error message that the TQuery component does not
belong to the TDatabase component that it is assigned to.)

I want to update records in a table with a statement like the one below:

UPDATE "Locations.db" L
SET L."Active"=TRUE
WHERE L."Code"=1

Now if you execSQL the above and a record is locked then an error is raised
to inform you but all the other records that meet the criteria is changed.
Now if an error occurs I want to 'rollback' the changes. So how do I do
this. I could of course apply the query again this time setting L."Active"
to FALSE but by this time another different record may have been locked and
further problems ensuing.

Any ideas gratefully received

 

Re:Cached Updates and TQuery


Quote
> Can you use cached updates with an SQL statement that does does not
return
> a result set (ie: you need to use execSQL instead of open with the TQuery
> component). If (as seems to be the case) you can't then what's the best
way
> around this problem.

Don't know

Quote
> (When I do try I get the error message that the TQuery component does not
> belong to the TDatabase component that it is assigned to.)

Make sure that you have

        qry.databasename := db.databasename;
        qry.sessionname := db.sessionname;

Quote

> I want to update records in a table with a statement like the one below:

> UPDATE "Locations.db" L
> SET L."Active"=TRUE
> WHERE L."Code"=1

> Now if you execSQL the above and a record is locked then an error is
raised
> to inform you but all the other records that meet the criteria is
changed.
> Now if an error occurs I want to 'rollback' the changes. So how do I do
> this. I could of course apply the query again this time setting
L."Active"
> to FALSE but by this time another different record may have been locked
and
> further problems ensuing.

Theoretically you can use:

        db.StartTransaction;
        try
                qry.ApplyUpdates;      
                db.Commit;
                qry.CommitUpdates;
        except
                db.Rollback;
                qry.CancelUpdates;      // if you want the record restored.
                throw;
        end;

However, I have not been able to get the rollback to work - if you look at
the ODBC log, the Rollback takes place against a different connection
handle than the one use to execute the SQL required by ApplyUpdates.

Re:Cached Updates and TQuery


Quote
"Sean Radford" <s...@bladesys.demon.co.uk> wrote:
>Can you use cached updates with an SQL statement that does does not return
>a result set (ie: you need to use execSQL instead of open with the TQuery
>component). If (as seems to be the case) you can't then what's the best way
>around this problem.
>(When I do try I get the error message that the TQuery component does not
>belong to the TDatabase component that it is assigned to.)
>I want to update records in a table with a statement like the one below:
>UPDATE "Locations.db" L
>SET L."Active"=TRUE
>WHERE L."Code"=1
>Now if you execSQL the above and a record is locked then an error is raised
>to inform you but all the other records that meet the criteria is changed.
>Now if an error occurs I want to 'rollback' the changes. So how do I do
>this. I could of course apply the query again this time setting L."Active"
>to FALSE but by this time another different record may have been locked and
>further problems ensuing.
>Any ideas gratefully received

You can use explicit transaction control:-
Database.StartTransaction
try
        ExecSQL
        ExecSQL
        Database.Commit
except
        on EDatabaseError do
                Database.Rollback
end

Other Threads