TQuery and Prepare questions

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;

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Stuart_JohnsonConnect With a Mentor Commented:
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.


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)
joeslowAuthor Commented:
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,
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
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.

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;
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.

joeslowAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.