ZifNab
asked on
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.
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.
ASKER
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
begin
If Value <> FActive Then begin
FActive := Value;
CSession.Active := Value;
If CSession.Active Then
CSession.GetTableNames('DB
else
Items.Clear;
Update;
end;
end;
procedure Register;
begin
RegisterComponents('Sample
end;
end.
Try it out...
Regards,
Itamar
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
begin
If Value <> FActive Then begin
FActive := Value;
CSession.Active := Value;
If CSession.Active Then
CSession.GetTableNames('DB
else
Items.Clear;
Update;
end;
end;
procedure Register;
begin
RegisterComponents('Sample
end;
end.
Try it out...
Regards,
Itamar
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...
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...
ASKER
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.
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.
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
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
ASKER
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.
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.
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.
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.
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'
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
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'
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
ASKER
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 !
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 !
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.
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.
ASKER
I'm going to try to formulate it in the best way I can.
ASKER
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.
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.
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
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
ASKER
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?
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?
ASKER
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.
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.
All right Zif.
Regards,
Itamar
Regards,
Itamar
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