Solved

TQuery and Prepare questions

Posted on 2001-08-14
8
1,725 Views
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,
Joe

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

    try
    {
        qryGetSomeInfo->Prepare();

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

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

            if (strCompany != "")
            {
                lstCompanies->Add(strCompany  + " at " + IntToStr(i));
            }
        }
    }
    __finally
    {
        ShowMessage(lstCompanies->Text);
        qryGetSomeInfo->Close();
        qryGetSomeInfo->UnPrepare();
        lstCompanies->Clear();
        delete lstCompanies;
        Screen->Cursor = Save_Cursor;
    }

}
0
Comment
Question by:joeslow
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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)
0
 

Author Comment

by:joeslow
Comment Utility
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,
Joe
0
 
LVL 6

Accepted Solution

by:
Stuart_Johnson earned 200 total points
Comment Utility
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}
qryGetSomeInfo.Prepare;

for i := 0 to NumEntries do
  begin
    qryGetSomeInfo.ParamByName('dbCustNo').asInteger = i;
    qryGetSomeInfo.Open;
    {processing code here}
    qryGetSomeInfo.Close;
  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.

Cheers,


Stu.
0
 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
Well this is what Borland has to say about it.

QUOTE

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

procedure Prepare;

Description

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.

UNQUOTE

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

if not TQuery.Prepared then TQuery.Prepare;


The Crazy One
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
This is interesting as well

QUOTE

TQuery.Prepared

Determines whether or not a query is prepared for execution.

property Prepared: Boolean;

Description

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.

UNQUOTE
0
 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
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;
     TQuery.Open;
end;
0
 
LVL 4

Expert Comment

by:jsweby
Comment Utility
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.

J.
0
 

Author Comment

by:joeslow
Comment Utility
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.

Joe
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now