[Webinar] Streamline your web hosting managementRegister Today


TQuery and Prepare questions

Posted on 2001-08-14
Medium Priority
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
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 800 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.


Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

612 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