?
Solved

2  Simple Query Questions

Posted on 1997-09-19
36
Medium Priority
?
204 Views
Last Modified: 2010-04-04
First, I'm having problems trying to use SQL with the TQuery object because my table's field name is the same as (I guess) some SQL code.  My code is, Select * from Activity where ((Activity.Check)=True));  Also, I'm having problems qquerying fields that have spaces in the field names.  For example, I get an invalid token when this string - SELECT Table1.(hh Name), Table1.[First Name] FROM Table1
ORDER BY Table1.(Last Name], Table1.[First Name];
How do I use the query correctly with these strings?

Secondly, how do I use the progressbar with a Query?  I want to move the progress bar by the number of records the encounters until the query is finished.
0
Comment
Question by:d4jaj1
[X]
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
  • 18
  • 14
  • 3
  • +1
36 Comments
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345591
Edited text of question
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345592
Hi d4jaj1,

Q1 : I don't know but maybe this helps. Try it out on your code.

Select A.*
from Activity A
where ((A.Check)=True));

Q2 : They always recommended not using spaces in a field name. So maybe the best solution is to redefine your fieldnames, also for the compatibility with other database structures. Because I believe that some databases don't let you use spaces in their field-names. You can use the underscore char instead of spaces. Then your fieldname will still be readable. like this : First_Name, Last_Name, ...

Q3 : Fill in the max-property of the progressbar the total number of records. Then use the property OnDataChange of your datasource to progress the bar.

Hope I could help you with this.

Have fun,
ZifNab;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345593
Q1 - That didn't work

Q2 - Any easier way?

Q3 - The problem is I can't set the max property to the query's recordcount until AFTER it is opened.  Once it's already opened, the ondatachange event already occurs.  Then it does something really strange, the progressbar goes from beginning to end continuously & I can't stop it unless I return to Delphi & hit 'program reset'  

Also, did you get my e-mail regarding the 2 filter questions and my additional Tquery question?
0
Technology Partners: 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!

 
LVL 8

Expert Comment

by:ZifNab
ID: 1345594
I'm going to look at it closer. But that's going to take some time. So, reject my answer, so another expert can give you an answer  as he knows it.

Have fun,
ZifNab;
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345595
Hi d4jaj1,

Q1 : think it's not the fault you think. Try this :

 Select *
 from  A
 where [Check];

 or Select *
    form Activity
    where NOT[Check];

 It should work now.

Looking further to the other 2 questions....

ZifNab;

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345596
Q2: don't know, think not.

Q3: More difficult then I thought.
    With a table it's easy -> OnFilter event.
    In Query : you can define a OnDataChange event when you
    need it, and when you don't assign nil to it. But offcourse
    the recordcount is one of the big problems.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345597
Don't know if you were waiting for me to add a comment so I'll add one anyway.  The first two questons are less significant than the last one.  I really need the last one answered since my user might have to sit through the query (3000 + records) without any idea of when it's going to end.  I've added points to the question because I really need an answer to this one.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345598
Sorry, I'm very busy. The European comunity soon will check the progres of my program, so I haven't much time to do other things.

Q1 : doesn't this work?

Select *
from Activity
where [Check];

                      or Select *
                         form Activity
                         where NOT[Check];

Q3 : I know this sounds silly and it's not really solving the problem. Do you really need a progress bar? Why not just a activity meter? Like the hourglass of windows? It's much easier to implement and it also let the user see that the computer is working on the database.
     I found a progress bar (freeware) but haven't tested it yet. I'll test it later. But you can test it too. It's in the component pack called RX Library. Lot's of cool components, have a look.
I look at it this weekend, and I'll let you monday now, what I've found.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345599
Sorry, too busy.

Did you tried to use RX library?

What do you think about an activity meter?
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345600
Nope, that component wouldn't drive the progress bar (it returned text messages) and it wasn't freeware.  I did find code in the Delpi TI help file called BDE Callbacks.  It was intended for batchmoves, thus I tried to configure it for the query. It only steps once for me.  Maybe you can tell me where the error in the code is.

var
  CbDataBuff: CBPROGRESSDesc; {DBi Structure}
  OldDbiCbInfo : TDbiCbInfo;  {data structure to save previous
                               callback info}
begin
 {Make sure the table we are moving from is open}
//  Table1.Open;
 {make sure the table we are batch moving to is closed}
//  Table2.Close;
 {get info about any installed callback}
  DbiGetCallBack(query1.Handle,
                 cbGENPROGRESS,
                 @OldDbiCbInfo.iClientData,

                 @OldDbiCbInfo.DataBuffLn,
                 @OldDbiCbInfo.DataBuff,
                 pfDBICallBack(OldDbiCbInfo.DbiCbFn));
 {register our callback}
  DbiRegisterCallBack(query1.Handle,
                      cbGENPROGRESS,
                      longint(@OldDbiCbInfo),
                      SizeOf(cbDataBuff),
                      @cbDataBuff,
                      @DbiCbFn);

  Form1.ProgressBar1.Position := 0;
//  BatchMove1.Execute;
  query1.open;

 {if a previous callback exists - reinstall it else}
 {unregister our callback}

  if OldDbiCbInfo.DbiCbFn <> nil then
    DbiRegisterCallBack(query1.Handle,
                        cbGENPROGRESS,
                        OldDbiCbInfo.iClientData,
                        OldDbiCbInfo.DataBuffLn,
                        OldDbiCbInfo.DataBuff,
                        OldDbiCbInfo.DbiCbFn) else
    DbiRegisterCallBack(query1.Handle,
                        cbGENPROGRESS,
                        longint(@OldDbiCbInfo),
                        SizeOf(cbDataBuff),
                        @cbDataBuff,
                        nil);

0
 
LVL 1

Expert Comment

by:dvaline
ID: 1345601
Q1.  Try putting reserved and fields/tables containing spaces, slashes, other special characters in quotes, or aliasing (watch out for SQL compliance) the table

Select * from "Activity" as MyTable
Where (Mytable.FieldName = "Something")
and
(MyTable."Another Field" = "Something Else")

Q2.  Unfortunately there is no easy answer to this question.  Personally I would Assign the MaxValue property to a number I knew to be greater than the recordset and then increment by a fraction of that number. (Using the OnDataChange Property).

But I guarantee that the first should take care of any problem you have with the first query.  (I accidentally named a table "transaction", dough, I learned my lesson)
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345602
I tried to set an arbitrary number as the query's record count (progressbar's MAX) and called the Stepit on the query's ondatachange.  On eof two things happen - 1) the progressbar doesn't step until after the query is completed - then only once 2) the progressbar goes from beginning to end for every single record.  If you can give me a code example of how to step the progressbar correctly with the query, using any number as the recordcount, I'll award the points.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345603
I also tried some this with the progressbar. I coudln't find a solution. Still, why don't you just use an activity-meter? (please, say why you don't like such a meter) It would save you a lot of thinking work, right at the moment.
c.u. ZifNab;
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345604
d4jaj1, do you still use a filter command on your query? then you could use OnFilterEvent. That works!
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345605
The OnDateChange Event won't work, because only at the end of the query the ondate event will be triggered.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345606
No, thi sparticular filter doesn't use a filter.  Anyone know how to modify the BDE Callback prodecure I have above?  My guess is, if it worked for a batchmove, it should work for a query.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345607
Question (BDE CallBack) how does code knows that he has to progress progressBar1?
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345608
I forgot to add the Function itself.  Pretty dumb huh?  In any case, I didn't see your comment about the activity meter.  I'm not sure what you mean by activity meter, but it sounds like you mean Hourglass or some 'Please Wait Message'.  The problem with these types of methods is they don't tell the user where they are in the process, especially for long database operations.  It's sort of like riding the bus or train.  You know your going to get there eventually, but if you don't have nay idea of how long it's going to take, it makes the ride SEEM longer.  Here's the function;

function DbiCbFn(ecbType     : CBType;
                 iClientData : Longint;
                 CbInfo      : pointer): CBRType stdcall;
var
  s : string;
begin
 {Check to see if the callback type is what we expect}
  if ecbType = cbGENPROGRESS then begin
   {if iPercentDone is less that zero then extract the number}
   {of records processed from szMsg parameter}
    if pCBPROGRESSDesc(cbInfo).iPercentDone < 0 then begin
      s := pCBPROGRESSDesc(cbInfo).szMsg;

      Delete(s, 1, Pos(': ', s) + 1);
     {Calculate percentage done and set the progress bar}
      Form1.ProgressBar1.Position :=
        Round((StrToInt(s) / 2500 {Form1.Table1.RecordCount}) * 100);
        Form1.ProgressBar1.stepit;
    end else
    begin
     {Set the progress bar}
      Form1.ProgressBar1.Position :=
        pCBPROGRESSDesc(cbInfo).iPercentDone;
    end;
  end;
 {was there a previous callback registered}
 {if so - call it and return}
  if PDbiCbInfo(iClientData)^.DbiCbFn <> nil then
    DbiCbFn :=
      pfDBICallBack(PDbiCbInfo(iClientData)^.DbiCbFn)
        (ecbType,

         PDbiCbInfo(iClientData)^.iClientData,
         cbInfo) else
    DbiCbFn := cbrCONTINUE;
end;


0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345609
I've found it on TI. When you put a breakpoint in DbiCbFn, how many times is this function called? And what are the values of the variables?
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345610
cbGenProgress is used for large batch operations. I don't think it's usefull for your purpose. Look for explanation at the BDE32.hlp file. Do you use paradox tables? Try using cbTableChanged
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345611
Sorry, can't get it to work either. But was just browsing on a delphi site and found a freeware (really, this one is!) which claims it works like you want (At least thats how I interprete it, my english isn't so good). Here is a piece of the text file:

The component is best called from an OnDataChange event for a TDataSource component.
Set the Dataset Property for the Dataset this can be done within The OnDataChange event for the TDataSource component in case your are using multiple dataset's, i.e SQL commands and Tables.

Then call the function UpdatePosition, e.g.
 DBPos1.Dataset := DataDB;      {DataDB is a TTable Component}
 DBPos1.UpdatePosition;      {This will update the control and relevant values}

I think you can use this for progress, if offcourse the ondatachange event works :

 ftp.coriolis.com/pub/controls/dbpos.zip

Well, I'm of now, have a nice weekend!
c.u. ZifNab;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345612
Sorry, this component doesn't work either.  It's a D1 component & I get a unsupported 16 resource file error.  The author's email is no longer valid (file date of 11/95).  Yes, I am using paradox tables & I'm not really sure where cbTableChanged should go.  I don't understand teh code I gave, I just copied it from teh TI file & hoped it would work.
0
 
LVL 1

Expert Comment

by:dvaline
ID: 1345613
There really isn't an easy way to use the progress bar with the recordset (TQuery) object.  Why don't you use an animated sprite, much like the microsoft delete to recycle bin.  You can pick up the TAnimatedSprite component at any of the archives.  

Also, make sure you use application.processmessages to allow the windows message to process your increment, since it is visual based, otherwise your query's progress will take up the system time that you'd like to be using to update the visual progress piece.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345614
Does teh TAnimatedSpirt have some sort of a progress indicator?  If not, that's not the naswer I'm looking for.  Otherwise, the question remains, how do I increment the bar/gauge?
0
 
LVL 1

Expert Comment

by:dvaline
ID: 1345615
Ok, you are not going to get a progress indicator for you're query, without incurring the time of running a simular query.  My suggestion is to not use the guage and get off the progress thing, it's not a good idea, or sound programming practice.  If you use the animated sprite you can show progress without telling you're user exactly where they are.  

You are not going to be able to attain your desired result.  There are some battles best left un-fought.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345616
Spoken as a true developer. Don't take this grade personally, if you look at my grading history, I always give A's.  My customer requests this functionality, therefore, it HAS to happen.  Besides, I've seen a Sharware component do this - it's simply not worth $39 to me.  Thanks anyway.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345617
d4jaj1,
If it's only a resource error, then you can solve the problem. Do it like this. Load the resource into the image editor. Then save it back -> now it should be a compatible resource file. RES D1 & RES D2 aren't compatible so you've to do it this way.

cbTableChanged should go where cbGENPROCCESS stands. LOOK at BDE32.hlp files as I earlier said, that explains a lot! By the way, RXlib is freeware, saw it on a site where they said it is the BEST FREEWARE Library.

Have fun,
c.U. ZifNab;
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345618
ZifNab,

What site did you find RXlib?
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345619
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 1345620
ZifNab,

Thanks for the link.  This is a suite of components and one of them get BDE callback information automatically.  You simply assign a gauge or progress to the component and it increments.

If you want, answer the question & I will grade it.
0
 
LVL 8

Accepted Solution

by:
ZifNab earned 200 total points
ID: 1345621
A component which does this exists in the freeware RX Library:

http://rx.demo.ru 

Ok, I answer the question, but I'm confused. In one of the comments above I already related to this library. {...I found a progress bar (freeware) but haven't tested it yet. I'll test it later. But you can test it too.  Component pack called RX Library. Lot's of cool components. It's in the components, have a look....}
But your answer was that the component didn't work and wasn't freeware. But now, it looks like it's the first time you use these components??

Well, the problem is solved, so we don't have to think about it anymore.

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

Author Comment

by:d4jaj1
ID: 1345622
I think I was refering to another mail message you had sent directly to me (not on this site).  Something called dnProgress. Thanks.
0
 

Expert Comment

by:douglascardoso
ID: 4232455
it seems more a contest among mates, rather than expert solution...
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 4278145
I don't like such accusations...
Which delphi problem do you have? Tell me and i'll try to provide you with an answer.
0
 
LVL 3

Author Comment

by:d4jaj1
ID: 4280785
Hey Zif,

Long time no see.  I can't believe this was 3 years ago - my how time flys.  Hope things are going well for you.

Doug,

Nevermind the friendly chatter on this thread, Zifnab has been one of the most helpful individuals on this website.  RxLib turned out to be teh answer (if I rememeber correctly), so he got the points.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 4281549
yeah, time flies, haven't been lately on EE. (you can see that also on my rank-position :-))). Regards, zif.
0

Featured Post

Technology Partners: 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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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