Pascal Newsletter #9
INDEX
1. A FEW WORDS FROM THE EDITOR
2. DATABASE PROGRAMMING IN CODE (III)
* ACTION QUERIES
- UPDATE
- INSERT
- DELETE
* DATA DEFINITION LANGUAGE (DDL)
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
- CREATE INDEX
- DROP INDEX
* SUBQUERIES
- IN THE FROM CLAUSE
- IN THE WHERE OR HAVING CLAUSES
- Exists
- In
- AS A VALUE
3. SOME NOTES ABOUT PROGRAMMING IN NETWORKING ENVIRONMENTS
* PARADOX DATABASES
* IMPROVING PERFORMANCE
- CACHED UPDATES
- CACHING COMPONENTS
- KEY RANGES
- PREPARE QUERIES
- PACK TABLES
- LOCAL TABLES AND CLIENT DATASETS
4. ROUNDING NUMBERS
5. KYLIX LINKS
________________________________________________________________________
1. A FEW WORDS FROM THE EDITOR
About the article "KYLIX FAQ" of the last issue, a subscriber informed
us that the beta version of Delphi for Linux runs on a select number of
Linux distributions and that the executables it produces require large
runtime libraries. We really hope the final version will run on all
distros and will produce standalone executables, but we can't tell
something for sure since all the information provided by Borland about
the Kylix project is non official and subject to change until near the
the date of the release, when the real facts will be known.
Borland announced the dates and location of the BorCon 2001. The 12th
Annual Borland Conference (Americas) will take place from July 21 to
July 25, 2001 in Long Beach, California. Check this URL for more
information: http://www.borland.com/conf2001/
There are a couple of new tips in our Delphi Tips section: How to...
* Change the screen resolution?
* Make an application run automatically when Windows starts?
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 (III)
ACTION QUERIES
==============
UPDATE
------
UPDATE tablename SET field1 = value1 [, field2 = value2 [, ...]]
[WHERE condition]
This SQL sentence allows you to modify the values of one or more fields
in one or more records of a table.
In xBase:
USE custoly
REPLACE ALL Country WITH "USA" FOR Country = "U.S.A."
In SQL:
UPDATE custoly SET Country = "USA" WHERE Country = "U.S.A."
In Delphi:
procedure TForm1.Button1Click(Sender: TObject);
var
Query1: TQuery;
begin
Query1 := nil;
try
Query1 := TQuery.Create(nil);
Query1.DatabaseName := 'DBDEMOS';
Query1.SQL.Add('UPDATE custoly SET Country = "USA"');
Query1.SQL.Add('WHERE Country = "U.S.A."');
Query1.ExecSQL;
except
Query1.Free;
raise;
end;
Query1.Free;
end;
INSERT
------
INSERT INTO tablename (field1 [, field2 [, ...]])
VALUES (value1 [, value2 [, ...]])
INSERT INTO tablename (field1 [, field2 [, ...]])
SELECT ...
This SQL sentence adds a record or records to a table. The following
example shows how to add a single record to a table:
In xBase:
USE custoly
APPEND BLANK
REPLACE Last_Name WITH "Doe", First_Name WITH "John", ;
EMail WITH "johndoe@nn.com"
In SQL:
INSERT INTO custoly (Last_Name, First_Name, EMail)
VALUES ("Doe", "John", "johndoe@nn.com");
In Delphi:
...
Query1.SQL.Add('INSERT INTO custoly (Last_Name, First_Name,');
Query1.SQL.Add(' EMail) VALUES ("Doe", "John", "johndoe@nn.com");');
Query1.ExecSQL;
...
The following example shows how to add many records to a table:
In xBase:
USE custoly
APPEND FROM custoly2 FOR Country = "USA" ;
FIELDS Last_Name, First_Name, EMail
In SQL:
INSERT INTO custoly (Last_Name, First_Name, EMail)
SELECT Last_Name, First_Name, EMail FROM custoly2
WHERE Country = "USA";
In Delphi:
...
Query1.SQL.Add('INSERT INTO custoly (Last_Name, First_Name, EMail)');
Query1.SQL.Add('SELECT Last_Name, First_Name, EMail FROM custoly2');
Query1.SQL.Add('WHERE Country = "USA";');
Query1.ExecSQL;
...
DELETE
------
DELETE FROM tablename [WHERE condition]
This SQL sentence deletes one or more records from a table.
In xBase:
USE custoly
DELETE ALL FOR Country = "US"
In SQL:
DELETE FROM custoly WHERE Country = "US"
In Delphi:
...
Query1.SQL.Add('DELETE FROM custoly WHERE Country = "US"');
Query1.ExecSQL;
...
The following example shows how to delete all records of the table:
In xBase:
USE custoly
DELETE ALL
In SQL:
DELETE FROM custoly
DATA DEFINITION LANGUAGE (DDL)
==============================
Local SQL supports a few DDL sentences that allow you to create, modify
and delete tables, and to create and delete indexes.
CREATE TABLE
------------
CREATE TABLE tablename (field1 type1 [(scale1 [,precision1])]
[, field2 type2 [(scale2 [,precision2])] [, ...] ]
[, [CONSTRAINT constraint]
PRIMARY KEY (keyfield1 [, keyfield2 [, ...]]) ] )
This SQL sentence allows you to create a table in a much simpler way
than using the CreateTable method we introduced in a past issue.
Example (SQL):
CREATE TABLE "Products.DBF" (ProductID SMALLINT,
Description VARCHAR(25), Price FLOAT(10,2),
PRIMARY KEY (ProductID))
Example (Delphi):
...
Query1.SQL.Add('CREATE TABLE "Products.DBF" (ProductID SMALLINT,');
Query1.SQL.Add('Description VARCHAR(25), Price NUMERIC(10,2),');
Query1.SQL.Add('PRIMARY KEY (ProductID))');
Query1.ExecSQL;
...
ALTER TABLE
-----------
ALTER TABLE tablename { DROP [COLUMN] field1 | ADD [COLUMN] field1
type1 [(scale1 [,precision1])] } [, { DROP [COLUMN] field2 | ADD
[COLUMN] field2 type2 [(scale2 [,precision2])] } [,...] ]
This SQL sentence allows you to add and/or remove table columns
(fields). For example:
ALTER TABLE "Products.DBF" ADD Cost NUMERIC(10,2),
ADD Stock INTEGER
ALTER TABLE "Products.DBF" DROP Stock, ADD OnHand FLOAT(6,1)
In Delphi:
...
Query1.SQL.Add('ALTER TABLE "Products.DBF" ADD Cost NUMERIC(10,2),');
Query1.SQL.Add('ADD Stock INTEGER');
Query1.ExecSQL;
Query1.SQL.Clear;
Query1.SQL.Add('ALTER TABLE "Products.DBF" DROP Stock,');
Query1.SQL.Add('ADD OnHand FLOAT(6,1)');
Query1.ExecSQL;
...
DROP TABLE
----------
DROP TABLE tablename
This SQL sentence deletes a table.
Example:
...
Query1.SQL.Text := 'DROP TABLE "Products.DBF"';
Query1.ExecSQL;
...
CREATE INDEX
------------
CREATE [UNIQUE] [ASC | DESC] INDEX indexname ON tablename
(keyfield1 [, keyfield2 [,...]])
This SQL sentence creates an index on the specified table. For example:
In xBase:
USE vendors
INDEX ON VendorName TO ByName
In SQL:
CREATE INDEX ByName ON vendors (VendorName)
In Delphi:
...
Query1.SQL.Text := 'CREATE INDEX ByName ON vendors (VendorName)';
Query1.ExecSQL;
...
DROP INDEX
----------
DROP INDEX tablename.indexname
This SQL sentence deletes an index of the specified table. To delete the
primary key you have to write PRIMARY for the index name.
Example:
...
Query1.SQL.Text := 'DROP INDEX vendors.ByName';
Query1.ExecSQL;
...
SUBQUERIES
==========
A subquery is a selection query inside another query. Subqueries are
enclosed in parenthesis. There are many places where you can use a
subquery...
IN THE FROM CLAUSE
------------------
Most SQL engines allow a subquery to be used as a table in the FROM
clause, but Local SQL is not among them. For example, this wouldn't
work:
SELECT Company, OrderNo, ItemsTotal
FROM
(SELECT OrderNo, CustNo, ItemsTotal FROM Orders
WHERE ItemsTotal > 50000) Ord
INNER JOIN Customer ON Ord.CustNo = Customer.CustNo
WHERE Country = "US"
In the case of the example, the query can be modified to get the desired
result without using subqueries:
SELECT Company, OrderNo, ItemsTotal
FROM Orders INNER JOIN Customer ON Orders.CustNo = Customer.CustNo
WHERE Country = "US" AND ItemsTotal > 50000
When this is not possible, the solution is:
1) Create a temporary table:
CREATE TABLE tmp (OrderNo NUMERIC(8),
CustNo NUMERIC(8), ItemsTotal MONEY);
2) Make it hold the results of the former subquery:
INSERT INTO tmp (OrderNo, CustNo, ItemsTotal)
SELECT OrderNo, CustNo, ItemsTotal
FROM Orders WHERE ItemsTotal > 50000;
3) Use this temporary table in the original query:
SELECT Company, OrderNo, ItemsTotal
FROM tmp Orders INNER JOIN Customer ON Orders.CustNo = Customer.CustNo
WHERE Country = "US"
4) Delete the temporary table once it is not needed:
DROP TABLE tmp
IN THE WHERE OR HAVING CLAUSES
------------------------------
A subquery can be part of a condition using different "predicates"
(operators). These subqueries can use values that come from the
enclosing query and in this case they are processed for each record
of the enclosing query.
Exists
------
EXISTS subquery
This predicate returns True if the subquery returned at least one row.
For example, the following query will return the names of all the
employees who didn't make sales between 1990 and 1994:
SELECT LastName, FirstName FROM employee
WHERE NOT EXISTS (SELECT OrderNo FROM orders WHERE
orders.EmpNo = employee.EmpNo AND
EXTRACT(YEAR FROM SaleDate) BETWEEN 1990 AND 1994)
In
--
value IN (set)
This predicate returns True if the value is in the set. The set can be
either a list of values (for example "CA", "FL", "VA") or a subquery
that results in 0, 1 or more records. To reverse the condition you can
use NOT IN.
The following example will return the customers of South America:
SELECT CustNo, Company, Country FROM customer
WHERE Country IN (SELECT Name FROM Country
WHERE Continent = "South America")
AS A VALUE
----------
In general, you can use a subquery in any other situation where you can
use a single value. The limitation is that the subquery must return one
row and only the first field is used (other SQL engines don't limit the
number of rows).
For example, you can use a subquery to calculate the value of a field:
SELECT (SELECT SUM(Qty) FROM Items
WHERE OrderNo = 1004 GROUP BY OrderNo) AS Qty1004,
(SELECT SUM(Qty) FROM Items
WHERE OrderNo = 1005 GROUP BY OrderNo) AS Qty1005
FROM Customer WHERE CustNo = 1221
We didn't use the customer #1221 in the query, but it is a trick to
produce a single record in the result (other SQL engines allow the
omission of the FROM clause for the same purpose). The result would be:
Qty1004 Qty1005
------- -------
33 21
To give another example, the following query returns all orders whose
amounts are above the average:
SELECT OrderNo, ItemsTotal FROM orders
WHERE ItemsTotal > (SELECT AVG(ItemsTotal) FROM orders)
________________________________________________________________________
3. SOME NOTES ABOUT PROGRAMMING IN NETWORKING ENVIRONMENTS
PARADOX DATABASES
=================
All applications accessing tables of the same Paradox database should
use the same PDOXUSRS.NET file. Record locking and other features depend
on this. Failure to use the same PDOXUSRS.NET file may result in corrupt
data and data loss.
The setup program installs the PDOXUSRS.NET file in the root directory
of the first hard drive of each computer where you run it ("C:\"), and
sets the NET DIR configuration in your IDAPI.CFG file to "C:\".
Therefore, by default, all applications of a single workstation share
the same PDOXUSRS.NET file, but not all applications running in the
network!
There are two ways of correcting this. One way is changing the IDAPI.CFG
file in each workstation:
1) Run the BDE Administrator
2) In the menu "Object" choose "Open configuration..."
3) Open the IDAPI.CFG file
4) In the "Configuration" tab go to Configuration/Drivers/Native/
Paradox
5) Change the NET DIR entry to point to the location of the shared
PDOXUSRS.NET file. This can be for example F:\ in one workstation
or E:\ in another, depending on how the drive mappings are
configured in each workstation. To avoid dependency on drive
mappings and use a uniform name in all workstations, you can use
the network path \\MACHINE\RESOURCE\[PATH\], where MACHINE is the
name of the server where the file is stored, RESOURCE is the name
of the shared resource (a directory) and optionally PATH is the
subdirectory path where the PDOXUSRS.NET is located.
For example, if the file is located at C:\ALL\DATABASE\PDOXUSRS.NET
in the server named "SERVER" that shares the C:\ALL directory under
the name "SHARED", then the NET DIR in all workstation (including
the server) should be \\SERVER\SHARED\DATABASE\
6) Save the changes (Object / Save As configuration).
The other way to do it is setting the NetFileDir property of the Session
object of your applications, before opening any table or query. For
example:
Session.NetFileDir := '\\SERVER\SHARED\DATABASE\';
IMPROVING PERFORMANCE
=====================
The BDE has some serious problems regarding performance in a network,
and this is why there are so many replacements for the BDE going around:
* BDE Alternatives Guide
http://bdealternatives.com/
It is not much what can be done to improve the performance of the BDE,
but you might want to try the following tips to see if they help.
CACHED UPDATES
--------------
Using cached updates apparently not only caches updates to a table, but
it also caches records read from the table. The problem is that it makes
things more complicated and has some disadvantages... You can find
information about cached updates in the Delphi help file:
Developing Database Applications
Working with cached updates
CACHING COMPONENTS
------------------
If you search the Internet you should be able to find replacements for
some data access components (like TTable and TQuery) and some data
controls (like TDBGrid, TDBLookupListBox and TDBLookupComboBox) that
perform some caching to render a better performance. These components
are usually not freeware, but we suggest you give them a try because
they might be well worth their registration fees.
KEY RANGES
----------
Key ranges are by far more performant than filters. You should apply key
ranges instead of filters whenever possible to reduce the number of
records and then if necessary use filters to get the records you want.
For example, if your filter condition looks like this:
CustNo = X AND SaleDate > D1 AND SaleDate < D2 AND Code = Y
and you had an index by CustNo and SaleDate, then you should apply a key
range ( SetRange([X,D1], [X,D2]); ) and then use a filter with the
condition Code = Y.
PREPARE QUERIES
---------------
If you use a query more or less often you can prepare it (with the
Prepare method) to make the BDE compile it and keep it compiled until
you unprepare it (with the Unprepare method). When a query is prepared
it executes faster.
PACK TABLES
-----------
Deleted records occupy space and when deletion of records happens more
or less often, sooner or later the deleted records become more than 50%
of the total number of records. Unused disk space is not itself the
problem, but network bandwidth gets wasted transferring records that
won't be used. For this reason you should pack the tables removing the
deleted records from time to time. The example of DbiPackTable in the
help file shows clearly how to accomplish this.
LOCAL TABLES AND CLIENT DATASETS
--------------------------------
Maybe the best and simplest way to reduce network traffic and improve
performance is to have a local copy of some of the tables of the
database and use these local copies to read records. When you make an
update on a table, you should update both the local and the server
table. This insures the server database is always updated.
The problem is that the changes made by other users won't be visible to
your application until you "refresh" the local tables (copy them again
from the server), and therefore this technique should only be used with
tables for which it is pretty unlikely two or more users will add or
edit the same record record with different data.
You should refresh the local tables from time to time: when Windows
starts, when your application starts, once a day, once a week, when
the user presses a Refresh button, or whenever... It will depend on the
table, and you can judge case by case: some tables may require more
frequent refreshes than others, and you should take advantage of this
fact to avoid refreshing tables more often than needed.
If you use Paradox tables, you should use a different session for the
local tables with its own NetFileDir containing the location of a local
PDOXUSRS.NET file.
If you have the Enterprise Edition of Delphi, you can use Client
Datasets for some of the local tables. Client Datasets store the records
in memory (so you shouldn't use them with big tables) and they are very
fast and powerful.
________________________________________________________________________
4. ROUNDING NUMBERS
The Round function that comes with Delphi performs what is called
"banker's rounding", meaning that a number with a fractional part of
0.5 is rounded sometimes up and sometimes down, always towards the
nearest even number. This means that for example Round(3.5) gives 4
while Round(2.5) gives 2.
Here goes a set of functions for rounding numbers, including RoundN
which rounds a number "normally" (i.e. RoundN(3.5) is 4 and RoundN(2.5)
is 3).
function Sgn(X: Extended): Integer;
// Returns -1, 0 or 1 according to the
// sign of the argument
begin
if X < 0 then
Result := -1
else if X = 0 then
Result := 0
else
Result := 1;
end;
function RoundUp(X: Extended): Extended;
// Returns the first integer greater than or
// equal to a given number in absolute value
// (sign is preserved).
// RoundUp(3.3) = 4 RoundUp(-3.3) = -4
begin
Result := Int(X) + Sgn(Frac(X));
end;
function RoundDn(X: Extended): Extended;
// Returns the first integer less than or
// equal to a given number in absolute
// value (sign is preserved).
// RoundDn(3.7) = 3 RoundDn(-3.7) = -3
begin
Result := Int(X);
end;
function RoundN(X: Extended): Extended;
// Rounds a number "normally": if the fractional
// part is >= 0.5 the number is rounded up (see RoundUp)
// Otherwise, if the fractional part is < 0.5, the
// number is rounded down (see RoundDn).
// RoundN(3.5) = 4 RoundN(-3.5) = -4
// RoundN(3.1) = 3 RoundN(-3.1) = -3
begin
(*
if Abs(Frac(X)) >= 0.5 then
Result := RoundUp(X)
else
Result := RoundDn(X);
*)
Result := Int(X) + Int(Frac(X) * 2);
end;
function Fix(X: Extended): Extended;
// Returns the first integer less than or
// equal to a given number.
// Int(3.7) = 3 Int(-3.7) = -3
// Fix(3.7) = 3 Fix(-3.1) = -4
begin
if (X >= 0) or (Frac(X) = 0) then
Result := Int(X)
else
Result := Int(X) - 1;
end;
function RoundDnX(X: Extended): Extended;
// Returns the first integer less than or
// equal to a given number.
// RoundDnX(3.7) = 3 RoundDnX(-3.7) = -3
// RoundDnX(3.7) = 3 RoundDnX(-3.1) = -4
begin
Result := Fix(X);
end;
function RoundUpX(X: Extended): Extended;
// Returns the first integer greater than or
// equal to a given number.
// RoundUpX(3.1) = 4 RoundUpX(-3.7) = -3
begin
Result := Fix(X) + Abs(Sgn(Frac(X)))
end;
function RoundX(X: Extended): Extended;
// Rounds a number "normally", but taking the sign into
// account: if the fractional part is >= 0.5 the number
// is rounded up (see RoundUpX)
// Otherwise, if the fractional part is < 0.5, the
// number is rounded down (see RoundDnX).
// RoundX(3.5) = 4 RoundX(-3.5) = -3
begin
(*
if Abs(Frac(X)) >= 0.5 then
Result := RoundUpX(X)
else
Result := RoundDnX(X);
*)
Result := Fix(X + 0.5);
end;
The functions we've just presented always round to the last integer
digit, but sometimes we need to round for example to the second decimal
or to the thousands, millions or billions. You can overload the RoundN
function with this version that takes an extra parameter to indicate the
digit to be round:
function RoundN(x: Extended; d: Integer): Extended;
// RoundN(123.456, 0) = 123.00
// RoundN(123.456, 2) = 123.46
// RoundN(123456, -3) = 123000
const
t: array [0..12] of int64 = (1, 10, 100, 1000, 10000, 100000,
1000000, 10000000, 100000000, 1000000000, 10000000000,
100000000000, 1000000000000);
begin
if Abs(d) > 12 then
raise ERangeError.Create('RoundN: Value must be in -12..12');
if d = 0 then
Result := Int(x) + Int(Frac(x) * 2)
else if d > 0 then begin
x := x * t[d];
Result := (Int(x) + Int(Frac(x) * 2)) / t[d];
end else begin // d < 0
x := x / t[-d];
Result := (Int(x) + Int(Frac(x) * 2)) * t[-d];
end;
end;
________________________________________________________________________
5. KYLIX LINKS
http://www.borland.com/kylix/
http://slashdot.org/search.pl?query=kylix
http://linuxtoday.com/search.php3?query=kylix
http://www.drbob42.com/kylix/index.htm
http://www.delphi4linux.org/
http://www.oreilly.com/news/kylix_0400.html
http://www.oreilly.com/news/kylix_0800.html
http://www.delphi32.com/info_facts/kylix/index.asp
http://www.hadp.org/kylix.htm
http://www.pinpub.com/delphi/kylix1.htm
http://www.pinpub.com/delphi/kylix2.htm
http://www.linuxworld.com/linuxworld/lw-2000-07/lw-07-newslint_1.html
http://www.delphizine.com/opinion/2000/10/di200010fn_o/di200010fn_o.asp
http://www.elists.org/mailman/listinfo/kylix
http://www.delphi-jedi.org/Jedi:VOYJEDIX:646728132
http://209.35.83.42/bbug/kylix/main.htm
http://www.delphizine.com/search/results.asp?QU=kylix
http://delphi.about.com/compute/delphi/library/weekly/aa031400a.htm
________________________________________________________________________
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=p09
________________________________________________________________________
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.
________________________________________________________________________
|
