Board index » delphi » When to prepare and unprepare a TQuery??

Joe Nodeland

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

When to prepare and unprepare a TQuery??

My application typically goes in the following cycle.

1:Create a datamodule.
2:Prepare the TQuerys on the datamodule.
3:Use the querys.
4:Free the datamodule.

Question: Do I need to unprepare the querys before freeing the
datamodule?

Sometimes the typical cycle is as follows.

1:Create a datamodule.
2:Prepare the TQuerys on the datamodule.
3:Use the querys.
4:Construct new sql statements on the fly.
5:Prepare the TQuery with the new sql.
6:Use the TQuery.
7:Free the datamodule.

Question: Do I need to unprepare the TQuery between steps 3 and 4?

 
Sundial Servic

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:When to prepare and unprepare a TQuery??


Quote
In article <34084574.4...@EasySoft-Law.com> Joe Nodeland <J...@EasySoft-Law.com> writes:
>My application typically goes in the following cycle.
>1:Create a datamodule.
>2:Prepare the TQuerys on the datamodule.
>3:Use the querys.
>4:Free the datamodule.
>Question: Do I need to unprepare the querys before freeing the
>datamodule?
>Sometimes the typical cycle is as follows.
>1:Create a datamodule.
>2:Prepare the TQuerys on the datamodule.
>3:Use the querys.
>4:Construct new sql statements on the fly.
>5:Prepare the TQuery with the new sql.
>6:Use the TQuery.
>7:Free the datamodule.
>Question: Do I need to unprepare the TQuery between steps 3 and 4?

"Preparing" a query is an explicit request to the database engine for it to
view the SQL text and construct the corresponding execution plan.  You are
asking the engine to do this at a certain time so that you can control exactly
when it occurs.  You do not have to do this.  If the query is simply run, and
it has not yet been prepared, the engine will do this automatically.

According to the manual, "When you change the text of a query at run time,
Delphi automatically closes and unprepares the query."

However, what I recommend is... if you explicitly prepare it, finish the job
and explicitly un-prepare it also.  Once you have taken explicit control over
the process, explicitly perform the entire process.  Don't leave the software
in the dark.  This also helps the programmers who will follow you...  you are
telling them, as well as the computer, "at this point in the program I no
longer intend to make use of the query I have prepared."

J?rgen Aas

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:When to prepare and unprepare a TQuery??


Quote
Joe Nodeland wrote:

> My application typically goes in the following cycle.

> 1:Create a datamodule.
> 2:Prepare the TQuerys on the datamodule.
> 3:Use the querys.
> 4:Free the datamodule.

> Question: Do I need to unprepare the querys before freeing the
> datamodule?

> Sometimes the typical cycle is as follows.

> 1:Create a datamodule.
> 2:Prepare the TQuerys on the datamodule.
> 3:Use the querys.
> 4:Construct new sql statements on the fly.
> 5:Prepare the TQuery with the new sql.
> 6:Use the TQuery.
> 7:Free the datamodule.

> Question: Do I need to unprepare the TQuery between steps 3 and 4?

You prepare a TQuery when you use parameters. This will make execution
faster becasue it parses, validates and compiles the sql statement.

TQquery.SQL.Add(...);
TQquery.Prepare;
try
   for i := 1 to 1000 do
   begin
      TQuery.ParamByName('...').AsString := '...';
      TQuery.Open;
      try
         // use the result set here
      finaly
         TQuery.Close;
   end;
   finaly
     TQuery.Unprepare;  
end;

Unpreparing the query releases the resources used by Prepare.

Here's what i often do

Create a query
Set the databaseName
Assign sql strings
Free the query

If you frequently Open and Close queries, its better to use a TDataBase
component which keeps a conection to the database.

Mark Icke

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:When to prepare and unprepare a TQuery??


By "Unprepare" of the TQuery are you just talking about TQuery.Close?  I'm
having some weird problems using a TQuery object where I change the SQL
several times and .Open and .Close it between each time.  I will eventually
get an Access Violation.

What I did discover is that it only happens when using BDE v3.5.  With BDE
v3.0 and 4.0 the problem doesnt happens.  Do yo know of any known problems
reusing the same TQuery object to run multiple queries?
Thanks,
Mark

Quote
> "Preparing" a query is an explicit request to the database engine for it
to
> view the SQL text and construct the corresponding execution plan.  You
are
> asking the engine to do this at a certain time so that you can control
exactly
> when it occurs.  You do not have to do this.  If the query is simply run,
and
> it has not yet been prepared, the engine will do this automatically.

> According to the manual, "When you change the text of a query at run
time,
> Delphi automatically closes and unprepares the query."

> However, what I recommend is... if you explicitly prepare it, finish the
job
> and explicitly un-prepare it also.  Once you have taken explicit control
over
> the process, explicitly perform the entire process.  Don't leave the
software
> in the dark.  This also helps the programmers who will follow you...  you
are
> telling them, as well as the computer, "at this point in the program I no
> longer intend to make use of the query I have prepared."

adas

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:When to prepare and unprepare a TQuery??


Quote
> By "Unprepare" of the TQuery are you just talking about TQuery.Close?
I'm
> having some weird problems using a TQuery object where I change the SQL
> several times and .Open and .Close it between each time.  I will
eventually
> get an Access Violation.
> What I did discover is that it only happens when using BDE v3.5.  With
BDE
> v3.0 and 4.0 the problem doesnt happens.  Do yo know of any known
problems
> reusing the same TQuery object to run multiple queries?
> Thanks,
> Mark

I experienced the same problem with BDE v3.5. Seems like in random cases (I
suspect it has a thread sync problem), DbiOpenCursor returns an invalid
handle, so when TQuery uses this hanle to retrieve data, it gets an Access
Violation. Workaround? hum, thinking about....

--
-------------------------
Regards,
Robert

MailTo:robert.ce...@eunet.si

Other Threads

1. TQuery Prepare/Unprepare Problems

2. TQuery Prepare/Unprepare Balancing

3. prepare&unprepare SQL-methods

4. Prepare and Unprepare

5. Preparing/unpreparing parametrized query

6. Preparing / unpreparing queries and stored procs

7. BDE not enough memory with prepare/unprepare

8. Stored Proc Prepare/Unprepare

9. Prepare and Unprepare

10. Prepare/Unprepare