|
Database
No showing of P_RECNO
One table and database information on two physical forms
Stop the DBGrid control from auto-appending a new entry
Passwords on dBase-files
Create Paradox-Tables
Write a stream into a BLOB-field
Copy a DBMemo contents to another DBMemo field
Commands of the TDBNavigator in code
Search text in a DBMemo
Getting Field Information for a table
Reorder the columns in a TDBGrid control
Create new tables with a structure of an existing one
Look for an existing Record in database before it will be inserted
Display certain database fields in columns
Reorder DBGrid fields programmatically at runtime
Multiple tables in one DBGrid
Loading a TMemoField into a TMemo component
DBGrid location
Case insenstive search on a Paradox primary index
Refreshing a grid after editing in a single record edit form
Floating point numbers in DBGrid
Sorted DBLookupCombobox by secondary index
TQuery and TDBGrid
Descanding indexes
Cancel on related tables
From database to variables
Duplicating a set of records
DBGrid MoveToNextField
Creating a table in a unit
Make index files in delphi
Changing the dir from an Alias at run time
Pre-setting TDBLookupCombo boxes
Variable length record and phrase search
Tables in memory
Put a variable in a memo field
Table locking
Refreshing database data
Data aware outline control
Code for age
ASCII delimited file memo field into a DB table memo
Problems with AddIndex
Master-Detail form
Create a Paradox table
How to retrieve the path from an Alias or TDatabase
Value of TDBLookupCombo
Properly Closing Delphi Database Application
Default key field when inserting new record
Quickie DB searcher
DBGRID saving the user configuration
DBGrid resize
Getting Deletes to cascade across linked tables
dbGrid and Memo Fields
Popup menu that construct from DB
DBGrid as Navigator
Display a customer database entry form from a work order form
StringGrid
Two tables in one DBGrid
Text File Line Counts
FindKey on multiple fields
Creating database aliases in code
Creating and deleting TFields at run-time
Iterating through the fields of a table
Loading bitmaps into dBase/Paradox BLOB fields
Extracting a bitmap from a BLOB field
Searching through query result sets
Cascading deletes with Paradox referential integrity
dBase expression indexes: a primer
Interbase BLOB Fields: a primer
Automate logon for Paradox tables
Working with auto-increment field types
Showing deleted record in a DBF file
Recalling deleted records in DBF files
Rebuilding Indices with TBatchMove
Referential Integrity
Compressing Paradox Files
KEY VIOLATION
No showing of P_RECNO
Question
I have a Master/Detail Form and would like the linked field P_RECNO not to
be displayed in the DBGrid as it is unnecessary information. Can this be
done using DBGrid or do I have to find a thrid party component?
Answer
A:
You can do either
1. edit TTable to exclude P_RECNO
or
2. set
TableX.FieldbyName('P_RECNO).Visible := False;
A:
It can be done via the Fields Editor, which is associated not with the
DBGrid but rather with the Table component in question. To get to this,
right-click on the appropriate Table component and select the top option.
You should then add all fields to the field list, and highlight the one you
wish to suppress from the DBgrid. Look for its Visible property and set
that to False.
A:
If you have a TTable component, double click on the component icon (on =
the form), you then get a list of the fields related to the table in a =
dialog, click on one of them and check the object inspector =
properties....make visible false.
One table and database information on two physical forms
Question
I have a requirement to create a delphi app that uses only one table but has
database information on two physical forms.
Answer
A:
1. Add a TTable-component on form2
2. Just for designtime give this table the same values as the table on form1
3. Add the following codefragment in the IMPLEMENTATION-section of
form2 so that it looks like this:
unit form2;
interface
{...}
implementation
uses
form1;
{...}
4. Connect a procedure to the OnCreate-event of form2 (with the
objectinspector)
5. Add the following line to this procedure:
table1 := form1.table1;
After this you can all the components you connected with table1 at
designtime will work with the Table of form1.
A:
It appears as if the problem you are experiencing is one of synchronization.
Try the following:
- On Form1
place Table1
place DataSource1
set DataSource1.DataSet := Table1
place DataGrid
set DataSource := DataSource1
Up until now, this has been straight forward. Now do:
- On Form2
place DataSource1 (#1 for this form)
place whatever other components you need
in any DB components; point the datasource at DataSource1
In the OnCreate event for this form (e.g. FormCreate) put the
following code.
With Form1 do
begin
Form2.DataSource1.DataSet := Table1;
end;
This code attaches the Table1 on Form1 to the DataSource
on Form2. This forces the data displayed on Form2 to be in synch
with the data displayed on Form1. Since you are really only using
one Table.
The only caveat of here is if you are really using TDatabase then this might
not be what you want. The TDatabase component is not required for database
access, however, it supplies you with additional control for client/server
applications.
Thus, if the application is not running in a client/server environment then
the use of the TDatabase is not needed. All you need is the TDataSource and
TTable and any TDB components.
Stop the DBGrid control from auto-appending a new entry
Question
How to stop the dbgrid control from auto-appending a new entry when you move
down after the last record in a table.
It creates a new blank line / record in the table. Can this be stopped?
Answer
A:
Add to your TTables's "BeforeInsert" event the following line:
procedure TForm1.Tbable1BeforeInsert(DataSet: TDataset);
begin
Abort; <<---this line
end;
A:
It traps the down key and checks for end-of-file.
procedure TForm8.DBGrid1KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
begin
if (Key = VK_DOWN) then
begin
TTable1.DisableControls ;
TTable1Next ;
if TTable1.EOF then
Key := 0
else
TTable1.Prior ;
TTable1.EnableControls ;
end ;
end;
Passwords on dBase-files
Question
i wrote a program with delphi, that uses a dbase-table. Now
I want to allow the users to create, change and remove passwords to protect their data.
Answer
A:
dBase files do not support passwords. You can of course create your
own password routines. These will however only work within your app.
I am afraid that there are literally thousands of dBase readers /
converters around.
Create Paradox-Tables
Question
How to create Paradox-Tables?
Answer
A:
Here's a bit of code for creating paradox tables:
with TTable.create(self) do begin
DatabaseName := 'C:\temp';
TableName := 'FOO';
TableType := ttParadox;
with FieldDefs do Begin
Add('Age', ftInteger, 0, True);
Add('Name', ftString, 25, False);
Add('Weight', ftFloat, 0, False);
End;
IndexDefs.Add('MainIndex','IntField', [ixPrimary, ixUnique]);
CreateTable;
End;
Write a stream into a BLOB-field
Question
How can I write a Stream into a BLOB-Field?
Answer
A:
The trick is in the StrPcopy to put your String into a PChar and in the
writing of the buffer to the stream. You can't pass it a PChar because it
needs the buffer, so use [0] and use StrLen() to get the buffer
size which also needs to be passed.
Here is quick example of using a TMemoryStream and writing it to a Blob Field;
var
cString: String;
oMemory: TMemoryStream;
Buffer: PChar;
begin
cString := 'This is a String Darnit!';
{ Create a new Memory Stream }
oMemory := TMemoryStream.Create;
{!! Copy String to a PChar }
StrPCopy( Buffer, cString );
{ Write the =buffer= and it's size to the stream }
oMemory.Write( Buffer[0], StrLen( Buffer ) );
{Write that sucker to the field}
.LoadFromStream( oMemory );
{ No leaky Apps}
oMemory.Free;
end;
Copy a DBMemo contents to another DBMemo field
Question
I want to copy a DBMemo contents to another DBMemo field.
I have used copyfrom and pastefrom commands but I was getting errors.
I also used Read and write commmands as shown below:
DBMemo6.Read(Mybuf, 4096);
DBMemo5.Write(buf, 4096);
I am trying to copy the contents of DBMemo6 to DBMemo5 but I was getting an
error "Field identifer expected".
Answer
Try DBMemo6.Lines:=DBMemo5.Lines.Assign;
Commands of the TDBNavigator in code
Question
Can anyone give me the commands to do in code for the buttons on the DBNavigator?
Answer
A:
I think the example below (lifted out of DELPHI online help) will
sort out the navigator. I have seen a couple of messages about
needing to trap for explicit record posts - like when you scroll over
the record that you have changed. If you intend having data
validation checks then you will need to do this extra trapping.
Can't recall exactly HOW - maybe someone else can help us both.
Before you do any changes / posting you probably should make sure the
table is in edit mode. Check out the state property in DELPHI help.
It will show you what to do.
The following code determines which database navigator button was clicked and displays a message identifying the name of the button.
procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
var
BtnName: string;
begin
case Button of
nbFirst : BtnName := 'nbFirst';
nbPrior : BtnName := 'nbPrior';
nbNext : BtnName := 'nbNext';
nbLast : BtnName := 'nbLast';
nbInsert : BtnName := 'nbInsert';
nbDelete : BtnName := 'nbDelete';
nbEdit : BtnName := 'nbEdit';
nbPost : BtnName := 'nbPost';
nbCancel : BtnName := 'nbCancel';
nbRefresh: BtnName := 'nbRefresh';
end;
MessageDlg(BtnName + ' button clicked.', mtInformation, [mbOK], 0);
end;
Search text in a DBMemo
Question
What I want to do is search the Text in the TDBMemo, just like a Word Processor searches for a particular word. I suspect that because the TDBMemo is attached to the Paradox Table that searching this way is out of the
Question.
Answer
A:
Try this procedure: Attach it to your OnFind Event for the FindDialog. The
only problem is that I can't seem to get the found text to highlight in the
DBMemo, although it works fine in a standard text Memo.
procedure TMainForm.FindDialog1Find(Sender: TObject);
var Buff, P, FT : PChar;
BuffLen : Word;
begin
With Sender as TFindDialog do
begin
GetMem(FT, Length(FindText) + 1);
StrPCopy(FT, FindText);
BuffLen:= DBMemo1.GetTextLen + 1;
GetMem(Buff,BuffLen);
DBMemo1.GetTextBuf(Buff,BuffLen);
P:= Buff + DBMemo1.SelStart + DBMemo1.SelLength;
P:= StrPos(P, FT);
if P = NIL then MessageBeep(0)
else
begin
DBMemo1.SelStart:= P - Buff;
DBMemo1.SelLength:= Length(FindText);
end;
FreeMem(FT, Length(FindText) + 1);
FreeMem(Buff,BuffLen);
end;
end;
To highlight the found text:
begin
DBMemo1.SelStart:= P - Buff;
DBMemo1.SelLength:= Length(FindText);
end;
FreeMem(FT, Length(FindText) + 1);
FreeMem(Buff,BuffLen);
DBMemo1.SetFocus;
end;
Getting Field Information for a table
Question
I'm trying to get field information for database tables. I can get the database name and the table names but I can't get all the fields for a given table. I tried declaring a ttable component and assigning the database name and table name to the object.
Answer
A:
You need to use the FieldDefs property. The following example will add the
list of fields and their respective sizes to a TMemo component named Memo1
on the form:
procedure TForm1.ShowFields;
var
i : Word;
begin
Memo1.Lines.Clear;
Table1.FieldDefs.Update; { must call in case Table1
is not active }
for i := 0 to Table1.FieldDefs.Count - 1 do
With Table1.FieldDefs.Items[i] do
Memo1.Lines.Add(Name + ' - ' + IntToStr(Size));
end;
A:
If you just wan the names then use the GetFieldNames Method of TTable to get
the FieldNames:
GetIndexNames to get Index Names:
var FldNames, IdxNames : TStringList
begin
FldNames := TStringList.Create;
IdxNames := TStringList.Create;
If Table1.State = dsInactive then Table1.Open;
Table1.GetFieldNames(FldNames);
Table1.GetIndexNames(IdxNames);
{...... do whatever the next bit is ......}
FldNames.Free; {release the stringlist}
IdxNames.Free;
end;
To get specific field info, you will have to use FieldDef.
Reorder the columns in a TDBGrid control
Question
I need to allow the end user to reorder the columns in a TDBGrid control and select an index based upon the new first column.
Answer
A:
Var
i : Integer ;
fName : string ;
............
{ Detect that columns reordered }
............
with DBGrid1.DataSource.DataSet as TTable do
for i := 0 to IndexDefs.Count - 1 do begin
fName := DBGrid1.Fields[0].FieldName ;
if Copy( IndexDefs[i].Fields, 1, Length( fName ) ) = fName then
IndexName := IndexDefs[i].Name
end ;
Create new tables with a structure of an existing one
Question
I must create new tables with the same structure of an already existing one.
Answer
A:
I keep forgetting that when a property is assigned a value (ie on the left
side of the ':='), Delphi actually calls the 'write' method and passes
whatever is on the right side of the assignment as the lone parameter.
If a property does not have a write method, it is read only.
This is the FieldDefs property definition for the TDataSet object in the
DB.PAS file:
property FieldDefs: TFieldDefs read FFieldDefs write SetFieldDefs;
As you can see, it has a write method. Therefore when you code:
Destination.FieldDefs := Source.FieldDefs;
What actually is done is this:
Destination.SetFieldDefs(Source.FieldDefs);
(Except you can't code this line because SetFieldDefs is Private.)
This is the IndexDefs property for the TTable object in the DBTABLES.PAS file:
property IndexDefs: TIndexDefs read FIndexDefs;
As you can see this time, it does not have a write method, therefore it is
read only. However, there is an Assign method for the TIndexDefs object
itself. Therefore the following line should work.
Source.IndexDefs.Update;
Destination.IndexDefs.Assign(Source.IndexDefs);
Call the Update method for the Source.IndexDefs prior to the Assign to be
sure you get what you expect.
The SetFieldDefs method is a one line procedure that calls the FieldDefs'
Assign method.
You may want to check that there is actually an index defined; because
if not, you will probably get an exception like "List Index Out Of Bounds"
(or something like that) when doing the IndexDefs.Assign. ie: if
Source.IndexDefs.Count > 0 then ...
You need to do this because the TIndexDefs.Assign method does not check this
before copying the Index info. Also, you do not need to Clear the Destination
IndexDefs prior to the call because the Assign method does it for you.
Look for an existing Record in database before it will be inserted
Question
I'm trying to delete the record inserted or not insert it at all if this
new record has a key field that already exists in the database.
Answer
A:
If you are already in the Edit mode or Insert mode, when you change states,
you will automatically attempt to post the record.
And of course, a key violation will cause an error. A way around it is to use
another TTable component mapped to the same table, and do the search on that
one. That way, the table being edited remains unaffected.
A:
Use two TTable components (both pointing to the same table). Use
one for the search and the other for the editing.
A:
If you "key" the table the BDE will automatically generate an exception
when the user tries to post that create a duplicate key. Use the
Database Desktop to set up the table.
A:
Can you make that field a Primary Index. Then create some DB exception
handling for that key violation.
A:
What I did was prompt the user, with a different form, for the portion of
the record which makes up the unique key (usually just a single entry). I
then did a FindKey to see if it already exists. If it does, the user is
informed via a MessageDlg, and then returned to the edit form without
creating a new record. Remember that if FindKey fails, the dbCursor is not
moved; no need to bookmark. If it works, then the found record is now
displayed on the edit form and the user can see the contents right away.
Otherwise the following occurs:
Table.Append;
Table.FieldByName('KeyField').AsString := UserEntry;
{ ... allow the user to edit all other fields for the record ... }
{ a Cancel button is made active during this time so that if the
user presses it, the entire new record is canceled. }
In my edit forms, the Unique key field is disabled and shown with a
different color. This way the user cannot flub up the referential integrity
of the RDB.
Display certain database fields in columns
Question
Is there a way to only display certain database fields in the columns?
i.e., if I have a table with 20 columns, but, I want to display the 2, 4,
5, 10, 11 and 16th in the grid.
Answer
A:
Here is a runtime solution:
Table1.FieldByName(RemovedFieldName).Visible := False;
or
Table1.Field[removedFieldNumber-1].Visible := false;
Reorder DBGrid fields programmatically at runtime
Question
At design time you can use the fields editor, and at run time you can use
the mouse to reorder database fields in a DBGrid. How do you do this
programmatically?
Answer
A:
uses .index :=
Multiple tables in one DBGrid
Question
Anyone know of a way to have several fields from multiple tables in one
DBGrid?
Answer
A:
The only easy way to do it I know is Calculated fields.
A:
As far as I have been able to determine, the only way to put data from
multiple tables into a DBGrid is to use a TQuery object. The catch is:
TQuery is read-only unless you meet some pretty rigid guidelines, one of
which is the data can only come from one table.
Loading a TMemoField into a TMemo component
Question
I am looking for a good way to load a TMemoField from a DBase table into a
TMemo component. I do not want to use a DBMemo since I want to control when
and how edits are saved, etc.
The only way that seems to work consistently is to load it into a DBMemo
component and then assign the lines property of the TMemo to the lines property
of the DBMemo. This is not what I want to do.
Answer
A:
Procedure TMemoToTMemoField;
begin
TMemoField.Assign( TMemo.Lines );
end;
Procedure TMemoFieldToTMemo;
VAR aBlobStream : TBlobStream;
begin
aBlobStream := TBlobStream.Create(TMemoField, bmRead);
TMemo.Lines.LoadFromStream( aBlobStream );
aBlobStream.Free;
end;
DBGrid location
Question
How can I find know the current record which I selected if I use DBGRID and
TQuery?
Answer
A:
In design mode, double click on the TQuery component, and select all the fields
that you want to use in the DBGrid. Then in the doubleclick event procedure of
DBGrid, check to see what the DBGrid.SelectedIndex is. If it is < 0, there are
no items selected. Else, the current record of TQuery will point to the same
row selected in the DBGrid. Thus, you will be able to use something like
requiredvalue := Query1Field1.AsString; etc.....
Of course, the TQuery component and DBGrid component must be connected to each
other.
Case insenstive search on a Paradox primary index
Question
How do you do a case insenstive search on a Paradox primary index in
Delphi?
Answer
A:
Unfortunately, it can't be done. You can create another (secondary)
index, a case-insensitive one, on the same field (or fields) that make up
the primary index, but as you can guess, this will result in more overhead.
Refreshing a grid after editing in a single record edit form
Question
Refreshing a grid after editing in a single record edit form.
Answer
A:
Are you posting the record before you close the form? Closing the
detail form will not post the changes by itself. You must either
post the changes using a dbnavigator component or put in a line of code
that posts the underlying table on form close.
On page 95 of the Database Application Developers Guide that ships
with Delphi it discusses the twoforms sample project and illustrates
a nice technique for using a ttable from a master form as the dataset
for a detail.
A:
One solution to your problem is to direct your DataSource component
on Form2 to use the DataSet on Form1. This can be achieved by
writing the following code in the OnActivate procedure of Form2:
MyDataSource.DataSet := Form1.MyTable;
This method has 3 advantages:
1) the changes you made is reflected immediately because you are
usually the same logical table;
2) if you need to define settings for your table fields, such as
DisplayFormat and EditMask, you only need to do it once on the
Table on Form1, you don't need do it on every form that use it;
3) it can save resources and also faster because your application
hold only one session to one table at one time. However, during
design time, you need to have a TTable on your Form2 so you can
choose fields for your db-aware controls; after that, you can
delete the TTable.
Floating point numbers in DBGrid
Question
I have a project it uses DBGrid but the problem is. I have fields defined as
floats with one dec... But no Decimals appear when I input any data, and the
data is rounded.
Answer
A:
To display decimals in a grid select the table your grid is linked
with (via datasource).
Activate the field-editor (right mouse button) and select the field
you want to have decimals.
Change the value of the properties 'DisplayFormat' and 'EditFormat'
so that decimals are shown in the format you want (e.g. the value
'0.00' forces the grid to display the field with two decimals).
A:
On the table component on the form, double click on it. You will see an
'Add' button, click on it. This displays all the field in your table.
High light them from the "Available field" box and click on OK. Now
click on the field name, in the Object Inspector all your properties
will relate to each field, you can change the column title, the
"DisplayFormat" (this is what you are looking for, change it to ####0.0
Sorted DBLookupCombobox by secondary index
Question
Can somebody please tell me how to make a DBLookupComboBox become sorted
by a secondaryindex (if possible).
Answer
A:
One way to display your data in a different order would be to select the
data through a TQuery and include the "order by" clause in your SQL
statement. Then you can set this query as the DataSource to your
DBLookupComboBox.
EXAMPLE:
If you have a Customer table containing "Customer_No" and "Customer_Name"
indexed by Customer_No your statement in the string list editor (the SQL
Property) for the TQuery would be:
select Customer_No, Customer_Name from Customer
order by Customer_Name
TQuery and TDBGrid
Question
1. How do I index the list generated by TQuery ?
2. How do I create an OnClick Event in a TDBGrid ?
Answer
A:
1) After your where clause place an order by clause
Select fname, lname, title
from T_EMPLOYEE
where title = 'MGR'
order by lname, fname
2) Try the ColEnter event.
Descanding indexes
Question
I can't figure out how to create a descending secondary index in Paradox!
Answer
A:
I just found a way of getting a descending index. It's pretty easy as
anything else
in Delphi.
Table1.AddIndex('NewIndex', 'CustNo;CustName', [ixDescending]);
Cancel on related tables
Question
I have a data base app. in it there is a cancel Btn. I try to do this:
when the user press this button, the changes on related table will be
canceled. The code is somehow like this :
procedure TForm1.CancelSpdBtnClick(Sender: TObject);
begin
ExTable.Cancel;
ExTable.Refresh;
PostSpdBtn.Enabled := false;
CancelSpdBtn.Enabled := false;
EditSpdBtn.Enabled := true;
end;
But to my surprising, after some modification has been done, I can not
cancel the changes, even I press this button. All of the changes have been
written into database no matter cancel or not.
Answer
A:
In 'Delphi unleashed' book pp 520, the author wrote:
'...,you can still undo your work at any time, as long as you have
not either directly or indirectly posted. '
^^^^^^^^^^
My problem was caused by assigning ExTable.Edit twince in diffrent
procedures. The codes somehow like this...
Procedure1 ....
begin
ExTable.Edit ;
ExTable.FieldByName('...').AsString := ...;
...
end;
procedure2 .....
begin
ExTable.Edit;
.....
end;
After these 2 procedures were called, the CancelSpdBtnClick procedure
was called. Actually, before cancelling was done, posting was
indirectly called between 2 ExTable.Edit statements were executed.
^^^^^^^^^^ ^^^^^^^
Now I made the modification, the program works fine.
From database to variables
Question
I want to retrieve data from DB (or dbf) to variables or arrays.
For example, assign a variable with a data in a selected FIELD and RECORD,
use assignment (:=) to do it? or something? How?
Answer
A:
You can retrieve field values programmatically like so:
aValue := TMyTable.FieldByName('SomeField').AsText;
or
aValue := TMyTable.FieldByName('SomeField').AsInteger;
or
aValue := TMyTable.Fields[1].AsFloat;
What you're actually doing here is getting a TField object from the TTable (or
TQuery) object, then calling a method of the TField object to get at the data
itself. You can also assign values to the field in the same way, if the TTable
object is in Insert or Edit mode. The AsFloat, AsInteger, AsDateTime, AsString
members are actually properties, and as such can accept assignment. You can
also create field objects using the Fields Editor by double-clicking on the
TTable or TQuery object. These field objects can be used instead of getting it
from the TTable or TQuery object every time.
Duplicating a set of records
Question
Does anyone know it there is a smart way of doing this:
given a set of records in a table, modify some field(s) and create new
records within the _same_ table. (Yes, this is a detail set of a master-
detail pair, the purpose being to create a new master record inheriting
all detail records.)
With "smart" I am aiming at something that does not look like jumping
here and there ("repeat read; modify; write until done").
Answer
A:
You could either use a second TTable object working on the same table, or you
could call the DisableControls method in your TTable object before making
your changes, followed by EnableControls. If you want to keep your same
place you might try using a bookmark. Sort of like this:
procedure TMyForm.MakeChanges;
var
aBookmark: TBookmark;
begin
Table1.DisableControls;
aBookmark := Table.GetBookmark;
try
{do stuff}
finally
Table1.GotoBookmark(aBookmark);
Table1.FreeBookmark(aBookmark);
Table1.EnableControls;
end;
end;
DBGrid MoveToNextField
Question
Does anyone know if there is a TDBGrid method (or similar) to perform the same
as entering a TAB, moving to next field in the grid?
Answer
A:
To go to the next field
MyDBGrid.SelectedIndex := MyDBGrid.SelectedIndex + 1;
The grid columns are 0 indexed so SelectedIndex := 0 goes to the first
column. The FieldCount property gives you the number of columns so you can
handle wrapping at the end of the row.
Creating a table in a unit
Question
How can I create a Table in a unit (without a form) at runtime? The following
code doesn't work:
procedure CreateATableInAUnit;
var
myTable : TTable;
begin
myTable := TTable.Create(Self);
end;
Answer
A:
The TTable object can be created with an owner, or without one. Since you
are declaring it as local to a procedure you should not give it an owner.
Whenever you don't give it an owner, that means that its your job to free it
when you're done. Otherwise, the owner will free it whenever the owner is
itself freed. Make sense? To create it without an owner, do the following:
procedure CreateATableInAUnit;
var
myTable : TTable;
begin
myTable := TTable.Create(nil);
try
myTable.DatabaseName := 'MyDB';
myTable.TableName := 'MyTable.db';
mytable.IndexName := 'MyIndex';
myTable.Open;
{do stuff}
finally
myTable.Free;
end;
end;
Make index files in delphi
Question
Does someone know how to create, maintaince, refresh a index file in
delphi, please give me a help.
Answer
A:
If you are using dBASE or Paradox tables, you can use the AddIndex to create
a new index. For example:
Table1.AddIndex('Articles','Title', []) ;
will create an index file named ARTICLES using the field TITLE as index key.
You can also specify various index options (e.g., unique, non-maintained,
etcetera) -- check the Delphi help file for more information. NOTE: Your
table must be opened exclusively in order to use the AddIndex method.
As to maintaining/refreshing an index file, unless you specifically create a
non-maintained index file, it's automatic.
Changing the dir from an Alias at run time
Question
Is it possible to change the directory from an Alias at run-time?
Answer
A:
I do this all the time. I have an INI file that specifies wher to
look for the tables and the directories to use here is mi code:
procedure CheckTable( var Table : TTable; var TName : string );
var
ChangePath : boolean;
Path : string;
ActiveState : Boolean;
begin
if ( TName = '' ) then
TName := Table.TableName
else
with Table do
begin
ActiveState := Active;
Close;
Path := ExtractFilePath( TName );
ChangePath := HasAttr( DatabaseName, faDirectory ) or
( CompareText( DatabaseName, Path ) <> 0 );
if ( Length ( Path ) > 0 ) and ChangePath then
DatabaseName := Path;
if ( CompareText( ExtractFileName( Tname ), TableName ) <> 0 )
then
TableName := ExtractFileName( Tname );
Active := ActiveState;
end;
end;
Pre-setting TDBLookupCombo boxes
Question
How do you "pre-set" the value of a TDBLookupCombo box?
Example: when the user brings up a form and goes into edit mode.. there are
several TBDBLookupCombo boxes on it... (about 17-20 per form).
Aall of the combo's have their lookupsource, lookupdisplay, and lookupfield
properties set the same... but this still forces the user to select each
combo box and pick a value.
I'd like to have all of combo's pre-set when the user goes into edit mode.
Answer
A:
You can edit your datasource. Say, you want to save your data lookuping
from customer table into sales table - 'Cust No'. You can simply preset
(default value) by editing sales table "Cust No"
with tbSales do
begin
Edit;
FieldByName('Cust No').AsInteger := 1;
Post;
end;
Variable length record and phrase search
Question
I wonder if any of you have done this. I am trying to redo a Windows
application for a Helpdesk application.
The database must allow variable length text. And it must allow search based
on synonyms or fuzzy match. Example "PC kaput" will still retrieve "PC hang"
and "hanged PC" will get "PC hang" too.
I did this before but it was rather slow even when I have my operators up to
30 Meg RAM and CPU 100 Mhz Overdrive processors.
Answer
A:
For variable length text you can use a DBmemo. Most people will do this by
scanning "on the fly" (when an operator poses a query), but to really speed
up processing, try pre-scanning the way the "big boys" do it (ie. operators
of large databases):
1) when a new entry is placed into the database, it is scanned for keywords
(this can be either a pre-defined list of keywords or any word not in an
exclusion list [eg. "the", "of", "and"])
2) the keywords are added to a keyword list referencing the record number,
eg. "hang",46 or "PC",22.
3) when a user does a query, you retrieve all records meeting each keyword,
eg. "hang" might return record numbers 11, 46, and 22, while "PC" might
return record numbers 91, 22, and 15.
4) you merge the numbers on each list, which in the above example results in
record 22 (if it is an AND match) or records 11, 15, 22, 46, and 91 (if it
is an OR match). You then retrieve and display those records.
5) for synonyms, you define a synonym table (eg. "hang","kaput") and look up
the synonyms too, adding them to the same list as the original word.
6) for common endings (eg. "hang" and "hanged"), you can either make them
synonynms, have your program analyze common word endings, or what most
systems do is allow a partial match (eg. "hang" will match any word whose
first 4 letters are "hang").
Of course, there are a lot of technical details to be determined, most
especially how to organize the lists so they are most efficiently merged and
managed. This can give you very fast retrieval times (witness search
engines such as Nexus, Lycos, or WebCrawler, searching hundreds of thousands
of records in less than a second).
Tables in memory
Question
How do I make a virtual table (in memory)?
Answer
A:
{
This is an InMemoryTable example. Free for anyone to use, modify and do
whatever else you wish.
Just like all things free it comes with no guarantees. I cannot be
responsible for any damage this code may cause. Let me repeat this:
WARNING! THIS CODE IS PROVIDED AS IS WITH NO GUARANTEES OF ANY KIND!
USE THIS AT YOUR OWN RISK - YOU ARE THE ONLY PERSON RESPONSIBLE FOR
ANY DAMAGE THIS CODE MAY CAUSE - YOU HAVE BEEN WARNED!
THANKS to Steve Garland <72700.2407@compuserve.com> for his help. He
created his own variation of an in-memory table component and I used it
to get started.
InMemory tables are a feature of the Borland Database Engine (BDE).
InMemory tables are created in RAM and deleted when you close them. They
are much faster and are very useful when you need fast operations on
small tables. This example uses the DbiCreateInMemoryTable DBE function call.
This object should work just like a regular table, except InMemory
tables do not support certain features (like referntial integrity, secondary
indexes and BLOBs) and currently this code doesn't do anything to
prevent you from trying to use them. You will probably get some error if
you try to create a memo field.
If you have comments - please contact me at INTERNET:grisha@mira.com
}
unit Inmem;
interface
uses DBTables, WinTypes, WinProcs, DBITypes, DBIProcs, DB, SysUtils;
type TInMemoryTable = class(TTable)
private
hCursor: hDBICur;
procedure EncodeFieldDesc(var FieldDesc: FLDDesc;
const Name: string; DataType: TFieldType; Size: Word);
function CreateHandle: HDBICur; override;
public
procedure CreateTable;
end;
implementation
{ luckely this function is virtual - so I could override it. In the
original VCL code for TTable this function actually opens the table - but since
we already have the handle to the table - we just return it }
function TInMemoryTable.CreateHandle;
begin
Result := hCursor;
end;
{ This function is cut-and-pasted from the VCL source code. I had to do
this because it is declared private in the TTable component so I had no
access to it from here. }
procedure TInMemoryTable.EncodeFieldDesc(var FieldDesc: FLDDesc;
const Name: string; DataType: TFieldType; Size: Word);
const
TypeMap: array[TFieldType] of Byte = (
fldUNKNOWN, fldZSTRING, fldINT16, fldINT32, fldUINT16, fldBOOL,
fldFLOAT, fldFLOAT, fldBCD, fldDATE, fldTIME, fldTIMESTAMP, fldBYTES,
fldVARBYTES, fldBLOB, fldBLOB, fldBLOB);
begin
with FieldDesc do
begin
AnsiToNative(Locale, Name, szName, SizeOf(szName) - 1);
iFldType := TypeMap[DataType];
case DataType of
ftString, ftBytes, ftVarBytes, ftBlob, ftMemo, ftGraphic:
iUnits1 := Size;
ftBCD:
begin
iUnits1 := 32;
iUnits2 := Size;
end;
end;
case DataType of
ftCurrency:
iSubType := fldstMONEY;
ftBlob:
iSubType := fldstBINARY;
ftMemo:
iSubType := fldstMEMO;
ftGraphic:
iSubType := fldstGRAPHIC;
end;
end;
end;
{ This is where all the fun happens. I copied this function from the VCL
source and then changed it to use DbiCreateInMemoryTable instead of
DbiCreateTable.
Since InMemory tables do not support Indexes - I took all of the
index-related things out }
procedure TInMemoryTable.CreateTable;
var
I: Integer;
pFieldDesc: pFLDDesc;
szTblName: DBITBLNAME;
iFields: Word;
Dogs: pfldDesc;
begin
CheckInactive;
if FieldDefs.Count = 0 then
for I := 0 to FieldCount - 1 do
with Fields[I] do
if not Calculated then
FieldDefs.Add(FieldName, DataType, Size, Required);
pFieldDesc := nil;
SetDBFlag(dbfTable, True);
try
AnsiToNative(Locale, TableName, szTblName, SizeOf(szTblName) - 1);
iFields := FieldDefs.Count;
pFieldDesc := AllocMem(iFields * SizeOf(FLDDesc));
for I := 0 to FieldDefs.Count - 1 do
with FieldDefs[I] do
begin
EncodeFieldDesc(PFieldDescList(pFieldDesc)^[I], Name,
DataType, Size);
end;
{ the driver type is nil = logical fields }
Check(DbiTranslateRecordStructure(nil, iFields, pFieldDesc,
nil, nil, pFieldDesc));
{ here we go - this is where hCursor gets its value }
Check(DbiCreateInMemTable(DBHandle, szTblName, iFields, pFieldDesc,
hCursor));
finally
if pFieldDesc <> nil then FreeMem(pFieldDesc, iFields *
SizeOf(FLDDesc));
SetDBFlag(dbfTable, False);
end;
end;
end.
{This code came from Lloyd's help file!}
Put a variable in a memo field
Question
How do I get a variable into a Memo Field. I want to ask the user for
his / her name and display that name, plus more, in a memo.
Answer
A:
If I realized your question, I thing you need something like this (to GET data)
Memos := TStringList.Create;
Memos.Assign(Table1Memo);
yourvariable_0 := Memos[0];
yourvariable_1 := Memos[1];
..........................
yourvariable_n := Memos[n];
Memos.Free;
and like this (to SET data)
Memos := TStringList.Create;
Memos.Add(yourvariable_0);
Memos.Add(yourvariable_1);
..........................
Memos.Add(yourvariable_n);
Table1Memo.Assign(Memos);
Memos.Free;
Table locking
Question
If one people is editing the record, the other people cannot view
the record. Can I prompt the user that the message " The record is currently
edited by other user"?
Answer
A:
When you get this or similars error, you can intercept these using the try
construct in this way (supposing you are trying to post a record):
try
Table1.Post;
except
MessageDlg ('Error posting record', etc...
Table1.Cancel;
end;
Otherwise, you -shouldn't- get an error if an looks to a record currently
viewed by another user (if you are using the Paradox database provided with
Delphi) if you had correctly set it. Paradox self-creates a file called
pdxusers.lck viewed by every users in the net dir, so every BDE on every
local machine can be able to lock a record forbiding other users to post it
until he had relased. I can't imagine what kind of things you are doing to
get this error, if I don't know some other specs.
Refreshing database data
Question
I have a form that reads and updates from a paradox table. Its also calls
another form that will add records into the same table and this works fine. It
also displays another modal form that has a DBGRID in it pointing to the same
table.
Everything works to start with, however when I add or change data and then
call this modal form with the DBGRID in it, the DBGRID is not showing any
updated information eg it still shows deleted records and wont show any new
records that have been added. It is almost like it is pointing to the
'original' version of the table.
After I terminate my application and start it again the DBGRID does reflect
the true information, so it does prove that it is pointing to the correct
table. (I have checked this all out in the sample MASTAPP application
supplied with DELPHI and it all seems to work fine in a similar circumstance
- seemingly with no 'extra' code to handle this)
Answer
A:
Perhaps it will help to use the same table and datasource in your
modal form as in your main form. Try to modify the code of your modal
form like this:
unit myModalF;
interface
{...}
implementation
{...}
uses
MainForm; {The file-name of the parent form of your modal form)
MyModalForm.OnCreate(Sender: TObject);
begin
DBGrid1.DataSource := MyMainForm.DataSource1;
end;
Data aware outline control
Question
Another quick query, does anyone know of a data aware outline control?
My preference is obviously for a freeware one but shareware would do
if the source is available after registration.
If not, does anyone have any bright ideas on a method to store in a
database and display in an outline a relationship tree of undefined
depth?
To explain...we have a table of individuals and wish to relate
them to each other in a corporate hierarchy, ie. Bob reports to Peter,
Peter has Jane and Simon working for him etc... This structure could
have any number of levels and may have to start centred on any person,
at any level.
Answer
A:
I've done something like you would like to do.
I cannot explain everything but I can give you an idea on how to do.
You must have a table that make a relation between the individuals.
If Peter has Jane and Simon working for him you must have a table (RELATION)
with these two records
Master Slave ------- fields name
Peter Jane
Peter Simon
If George and Elisa work for Jane then the table becomes:
Master Slave ------- fields name
Peter Jane
Peter Simon
Jane George
Jane Elisa
and so on.
When you have to construct the tree starting at Peter you've to add a main
node called Peter in the tree an position the RELATION table on the first
record where Master = Peter. Than add a child node
for each record that satisies the condition Master = Peter.
After adding a child you've to see if this child has child himself. The
child becomes now a probably father
so you've to position the RELATION table on the first record where Master =
child and so on recursively.
This grants you to build a correct tree.
Example
AddFather('Peter')
AddChild('Peter',1)
Procedure AddFather(Name: String)
Begin
Tree.Add(Name);
End;
Procedure AddChildr(Name: String, Index:Integer)
Begin
Relation.FindKey([Name])
while RelationMaster.AsString = Name do
Begin
Tree.AddChild(Index,RelationSlave.AsString);
AddChild(RelationSlave.AsString,Tree.ItemsCount);
Relation.Next;
End;
End;
Maybe there's some error but the this is the way.
Code for age
Question
1. How do I use this function, where do I put it. I have a table listing
the birthdays in one column but not a column for age. Would like to have
a field on a form/notebook showing the age automatically on run.
2. What about the ages of those less than 1 year. How do I show the age
in months for those occasions?
Answer
Double-click on your TTable or TQuery component on your form to go to the
Fields Editor dialog (or right-click and select Fields Editor). Add all the
fields that you will be working with in the form (even those you don't want
to be visible but need access to--you can set the visible property to false
for any fields you wish to suppress). Then click on "Define..." to add a
calculated field. Type in a name for the calculated field that is not the
same as any fields in the table, select a type (probably StringField) and
enter a length (20 should be fine). Make sure the 'calculated' box is
checked. Then add an event-handler to your TTable or TQuery object for
'OnCalcFields'. In this handler you will look at one of the real fields in
your table, do a calculation, and put the results into your calculated field
object that you just created. This will cause it to show up in the TDBGrid
or you can add a TDBText control to display the value if you're using a data
entry form rather than a grid.
So far as displaying months as well as years, hear's a function which should
do the trick. Since not all the months are the same length, I just took an
average, so it isn't going to be perfectly accurate, but for most people it
should suffice:
function AgeStr(aDate: TDateTime): string;
var
DaysOld : Double;
Years,
Months : Integer;
begin
DaysOld := Date - aDate;
Years := Trunc(DaysOld / 365.25);
DaysOld := DaysOld - (365.25 * Years);
Months := Trunc(DaysOld / 30.41);
Result := Format('%d years, %d months',[Years, Months]);
end;
So, in my case, my OnCalcFields method looked like this:
procedure TEntryForm.TableNameOrderCalcFields(DataSet: TDataset);
begin
TableNameOrderAge.AsString := AgeStr(TableNameOrderDateOfBirth.AsDateTime);
end;
ASCII delimited file memo field into a DB table memo
Question
How do you get an ASCII delimited file Memo field into a DB Table Memo?
I create the Table and then populate it with the fields depending on the
type read on the first line of the ASCII delimited file.
I know that you can't do :
Table.Fields[MemoField].AsString := ASCII.MemoString;
Answer
A:
You need to use the getTextBuf procedure. Here's the example from
online help:
This example copies the text in an edit box into a null-terminated string,
and puts this string in another edit box when the user clicks the
button on the form.
procedure TForm1.Button1Click(Sender: TObject);
var
Buffer: PChar;
Size: Byte;
begin
Size := Edit1.GetTextLen; {Get length of string in Edit1}
Inc(Size); {Add room for null character}
GetMem(Buffer, Size); {Creates Buffer dynamic variable}
Edit1.GetTextBuf(Buffer,Size); {Puts Edit1.Text into Buffer}
Edit2.Text := StrPas(Buffer); {Converts Buffer to a Pascal-style string]
FreeMem(Buffer, Size); {Frees memory allocated to Buffer}
end;
Problems with AddIndex
Question
I have problems creating a new secondary index.
I added field to the TTable then the table is created as by Table.Create. Next
I create the primary index with Table.AddIndex('PRIMARY','ID',[ixPrimary]);.
But when I want to create a secondary index I always get an error at runtime.
Answer
A:
I am using a paradox table on a local station.
I use the following commands:
Table.DatabaseName := 'ABC';
Table.TableName := 'TEST';
Table.CreateTable;
Table.AddIndex('Primary','ID',[ixPrimary]); (works fine)
Table.AddIndex('Number_IDX','NUMBER',[ixUnique]); (here I get a runtime error)
ID is a Long-Integer field
NUMBER is a char[15] field
Master-Detail form
Question
I am using a master-detail from (created with the Expert) to
display a list of people from one table and items from
another table for each individual. The problem is that I
can't seem to edit any of the fields on my form from either
of the tables. Also, the add, delete, and post buttons on
the VCR DB control are greyed out.
Answer
A:
This is normal for two reasons:
1) The Database expert by default creates query with RequestLive set to
False; if you want to modify something, you have to set RequestLive to True.
2) In a one-many relationship, due to referential integrity rules, you are
allowed to make changes only in the "many" form, not in the "one" form. BTW,
this is right, if you think about it. Suppose you have a one-many
relationship where the one are your customers, and the many are their
invoices: of course there could be more than one invoice per customer. If
the system would allow you to modify the customer by, for example, deleting
records, you'd find some invoice not related to anybody.
Create a Paradox table
Question
How can i do to create a Paradox table with the following structure:
TableName = Empresa
Field Type
CODEMP AUTOINCREMENT (Key)
NomeEmp CHARACTER(50)
Answer
A:
unit Autoinc;
interface
uses
SysUtils, WinTypes, WinProcs, Messages, Classes, Graphics, Controls,
Forms, Dialogs, DBTables, DB, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls,
DbiTypes, DbiErrs, DBIProcs;
const
szTblName = 'CR8PXTBL'; { Name of table to be created. }
szTblType = szPARADOX; { Table type to use. }
{ Field Descriptor used in creating a table }
const
fldDes: array[0..1] of FLDDesc = (
( { Field 1 - AUTOINC }
iFldNum: 1; { Field Number }
szName: 'AUTOINC'; { Field Name }
iFldType: fldINT32; { Field Type }
iSubType: fldstAUTOINC; { Field Subtype }
iUnits1: 0; { Field Size }
iUnits2: 0; { Decimal places ( 0 ) }
iOffset: 0; { Offset in record ( 0 ) }
iLen: 0; { Length in Bytes ( 0 ) }
iNullOffset: 0; { For Null Bits ( 0 ) }
efldvVchk: fldvNOCHECKS; { Validiy checks ( 0 ) }
efldrRights: fldrREADWRITE { Rights }
),
( { Field 2 - ALPHA }
iFldNum: 2; szName: 'ALPHA';
iFldType: fldZSTRING; iSubType: fldUNKNOWN;
iUnits1: 10; iUnits2: 0;
iOffset: 0; iLen: 0;
iNullOffset: 0; efldvVchk: fldvNOCHECKS;
efldrRights: fldrREADWRITE
) );
type
TForm1 = class(TForm)
Button1: TButton;
Database1: TDatabase;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
Var
TblDesc: CRTblDesc;
uNumFields: Integer;
Rslt : DbiResult;
ErrorString : Array[0..dbiMaxMsgLen] of Char;
begin
FillChar(TblDesc, sizeof(CRTblDesc), #0);
lStrCpy(TblDesc.szTblName, szTblName);
lStrCpy(TblDesc.szTblType, szTblType);
uNumFields := trunc(sizeof(fldDes) / sizeof (fldDes[0]));
TblDesc.iFldCount := uNumFields;
TblDesc.pfldDesc := @fldDes;
Rslt := DbiCreateTable(Database1.Handle, TRUE, TblDesc);
If Rslt <> dbiErr_None then
begin
DbiGetErrorString(Rslt, ErrorString);
MessageDlg(StrPas(ErrorString),mtWarning,[mbOk],0);
end;
end;
end.
***************** EXAMPLE 2 **************************************
Here's a bit of code for creating paradox tables:
with TTable.create(self) do begin
DatabaseName := 'C:\temp';
TableName := 'FOO';
TableType := ttParadox;
with FieldDefs do Begin
Add('Age', ftInteger, 0, True);
Add('Name', ftString, 25, False);
Add('Weight', ftFloat, 0, False);
End;
IndexDefs.Add('MainIndex','IntField', [ixPrimary, ixUnique]);
CreateTable;
End;
How to retrieve the path from an Alias or TDatabase
Question
I need help in determining the path of the database programmatically
so that I can put an .INI file there with it.
Answer
A:
Here's a quick demo to demonstrate how to get info about aliases in Delphi.
First, create a new project with a listbox and 3 labels (called ListBox1,
Label1, Label2, and Label3). Then add an OnCreate event handler for the form
with this code in it:
procedure TForm1.FormCreate(Sender: TObject);
begin
Session.GetAliasNames(ListBox1.Items);
end;
Now add an OnClick event for the list box:
procedure TForm1.ListBox1Click(Sender: TObject);
var
tStr: array[0..100] of char;
Desc: DBDesc;
begin
if ListBox1.Items.Count = 0 then
exit;
StrPLCopy(tStr, ListBox1.Items.Strings[ListBox1.ItemIndex], High(tStr));
DbiGetDatabaseDesc(tStr, @Desc);
with Desc do
begin
Label1.Caption := StrPas(Desc.szName);
Label2.Caption := StrPas(Desc.szPhyName);
Label3.Caption := StrPas(Desc.szDbType);
end;
end;
Now add the following to the 'uses' clause at the top of the unit:
DB, DBTables, DBITypes, DBIProcs;
You'll see that this gets the path from all of your STANDARD aliases (Paradox
and dBase).
Value of TDBLookupCombo
Question
I am trying to use the TDBLookupCombo on a dialog box that will display the
value of a database field. I then take that value returned from the
TDBLookUpcombo value and store it in a StringList of a ListBox.
Answer
A:
I think I have what you want, um, heres some source code I wrote
awhile to accomplish much the same thing. When you reference the
LookUpValue property you get the field that was .... looked up.
I hope this is what you're after,..... I havn't seen anyone else post
anything here for this so I guess no else thought of this..
unit clookup;
interface
uses
SysUtils, WinTypes, WinProcs, Messages, Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls, DB, DBLookup;
type
TDBJustLookupCombo = class(TDBLookupCombo)
private
{ Private declarations }
protected
{ Protected declarations }
function GetLValue: TField;
public
{ Public declarations }
property LookUpValue: TField read GetLValue;
published
{ Published declarations }
end;
TDBJustLookupList = class(TDBLookupList)
private
{ Private declarations }
protected
{ Protected declarations }
function GetLValue: TField;
public
{ Public declarations }
property LookUpValue: TField read GetLValue;
published
{ Published declarations }
end;
procedure Register;
implementation
procedure Register;
begin
RegisterComponents('Data Controls', [TDBJustLookupList,
TDBJustLookupCombo]);
end;
function TDBJustLookupCombo.GetLValue: TField;
begin
Result := LookupSource.DataSet.FieldByName(LookUpField);
end;
function TDBJustLookupList.GetLValue: TField;
begin
Result := LookupSource.DataSet.FieldByName(LookUpField);
end;
end.
Properly Closing Delphi Database Application
Question
In Windows, we all know that a Windows app will automatically close when
you close Windows itself.
Let's say we have a Delphi app opened, and it has a number of data
tables opened. Can we assume that if a user exits Windows without first
exiting the Delphi app, that all opened tables get updated and close
properly?
If this is not the case, how would one intercept the 'Windows Close'
event and then properly shut down the Delphi app first? Or would there
be a way to intercept the 'Windows Close' event and tell the user that
they must first close down the Delphi application before exiting
windows?
Answer
A:
A very interesting and insightful question!! I'm not entirely sure myself!
But I'll tell what I do know about what happens when there is a request
to shut down Windows:
1. Windows sends a WM_QUERYENDSESSION message to the main
window of all running applications, in effect asking if it is ok to shut down.
If any app responds negatively to this message, Windows will abort the
shutdown.
2. Delphi intercepts this message, and in turn calls TForm.CloseQuery,
(in the main form, presumeably), which triggers your OnCloseQuery event,
if you have one for that form, giving you the opportunity to stop Windows
from shutting down.
3. If I remember correctly, if your app does not prevent Windows from
shutting down, Windows does NOT cause your app to do an orderly
shutdown, because there is no need for it to free up memory or resources,
etc. So, if this is correct (and this could probably be tested quite easily,
but I'm too lazy at the moment), then this OnCloseQuery event is your one
chance to save any data to disk that may be necessary. I don't see this
as a bad thing myself, but it is one of the quirks of Windows programming
that I did not catch the first time around, but several users found it and
reported it. So far as I can know, the worst that can happen with a
database in this scenario is that a record that the user currently has been
editing might not get posted, but the same thing could just as easily happen
during a normal shutdown of the app.
A:
When you exit windows, you call the WM_CLOSE api (or something like
that) for each application currently running. The program closes in
the exact same way it would close if you double clicked on the control
menu or called close from the main form. You do not need to do
anything special to shut down your tables.
Default key field when inserting new record
Question
Does anyone know what is the best way to handle default key values for a
table. I am writing an application that has a Work Order form tied to a
Dbase Work Oder table. I want the user to be able to hit the 'Insert
Record' button on the DBNavigator and have the Work Oder Id automatically fill
in.
I am also storing the Work Oder Id in a TEdit control that is editable
by the user. A second question is how to handle table navigation when
the user manually enters an Id in the Work Order field.
Answer
A:
There are a few different ways to give the table a unique ID.
1. You can use an autoincrement field
This method is not very safe. If your table is corrupted in any way, and you
want to rebuild it, the autoincrement fields will renumber. Although this is
a very easy solution for situation in which you don't refer to the table-id in
other tables, it is not wise to use it in any other situation.
2. You can use a ID-Table
If you have an application where several tables need unique ID's, build a ID
table with two fields:
Table Name A (primary key)
Last Id N
In the BeforePost method of the a table that needs a unique ID, do something
like this:
TableBeforePost(Sender: TObject)
var
Id: Integer;
begin
with TTable(Sender) do
begin
{some check to see if an ID already exists for this record}
if Field[0].AsInteger=0 then
begin
{find the tablename in the ID table}
IDTable.FindKey[Name]
{retrieve the last Id - implicitly lock the record}
Id := IDTable.FieldByName['Last Id'].AsInteger;
Inc(Id);
{write the new Id to the ID table - implicitly unlock the table}
IDTable.FieldByName['Last Id'].AsInteger := Id;
IDTable.Post;
{write the retrieved ID to your table}
Field[0].AsInteger := Id;
end;
end;
end;
end;
If you put this code in the BeforePost event of the table, you'll make sure
that all given ID's are sequential (no "holes"). The drawback is that if a
user tries to insert a new record, but changes her mind, you'll have a record
with only the ID filled in.
If you don't care about sequential ID's, put the above code in the table's
OnNewRecord event.
3. You can use an ID File
Using the same method as the one above, you can use a file instead of a table
to do the same. This has the advantage of higher speed, but in a multi user
environment you must make take care of record locking yourself.
Quickie DB searcher
Question
Answer
Here's a quickie DB search utility. It does its work by finding a string
inside the field (it converts everything to an uppercase string, even floats).
This may be the slowest way to search, but it seems to work faster
than the other solutions I've found on the Net. And it'll hit just
about anything. For ex. say you have float field that has the
number 4.509375354, and you search for 7, you'll hit it. Also, It'll
search on more than one field at a time - handy if you have two address fields.
I'm posting this little unit because when I first started in
Delphi, one of my biggest troubles was finding a search utility and I
didn't know enough to write my own (fear struck deep into my bones).
So I hope this will help those of you who need it like I did.
It's fairly easy to understand, but if you need to know exactly
how to use it, just e-mail me personally and I'll be glad to help.
Look at the case statement to find out which field types are
supported (easy to add more).
{<<<>>>>}
unit Finder;
interface
uses DB, DBTables, SysUtils;
function GrabMemoFieldAsPChar(TheField : TMemoField): PChar;
function DoFindIn(TheField : TField; SFor : String): Boolean;
function FindIt(TheTable : TDataSet; TheFields : array of integer;
SearchBackward : Boolean; FromBeginning : Boolean; SFor : String): Boolean;
{ex. of FindIt -
if FindIt(NotesSearchT,
[NotesSearchT.FieldByName('Leadman').Index],
False, True, SearchText.Text) then DoSomething; }
implementation
function GrabMemoFieldAsPChar(TheField : TMemoField): PChar;
begin
with TBlobStream.Create(TheField, bmRead) do
begin
GetMem(Result, Size + 1);
FillChar(Result^, Size + 1, #0);
Read(Result^, Size);
Free;
end;
end;
function DoFindIn(TheField : TField; SFor : String): Boolean;
var
PChForMemo : PChar;
begin
Result := False;
case TheField.DataType of
ftString :
begin
if (Pos(SFor, UpperCase(TheField.AsString)) > 0) then
Result := True;
end;
ftInteger :
begin
if (Pos(SFor, TheField.AsString) > 0) then Result := True;
end;
ftBoolean :
begin
if SFor = UpperCase(TheField.AsString) then
Result := True;
end;
ftFloat :
begin
if (Pos(SFor, TheField.AsString) > 0) then Result := True;
end;
ftCurrency :
begin
if (Pos(SFor, TheField.AsString) > 0) then Result := True;
end;
ftDate .. ftDateTime :
begin
if (Pos(SFor, TheField.AsString) > 0) then Result := True;
end;
ftMemo :
begin
SFor[Ord(SFor[0]) + 1] := #0;
PChForMemo := GrabMemoFieldAsPChar(TMemoField(TheField));
StrUpper(PChForMemo);
if not (StrPos( PChForMemo, @SFor[1] ) = nil) then Result :=
True; FreeMem(PChForMemo, StrLen(PChForMemo + 1));
end;
end;
end;
function FindIt(TheTable : TDataSet; TheFields : array of integer;
SearchBackward : Boolean; FromBeginning : Boolean; SFor : String): Boolean;
var
i, HighTheFields, LowTheFields : integer;
BM : TBookmark;
begin
TheTable.DisableControls;
BM := TheTable.GetBookmark;
try
LowTheFields := Low(TheFields);
HighTheFields := High(TheFields);
SFor := UpperCase(SFor);
Result := False;
if FromBeginning then TheTable.First;
if SearchBackward then
begin
TheTable.Prior;
while not TheTable.BOF do
begin
for i := LowTheFields to HighTheFields do
begin
if DoFindIn(TheTable.Fields[TheFields[i]], SFor) then
begin
Result := True;
Break;
end;
end;
if Result then Break else TheTable.Prior;
end;
end else
begin
TheTable.Next;
while not TheTable.EOF do
begin
for i := LowTheFields to HighTheFields do
begin
if DoFindIn(TheTable.Fields[TheFields[i]], SFor) then
begin
Result := True;
Break;
end;
end;
if Result then Break else TheTable.Next;
end;
end;
finally
TheTable.EnableControls;
if not Result then
TheTable.GotoBookmark(BM);
TheTable.FreeBookmark(BM);
end;
end;
end.
DBGRID saving the user configuration
Question
Is their a way to save the column order of a grid after the user
reorders the columns via drag n drop.
Answer
I resolved this problem time ago for my one application. Following code is
adapted for you, not tested, but I think it works fine. It create, save and
load configuration's file for order AND SIZE too of fields. I'm at your
disposal for further into something.
procedure TMainForm.NewIni(const NomeIni: string);
var F: System.Text;
i: Byte;
begin
System.Assign(F, NomeIni);
System.ReWrite(F);
System.WriteLn(F, '[Campi_Ordine]');
for i:=1 to Table1.FieldCount do
System.WriteLn(F, 'Campo',i,'=',Table1.Fields[i-1].FieldName);
System.WriteLn(F, '');
System.WriteLn(F, '[Campi_Size]');
for i:=1 to Table1.FieldCount do
System.WriteLn(F, 'Campo',i,'=',Table1.Fields[i-1].DisplayWidth);
System.Close(F);
end;
procedure TMainForm.SaveIni(const FN: String);
var Ini: TIniFile;
i: Integer;
begin
NewIni(FN);
Ini := TIniFile.Create(FN);
with Ini do
begin
for i:=1 to Table1.FieldCount do
begin
S:= Table1.Fields[i-1].FieldName;
WriteString('Campi_Ordine', 'Campo'+IntToStr(i),
Table1.Fields[i-1].FieldName);
WriteInteger('Campi_Size', 'Campo'+IntToStr(i),
Table1.Fields[i-1].DisplayWidth);
end;
end;
Ini.Free;
end;
procedure TMainForm.LoadIni(const FN: String);
var Ini: TIniFile;
i: Integer;
j: Longint;
S: String;
function MyReadInteger(const Section, Ident: string): Longint;
begin
result := Ini.ReadInteger(Section, Ident, -1);
if result=-1 then
raise Exception.Create('Errore nel file di configurazione.');
end;
function MyReadString(const Section, Ident: string): String;
begin
result := Ini.ReadString(Section, Ident, '');
if result='' then
raise Exception.Create('Errore nel file di configurazione.');
end;
begin
Ini := TIniFile.Create(FN);
try
with Ini do
begin
for i:=1 to Table1.FieldCount do
begin
S:= MyReadString('Campi_Ordine', 'Campo'+IntToStr(i));
j:= MyReadInteger('Campi_Size', 'Campo'+IntToStr(i));
Table1.FieldByName(S).Index := i-1;
Table1.FieldByName(S).DisplayWidth := j;
end;
end;
finally
Ini.Free;
end;
end;
DBGrid resize
Question
I have a form. In that an Edit field, an SQL Query, a DBGrid and a Button.
I can write into the edit, and the Query result will put into the grid.
How can I resize the grid and the form to the fields size which appears in
the grid. The fields Which I select with the query does not fill the full
size of the grid or does not fit into it.
Answer
A:
You can change the size of a column at run-time by changing the DisplayWidth
property of the underlying field object...
MyTableMyField.DisplayWidth := Length(MyTableMyField.value);
If you need to actually calculate the width of the entire grid, use the
following (from a tips library)...
function NewTextWidth(fntFont : TFont; const sString : OpenString) :
integer;
var
fntSave : TFont;
begin
result := 0;
fntSave := Application.MainForm.Font;
Application.MainForm.Font := fntFont;
try
result := Application.MainForm.Canvas.TextWidth(sString);
finally
Application.MainForm.Font := fntSave;
end;
end;
{ calculate the width of the grid needed to exactly display with no }
{ horizontal scrollbar and with no extra space between the last }
{ column and the vertical scrollbar. The grid's datasource must be }
{ properly set and the datasource's dataset must be properly set, }
{ though it need not be open. Note: this width includes the width }
{ of the vertical scrollbar, which changes based on screen }
{ resolution. These changes are compensated for. }
function iCalcGridWidth
(
dbg : TDBGrid { the grid to meaure }
)
: integer; { the "exact" width }
const
cMEASURE_CHAR = '0';
iEXTRA_COL_PIX = 4;
iINDICATOR_WIDE = 11;
var
i, iColumns, iColWidth, iTitleWidth, iCharWidth : integer;
begin
iColumns := 0;
result := GetSystemMetrics(SM_CXVSCROLL);
iCharWidth := NewTextWidth(dbg.Font, cMEASURE_CHAR);
with dbg.dataSource.dataSet do
for i := 0 to FieldCount - 1 do with Fields[i] do
if visible then
begin
iColWidth := iCharWidth * DisplayWidth;
if dgTitles in dbg.Options then
begin
iTitleWidth := NewTextWidth(dbg.TitleFont, DisplayLabel);
if iColWidth < iTitleWidth then iColWidth := iTitleWidth;
end;
inc(iColumns, 1);
inc(result, iColWidth + iEXTRA_COL_PIX);
end;
if dgIndicator in dbg.Options then
begin
inc(iColumns, 1);
inc(result, iINDICATOR_WIDE);
end;
if dgColLines in dbg.Options
then inc(result, iColumns)
else inc(result, 1);
end;
-----
I had to use the function NewTextWidth, rather than the Grid's
Canvas.TextWith as the Canvas of the Grid may not initialized when you need
to call iCalcGridWidth.
Getting Deletes to cascade across linked tables
Question
Getting Deletes to cascade across linked tables? How do I do this I even tried
referential integrity. When I delete a record in one table I want to delete the
record LINKED in the other tables.
Answer
A:
I found this procedure at the Borland site and it works in my projects
procedure TForm1.Table1BeforeDelete(DataSet: TDataset)
begin
with Table2 do begin
DisableControls;
First;
While not EOF do
Delete;
EnableControls;
end;
end;
Where table1 is the parent and table2 is the child.
dbGrid and Memo Fields
Question
How can I view the contents of a memo field within a dbGrid component (rather
than viewing ).
Answer
A:
In the GetText event of the TMemoField put this
Text := GrabMemoAsString(TMemoField(Sender));
and put this function somewhere accessible
function GrabMemoAsString(TheField : TMemoField): String;
begin
if TheField.IsNull then
Result := '' else
with TBlobStream.Create(TheField, bmRead) do
begin
if Size >= 255 then
begin
Read(Result[1], 255);
Result[0] := #255;
end else
begin
Read(Result[1], Size);
Result[0] := Chr(Size);
end;
Free;
while Pos(#10, Result) > 0 do
Result[Pos(#10, Result)] := ' ';
while Pos(#13, Result) > 0 do
Result[Pos(#13, Result)] := ' ';
end;
end;
Popup menu that construct from DB
Question
I just create a form and popup menu attached to it. Once user run the form,
it'll read a field from DB and update the popup menu.
Answer
A:
var m:TMenuItem;
navidummy:TComponent;
..........................................................
procedure TMyForm.CreatePopUpMM(Sender: TObject);
begin
Navidummy.free;
Navidummy:=TComponent.create(self);
While not NaviT.EOF do
begin
m := TMenuItem.create(navidummy);
II:=II+1;
with m do
begin
name :='MM'+IntToStr(II);
caption := NaviT.Fieldbyname('MyWHAT').AsString ;
tag := NaviT.Fieldbyname('MyTAG').AsInteger;
visible:=True;
OnClick:= NaviExec ;
end;
MyMenuItem.add(m);
NaviT.Next;
end;
NaviT.Close;
end;
procedure TMyForm.NaviExec(Sender:TObject);
begin
What.text := (Sender as TMenuItem).Caption; { There I get what I want ! }
Key:= (Sender as TMenuItem).Tag ;
end;
DBGrid as Navigator
Question
I'd like to use a DBGrid as a navigating tool as follows:
* Clients table with standard name, phone number etc
* One DBText for each editable field
* DBGrid contains just the name of the client
* Click on a record in the DBGrid and the DBTexts are updated according
to the record clicked.
At the moment, I'm using two DataSources, because I don't want phone
number etc to show in the DBGrid. However, when I click on the DBGrid,
because they're separate datasources, the DBTexts are not updated.
Is there any way I can do this? The clients table is going to be hundreds
of entries long, and paging through them is a pain. Also, editing them
all in a grid would result in a grid about 25 columns wide :-( and this is
also a pain.
Answer
A:
1. Place a table component on a blank form and link it to your Client table.
2. Place a Datasource component and link it to the table component above.
3. Place a grid component and link it the datasource component above.
4. Use the Fields Editor to create TField components for all the fields
in the client table.
5. Set the Visible property of all the TField components, except Client
Name (Or whatever field you would like displayed in the DBGrid), to
False. The gird will now display only the Client Name.
6. Place DBEdit components below the grid to display fields from the
Client table that you want the user to view or edit. They can use the
same datasource as the grid.
The user can now use the grid to navigate and enter/edit data using the
DBEdits.
Display a customer database entry form from a work order form
Question
I am developing a work order program where in one of the fields on the
work order data entry form are "CUST LNAME" and "CUST FNAME". The
"CUST LNAME" field is in a DBLookupCombo box.
The DBLookupCombo box displays the records in the CUSTOMER.DB. If the
customer has no record in the CUSTOMER.DB (therefore new), the user
closes the DBLookupCombo box and has the option to click a "Add
Button".
Another form (lets call it form2) will then be displayed wherein the
user can insert the new customer's record by filling in a customer
database entry form. When the user is done, the form closes and goes
back to the work order data entry form and finish the form.
My problem is:
1. What code do I write in the onclick event on the "Add Button" to
display and execute Form2?
2. Any uses statements that I have to implement?
Answer
A:
I've got a similar function in my project for location numbers for stock items
1. In the OnClick event I create the data entry form and then show it with
.ShowModal
Then I check .ModalResult - if its mrOk, I post the record, otherwise I cancel
2. I put the unit name for my data entry form in my uses clause
Here's the basic outline of my code:
procedure TFrmItemNav.BtnChangeLocClick(Sender: TObject);
{var DlgItemLoc: TDlgItemLoc;}
begin
DlgItemLoc := TDlgItemLoc.Create(FrmItemNav);
DlgItemLoc.ShowModal;
if DlgItemLoc.ModalResult = mrOk then
{do whatever needs doing to Post}
else
{cleanup and Cancel};
DlgItemLoc.Free;
end;
StringGrid
Question
Has anyone successfully load a table into a stringGrid? You might ask why
not use a DBGrid? I need the fixcolumns property of the stringgrid and
rearranging of where some fields are drawn.
Answer
A:
Something like this should work (this is OTTOMH, may need some work):
table.first;
row := 0;
grid.rowcount := table.recordCount;
while not table.eof do begin
for i := 0 to table.fieldCount-1 do
grid.cells[i,row] := table.fields[i].asString;
inc (row);
table.next;
end;
A:
I have my own reasons for using a TStringGrid also. Here's my
code that loads from a filtered table. It's not too elegant
since it was a quick-and-dirty hack. But it works great for
me. Very fast, even on hundreds of rows being loaded. There
are a number of external variable references. Hopefully they
aren't too obscure.
PROCEDURE TformLookupDB.FillCells;
VAR Row, i :INTEGER
; w :INTEGER
; grid :TStringGrid
;
BEGIN
; doGrid.RowCount := 0
; IF NOT ASSIGNED(fDB) THEN EXIT
; Row := 0
; FOR i := LOW(fColWidths) TO HIGH(fColWidths) DO fColWidths[i] := 100
// This temporary grid object is used to prevent a zillion
// implicit Application.ProcessMessages events from being
// triggered by the database, causing really bad flicker
// on the doGrid object. So we'll load into the grid object
// and then copy the data column-wise at the bottom.
; grid := TStringGrid.Create(Self)
; grid.Visible := FALSE
; WITH fDB
DO TRY
; grid.ColCount := fFields.Count
; DisableControls
// Filter should have been set through Self.Filter property
; First
; WHILE NOT EOF
DO TRY
; grid.RowCount := Row+1
; FOR i := 0 TO grid.ColCount-1
DO BEGIN
; grid.Cells[i,Row] :=
FieldByName( fFields.Strings[i] ).AsString
; w := doGrid.Canvas.TEXTWIDTH( grid.Cells[i,
Row] )
; IF fColWidths[i]
Two tables in one DBGrid
Question
I'm trying to make a dbGrid as follow:
Col1: Table1Code
Col2: Table2Description
Answer
If you have D2 you can use the Lookup field properties. To use, double
click on the table object to bring up the fields editor. Then press
+N to add a new field. Just follow the comboboxes and there you
have a lookup field. The TDBGrid will automatically create a drop down
in the grid so the users can select the item.
Text File Line Counts
Question
I'm reading a large text file, extracting field values and inserting a record
for each line into a database. What is the quickest method of obtaining the
number of lines in the text file so that I can display a percentage gauge
while the import is running. The only related info. I could find in the help
file did NOT apply to text files, only binary.
Answer
If the files are not too large, you could do something like:
List := TStringList.Create;
try
List.LoadFromFile('C:\FILE.TXT');
Gauge.MaxValue := List.Count;
finally
List.Free;
end;
But this reads the entire text into memory and I assume this won't do
for you. Another idea is not to use a line counter, but a byte
counter. In the beginning you request the filesize (using the Delphi
FileSize function), and in the loop you maintain the number of bytes
read as you are reading the lines. The loop would be something like
this (assuming you text file is of the standard Pascal type TEXT):
Gauge.MaxValue := FileSize(TextFile);
Reset(TextFile);
while not eof(TextFile) do
begin
Readln(TextFile, Line);
{ Process the line }
with Gauge do
begin
Progress := Progress + Length(Line) + 2; { 2 is for the CR/LF }
Refresh;
end;
end;
FindKey on multiple fields
Question
Could someone show me how to use FindKey with more than one field
without getting some kind of index error message.
Answer
A:
with Table1 do
begin
SetKey;
FieldByName('State').AsString := 'CA';
FieldByName('City').AsString := 'Scotts Valley';
GotoKey;
end;
You cannot use Findkey with DBase files on more than one field .
A:
oEmetb.indexName:='PrimaryKey';
if oEmeTb.findkey([prCLient,prDiv,prEme])then
where the parameters passed to findkey match the Primary Keyfields.
I have found the Index name to be case sensitive, which can catch you out.
You can also use oEmeTb.indexfieldnames, but make sure your keyfield list
exactly matches the key fields you give it to search on or it wont seem to
find existing records.
oEmetb.indexfieldNames:='EmeClient;EmeDiv;EmeNo';
if oEmeTb.findkey([123,'A',96])then
Creating database aliases in code
Question
How can I create aliases in code?
Answer
Typically, you use the BDE Configuration Utility BDECFG.EXE to
create and configure aliases outside of Delphi. However, with
the use of the TDatabase component, you have the ability to
create and use this ALIAS within your application-- not
pre-defined in the IDAPI.CFG.
The ability to create Aliases that are only available within
your application is important. Aliases specify the location
of database tables and connection parameters for database servers.
Ultimately, you can gain the advantages of using ALIASES within
your applications-- without having to worry about the existance
of a configuration entry in the IDAPI.CFG when you deploy your
application.
Summary of Examples:
------- -- ---------
Example #1:
Example #1 creates and configures an Alias to use
STANDARD (.DB, .DBF) databases. The Alias is
then used by a TTable component.
Example #2:
Example #2 creates and configures an Alias to use
an INTERBASE database (.gdb). The Alias is then
used by a TQuery component to join two tables of
the database.
Example #3:
Example #3 creates and configures an Alias to use
STANDARD (.DB, .DBF) databases. This example
demonstrates how user input can be used to
configure the Alias during run-time.
Example #1: Use of a .DB or .DBF database (STANDARD)
1. Create a New Project.
2. Place the following components on the form:
- TDatabase, TTable, TDataSource, TDBGrid, and TButton
3. Double-click on the TDatabase component or choose Database
Editor from the TDatabase SpeedMenu to launch the Database
Property editor.
4. Set the Database Name to 'MyNewAlias'. This name will
serve as your ALIAS name used in the DatabaseName Property for
dataset components such as TTable, TQuery, TStoredProc.
5. Select STANDARD as the Driveer Name.
6. Click on the Defaults Button. This will automatically add
a PATH= in the Parameter Overrides section.
7. Set the PATH= to C:\DELPHI\DEMOS\DATA
(PATH=C:\DELPHI\DEMOS\DATA)
8. Click the OK button to close the Database Dialog.
9. Set the TTable DatabaseName Property to 'MyNewAlias'.
10. Set the TDataSource's DataSet Property to 'Table1'.
11. Set the DBGrid's DataSource Property to 'DataSource1'.
12. Place the following code inside of the TButton's
OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Tablename:= 'CUSTOMER';
Table1.Active:= True;
end;
13. Run the application.
*** If you want an alternative way to steps 3 - 11, place the
following code inside of the TButton's OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Database1.DatabaseName:= 'MyNewAlias';
Database1.DriverName:= 'STANDARD';
Database1.Params.Clear;
Database1.Params.Add('PATH=C:\DELPHI\DEMOS\DATA');
Table1.DatabaseName:= 'MyNewAlias';
Table1.TableName:= 'CUSTOMER';
Table1.Active:= True;
DataSource1.DataSet:= Table1;
DBGrid1.DataSource:= DataSource1;
end;
*****
Example #2: Use of a INTERBASE database
1. Create a New Project.
2. Place the following components on the form:
- TDatabase, TQuery, TDataSource, TDBGrid, and TButton
3. Double-click on the TDatabase component or choose Database
Editor from the TDatabase SpeedMenu to launch the Database
Property editor.
4. Set the Database Name to 'MyNewAlias'. This name will
serve as your ALIAS name used in the DatabaseName Property for
dataset components such as TTable, TQuery, TStoredProc.
5. Select INTRBASE as the Driver Name.
6. Click on the Defaults Button. This will automatically add
the following entries in the Parameter Overrides section.
SERVER NAME=IB_SERVEER:/PATH/DATABASE.GDB
USER NAME=MYNAME
OPEN MODE=READ/WRITE
SCHEMA CACHE SIZE=8
LANGDRIVER=
SQLQRYMODE=
SQLPASSTHRU MODE=NOT SHARED
SCHEMA CACHE TIME=-1
PASSWORD=
7. Set the following parameters
SERVER NAME=C:\IBLOCAL\EXAMPLES\EMPLOYEE.GDB
USER NAME=SYSDBA
OPEN MODE=READ/WRITE
SCHEMA CACHE SIZE=8
LANGDRIVER=
SQLQRYMODE=
SQLPASSTHRU MODE=NOT SHARED
SCHEMA CACHE TIME=-1
PASSWORD=masterkey
8. Set the TDatabase LoginPrompt Property to 'False'. If you
supply the PASSWORD in the Parameter Overrides section and set
the LoginPrompt to 'False', you will not be prompted for the
password when connecting to the database. WARNING: If an
incorrect password in entered in the Parameter Overrides
section and LoginPrompt is set to 'False', you are not prompted
by the Password dialog to re-enter a valid password.
9. Click the OK button to close the Database Dialog.
10. Set the TQuery DatabaseName Property to 'MyNewAliias'.
11. Set the TDataSource's DataSet Property to 'Query1'.
12. Set the DBGrid's DataSource Property to 'DataSource1'.
13. Place the following code inside of the TButton's
OnClick event.
procedure TForm1.Button1Click(Sender: TObject);
begin
Query1.SQL.Clear;
Query1.SQL.ADD(
'SELECT DISTINCT * FROM CUSTOMER C, SALES S
WHERE (S.CUST_NO = C.CUST_NO)
ORDER BY C.CUST_NO, C.CUSTOMER');
Query1.Active:= True;
end;
14. Run the application.
Example #3: User-defined Alias Configuration
This example brings up a input dialog and prompts the
user to enter the directory to which the ALIAS is to
be configured to.
The directory, servername, path, database name, and other
neccessary Alias parameters can be read into the
application from use of an input dialog or .INI file.
1. Follow the steps (1-11) in Example #1.
2. Place the following code inside of the TButton's
OnClick event.
procedure TForm1.Buttton1Click(Sender: TObject);
var
NewString: string;
ClickedOK: Boolean;
begin
NewString := 'C:\';
ClickedOK := InputQuery('Database Path',
'Path: --> C:\DELPHI\DEMOS\DATA', NewString);
if ClickedOK then
begin
Database1.DatabaseName:= 'MyNewAlias';
Database1.DriverName:= 'STANDARD';
Database1.Params.Clear;
Database1.Params.Add('Path=' + NewString);
Table1.DatabaseName:= 'MyNewAlias';
Table1.TableName:= 'CUSTOMER';
Table1.Active:= True;
DataSource1.DataSet:= Table1;
DBGrid1.DataSource:= DataSource1;
end;
end;
3. Run the Application.
Creating and deleting TFields at run-time
Question
How can I create and delete TFields at run-time?
Answer
TField components (or more appropriately, descendants of the TField
component of types corresponding to field types) can be created at design-
time using the Fields Editor. The Fields Editor is invoked by double-
clicking on the design icon for a TTable or TQuery component. But TField
descendants can also be created and deleted at run-time.
Descendants of the TField component (such as TStringField, TIntegerField,
etc.) are created by invoking the Create method for the type of TField
descendant appropriate to the field in the data set. That is, the Create
method for the TStringField descendant of TField would be called to create
a TField descendant for a string-type field in the current data set. The
Create method requires one parameter, that of the owner of the TField
descendant, which is the containing TForm. After creating the TField
descendant component, a number of key properties need to be set in order
to connect it with the field in the data set. These are:
FieldName: the name of the field in the table.
Name: a unique identifier for the TField descendant component.
Index: the TField descendant component's position in the array of
TFields (the Fields property of the TTable or TQuery with which
the TField will be associated).
DataSet: the TTable or TQuery with which the TField will be associated.
The code snippet below demonstrates creating a TStringField. The
containing TForm is called Form1 (referred to here with the Self
variable), the active data set is a TQuery named Query1, and the field for
which the TStringField component is being created is a dBASE table field
named CO_NAME. This new TField descendant will be the second TField in the
Fields array property of Query1. Note that the data set with which the new
TField descendant will be associated (in this case, Query1) must be closed
prior to adding the TField and then reopened afterwards.
procedure TForm1.Button2Click(Sender: TOObject);
var
T: TStringField;
begin
Query1.Close;
T := TStringField.Create(Self);
T.FieldName := 'CO_NAME';
T.Name := Query1.Name + T.FieldName;
T.Index := Query1.FieldCount;
T.DataSet := Query1;
Query1.FieldDefs.UpDate;
Query1.Open;
end;
The example above example creates a new TStringField named Query1CO_NAME.
Deleting an existing TField descendant is merely a matter of invoking the
Free method for that component. In the example below, the TForm's Find-
Component method is used to return a pointer to the TStringField component
named Query1CO_NAME. The return value for the FindComponent will either be
of type TComponent if successful or nil if unsuccessful. This return value
can be used to determine whether the component actually exists prior to
invoking its Free method.
procedure TForm1.Button1Click(Sender: TObject);
var
TC: TComponent;
begin
TC := FindComponent('Query1CO_NAME');
if not (TC = nil) then begin
Query1.Close;
TC.Free;
Query1.Open;
end;
end;
As with creating a TField, if the data set associated with the TField
descendant is currently active, it must be closed or deactivated prior to
invoking this method.
Iterating through the fields of a table
Question
How can I iterate through the fields of a table?
Answer
There are a number of reasons why a program might need to query the
structure of a table used in the application. One reason is a prelude to
creating TField components at run-time that represent the fields in the
table. The information gleaned from the structure of the table form the
basis of the TField components to be created.
The example below demonstrates how to iterate through the fields available
in a TTable or TQuery. The example extracts information about the available
fields and displays the information in a TListBox, but the same methodology
can be used to provide information necessary for the dynamic building of
TField descendants. The example uses a TTable as the data set, but a TQuery
can be used in the same manner as both TTable and TQuery components incorp-
orate the Field-Defs property the same way.
procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
F: TFieldDef;
D: String;
begin
Table1.Active := True;
ListBox1.Items.Clear;
with Table1 do begin
for i := 0 to FieldDefs.Count - 1 do begin
F := FieldDefs.Items[i];
case F.DataType of
ftUnknown: D := 'Unknown';
ftString: D := 'String';
ftSmallint: D := 'SmallInt';
ftInteger: D := 'Integer';
ftWord: D := 'Word';
ftBoolean: D := 'Boolean';
ftFloat: D := 'Float';
ftCurrency: D := 'Currency';
ftBCD: D := 'BCD';
ftDate: D := 'Date';
ftTime: D := 'Time';
ftDateTime: D := 'DateTime';
ftBytes: D := 'Bytes';
ftVarBytes: D := '';
ftBlob: D := 'BLOB';
ftMemo: D := 'Memo';
ftGraphic: D := 'Graphic';
else
D := '';
end;
ListBox1.Items.Add(F.Name + ', ' + D);
end;
end;
Table1.Active := False;
end;
Loading bitmaps into dBase/Paradox BLOB fields
Question
How can I load bitmaps into dBase / Paradox BLOB fields?
Answer
There are a number of ways to load a bitmap image into the BLOB field of a
dBASE or Paradox table. Three of the easier methods involve 1) copying the
data from the Windows clipboard into a TDBImage component connected to the
BLOB field, 2) using the LoadFromFile method of the TBLOBField component,
and 3) using the Assign method to copy an object of type TBitmap into the
Picture property of a TBDBImage.
The first method, copying the bitmap from the clipboard, is probably most
handy when an application needs to add bitmaps to a table when the end-
user is running the application. A TDBImage component is used to act as an
interface between the BLOB field in the table and the image stored in the
clipboard. The PasteFromClipboard method of the TDBImage component is
invoked to copy the bitmap data from the clipboard into the TDBImage. When
the record is posted, the image is stored into the BLOB field in the
table.
Because the Windows clipboard can contain data in formats othher than just
bitmap, it is advisable to check the format prior to calling the CopyFrom-
Clipboard method. To do this, a TClipboard object is created and its Has-
Format method is used to determine if the data in the clipboard is indeed
of bitmap format. Note that to use a TClipboard object, the Clipbrd unit
must be included in the Uses section of the unit that will be creating
the object.
Here is an example showing the contents of the clipboard being copied into
a TDBImage component, if the contents of the clipboard are of bitmap
format:
procedure TForm1.Button1Click(Sender: TObject);
var
C: TClipboard;
begin
C := TClipboard.Create;
try
if Clipboard.HasFormat(CF_BITMAP) then
DBImage1.PasteFromClipboard
else
ShowMessage('Clipboard does not contain a bitmap!');
finally
C.Free;
end;
end;
The second method of filling a BLOB field with a bitmap involves loading
the bitmap directly from a file on dissk into the BLOB field. This method
lends itself equally well to uses at run-time for the end-user as for
the developer building an application's data.
This method uses the LoadFromFile method of the TBLOBField component, the
Delphi representation of a dBASE for Windows Binary field or a Paradox for
Windows Graphic field, either of which may be used to store bitmap data
in a table.
The LoadFromFile method of the TBLOBField component requires a single
parameter: the name of the bitmap file to load, which is of type String.
The value for this parameter may come from a number of sources from the
end-user manually keying in a valid file name to the program providing a
string to the contents of the FileName property of the TOpenDialog comp-
onent.
Here is an example showing the use of the LoadFromFile method for a
TBLOBField component named Table1Bitmap (a field called Bitmap in the
table associated with a TTable component named Table1):
procedure TForm1.Button2Clicck(Sender: TObject);
begin
Table1Bitmap.LoadFromFile(
'c:\delphi\images\splash\16color\construc.bmp');
end;
The third method uses the Assign method to copy the contents of an object
of type TBitmap into the Picture property of a TDBImage component. An
object of type TBitmap might be the Bitmap property of the Picture object
property of a TImage component or it may be a stand-alone TBitmap object.
As with the method copying the data from the clipboard into a TDBImage
component, the bitmap data in the TDBImage component is saved into the
BLOB field in the table when the record is successfully posted.
Here is an example using the Assign method. In this case, a stand-alone
TBitmap object is used. To put a bitmap image into the TBitmap, the
LoadFromFile method of the TBitmap component is called.
procedure TForm1.Button3Click(Sender: TObject);
var
B: TBitmap;
begin
B := TBitmap.Create;
try
B.LoadFromFile('c:\delphi\images\splashh\16color\athena.bmp');
DBImage1.Picture.Assign(B);
finally
B.Free;
end;
end;
Extracting a bitmap from a BLOB field
Question
How can I extract a bitmap from a BLOB field?
Answer
Extracting a bitmap from a dBASE or Paradox blob field -- without first
saving the bitmap out to a file -- is a simple process of using the Assign
method to store the contents of the BLOB field to an object of type
TBitmap. A stand-alone TBitmap object or the Bitmap property of the
Picture object property of a TIMage component are examples of compatible
destinations for this operation.
Here is an example demonstrating using the Assign method to copy a bitmap
from a BLOB field into a TImage component.
procedure TForm1.Button1Click(Sender: TObject);
begin
Image1.Picture.Bitmap.Assign(Table1Bitmap);
end;
In this example, the TBLOBField object Table1Bitmap is a BLOB field in a
dBASE table. This TBLOBField object was created using the Fields Editor.
If the Fields Editor is not used to create TFields for the fields in the
table, the fields must be referenced using either the FieldByName method
or the Fields property, both part of the TTable and TQuery componentts. In
cases where one of those means is used to reference the BLOB field in a
table, the field reference must be type-cast as a TBLOBField object prior
to using the Assign method. For example:
procedure TForm1.Button1Click(Sender: TObject);
begin
Image1.Picture.Bitmap.Assign(TBLOBField(Table1.Fields[1]));
end;
A bitmap stored in a BLOB field may also be copied directly to a stand-
alone TBitmap object. Here is an example showing the creation of a
TBitmap object and storing into it a bitmap from a BLOB field.
procedure TForm1.Button2Click(Sender: TObject);
var
B: TBitmap;
begin
B := TBitmap.Create;
try
B.Assign(Table1Bitmap);
Image1.Picture.Bitmap.Assign(B);
finally
B.Free;
end;
end;
Searching through query result sets
Question
The TQuery component does not offer the index-based search capabilities of
the TTable component (FindKey, GotoKey, and GotoNearest). So how do you
search within the result data set from a TQuery to find a row with a spec-
ific field value?
Answer
One way to search a query result set is a sequential search. This type of
search starts at the first row in the data set and, in a While loop,
sequentially compares the value of a field in the row with a search value.
One of two results are possible: a value will be found (success) or the
end of the data set will be reached (failure). The problem with this way
of searching the data set is that the further into the data set a row with
a matching value is, the longer it takes to arrive at that row. And, a
failed search takes longest of all because it must go all the way to the
last row in the data set. If the data set being searched is a large one,
this process may take a considerable amount of time.
Here is a function that will perfoorm a sequential search of the result set
from a TQuery:
function SeqSearch(AQuery: TQuery; AField, AValue: String): Boolean;
begin
with AQuery do begin
First;
while (not Eof) and (not (FieldByName(AField).AsString = AValue)) do
Next;
SeqSearch := not Eof;
end;
end;
This function takes three parameters:
1. AQuery: type TQuery; the TQuery component in which the search is to
be executed.
2. AField: type String; the name of the field against which the search
value will be compared.
3. AValue: type String; the value being searched for. If the field is of
a data type other than String, this search value should be
changed to the same data type.
The Boolean return value of this function indicates the success (True) or
failure (False) of the search.
An alternative is using a bracketing approach. On a conceptual level, this
method acts somewhat like a bb-tree index. It is based on the given that
for a row at a given point in the data set, the value of the field being
searched compared to the search value will produce one of three possible
conditions:
1. The field value will be greater than the search value, or...
2. The field value will be less than the search value, or...
3. The field value will be equal to the search value.
A bracketing search process uses this means of looking at the current row
in respect to the search value and uses it to successively reduce the rows
to be search by half, until only one row remains. This search field value
for this sole remaining row will either be a match to the search value
(success) or it will not (failure, and no match exists in the data set).
Functionally, this process lumps the condition of the search field being
less than or equal to the search value into a single condition. This
leaves only two possible results for the comparison of the current
search field valuue with the search value: less than/equal to or greater
than. Initially, a range of numbers is established. The low end of the
range is represented by an Integer, at the start of the search process set
to 0 or one less than the first row in the data set. The far end of the
range is also an Integer, with the value of the RecordCount property of
the TQuery. The current row pointer is then moved to a point half way
between the low and high ends of the range. The search field value at that
row is then compared to the search value. If the field value is less than
or equal to the search value, the row being sought must be in the lower
half of the range of rows so the high end of the range is reduced to the
current row position. If the field value is greater than the search value,
the sought value must be in the higher half of the range and so the low
end is raised to the current point. By repeating this process, the number
of rows that are encompassed in the range are successivelly reduced by
half. Eventually, only one row will remain.
Putting this into a modular, transportable function, the code would look
like that below:
function Locate(AQuery: TQuery; AField, AValue: String): Boolean;
var
Hi, Lo: Integer;
begin
with AQuery do begin
First;
{Set high end of range of rows}
Hi := RecordCount;
{Set low end of range of rows}
Lo := 0;
{Move to point half way between high and low ends of range}
MoveBy(RecordCount div 2);
while (Hi - Lo) > 1 do begin
{Search field greater than search value, value in first half}
if (FieldByName(AField).AsString > AValue) then begin
{Lower high end of range by half of total range}
Hi := Hi - ((Hi - Lo) div 2);
MoveBy(((Hi - Lo) div 2) * -1);
end
{Search field less than search value, value in far half}
else begin
{Raise low end of range by half of total range}
Lo := Lo + ((Hi - Lo) div 2);
MoveBy((Hi - Lo) div 2);
end;
end;
{Fudge for odd numbered rows}
if (FieldByName(AField).AsString > AValue) then Prior;
Locate := (FieldByName(AField).AsString = AValue)
end;
end;
Because there will never be a difference of less than one between the low
and high ends of the range of rows, a final fudge was added to allow the
search to find the search value in odd numbered rows.
This function takes the same three three parameters as the SeqSearch
function described earlier.
The return value of this function is of type Boolean, and reflects the
success or failure of the search. As the search does move the row pointer,
the effects of this movement on the implicit posting of changed data and
on where the desired position of the row pointer should be after a failed
search should be taken into account in the calling application. For
instance, a TBookmark pointer might be used to return the row pointer to
where it was prior to a search if that search fails.
How is this process better than a sequential search? First, in bracketing
the search value, only a fraction of the number of rows will be visited as
would be the case in a sequential search. Unless the row with the value
being sought is in the first 1,000 rows, this search method will be faster
than a sequential search. Because this process always uses the same number
of records, the search time will be consistent whether searching for the
value in row 1,000 or row 90,000. This is in contrast with the sequential
search that takes longer the farther into the data set the desired row is.
Can this method be used with any TQuery result set? No. Because of the way
this method works in basing the direction of the search as either high or
low, it depends on the row being ordered in a descending manner based on
the field in which the search will be conducted. This means that it can
only be used if the datta set is naturally in a sequential order or an
ORDER BY clause is used in the SQL statement for the TQuery. The size of
the result set will also be a factor when deciding whether to perform a
sequential or bracketing search. This process is most advantageous for
speed when used with larger result sets. With smaller sets (1,00 or less
rows), though, a sequential search will often be as fast or faster.
Cascading deletes with Paradox referential integrity
Question
How cascade deletes with Paradox referential integrity?
Answer
Paradox tables offer a Referential Integrity feature. This feature pre-
vents adding records to a child table for which there is no matching
record in the parent table. It will also cause the key field(s) in the
child table to be changed when the corresponding key field(s) in the
parent are changed (commonly referred to as a cascading update). These
events occur automatically, requiring no intervention by a Delphi appli-
cation using these tables. However, the Paradox Referential Integrity
feature will not accommodate cascading deletes. That is, Delphi will not
allow you to delete a record in the parent table while matching records
exist in the child table. This would make "orphans" of the child records,
losing referential integrity. Delphi raises an exception when an attempt
is made to delete such a parent record.
To effect a cascading delete requires that the deletion of the matching
child records be deleted programmatically -- before the parent record is
deleted. In aa Delphi application, this is done by interrupting the process
of deleting the record in the parent table, deleting the matching records
in the child table (if there are any), and then continuing with the dele-
tion of the parent record.
A record in a table is deleted by a call to the Delete method of the
TTable component, which deletes the current record in the associated
table. Interrupting the this process to first perform some other opera-
tions is a matter creating a procedure associated with the BeforeDelete
event of the TTable. Any commands in a BeforeDelete event procedure are
executed before the call actually goes out from the application to the
Borland Database Engine (BDE) to physically remove the record from the
table file.
To handle the deletion of one or more child records, in a BeforeDelete
event procedure the Delete method for the TTable representing the child
table is called in a loop. The loop is based on the condition of the
record pointer in the taable not being positioned at the end of the data
set, as indicated by the Eof method of the TTable. This also accounts for
there being no child records at all matching the parent record to be
deleted: if there are no matching records, the record pointer will already
be at the end of the data set, the loop condition will evaluate to False,
and the Delete method in the loop never gets executed.
procedure TForm1.Table1BeforeDelete(DataSet: TDataset);
begin
with Table2 do begin
DisableControls;
First;
while not Eof do
Delete;
EnableControls;
end;
end;
In the above example, the parent table is represented by the TTable comp-
onent Table1 and the child by Table2. The DisableControls and Enable-
Controls methods are used as a cosmetic measure to freeze any data-aware
components that might be displaying data from Table2 while the records
are being deleted. These two methods make the process visually appear
smoother, but aree only optional and not essential to this process. The
Next method need not be called within this loop. This is because the loop
begins at the first record and, as each record is deleted, the record that
previously followed the deleted record moves up in the data set, becoming
both the first and the current record.
This example presumes that the parent and child tables are linked with a
Master-Detail relationship, as is typical for tables for which such
Referntial Integrity is configured. Linking the tables in this manner
results in only those records in the child table that match the current
record in the parent table being available. All other records in the child
table are made unavailable through the Master-Detail filtering. If the
tables are not so linked, there are two additional considerations that
must be accounted for when deleting the child records. The first is that
a call to the First method may or may not put the record pointer on a
record that matches the ccurrent record in the parent table. This necessi-
tates using a search method to manually move the record pointer to a
matching record. The second consideration affects the condition for the
loop. Because records other than those matching the current record in the
parent table will be accessible, the condition for the loop must check
that each record is a matching r |