Solved

Query in new component

Posted on 1998-02-06
20
277 Views
Last Modified: 2010-04-04
Hi,

I need to have a query in a component. The query is fixed and has two parameters. The user, must be able to select the table and the fieldname. The query must be build with these two values : eg.

 Select {FieldName}
 From {TableName}
 Where (Fieldname between :FromDate and :TillDate)

How can I do this?

Please, give a working example.

Regards, Zif.

10x in advance.
0
Comment
Question by:ZifNab
  • 10
  • 9
20 Comments
 
LVL 4

Expert Comment

by:itamar
ID: 1358495
Hi Zif !

I don't know if I got the point. What component you are talking about ?
If it's a data-access  why not construct the SQL on the fly ?

Smth like:

Query1.SQL.Add('SELECT ' + Edit1.Text + ' FROM ' + Edit2.Text + ' WHERE ' ..... and so on.

Sorry, if i missed something, but, please, give me more details and perhaps I could help further.

Regards,
Itamar
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358496
I want to make a component, and the user must be able to choose the table and the field. Well, choosing the field isn't a problem, but choosing a table? I don't know how to to this? You can't do it with choosing a datasource.
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358497
Hi itamar,
Here is some more information :

The component, needs to know which dates are available in a certain table in a certain range (one month). Ofcourse this month changes.
For this I've offcourse to iterate through the table. First I wanted to do this on BDE level. But I think it becomes difficult to find the first date of that range, iterating itself isn't that difficult.
But then I thought, why not making a query from that table? But for this I need to let the designer choose for a table. And I don't know how I can display such a list of tables available from the database. Look out, it's not the table name given by delphi. It's the table name given in the database.

Hope you can help me.
Regards, Zif.
0
 
LVL 4

Accepted Solution

by:
itamar earned 70 total points
ID: 1358498
Hi Zif !

Now I understand.

You must have a TSession object in your component. I'll give you an example in Listbox1-in-Form1-when-clicking-Button1 style and I'm very sure you'll do the rest:

Put a List box, a button and a TSession (DataAccess) in a Form.
And the code is...

procedure TForm1.Button1Click(Sender: TObject);
var Extensions, SystemTables : Boolean;
begin
      Extensions   := False;
      SystemTables := False; //Just to make things clear
      Session1.GetTableNames('DBDEMOS', '*.*', Extensions,                                SystemTables, Listbox1.Items);
end;

This will fill the list box with data tables of DBDEMOS database. I think you can do the same in your component.

Regards,
Itamar.

P.S.: This one was good, isn't it ? I'd be glad if you could increase the points ;-)))
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358499
Hi itamat,

This is from the Delphi help :

You cannot see nor explicitly create a TSession component, but you can use its methods and properties to globally affect an application. Delphi creates a TSession component named Session each time an application runs.

---> Do not attempt to create any other TSession or destroy and recreate Session itself. <----

What about it? May I create a Tsession in my component?

Maybe I've got a work around, if I may not creat a component, do you know how I can find a certain record in a table on BDE level?

Regards, Zif.

0
 
LVL 4

Expert Comment

by:itamar
ID: 1358500
Hi Zif,

I think there is no reason to worry. Notice that Delphi help talks about ANY OTHER TSession or destroy and recreate it.
I made a component called TTableList, that is a custom ListBox with an 'Active'property. When this property is set to True this list box will show all Tables in DBDEMOS database, just for example. I think it will work in your project.
This is the source code:

>>>

unit TableList;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
      StdCtrls, DB;

type
      TTableList = class(TListBox)
  private
        { Private declarations }
        FActive: Boolean;
        procedure SetActive(Value:Boolean);
      protected
        { Protected declarations }
  public
    { Public declarations }
        constructor Create(Owner: TComponent); override;
        destructor Destroy; override;
        property Active: boolean read FActive write SetActive;
  published
        { Published declarations }
      end;

var CSession : TSession;

procedure Register;

implementation

constructor TTableList.Create(Owner: TComponent);
begin
      inherited Create(Owner);
      CSession := TSession.Create(Self);
      CSession.SessionName := 'TableListSession';
end;

destructor TTableList.Destroy;
begin
      CSession.Free;
      inherited Destroy;
end;

procedure TTableList.SetActive(Value:Boolean);
begin
      If Value <> FActive Then begin
            FActive := Value;
            CSession.Active := Value;
            If CSession.Active Then
                  CSession.GetTableNames('DBDEMOS', '*.*', False, False, Items)
            else
                  Items.Clear;      
            Update;
      end;
end;
procedure Register;
begin
  RegisterComponents('Samples', [TTableList]);
end;

end.

Try it out...

Regards,
Itamar
0
 
LVL 4

Expert Comment

by:itamar
ID: 1358501
Hi Zif,

Now let's talk about the workaround.

" if I may not creat a component, do you know how I can find a certain record in a table on BDE level"

Find a record is easy, so I think I'm missing smth. What do you exactly means by BDE level ? What criteria is used to find this "certain" record ?

By...
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358502
Hi itamar, you sure get the point you want, because you stick with me.

Well here is my work around problem. (I think maybe this is faster also, because it doesn't uses a TQuery. But then I have to iterate through the existing dataset and I don't want to give some events (like onchange, etc....) For that I have to use DBE functions, like dbigetNextRecord...

What I want to accomplish is :

 designer selects a fieldname
 designer selects a datasource

Now, this fieldname can be a keyfield, or it can not be a key field. It can be the first field or the second, ... or the last from the table.

I know how to move to the next record in a table. I only need to know how I can move to the record with fieldname=a certain date. (This is quicker than iterating the whole table). Then I can use dbiGetNextRecord to move until the next certain date.

Regards,
Zif.
0
 
LVL 4

Expert Comment

by:itamar
ID: 1358503
Hi Zif,

at a first glancy, what comes to my mind is the Filter property of TTable.
You can set it as a Delphi expression at run-time. Something like:
      TTable.Filter := FieldName + '=' TheDate;
      TTable.Filtered := True;

I cannot check the syntax right now as I'm far from Delphi. But I think it's a good start-up. Tell me if you can make some progress with it.

Regards,
Itamar
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358504
Hi itamar,

If I use the work around, I don't use a TQuery component. If I set the filter of the Table, this would also effect all other dataaware controls attached to the datasource. This is just what I don't want. There must be a dbi... funtion which allows us to search for a record. I just don't know which one and how to get it to work. If you can find some examples, please say where i can find them.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:Ogunbo
ID: 1358505
Hey Zifnab,
I read your comments above and don't know if you are aware of a component called TQuickSelect.  The component does all the things you were asking for, plus more.  In addition, this component is free.  You can check a demo out at the address below:  
http://home.sol.no/~kbjerkne/QuickSelect
If you have further questions please let me know.
0
 
LVL 4

Expert Comment

by:itamar
ID: 1358506
Hi Zif,

sorry for the delay.

I think that the Locate method of TTable is the best way to find a specific record. Let's take a look at the Delphi help file:

"The Locate method locates and moves to the first record matching the supplied search criteria. KeyFields lists the field or fields you want to search; to search more than one, separate each field name with a semicolon. KeyValues is a variant specifying the field value to match, or an array of field values, if KeyFields lists more than one field. Options lets you specify case-insensitive or partial-key matching.
Locate uses the fastest possible method to locate a matching record; if the dataset has an index on the specified fields that's compatible with case-insensitive matching (if you specify that in Options), the index will be used. Otherwise, Locate sets up a Borland Database Engine filter for efficient searching.

Locate returns True if a matching record could be found or False if no matches were found."

And here is an example:

The following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":

var
  LocateSuccess: Boolean;
  SearchOptions: TLocateOptions;
begin
  SearchOptions := [loPartialKey];
  LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.',
    SearchOptions);
end;

If Locate finds a match, the first record containing the match becomes the current record. Locate returns True if it finds a matching record, False if it does not. If a search fails, the current record does not change.

IHTH,

Itamar
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358507
Ogunbo, did you make it? Very nice. But I don't want to make such a component. The users may not choose the query for themselfs, I only want the designer let the table choose and his field. That's all.

Itamar, through Locate is the best, but when I use this, it may not move the cursor of the database at all. I need it to go to the first field of e.g. a month. Then I iterate to the last day of that month. (So I know all the dates of that month in the database). But you see when locating and moving, the cursor of the table may not move !
0
 
LVL 4

Expert Comment

by:itamar
ID: 1358508
Hi Zif,

Hummm... I guess that you don't want to filter or move the cursor because you have some other component that shares the same datasource, am I right ? If so, couldn't be better to create a particular instance of the datasource just to search the table ?
Iterating trough a table means (to me)locating and moving a cursor...
And what about the TQuery solution ? Perhaps it's easier...

Regards,
Itamar

P.S.: If you want (and can) you can send me small example of the context. I think I'm still missing smth.
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358509
I'm going to try to formulate it in the best way I can.
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358510
This is what I want to accomplish :

I have a popup calendar component, and I want to make a sort of date lookup component.

So, the popup calendar may only display the dates which are inside the connected dataset.

What I had in mind :

 1. Calendar pops-up on a certain month :

     Component has a begin and end date of that month,
     searches through the dataset to the begin date.
     Iterates to the end date and makes the dates
     which are in the table selectable in the pop-up.

 2. Now, user can only see valid dates and select valid
    dates. If moves to next/previous month or next/previous
    year. Back to 1.

 3. User selects a selectable (valid) date, dataset moves
    to this date.

How I want to do this?

 1. with Query, must find a way to prepare query,
    that is, let designer choose table and field in which
    date is stored.
    --> makes it slow?
 2. Use the given dataset, but iterate and locate begin date
    without moving cursor, i.e. on dbi level.
    --> what if field with stored dates isn't a key field?

Any ideas?

10x Zif.


0
 
LVL 4

Expert Comment

by:itamar
ID: 1358511
Hi Zif,

I still think that the TTable approach is the best one. Why ? My reasons. With the TSession.GetTableNames and TTable.FieldDefs (limiting fields to Date type)  you can provide a way to the user choose the data to be searched. Regarding to performance, don't expect good results from any Dataset (TQuery or even TTable) if you don't have an index to the selected field.
There is no magic ! If you have a large table, without an index, the database engine will have to search the entire file selecting the records that match a given criteria.

I think the best results will be obtained using a little trick that  takes advantage of date indexes.

1. Make a loop from initial date to final date of the month that will be showed;
2. Use Locate method to find out if there is at least one record in each date of the loop.

This way you will have a consistent response time, on despite of the size of the table, because only 30/31 "reads" will be done. Of course it's not a good approach to small tables, but it's very efficient with large ones.
But, remember: if there isn't an index or a sorting step the time response can be very annoying in this kind of component.

Imagine a Table with 1,000,000 records. The 1st and the last one are in the date range, in reverse order, and there is no indexes. How can a database engine deals with that ? I know it's a extreme example, but even easier situations will cause problems.

I think it's easy to test my trick with your real data, so You can have a feeling of the results. BTW, are we talking about EMR - Eletronic medical records ;-))

Hei, Zif your component looks like very interesting. Don't give up ! I'd like to exhaust this question with you, and after that, if you could send me a sample of the ready component I would appreciate.

Regards,
Itamar

0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358512
I've been some busy outside the hospital, sorry for my late answer.

Hi itamar,

EMR, nope, it's fysical quality control records.

I still need to find a way to locate the date without notifying a change in record. I think this is only possible with BDi....
I've one other problem : How to make a List in a component? i.e. let the designer choose from the available tables and available date fields. How to I have to declare such a property?


0
 
LVL 8

Author Comment

by:ZifNab
ID: 1358513
Hi itamar,

I haven't worked on it anymore, it wasn't so urgent... But thanks for helping me out. I'm going to grade this question now, so you get the point you diserve. It can be that I 'll send some comments to this thread again if I'm going to work on it again. Hope you'll help me then too.

Regards,
Zif.
0
 
LVL 4

Expert Comment

by:itamar
ID: 1358514
All right Zif.

Regards,
Itamar
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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