TQuery and Prepare questions

Posted on 2001-08-14
Last Modified: 2007-12-19
I am having a hard time understanding TQuery and the Prepare method.
I have created a simple application that consists of 1 form with:

  a TButton named btnStart
  a TEdit named edtNumEntries
  a TQuery named qryGetInfo
      DatabaseName   = BCDEMOS
      SQL            = "SELECT * FROM Customer WHERE CustNo = :dbCustNo"
      UniDirectional = true

I have a loop that calls the query over and over.  
The query is prepared.  
It seems that I have to call ->Close() to change the parameter value.
Does calling Close "Unprepare" the query?
If I call close, it runs slow but works.
If I don't call close, it runs fast but finds no data.

Can someone please shed some light on this?  
What am I doing wrong (if anything)?

Thank you,

Note:  If you run this, make sure to enter a number > 1200 to find some data.

This is the code I run when btnStart is clicked (please note this is C++Builder code but the idea is the same as in Delphi)

void __fastcall TfrmMain::btnStartClick(TObject *Sender)
    TCursor Save_Cursor = Screen->Cursor;

    Screen->Cursor = crHourGlass;
    AnsiString strCompany = "";
    TStringList *lstCompanies = new TStringList();

    int NumEntries = StrToInt(edtNumEntries->Text);


        for (int i = 0; i < NumEntries; i++)
            qryGetSomeInfo->Close(); // <------ this is the line in question
            qryGetSomeInfo->ParamByName("dbCustNo")->AsInteger = i;

                strCompany = qryGetSomeInfo->FieldByName("Company")->AsString;
            catch (...)
                strCompany = "";

            if (strCompany != "")
                lstCompanies->Add(strCompany  + " at " + IntToStr(i));
        delete lstCompanies;
        Screen->Cursor = Save_Cursor;

Question by:joeslow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 27

Expert Comment

ID: 6385937
didn't understand the use of your parameter,
if you want all companies until the given id,
then why not use a statement like
SQL            = "SELECT * FROM Customer WHERE CustNo <= :dbCustNo"

just open once and iterate (next-method)  though the resultset until eof (property)

Author Comment

ID: 6386274
Please don't think this is an actual program.  This was just the smallest example I could put together to get to the root of a bigger problem I'm having.  I just used that table because most people who have Delphi/C++Builder have the table.  The point is, I'm inside a loop resetting the parameter of a prepared TQuery.  If I don't call Close before changing the parameter value, it doesn't work.  If I do call Close, it works but it's slow.

Thank you,

Accepted Solution

Stuart_Johnson earned 200 total points
ID: 6386447
You must close the TQuery to assign new values to the parameters.  However, from memory, you only need to actually prepare the SQL once.

As an example:

qryGetSomeInfo.Close; {Just make sure it's closed first}

for i := 0 to NumEntries do
    qryGetSomeInfo.ParamByName('dbCustNo').asInteger = i;
    {processing code here}
  end; {for i loop}

I'm not 100% sure of this, but I don't even think you need to call Prepare if you have created your parameters in the TQuery control and assigned types and directions in the Parameter Editor.  I usually only use prepare only when dynamically creating the TQuery and assigning the SQL at runtime.


Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 44

Expert Comment

ID: 6386770
Well this is what Borland has to say about it.


Sends a query to the Borland Database Engine (BDE) and the server for optimization prior to execution.

procedure Prepare;


Call Prepare to have the BDE and a remote database server allocate resources for the query and to perform additional optimizations. Calling Prepare before executing a query improves application performance.

Delphi automatically prepares a query if it is executed without first being prepared. After execution, Delphi unprepares the query. When a query will be executed a number of times, an application should always explicitly prepare the query to avoid multiple and unnecessary prepares and unprepares.

Preparing a query consumes some database resources, so it is good practice for an application to unprepare a query once it is done using it. The UnPrepare method unprepares a query.

Note:     When you change the text of a query at runtime, the query is automatically closed and unprepared.


I usually do it this way prior to all TQuery.Open methods.

if not TQuery.Prepared then TQuery.Prepare;

The Crazy One
LVL 44

Expert Comment

ID: 6386778
This is interesting as well



Determines whether or not a query is prepared for execution.

property Prepared: Boolean;


Examine Prepared to determine if a query is already prepared for execution. If Prepared is True, the query is prepared, and if Prepared is False, the query is not prepared. While a query need not be prepared before execution, execution performance is enhanced if the query is prepared beforehand, particularly if it is a parameterized query that is executed more than once using the same parameter values.

Note:     An application can change the current setting of Prepared to prepare or unprepare a query. If Prepared is True, setting it to False calls the Unprepare method to unprepare the query. If Prepared is False, setting it to True calls the Prepare method to prepare the query. Generally, however, it is better programming practice to call Prepare and Unprepare directly. These methods automatically update the Prepared property.

LVL 44

Expert Comment

ID: 6386786
In a loop I would use it something like this

for i := 0 to Something do begin
     if TQuery.Active then TQuery.Close;
     TQuery.ParamByName('TheField').asInteger = i;
     if not TQuery.Prepared then TQuery.Prepare;

Expert Comment

ID: 6387322
I think Stu is the closest here; if you Prepare a query, you can Close it and Open it as many times as you like it remains Prepared and the resultig execution time is significantly increased. If you are changing a parameter, as you state, you must Close the Query first then Reopen it which puts the new parameter value in place but does NOT affect the Prepared status of the Query. This works excellently for me.

As has been pointed out, one slight change to the SQL statement of the Query, however, and the query is no longer Prepared.


Author Comment

ID: 6389012
Thank you for the input.  I guess what I'm doing is right -keeping the close statement in.  It just doesn't seem to speed things up much.  I'm thinking now it may be better to run my query once, and just loop through the result each time I need to find a value.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question