Board index » delphi » Cached Updates using TQuery in code

Cached Updates using TQuery in code

Does anybody out there have the secret to using CachedUpdates for a
Tquery in code. ALL the examples everywhere seem to use a data aware
control such as a TDBGrid, and expect that you want to SELECT and modify
the data.

I am attempting the following:

- I have a program which is passed reference to a TQuery object.
- This program buids INSERT SQL to a *single* INTERBASE table
- The SQL is executed and the new record inserted
- input data is coming from a text file, so we many hundreds of thousand
records to insert.

This all works fine using normal SQL and TQuery procedures. However as
we are writing to a database server, we wish to try local cahed updates
to see if it speeds thins up a bit.

However, CachedUpdates does not seem to work if we try to do things in
code rather than use DBAware components.

It has been suggested we use TUpdateSQL component to create an update
query. things to note about this are:

- Our original SQL is updateable anyway, because it inserts to only a
single table
- The TUpdateSQL seems (in Borland examples) to be atteched to TQueries
that use SELECT statement. We don't want any SELECT statements. All we
want is INSERTs

Following is a code segment of the simple standard INSERT that works
fine:
(aQuery is reference to a Tquery)

....
aQuery.SQL.Add('INSERT INTO....');
aQuery.Prepare;
aQuery.ExecSQL;
....

Following is a code segment of one of our many failed attempts:
(aQuery is reference to a Tquery)

....
aQuery.SQL.Add('INSERT INTO....');
aUpdateSQL:=TUpdateSQL.Create(self);     //Create an TUpdateSQL
aQuery.UpdateObject(aUpdateSQL);         //attach it to TQuery
aUpdateSQL.InsertSQL:=aQuery.SQL;        //Set TUpdateSQL to INSERT
aQuery.Prepare;
aQuery.ExecSQL;
....
....
aQuery.Database.ApplyUpdate([aQuery]);
....

**Many version of this have been tried using a dummy 'SELECT' SQL in the
TQuery, using TUpdateSQL.ExecSQL etc..nothing seems to work.

The exception raised during the ApplyUpdates is:

"Dataset is not in edit or Insert mode".

If anyone can lend us their wisdom it would be most appreciated, as it
is not clearly documented anywhere what procedures have to be followed
to get CachedUpdates to work in code. As I said earlier, all examples
assume that the user is using data aware controls such as DBGRid *AND*
wants to SELECT and modify. We need only to INSERT.

Mark McKinlay
National Australia Financial Management

Mark_McKin...@nag.national.com.au

 

Re:Cached Updates using TQuery in code


What if you just add "Query1.Edit;" to the SQL code you execute just before
you use any insert statements?

I insert data that is not connected to a grid or anything else into SQL
Server all the time using a regular Query component.  To accomplish that I
assign the data to be inserted first to parameters which are then used in
the SQL code.

Since you are reading your data from a text file you could write a loop that
reads the rows one at a time, assigns the data elements to your query
parameters and then executes the SQL.  Once you complete the loops, you call
the update procedure.

Good luck,
Gene

Quote
National Financial Management wrote in message

<345925A9.6...@ozemail.com.au>...
Quote
>Does anybody out there have the secret to using CachedUpdates for a
>Tquery in code. ALL the examples everywhere seem to use a data aware
>control such as a TDBGrid, and expect that you want to SELECT and modify
>the data.

>I am attempting the following:

>- I have a program which is passed reference to a TQuery object.
>- This program buids INSERT SQL to a *single* INTERBASE table
>- The SQL is executed and the new record inserted
>- input data is coming from a text file, so we many hundreds of thousand
>records to insert.

>This all works fine using normal SQL and TQuery procedures. However as
>we are writing to a database server, we wish to try local cahed updates
>to see if it speeds thins up a bit.

>However, CachedUpdates does not seem to work if we try to do things in
>code rather than use DBAware components.

>It has been suggested we use TUpdateSQL component to create an update
>query. things to note about this are:

>- Our original SQL is updateable anyway, because it inserts to only a
>single table
>- The TUpdateSQL seems (in Borland examples) to be atteched to TQueries
>that use SELECT statement. We don't want any SELECT statements. All we
>want is INSERTs

>Following is a code segment of the simple standard INSERT that works
>fine:
>(aQuery is reference to a Tquery)

>....
>aQuery.SQL.Add('INSERT INTO....');
>aQuery.Prepare;
>aQuery.ExecSQL;
>....

>Following is a code segment of one of our many failed attempts:
>(aQuery is reference to a Tquery)

>....
>aQuery.SQL.Add('INSERT INTO....');
>aUpdateSQL:=TUpdateSQL.Create(self);     //Create an TUpdateSQL
>aQuery.UpdateObject(aUpdateSQL);         //attach it to TQuery
>aUpdateSQL.InsertSQL:=aQuery.SQL;        //Set TUpdateSQL to INSERT
>aQuery.Prepare;
>aQuery.ExecSQL;
>....
>....
>aQuery.Database.ApplyUpdate([aQuery]);
>....

>**Many version of this have been tried using a dummy 'SELECT' SQL in the
>TQuery, using TUpdateSQL.ExecSQL etc..nothing seems to work.

>The exception raised during the ApplyUpdates is:

>"Dataset is not in edit or Insert mode".

>If anyone can lend us their wisdom it would be most appreciated, as it
>is not clearly documented anywhere what procedures have to be followed
>to get CachedUpdates to work in code. As I said earlier, all examples
>assume that the user is using data aware controls such as DBGRid *AND*
>wants to SELECT and modify. We need only to INSERT.

>Mark McKinlay
>National Australia Financial Management

>Mark_McKin...@nag.national.com.au

Other Threads