[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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


What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

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!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

649 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