Solved

How to use TQuery

Posted on 1997-07-27
26
395 Views
Last Modified: 2012-05-05
Before I get into details, these are the three questions I want answered;

1   How do I link a Query to DB controls, ie DBText, Combo, grid, etc?
2   How do I pass variables/parameters to the Query?  I would want to pass values from various non-DB controls -text, combo, etc.
3   How do I execute this query once it receives its values and displays its results - in another form?  I would want to start the query from a button.

Details

I want the user to filter thier view by entering values in various controls (not DB controls) on one form and see the results in another.  What ever values they enter the calling form's controls should be used in the Query and displayed in the results form.  I only want values in the calling form that are NOT blank to be passed to the Query.  I am not using a datamodule, however, the tables, datasource and controls are on the results form.  I'm using D2 on Paradox tables. Note: I also want to do field comparisions in the Query, like Field 1 > Field 2 - or - >= (some value) and <= (some value).
0
Comment
Question by:d4jaj1
  • 11
  • 10
  • 2
  • +3
26 Comments
 

Expert Comment

by:kimfriis
ID: 1340036
Q1: You link it up thru a DataSource. eg.: DBText.Datasource := DataSource1 ; DBText.DataField := 'Myfield';
(I don't remember if the DataField is called datafield but I think so)

Q2: I believe that the easiest way to do that is to make the SQL string just before you execute the query. In your case anyway. You do it like this:
with Query1,Query1.SQL do begin
  Close;
  Clear;
  Add('Select * from customers');
  if (condition1) then
    Add('where custno='+mycustno);
  else
    Add('where orderno='+myorderno);
  Open;
end;
And here mycustno and myorderno are variables with the wanted values. What we do is, first we close the Query, then we fill in the SQL, and then we open the query. Look at the with statement for a little tip.

That should also be the answer to the 3. question.

If you do not want to build your query on runtime you can build it using the designer and then where you want your variables your place a :myvariable (The name) and on runtime your use:
query1.close;
query1.parambyname('myvariable').asstring:='test';
query1.Open;

Tjeck it out.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1340037
There are examples in the demos directory.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340038
I'm in the process of re-installing D2 because my disk got currupted.  Once I receive the new disk from Borland, I'll re-install the software, try your suggestion, then grade it.  Until then, I did have a few questions/clarifications.

In response to your Answer #1, the Query component is not listed as an option for any of the DB Controls, only the Table.  So again, how do I run the query and display its results in the form's DB controls (link them to the query)?

Secondly,  I also wanted to know how to do field comparisions in the Query, like Field 1 > Field 2 - or - >= (some value) and <= (some value).  I read in Help somewhere this type of comparision  is not supported on Local tables.  Is this true, and if so, how do I get around it

0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1340039
In a previous question of yours about filtering a table, I have
written an example of how to make a query, using the filter method. By the way I think the filter method is supposed to work faster then SQL becuase it uses internal operations in the database engine.
0
 
LVL 1

Expert Comment

by:Zonnald
ID: 1340040
with regard to the proposed anwser -

what about associating the Datasource to the Query just as you do for a table to get access to the tables fields - so do you get access to the Query's fields

Hope this helps a little

Zonnald
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340041
I finally got Delphi re-installed.  I apologize, I can see the query's fields and attach them to the DB controls once I add the table name to the SQL statement.

I also tried you example of assigning values to the query.  My code is below.

var
strET:    string;
begin
strET := movie.text;
with Query1,Query1.SQL do begin
Close;
Clear;
add('SELECT * FROM activities WHERE ');
if movie.text <> ''then Add('EntertainmentType=' + '"' + strET + '"');
Open;
end;
end;

The code above adds a statement to complete the WHERE clause if the movie textbox is NOT blank.  This works fine for one variable, but I need this same type of logic for this and 9 other fields, all at the same time.  For instance, if I wanted to use this logic on 3 fields, the SQL sintax would be incorrect because of the missing commas needed to separate the WHERE clauses.  Of course I could add the comma in the IF statement, but I would run into errors if the IF statement returned false (too many commas).  

So I tried to implement your second suggestion and add parameters to the query.  When I bring up the define parameter dialog box, none of the 3 input boxes are enabled.  Why can't I define the parameters?

I'm thinking, once I can define the parameters, I could write IF statements that pass values to it only if the text boxesaren't empty.  This should take care of the blank field issue, as well as, the commas issue above.  Make sense?
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340042
I need an answer to this as soon as possible.  Since I have not received a response from kimfriis's since the initial answer, I will open the question up for anyone else If I don't receive an answer by Wednesday.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340043
As mentioned in my last Coment, my question is simply - how do I pass multiple values to the Query.  The answer above only works for passing one value, not multiple ones.  The parameter option doesn't seem to work for me since I can't add any parameters at design time to accept variables at runtime (the fields aren't enabled).
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340044
Okay, I figured out how to use the Parameters option one my own.  Now my problem is, how do you get the Query to ignore an empty value in the parameter field?  In my app, I want to pass values to the Results form's Query if the corresponding Criteria form's field isn't blank.  It's easy enough to pass the value to the parameter if the Criteria field isn't blank, but the query will still search for the Value even if I don't pass one from the Criteria form (which is an empty string).  For example, I have two parameters City and State. If the user only entered data in the State field of the Criteria form - I would only pass the value to the State parameter - However, the TQuery would still search for "the State value" and "Blank" in the State and City fields.  How do I get the Query not to search for the parameter if the value is blank.  I'm using mostly string fields.

0
 

Accepted Solution

by:
boabyte earned 100 total points
ID: 1340045
There are two ways to ignore Parameters in SQL (that I know):

1.The SQL property of a TQuery is of type TStrings. What you can do is just prior to executing the Query is test for an empty parameters. The idea is to have two queries ready to go (one using parameters one without) and add the appropriate one into the SQL property. (this may be a hassle with many parameters)

2.If its mainly strings you are testing for then you can set your
parameters to accept all records. for <= >= type filters set your lower param to "A" and upper param to "z" (or appropriate boundries) to accept all strings. For = filters then you should use % signs in your params (eg '%'+StrET+'%') I know this works using the LIKE command, not sure about =, but you should be able to work around it.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340046
Hi d4jaj1,

I think, that the proposed answer of boabyte is a solution, but you can also use the INSERT statement.

Like this :

{from the database application developer's guide p 113 chapter 9}

For example, suppose a query component named CountryQuery has the following statement for its SQL property:

 INSERT INTO COUNTRY (NAME, CAPITAL, POPULATION)
  VALUES (:name, :capital, :population)

At run-time, your application could specify values for each of these parameters as follows:

 CountryQuery.ParamByName('name').AsString := 'Lichtenstein';
 CountryQuery.ParamByName('capital').AsString := 'Vaduz';
 CountryQuery.ParamByName('population').AsInteger := 420000;

I think this is a sollution for your problem. Before you execute the SQL, check which values are given. Then use the INSERT statement to insert the parameters which are given. And then execute your SQL. Sorry, but haven't tried this out. So you have to try it yourself if it works. Please, let me know something, if it does.

About Q1:

 I think I've lost the clue about this question. You can compare fields in a query. Like this :

 if DataSource.Query1.FieldByName('{Name}').AsInteger >=
 DataSource.Qurey1.FieldByName('{Name}').AsInteger then
  showmessage('First is bigger than or equal to Last field');

But, I don't think that's an answer to your question. If the proposed answer of boabyte doesn't solve your problem. Please refine your question.

I see you're working with query's, maybe this is interesting information for you, go at look at the OOPSite site. They gave there SQL-builder (SQL-express) away for free. I don't know if they still do, but it's worth it!

Have fun,
Questions? just ask!
c.u. ZifNab;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340047
I like your second suggestion, because I have many parameters.  I think the concept makes sense too, e.g., set the parameters for each field to either the value I pass from the calling form or some 'get all records' criteria.

It sounds like the easiest way for me to do this is to set all of the parameters values to this 'get all records' criteria at design time, then change them at runtime with something like;

Query1.ParamByName('EntertainmentType').AsString := StrET;

Is there a way to set the 'get all records' criteria above at runtime , I get errors or no data if I try in the parameter editor?  If not, I'm not sure where/how I would use the % example you gave me.  Most of my query events use = logic, not LIKE or <=, >=.  From your message, I gathered I should use something like below;

procedure TForm2.Button1Click(Sender: TObject);
var
strET:    string;
begin

strET := movie.text;

query1.close;
if movie.text <> '' then
  begin
   Query1.ParamByName('Entertain').AsString := StrET;
  end
else
   Query1.ParamByName('Entertain').AsString := '%'+StrET+'%';
query1.open;
end;

This only works if there is something in the Movie field. If it's blank, the query runs & returns 0 records.  What am I doing wrong?

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340048
Hi d4jaj1,

I don't think you can use the % option, because with % you use the LIKE statement. And then the Query will look for all the data which looks like the given string. For example, if you give a 'N' in movie, I think every record with a 'N' in his movie string will be given. Now if you give ' ', nothing will be given, because no record has a ' ' in its movie string.

Like here :

Only those customers will be shown who works at a company which name looks like the name given in Edit1.Text

Select * From customer

Where company like :CompanyName

procedure TForm1.Button1Click(Sender: TObject);
begin
  with Query1 do
  begin
    Close;
    ParamByName('CompanyName').AsString := Edit1.Text + '%';
    Open;
  end;
end;

Think you have to try it, like I've earlier said.

Hope I could help you,
Have fun,
c.u. ZifNab;


0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:ZifNab
ID: 1340049
Hi d4jaj1,

I don't think you can use the % option, because with % you use the LIKE statement. And then the Query will look for all the data which looks like the given string. For example, if you give a 'N' in movie, I think every record with a 'N' in his movie string will be given. Now if you give ' ', nothing will be given, because no record has a ' ' in its movie string.

Like here :

Only those customers will be shown who works at a company which name looks like the name given in Edit1.Text

Select * From customer

Where company like :CompanyName

procedure TForm1.Button1Click(Sender: TObject);
begin
  with Query1 do
  begin
    Close;
    ParamByName('CompanyName').AsString := Edit1.Text + '%';
    Open;
  end;
end;

Think you have to try it, like I've earlier said.

Hope I could help you,
Have fun,
c.u. ZifNab ( Who's almost on vacation ;-) );


0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340050
I don't want to use LIKE operations because it could give me some erroneous data.  For instance, doing this on a CITY field where edit1.text = 'Atlanta' would give me 'Atlanta' and 'Atlantic City', etc.

It's becoming apparent (now that I understand a little more about Queries) the parameter or the query isn't my problem - it's building the SQL string itself at runtime.  Sure, I can add the parameters to the query if necessary, but how would I pragmatically add the necessary WHERE statements to the Query for multiple values (see me comma problems above)???????  I could add a WHERE statement for every field in the table (or at least the ones I allow searches on) to the Query at design time with the associated parameter, but then I how would I get it to run correctly if I don't pass the query a parameter (i.e. edit.text, etc was blank)???????????

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340051
Hi d4jaj1,

I think I've found a solution. It's very bulky and I think there must be another way, but I think this should work.

First, make your Query at designtime with all your where's and parameters, etc...

Than, before executing the Query :

 First fill in all parameters with the value of the first record
 and with the value of the last record. These records you can  find from a Query or Table, with the same information (records)  as your  Query with parameters.

 Than look what the user definied for values. If Value is  different than ' ' or somethink likewise. Than change the  parameter with this given value.

 After that Excute the SQL.

 It's almost the same as boabytes idea, if it isn't the same!
 But it works.

c.u. ZifNab;
Have fun!
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340052
I'm not following you.  Examples?  and your right, it does seem bulky.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340053
Let's see,

Here it comes :

1. Two Query's

 * QParameters with SQL :
 
     SELECT D.SiteNumberID, D.Date, D.Time, D.Reference_mAS,             D.Reference_OD, P.Contrast,
            P.DMax, P.DMin, P.Gamma, P.MGrad, P.Speed, P.TGrad
     FROM tDaily D, tParameters P
     WHERE (D.SiteNumberID=P.SiteNumberID AND D.Date=P.Date AND             D.Time=P.Time)
           AND (D.Date>=:BeginDate AND D.Date<=:EndDate)
     ORDER BY D.SiteNumberID ASC, D.Date ASC, D.Time ASC
   
 * QTotal with SQL : Every the same, but without parameters

       SELECT D.SiteNumberID, D.Date, D.Time, D.Reference_mAS,                              D.Reference_OD, P.Contrast,
              P.DMax, P.DMin, P.Gamma, P.MGrad, P.Speed, P.TGrad
       FROM tDaily D, tParameters P
       WHERE (D.SiteNumberID=P.SiteNumberID AND D.Date=P.Date AND                             D.Time=P.Time)
       ORDER BY D.SiteNumberID ASC, D.Date ASC, D.Time ASC

2. User pushes a button after he filled in the values
   (In this example values are given for BeginDate and EndDate)
 
  QTotal.Active := true;
  QParameters.Active := false;

  { Initialise all parameter values }
  QTotal.First;
  QParameters.ParamByName('BeginDate').AsDateTime :=
    QTotal.FieldByName('Date').AsDateTime;
  QTotal.Last;
  QParameters.ParamByName('EndDate').AsDateTime   :=
    QTotal.FieldByName('Date').AsDateTime;
  QTotal.Active := false;

  { At this moment, if you run QParameters -> All records are     shown }

  { fill in chosen range, if any given by user }
  if EditBeginDate.text <> '' then
    QParameters.ParamByName('BeginDate').AsDateTime :=
     StrToDate(EditBeginDate.text);
  if EditEndDate.text <> '' then
    QParameters.ParamByName('EndDate').AsDateTime :=
     StrToDate(EditEndDate.text);
  QParameters.Prepare;
  QParameters.Active := True;

Hope this is clear enough. Hope this gives a solution for you, because otherwise it was a waste of time.

Let me here something of you, soon!

Have fun,
c.u. ZifNab;

0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340054
I'm going to try this in about a hour or so, I have to actually do some work at WORK.  But let me warn you, my table is pretty large and it ran pretty slow (6-7 seconds) whne just querying one value.  If I now have to do TWO queries on the same table - I'm looking at 12-14 seconds or more.  Think about that for a moment - you click a button about about 15 SECONDS later something happens!  I can't say my customers would appreciate that. So even it it works from a process prespective, it won't pass any usability standards, thus I can't use it.  There got to be a better way - and maybe it isn't a query.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340055
Hi d4jaj1,

I know it's time consuming, but you don't have to use 2 Query's!.
QTotal can also be the table where the query originates from. The code then becomes :

{Let TableTotal be the original table)

TabelTotal.Active := true;
QParameters.Active := false;

{ Initialise all parameter values }
 TabelTotal.First;
 QParameters.ParamByName('BeginDate').AsDateTime :=
     TabelTotal.FieldByName('Date').AsDateTime;
 TabelTotal.Last;
 QParameters.ParamByName('EndDate').AsDateTime :=
     TabelTotal.FieldByName('Date').AsDateTime;
 TabelTotal.Active := false;

Maybe use a progress-bar to show progression of Query?

Have fun,
c.u. ZifNab;
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340056
Just question,

if you only use one table (all values are in one table) why do you use then a query? Just use that table and use SetRange for filtering your table.

SetRange(const StartValues, EndValues: array of const);

But then again, with Paradox you can only use SetRange on indexed fields.

Have fun,
c.u. ZifNab;


0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340057
To be honest, I really don't want to use a Query (it's slow) but it seems to be the only way I can do what I want.  I have that perception because no one else has told me better.  At best, I'm a novice Delphi programmer.  If look at the questions awaiting answers - I have three questions regarding how to display values entered on one form that should appear one another, This one, How to filter a Table - 7/10 and Filter by Form 6/3?.  

Maybe to help people understand what I want, I should send who ever wants to answer these questions the small dpr-pas forms so they can 'visually' see what I'm trying to do.  At least that way, I won't get a lot of good answers that don't answer the question I asked.

And yes, I did try the SetRange method, see How to filter a Table - 7/10 for my errors.

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340058
Sorry, I apologize, didn't wanted to offend you. I'm also a beginner in Delphi.
But is my comment (Change one Query with one Table) not reducing the time? Or is that answer not what you want?



0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340059
Hi d4jaj1,

I think I found a solution, quit simple I guess, look at 'How to filter a table'.

It seems you can do this just with a table!!!!

Please, give me an answer.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1340060
I haven't ruled out your answer above, I just haven't gotten a chance to try it out yet.  It seems like a lot of work for two fields, especially since I have many more - but I will reserve judgment until I actually go through the code.

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340061
Isn't this question answered by my example i've sended you? If their other problems, please send them to me, by e-mail, or just redefine them here because I think all questions are already answered.

Have fun,
c.u. ZifNab;
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

20 Experts available now in Live!

Get 1:1 Help Now