Solved

2  Simple Query Questions

Posted on 1997-09-19
36
185 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
  • 18
  • 14
  • 3
  • +1
36 Comments
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
Edited text of question
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Question (BDE CallBack) how does code knows that he has to progress progressBar1?
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ZifNab,

What site did you find RXlib?
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
0
 
LVL 3

Author Comment

by:d4jaj1
Comment Utility
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 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
it seems more a contest among mates, rather than expert solution...
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
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
Comment Utility
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
Comment Utility
yeah, time flies, haven't been lately on EE. (you can see that also on my rank-position :-))). Regards, zif.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

11 Experts available now in Live!

Get 1:1 Help Now