Pascal Newsletter #7
INDEX
1. A FEW WORDS FROM THE EDITOR
2. DATABASE PROGRAMMING IN CODE
- CREATE STRUCTURE
- USE
- APPEND (and REPLACE)
- GO TOP, GO BOTTOM, SKIP, SKIP n and EOF
- REPLACE
- DELETE
- INSERT
- LOCATE
- LOCATE...CONTINUE and SET FILTER
- SET ORDER TO
- SEEK and (SOFTSEEK or SET NEAR)
- SET FILTER (RANGES)
3. THE NEWBIE & THE PRO
- Indexes and pointers
4. LINKS
________________________________________________________________________
1. A FEW WORDS FROM THE EDITOR
We would like to thank all our subscribers who answered the question-
naire we sent a couple of weeks ago, especially those who made comments
since they have ALL been very useful. We apologize for not replying, but
we hope you understand we received an avalanche of emails.
The result is that definitely the format of the newsletter will remain
more or less as it is now: fortnightly, 25K and plain text. A little bit
more concise maybe. About the attachments, in the future we will send
them only for those who choose to receive them (they are the great
majority, but part of the minority clearly rejected this idea, so we
feel there is no enough consensus to send attachments to all). The level
of the newsletter will keep being more or less the same in average, but
sometimes we will try to mix a bit of more and less advanced topics, or
we will try to mix "advanced" with "trivial" explanations. Won't be an
easy task...
The writing rated as "good" and this is enough for us since we are not
native English speakers, but we would like to improve if we can. We
would appreciate it very much if someone could spot the common mistakes
we make or tell us how he/she would have expressed this and that.
This newsletter is not something we do for ourselves, but for you, so
please don't wait till the next questionnaire in three months to make
comments, critics, suggestions, propose topics, etc. Ok?
Regards,
Ernesto De Spirito
eds2008 @ latiumsoftware.com
________________________________________________________________________
JfControls Library. Multi-language. Multi-appearance. Skins. Privileges.
More than 40 integrated and customizable components. Impressive GUI.
Centralized resources administration. Multiple programming problems
solved. For Delphi 3-2006 & C++ Builder 3-6. http://www.jfactivesoft.com
________________________________________________________________________
2. DATABASE PROGRAMMING IN CODE
Most of those who worked with an xBase language (like dBase, Fox or
Clipper) seem to miss the possibility of doing everything in code,
without getting involved with components on a form and an Object
Inspector... Well, the good news for everybody is that Delphi allows
everything that can be done visually to also be done by code.
Here we will provide some examples of database programming using the
BDE and in the future we will deal also with ADO (most things remain
the same). To try the examples, please create a new application and in
the form unit add DB and DBTables to the USES clause.
CREATE STRUCTURE
================
To create a table we can use a code like the following:
procedure TForm1.Button1Click(Sender: TObject);
var
Products: TTable;
begin
Products := TTable.Create(nil);
try
with Products do begin
// Table definition
DatabaseName := ExtractFilePath(Application.ExeName);
TableType := ttDBase;
TableName := 'Products.DBF';
with FieldDefs do begin // Field definitions
with AddFieldDef do begin
Name := 'ProductID';
DataType := ftSmallint;
end;
with AddFieldDef do begin
Name := 'Description';
DataType := ftString;
Size := 25;
end;
with AddFieldDef do begin
Name := 'Price';
DataType := ftFloat;
end;
end;
with IndexDefs do begin // Index definitions
with AddIndexDef do begin
Name := 'ByID';
Fields := 'ProductID';
end;
with AddIndexDef do begin
Name := 'ByDescr';
Fields := 'Description';
end;
end;
CreateTable; // Creates the table
end;
except
Products.Free;
raise;
end;
Products.Free;
end;
First we declare a TTable component and we create it with no owner (nil
parameter), meaning we will be responsible of freeing the object. If
we had written
Products := TTable.Create(Self);
instead, then the component would belong to Form1 and the form will
free the table when it gets freed itself (for example when the app
ends). Since we decided to "manually" free the object, we use a TRY
block to insure that we get to do it even if an exception occurs.
Then we do all the process of creating a table within a WITH block to
save us typing... Without a WITH block we would have to write:
Products.DatabaseName := ExtractFilePath(Application.ExeName);
Products.TableType := ttDBase;
Products.TableName := 'Products.DBF';
Products.FieldDefs.Clear;
...
The advantage of not using a WITH block is that you can use the code
insight features of the code editor and also sometimes your code is
clearer. Using WITH you write less and your code is more efficient when
the WITH expression resolves some reference (for example "with a.b do"
or "with a[i] do" or in some nested WITHs) because the resolution is
done once and the result is being kept in a CPU register (EBX to be
precise) for faster access.
Now, let's go back to the table properties...
* DatabaseName is the name of the database the table belongs (or will
belong) to. The value of this property can be either a directory (for
example we used the directory where the application is located) or a
BDE alias (for example DBDEMOS).
* TableType defines the type of the table. Normally we only set it when
we are creating a table. In the example we chose a dBase table.
* TableName is the name of the table. For example for dBase and Paradox
tables this is the name of the table file (the directory is determined
by DatabaseName).
* FieldDefs is a list of TFieldDef objects, each of them representing a
field definition. The method AddFieldDef adds a field definition to
the list and returns a reference to it, that we use in a WITH block to
set the properties of this newly added field (for example: Name,
DataType, Size, Precision, Required, etc.).
* IndexDefs is a list of TIndexDef objects, each of them representing an
index definition. The method AddIndexDef adds an index definition to
the list and returns a reference to it, that we use in a WITH block to
set the properties of this newly added index (for example: Name,
Fields, etc.). If you want an index for more than one field, all you
have to do is separate them with semicolons:
Fields := 'Field1;Field2;Field3';
Once we set all these properties (IndexDefs being optional) we proceed
to create the table invoking the CreateTable method. After this you
should have a file named Products.DBF in your application's directory.
USE
===
To open and close a table we can use the Active property or the Open and
Close methods:
procedure TForm1.Button2Click(Sender: TObject);
var
Products: TTable;
begin
Products := TTable.Create(nil);
try
with Products do begin
DatabaseName := ExtractFilePath(Application.ExeName);
TableName := 'Products.DBF';
Open; // Active := True;
// Work with the table here
Close; // Active := False;
end;
except
Products.Free;
raise;
end;
Products.Free;
end;
APPEND (and REPLACE)
====================
To add new records at the end of the table we can add some lines between
the Open and Close method calls above:
// APPEND BLANK
// REPLACE ProductID WITH 137, Description WITH 'Sodas', ;
// Price WITH 0.35
Append;
FieldByName('ProductID').AsInteger := 137;
FieldByName('Description').AsString := 'Sodas';
FieldByName('Price').AsFloat := 0.35;
Post;
Let's add a few more records to use them later in the examples:
Append;
FindField('ProductID').AsInteger := 243;
FindField('Description').AsString := 'Cigars';
FindField('Price').AsFloat := 1.15;
Post;
Append;
Products['ProductID'] := 395;
Products['Description'] := 'Cookies';
Products['Price'] := 1.35;
Post;
Append;
FieldValues['ProductID'] := 45;
FieldValues['Description'] := 'Candies';
FieldValues['Price'] := 0.05;
Post;
Append adds a blank record (not blank actually since fields can have
default values) but the operation is not completed until you call the
Post method. As you can see, there are many ways of referencing the
fields and their values:
* FieldByName and FindField both return a reference to a TField object.
The difference is that if the field name passed as parameter is not
found, FieldByName raises an exception while FindField returns nil.
* FieldValues is the default property of the TTable class (this means
that for example "Table1.FieldValues['Field1']" is the same as just
"Table1['Field1']") and represents an array of variants with the
values of the fields.
GO TOP, GO BOTTOM, SKIP, SKIP n and EOF
=======================================
Now that we have four records
ProductID Description Price
137 Sodas 0.35
243 Cigars 1.15
395 Cookies 1.35
45 Candies 0.05
we can move thru them:
First; // Goes to the first record (GO TOP)
ShowMessage(FieldByName('Description').AsString); // Sodas
Last; // Goes to the last record (GO BOTTOM)
ShowMessage(FieldValues['Description']); // Candies
Next; // Goes to the next record (SKIP)
if Eof then ShowMessage('EOF');
ShowMessage(Products['Description']); // Candies
Prior; // Goes to the previous record (SKIP -1)
ShowMessage(FieldValues['Description']); // Cookies
MoveBy(-2); // Goes up two records (SKIP -2)
ShowMessage(FieldByName('Description').AsString); // Sodas
In xBase dialects, when you are at the end of file (EOF), you are
positioned in a sort of blank record following the last record of the
table, and the fields value zero, false, spaces or the blank date
depending on the field types. Once you reached EOF, you can go back to
the last record with SKIP -1.
In Visual Basic, when you are at EOF, there is no current record (any
attempt to access field values results in an error), but you can go back
to the last record with MovePrevious.
In Delphi, EOF is a flag indicating you attempted to move past the end
of file, but you are still positioned in the last record (unless the
table is empty when there is no current record).
REPLACE
=======
To replace the values of one or more fields, we use Edit to prepare the
record for changes, then we update the fields and finally we save the
changes with Post:
// REPLACE Description WITH 'Diet Sodas', Price WITH 0.40
Edit;
FieldByName('Description').AsString := 'Diet Sodas';
FieldByName('Price').AsFloat := 0.40;
Post;
DELETE
======
ShowMessage(FieldByName('Description').AsString); // Diet Sodas
Delete;
ShowMessage(FieldByName('Description').AsString); // Cigars
In xBase languages, right after you have deleted a record, this record
is still current and can be recovered (with RECALL). You can move to the
next record with SKIP.
In Visual Basic, after calling the Delete method of a Recordset there is
no current record (any attempt to access field values results in an
error), but you can go to next record with MoveNext. Deleted records are
not recoverable (using VB Recordsets at least).
In Delphi, the Delete method deletes the current record and the next
record becomes current. Deleted records are not recoverable (using
Delphi Datasets at least).
INSERT
======
Last;
Insert; // Can add before the last record or at the end
FieldByName('ProductID').AsInteger := 137;
FieldByName('Description').AsString := 'Sodas';
FieldByName('Price').AsFloat := 0.35;
Post;
Insert is almost like Append. Depending on the database type the new
record can be added at the end of the table or be physically inserted
before the current record. For DBFs, Insert is the same as Append.
LOCATE
======
Locate finds the first record with the field values you specify. For
example:
// LOCATE FOR Description = 'CANDIES'
Locate('Description', 'CANDIES', [loCaseInsensitive]);
ShowMessage(FieldByName('Description').AsString); // Candies
// LOCATE FOR ProductID = 243 .AND. Description = 'CIGARS'
Locate('ProductID;Description', VarArrayOf([243,'CIGARS']),
[loCaseInsensitive]);
ShowMessage(FieldByName('Description').AsString); // Cigars
You can search for partial keys (only affects string fields):
Locate('Description', 'COOK', [loCaseInsensitive, loPartialKey]);
ShowMessage(FieldByName('Description').AsString); // Cookies
And you can perform case-sensitive searches:
if not Locate('Description', 'CANDIES', []) then
ShowMessage('CANDIES not found');
ShowMessage(FieldByName('Description').AsString); // Cigars
Locate returns True if a record was found, and False otherwise.
After an unsuccessful search, in xBase languages EOF gets to be the
current record, in Visual Basic there is no current record, and in
Delphi the current record doesn't change.
LOCATE...CONTINUE and SET FILTER
================================
If we needed to match the next record/s that matches a criteria, in
xBase we can do the following:
LOCATE FOR <condition>
WHILE .NOT. EOF()
* Process record here
CONTINUE
ENDDO
That can also be accomplished as:
SET FILTER TO <condition>
GO TOP
WHILE .NOT. EOF()
* Process record here
SKIP
ENDDO
SET FILTER TO
In Delphi Datasets we don't have an equivalent for CONTINUE, so we have
to use filters. There are two ways of setting a filter:
1) Using the Filter property (and the FilterOptions property)
FilterOptions := <filteroptions>;
Filter := <condition>; // Sets the filter
if FindFirst then
repeat
// Process record here
until not FindNext;
Filter := ''; // Removes the filter
2) Using the OnFilterRecord event
OnFilterRecord := <filter event procedure>; // Sets the filter
if FindFirst then
repeat
// Process record here
until not FindNext;
OnFilterRecord := nil; // Removes the filter
We'll see both ways in the following example:
type
TForm1 = class(TForm)
...
private
{ Private declarations }
procedure AFilter(DataSet: TDataSet; var Accept: Boolean);
public
{ Public declarations }
end;
...
procedure TForm1.AFilter(DataSet: TDataSet; var Accept: Boolean);
begin
Accept := Pos('K', DataSet['Company']) <> 0;
end;
procedure TForm1.Button3Click(Sender: TObject);
var
Customers: TTable;
begin
Customers := TTable.Create(nil);
try
with Customers do begin
DatabaseName := 'DBDEMOS';
TableName := 'Customer.DB';
Active := True;
FilterOptions := [foCaseInsensitive];
Filter := 'Country = ' + QuotedStr(InputBox(
'Filter', 'Enter Country to filter', 'bAhAmAs'));
if FindFirst then
repeat
ShowMessage(FieldValues['Company'] + ' ('
+ FieldValues['Country'] + ')');
until not FindNext;
Filter := ''; // Removes the filter
ShowMessage('Now with the OnFilterRecord event');
OnFilterRecord := AFilter;
if FindFirst then
repeat
ShowMessage(FieldValues['Company']);
until not FindNext;
OnFilterRecord := nil; // Removes the filter
Active := False;
end;
except
Customers.Free;
raise;
end;
Customers.Free;
end;
FindFirst, FindNext, FindPrior and FindLast are like First, Next, Prior
and Last, but they only move thru the records that match the filter
criteria and they return a value (True if a record is found and False
otherwise).
Filtering via the Filter property is simpler because it can be done in
situ and it is somehow more powerful in the sense that you can prompt
advanced users for the whole condition:
Customers.Filter := InputBox('Filter', 'Enter condition',
'Country = ''US'' OR Country = ''Bahamas''');
Using the OnFilterRecord event implies some more lines of code, but
it might be faster since the BDE doesn't have to parse the filter
condition, and it is somehow more powerful in the sense that you are not
limited to a "simple" filter condition and in the event handler you can
build your own logic to determine if a record should be filtered or not
(you can call functions, use IFs, search for records in other tables,
etc.). Finally you have to set the Accept parameter to True (the record
matches the criteria) or False (the record should be excluded).
SET ORDER TO
============
To determine which one is the active index all we have to do is set the
IndexName property to the name of the index. For example:
procedure TForm1.Button4Click(Sender: TObject);
var
Products: TTable;
begin
Products := TTable.Create(nil);
try
with Products do begin
DatabaseName := ExtractFilePath(Application.ExeName);
TableName := 'Products.DBF';
Open; // Active := True;
// No index marked as primary key. Default is natural order
ShowMessage(FieldByName('Description').AsString); // Cigars
IndexName := 'ByID'; // SET ORDER TO ByID
First;
ShowMessage(FieldValues['Description']); // Candies
IndexName := 'ByDescr'; // SET ORDER TO ByDescr
First;
ShowMessage(FieldValues['Description']); // Cookies
IndexName := ''; // SET ORDER TO
First;
ShowMessage(FieldValues['Description']); // Cigars
Close; // Active := False;
end;
except
Products.Free;
raise;
end;
Products.Free;
end;
SEEK and (SOFTSEEK or SET NEAR)
===============================
There is a short way and a long way to perform a seek. Here is the long
way:
// SEEK Value1+Value2
// IF FOUND()
// ...
SetKey; // Clears the key
FieldValues['Field1'] := Value1;
FieldValues['Field2'] := Value2;
if GotoKey then ...
// SEEK Value1+Value3
// IF FOUND()
// ...
EditKey; // Edits the key
FieldValues['Field2'] := Value3;
if GotoKey then ...
And here are the short versions for the above examples:
if FindKey([Value1, Value2]) then ...
if FindKey([Value1, Value3]) then ...
The long way is a bit more performant, but the difference is only
marginal, so we prefer the short one because it is much simpler.
GotoKey and FindKey return True if they found an exact match and False
otherwise. You can search for partial keys using GotoNearest and
FindNearest procedures instead, that are the equivalent of calling
SOFTSEEK in Clipper or using SET NEAR ON in FoxPro.
Here are some examples:
IndexName := 'ByID';
SetKey;
FieldByName('ProductID').AsInteger := 137;
if GotoKey then
ShowMessage(FieldValues['Description']); // Sodas
if FindKey([45]) then
ShowMessage(FieldValues['Description']); // Candies
IndexName := 'ByDescr';
SetKey;
FieldByName('Description').AsString := 'Cigars';
if GotoKey then
ShowMessage(FieldValues['Description']); // Cigars
SetKey;
FieldByName('Description').AsString := 'CANDIES';
if GotoKey then
ShowMessage(FieldValues['Description'])
else
ShowMessage('CANDIES not found');
SetKey;
FieldByName('Description').AsString := 'Co';
GotoNearest;
if Copy(FieldValues['Description'],1,2) = 'Co' then
ShowMessage(FieldValues['Description']); // Cookies
FindNearest(['Can']);
if Copy(FieldValues['Description'],1,3) = 'Can' then
ShowMessage(FieldValues['Description']); // Candies
SET FILTER (RANGES)
===================
There is a third way to set a filter: setting a key range in the active
index. For example:
// SET ORDER TO ByID
// SET FILTER TO ProductID >= 100 .AND. ProductID <= 300
// GO BOTTOM
// ? Description
// SEEK 45
// IF FOUND()
// ? Description
// ELSE
// ? 'Candies not found'
// ENDIF
// SET FILTER TO
IndexName := 'ByID';
SetRange([100],[300]);
ApplyRange;
Last;
ShowMessage(FieldValues['Description']); // Cookies
if FindKey([45]) then
ShowMessage(FieldValues['Description'])
else
ShowMessage('Candies not found');
CancelRange;
This would be it for now. We'll keep exploring the ways to do in Delphi
those things that can be done in xBase languages in the next issue. The
complete source code examples of this newsletter are available at:
http://www.latiumsoftware.com/en/file.php?id=p07
The examples of the last issue were uploaded a bit late. We apologize
for the inconvenience.
________________________________________________________________________
3. THE NEWBIE & THE PRO
Indexes and pointers
Here we present an example of a function that returns the position of
the first occurrence of a character within a string.
NEWBIE:
function Scan(s: string; char c): integer;
var
i: integer:
begin
Result := 0;
for i := 1 to Length(s) do begin
if s[i] = c then begin
Result := i;
break;
end;
end;
end;
PRO:
function Scan(const s: string; char c): integer;
var
i: integer:
p: pchar;
begin
Result := 0;
p := PChar(s);
for i := 1 to Length(s) do begin
if p^ = c then begin
Result := i;
break;
end;
inc(p);
end;
end;
EXPLANATION:
PChar stands for "pointer to char". The sentence "p := PChar(s);" makes
p point to the first character in the string s, so saying s[1] is the
same as saying p^ (that means "the character pointed by p").
The sentence "inc(p);" increments (i.e. add one to) the pointer, making
it point to the next character, so the first time it is executed p will
point to s[2] (so p^ = s[2]). The next time p is incremented, it will
point to s[3] (so p^ = s[3]), and so on, so we have the same effect as
if we used indexes. The difference is that with pointers the function
is faster.
Another change we made is adding "const" in the declaration of the
string parameter. This tells the compiler that the string should be
passed by reference (faster), but that we won't attempt to change it.
POINTER ARITHMETIC:
function Scan(const s: string; char c): integer;
var
p, q: pchar;
begin
p := PChar(s);
q := StrScan(p, c);
if q := nil then
Result := 0
else
Result := q - p + 1;
end;
Delphi provides a function named StrScan that takes a pchar instead of
a string as a parameter and returns a pointer to the first occurrence of
the character, instead of its position, so we use pointer arithmetic to
get this position.
For example, let's suppose we call "Scan('OOOXOOO', 'X');"
1) After "p := PChar(s);", p is the memory address of the first
character in the string s. A memory address is a number (like an
index in the memory array), for example $1000.
2) After "q := StrScan(p, c);" q points to the first occurrence of
the character c in the string, so in the example q would be
$1003.
3) To get position we use the formula "q - p + 1". In our example it
would be: $1003 - $1000 + 1 = 3 + 1 = 4
+---+---+---+---+---+-
| O | O | O | X | O |
+---+---+---+---+---+-
1 2 3 4 5
| | | | |
p = $1000 ----+ | | | |
$1001 --------+ | | |
$1002 ------------+ | |
q = $1003 ----------------+ |
$1004 --------------------+
So the found character is the fourth of the string.
________________________________________________________________________
4. LINKS
* Delphi applications with source code
http://sunsite.informatik.rwth-aachen.de/delphi/
* Delphi Links in the Open Directory Project
http://dmoz.org/Computers/Programming/Languages/Delphi/
* Delphi Links at Cetus-Links.org
http://www.cetus-links.org/oo_delphi.html
* Oberon Microsystems - Here you will find BlackBox, a component-based
software development tool that uses Oberon, a successor of the Pascal
language invented by the very same Prof. Niklaus Wirth. There is an
educational version available for download (~6 Mb). Check it out and
then tell us how you liked it. http://www.oberon.ch
________________________________________________________________________
YOU CAN HELP US
We need your help to keep this newsletter going and growing. You can
help by referring the newsletter to your colleagues:
http://www.latiumsoftware.com/en/pascal/delphi-newsletter.php
Or you can help by voting for us in some or all of these rankings to
give more visibility to our web site and thus increase the number of
subscriptions to this newsletter:
http://www.programmingpages.com/?r=latiumsoftwarecomenpascal
http://top100borland.com/in.php?who=20
It's just a few seconds for you that REALLY mean a lot to us.
________________________________________________________________________
If you haven't received the full source code examples for this issue,
you can get them from http://www.latiumsoftware.com/en/file.php?id=p07
________________________________________________________________________
This newsletter is provided "AS IS" without warranty of any kind. Its
use implies the acceptance of our licensing terms and disclaimer of
warranty you can read at http://www.latiumsoftware.com/en/legal.php
where you will also find a note about legal trademarks. Articles are
copyright of their respective authors and they are reproduced here with
their permission. You can redistribute this newsletter as long as you do
it in full (including copyright notices), without changes, and gratis.
________________________________________________________________________
Main page: http://www.latiumsoftware.com/en/pascal/delphi-newsletter.php
Group home page: http://groups.yahoo.com/group/pascal-newsletter/
Subscribe/join: pascal-newsletter-subscribe@yahoogroups.com
Unsubscribe/leave: pascal-newsletter-unsubscribe@yahoogroups.com
Problems with your subscription? eds2008 @ latiumsoftware.com
________________________________________________________________________
Latium Software http://www.latiumsoftware.com/en/index.php
Copyright (c) 2000 by Ernesto De Spirito. All rights reserved.
________________________________________________________________________
|
