Query in new component

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.
LVL 8
ZifNabAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itamarCommented:
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
ZifNabAuthor Commented:
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
ZifNabAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

itamarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZifNabAuthor Commented:
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
itamarCommented:
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
itamarCommented:
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
ZifNabAuthor Commented:
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
itamarCommented:
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
ZifNabAuthor Commented:
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
OgunboCommented:
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
itamarCommented:
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
ZifNabAuthor Commented:
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
itamarCommented:
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
ZifNabAuthor Commented:
I'm going to try to formulate it in the best way I can.
0
ZifNabAuthor Commented:
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
itamarCommented:
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
ZifNabAuthor Commented:
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
ZifNabAuthor Commented:
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
itamarCommented:
All right Zif.

Regards,
Itamar
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.