Pascal Newsletter #8
The full source code examples of this issue are available for download.
 |
 |
Pascal Newsletter #8 INDEX 1. A FEW WORDS FROM THE EDITOR 2. KYLIX FAQ - When will it be ready? - In which Linux distributions will it work? - KDE or GNOME? - Will it be available for other platforms? - Will the language change? - What kind of binaries will it produce? - Will I be able to link objects files compiled with gcc or gpp to my Delphi projects? - What is CLX? 3. DATABASE PROGRAMMING IN CODE (II) - SET RELATION - INDEX ON - QUERIES - SELECT - SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - JOINs - PARAMETERS - EXAMPLES 4. LINKS ________________________________________________________________________ 1. A FEW WORDS FROM THE EDITOR There are new tips in our Delphi Tips section. How to... * Determine the actual length of a string? * Look for files recursively in the hard disk? * Get the icon of an application or document? * Set the invisible color of a transparent image? * Imitate deltree.exe? * Get Windows important directories? * Compare a string with a pattern? * Determine if a file name matches a specification? * Set the desktop wallpaper? * Use custom cursors? * Automatically load a form on demand? * Make MessageDlg play the corresponding sound? * Play a custom sound? * Set the default printer? * Prevent the user from closing a form? * Create a unique temporary file? * Know if there is a disk/diskette/CD in a removable-disk drive? * Get the dates of the first and last day of the month of a given date? * Show the Control Panel window or run a Control Panel applet? Here are the answers: http://www.latiumsoftware.com/en/delphi/index.php Regards, Ernesto De Spirito eds2004 @ 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-7 and C++ Builder 3-6. http://www.jfactivesoft.com/ ________________________________________________________________________ 2. KYLIX FAQ * When will it be ready? When Borland was about to join forces with Corel, the release of Kylix was planned to happen by the midst of this year. As you know, the merge was terminated and Borland continued working on Kylix alone. Now the release will probably happen before the end of this year, hopefully. Meanwhile, the beta version already won two awards: http://www.borland.com/kylix/ * In which Linux distributions will it work? All. * KDE or GNOME? Kylix will work with KDE, GNOME and plain fvwm. However, the first release is better prepared for KDE, but will run on GNOME as well, although it won't make full use of GNOME-specific features. * Will it be available for other platforms? Not for now (only Intel-or-compatible PCs with Windows or Linux). In the future it might expand to other hardware and/or operating systems. * Will the language change? Just minimally. * What kind of binaries will it produce? Standalone native standard ELF programs and shared objects. * Will I be able to link objects files compiled with gcc or gpp to my Delphi projects? Yes for gcc object files and no for the g++ ones (you would have to recompile them with C++ Builder for Linux when it is available). * What is CLX? CLX (pronounced "kliks") stands for "component library for cross- platform" and it's a new easily-extensible component-based framework for widgets, database components, network components, etc. It sounds very much like the VCL, right? Certainly yes, except that CLX will be platform independent, so applications using CLX will be more portable. ________________________________________________________________________ 3. DATABASE PROGRAMMING IN CODE (II) SET RELATION ============ In xBase: SELECT 0 USE Customers INDEX CustNo SELECT 0 USE Orders SET RELATION TO CustNo INTO Customers In Delphi it's a bit more complicated: var Orders, Customers: TTable; MasterSrc: TDataSource; begin // Open the tables CreateAndOpen(Orders, 'DBDEMOS', 'orders.db'); CreateAndOpen(Customers, 'DBDEMOS', 'customer.db'); // Create a DataSource for the Master table MasterSrc := TDataSource.Create(nil); MasterSrc.DataSet := Orders; // Set the Master-Detail relationship Customers.MasterSource := MasterSrc; Customers.MasterFields := 'CustNo'; To create a master-detail relationship, first we need a DataSource of the table that would act as the "master table" (Orders in the above example) and then we set the MasterSource and MasterFields properties of the "detail table" (Customers in the above example). MasterSource is the DataSource of the master table, while MasterFields is a string listing the names of the fields (separated by semicolons) in the master table on which the relationship is made. The active index of the detail table should start with the corresponding fields in the detail table (the index can have more fields though). In the example, the primary key of Customers is based on 'CustNo' and since it is a primary key, this index is active by default. In a one-to-many relationship, normally the table on the one-side is the master table and the one in the many-side is the detail table, but you are free to choose. In the example above we considered the "many table" (Orders) to be the master table for example. This means that every time we move in the Orders table, the record pointer of Customers will be repositioned to the first (and only) corresponding record that matches the criteria Customers.CustNo = Orders.CustNo. In xBase you can move "freely" thru all the records of the detail table if you want, but in Delphi the detail table is affected by a key range so only those records that match the criteria are accessible. Here is a more complete example: uses Db, DBTables; procedure CreateAndOpen(var table: TTable; DatabaseName, TableName: string); begin if table <> nil then FreeAndNil(table); table := TTable.Create(nil); table.DatabaseName := DatabaseName; table.TableName := TableName; table.Open; end; procedure TForm1.Button1Click(Sender: TObject); var Orders, Customers: TTable; MasterSrc: TDataSource; Total: Currency; begin Orders := nil; Customers := nil; MasterSrc := nil; try // Open the tables CreateAndOpen(Orders, 'DBDEMOS', 'orders.db'); CreateAndOpen(Customers, 'DBDEMOS', 'customer.db'); // Create a DataSource for the Master table MasterSrc := TDataSource.Create(nil); MasterSrc.DataSet := Orders; // Set the Master-Detail relationship Customers.MasterSource := MasterSrc; Customers.MasterFields := 'CustNo'; ShowMessage(Format('%d=%d --> %s', [ Orders.FieldByName('CustNo').AsInteger, Customers.FieldByName('CustNo').AsInteger, Customers.FieldByName('Company').AsString ])); Orders.Next; ShowMessage(Format('%d=%d --> %s', [ Orders.FieldByName('CustNo').AsInteger, Customers.FieldByName('CustNo').AsInteger, Customers.FieldByName('Company').AsString ])); Customers.Next; if Customers.Eof then ShowMessage('EOF'); // Unset the relationship Customers.MasterSource := nil; // Set the inverse relationship MasterSrc.DataSet := Customers; Orders.IndexName := 'CustNo'; Orders.MasterSource := MasterSrc; Orders.MasterFields := 'CustNo'; Customers.First; // 'Kauai Dive Shoppe' // Total the amount paid by this customer Total := 0; while not Orders.Eof do begin Total := Total + Orders.FieldByName('AmountPaid').AsCurrency; Orders.Next; end; ShowMessage(Format('%s %m', [ Customers.FieldByName('Company').AsString, Total ])); // Should be 'Kauai Dive Shoppe $ 51,450.80' except Orders.Free; Customers.Free; MasterSrc.Free; raise; end; Orders.Free; Customers.Free; MasterSrc.Free; end; INDEX ON ======== In the past issue we showed how to add an index to a new table. Here we will show how to add an index to an existing table, and also how to remove it: procedure TForm1.Button2Click(Sender: TObject); var Customers: TTable; begin Customers := nil; try // Open the tables CreateAndOpen(Customers, 'DBDEMOS', 'customer.db'); with Customers do begin AddIndex('Location', 'Country;State', [ixCaseInsensitive, ixNonMaintained]); IndexName := 'Location'; First; ShowMessage(FieldByName('Company').AsString); // Unisco IndexName := ''; Close; Exclusive := True; Open; DeleteIndex('Location'); end; except Customers.Free; raise; end; Customers.Free; end; An index is added with the AddIndex method. In xBase, after you create an index it becomes active, but here you have to do it explicitly if this is what you want. The ixNonMaintained option is normally used for temporary indexes and it means this index is not updated when you add, delete and modify records unless it is the active index. These indexes are automatically reindexed every time they become the active index if they are outdated. To remove an index we use the DeleteIndex method. A table must be opened with exclusive access for it to work. A primary index cannot be removed. QUERIES ======= There are two kinds of queries: * Result queries or Selection queries: They are used to obtain certain rows (records) and columns (fields) from one or more tables. You can work with the result of a query in a similar way than you work with a table. * Action queries: They are used to perform an action on one or more tables of a database, like updating records, deleting records, etc. In this issue we are going to deal with result queries, and we'll leave action queries for the next issue. Let's start with an example. Suppose we wanted to see the customer number, customer name and total amount paid by our US customers with total amounts greater than $ 100,000, and we want the listing sorted alphabetically (by company name)... procedure TForm1.btnQueryClick(Sender: TObject); var Query: TQuery; begin Query := nil; try Query := TQuery.Create(nil); with Query do begin DatabaseName := 'DBDEMOS'; with SQL do begin Add('SELECT Orders.CustNo, Company,'); Add(' SUM(AmountPaid) As TotalAmount'); Add('FROM Orders, Customer'); Add('WHERE Orders.CustNo = Customer.CustNo AND Country = "US"'); Add('GROUP BY Orders.CustNo, Company'); Add('HAVING SUM(AmountPaid) > 100000'); Add('ORDER BY Company'); end; Open; while not Eof do begin ShowMessage(Format('%d %s %m', [ FieldByName('CustNo').AsInteger, FieldByName('Company').AsString, FieldByName('TotalAmount').AsCurrency ])); Next; end; end; except Query.Free; raise; end; Query.Free; end; As you can see, a result query is like a table, being the main difference that instead of having a TableName property, it has an SQL property which is a string list where you can write an SQL sentence (more on this later). In the example, the result of this SQL sentence is like a table with three fields and four records: CustNo Company TotalAmount 3053 American SCUBA Supply $ 183,094.40 1563 Blue Sports $ 165,245.45 3042 Gold Coast Supply $ 132,233.00 1560 The Depth Charge $ 126,889.35 SELECT ====== All the magic is done by the SELECT SQL sentence. If you are not familiar with it, here we'll give you a quick introduction to get you started. You can find more information in the Local SQL Help file that comes with the BDE. SELECT ------ The sentence starts with the SELECT reserved word and after it you can specify a list of the columns (fields) you want from the base table/s. In our example, we wanted three columns: Orders.CustNo, Company, SUM(AmountPaid) AS TotalAmount There is a field named CustNo in both tables we want to access, so we had to qualify the name to specify from which table we want to take it. The columns can be fields or expressions, including aggregated expressions -like SUM- that work with a group of records (see GROUP BY). The "AS" keyword serves to specify the name of the field in the result cursor. If we wanted all fields from a table we can use a syntax like this: Orders.* FROM ---- Specifies the list of tables where the data will be taken from. You are not restricted to the tables of a single database and tables of the same type. For example: SELECT ... FROM "C:\MyDB\Table1.dbf" T1, ':DBDEMOS:Table2.db' T2 Note: SQL supports single and double quotes. T1 and T2 are local aliases for referring to these tables. For example if both tables had a field named Code, you can refer to the one in the first table qualifying it with the table alias: T1.Code. WHERE ----- Specifies the condition to join and/or filter the records of the tables. If you don't specify a WHERE clause, the result of joining two or more tables is the Cartesian product of the tables, i.e. all the records of one table will be combined with all records of the others. For example, if T1 had 3 records and T2 had 2 records, then the result would have 6 records: T1 T2 --------------------- --------------------------- F11 F12 F13 F14 F21 F22 F23 F24 F25 --------------------- --------------------------- 5 ... ... ... 5 ... ... ... ... 7 ... ... ... 8 ... ... ... ... 8 ... ... ... T1 x T2 --------------------------------------------------- F11 F12 F13 F14 F21 F22 F23 F24 F25 --------------------------------------------------- 5 ... ... ... 5 ... ... ... ... 5 ... ... ... 8 ... ... ... ... 7 ... ... ... 5 ... ... ... ... 7 ... ... ... 8 ... ... ... ... 8 ... ... ... 5 ... ... ... ... 8 ... ... ... 8 ... ... ... ... Usually, this is not what we want, but rather we want to match the records on some key fields, so the first condition of a WHERE clause is usually a "join condition" like for example F11 = F21, that would yield the following result: T1 x T2 WHERE F11 = F21 --------------------------------------------------- F11 F12 F13 F14 F21 F22 F23 F24 F25 --------------------------------------------------- 5 ... ... ... 5 ... ... ... ... 8 ... ... ... 8 ... ... ... ... In the example of customers and orders we had both a "join condition" and a "filter condition": Orders.CustNo = Customer.CustNo AND Country = "US" GROUP BY -------- Specifies the list of fields on which records will be grouped. Grouping consists in making a set of records become one. In our example of customers and orders, without grouping we would have many records for each customer. For example, for customer 3053 there would be three records: CustNo Company AmountPaid 3053 American SCUBA Supply $ 10,263.75 3053 American SCUBA Supply $ 158,922.65 3053 American SCUBA Supply $ 13,908.00 When actually we wanted just a single record summarizing the three: CustNo Company TotalAmount 3053 American SCUBA Supply $ 183,094.40 How do we perform the grouping? With the GROUP BY clause listing all non aggregate fields (SUM(AmountPaid) is an aggregate field) in the SELECT clause, for example: GROUP BY Orders.CustNo, Company If you use an aggregate function (AVG, COUNT, MAX, MIN or SUM) in the SELECT clause, then GROUP BY is required (other database engines would assume all records should be grouped if no GROUP BY is specified and you used an aggregate function). HAVING ------ Specifies a filter condition to be applied after the grouping and it is used to filter records based on the values of aggregate fields (values that obviously aren't available before the grouping). For example: HAVING SUM(AmountPaid) > 100000 This clause is optional. ORDER BY -------- Specifies the list of fields on which the records will be sorted. For example: ORDER BY TotalAmount DESC, Company; This would order the records in descending order of total amount (higher first) and if two records had the same value for this field, they will be sorted in ascending order by their names. This clause is optional. JOINs ----- The join condition can be specified in the WHERE clause, but it can also be specified in the FROM clause. For example, these two SQL sentences are equivalent: SELECT Orders.CustNo, Company; FROM Orders, Customer WHERE Orders.CustNo = Customer.CustNo SELECT Orders.CustNo, Company; FROM Orders INNER JOIN Customer ON Orders.CustNo = Customer.CustNo The "INNER" keyword is optional. The first one is usually called equi- join while the second form is called inner join, but dispite their different names they produce the same result. The INNER JOIN discards all records in one table that has no corresponding key in the other. You can see it in the example we presented in the WHERE clause: The record with the key 7 in T1 was discarded from the result because there was no 7 in T2. Sometimes we do want this "orphan" records and this is why there is an OUTER JOIN. There are three kinds of OUTER JOIN: - LEFT JOIN: Is like INNER JOIN but it also includes records in the first table that don't have a match in the second table. - RIGHT JOIN: Is like INNER JOIN but it also includes records in the second table that don't have a match in the first table. - FULL JOIN: A combination of LEFT JOIN and RIGHT JOIN, i.e. it's like INNER JOIN but it includes non-matching records from both tables. When a match is not found in one table, it's fields have NULL values. In the example of T1 and T2, the following sentence SELECT T1.*, T2.* FROM T1 LEFT OUTER JOIN T2 ON F11 = F21 would render the following result: --------------------------------------------------- F11 F12 F13 F14 F21 F22 F23 F24 F25 --------------------------------------------------- 5 ... ... ... 5 ... ... ... ... 7 ... ... ... NULL NULL NULL NULL NULL 8 ... ... ... 8 ... ... ... ... We must warn you that we tested a few queries using OUTER JOIN and it doesn't seem to be working (it acts as an INNER JOIN). You can join two tables on more than one field. For example: SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.f1 = t2.f1 AND t1.f2 = t2.f2 You can also join on a match between a field and a concatenation of fields: SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.field1 = t2.field1 || t2.field2 You can join more than two tables. For example: SELECT t1.*, t2.*, t3.* FROM (t1 JOIN t2 ON t1.field1 = t2.field1) JOIN t3 ON t3.field1 = t1.field2 AND t3.field2 = t1.field2 SELECT t1.*, t2.*, t3.* FROM ((t1 JOIN t2 ON t1.field1 = t2.field1) JOIN t3 ON t3.field1 = t1.field2 AND t3.field2 = t1.field2) JOIN t4 ON t4.field1 = t1.field3 PARAMETERS ---------- In the first query example we got the sales amounts of the US customers, Add('WHERE Orders.CustNo = Customer.CustNo AND Country = "US"'); but what if now we wanted to see those of our Canadian customers or some country specified by the user? We can do two things: concatenate strings or declare a query parameter preceding its name with a colon (':') like you can see below: Add('WHERE Orders.CustNo = Customer.CustNo AND Country = :Country'); Before opening the query we have to supply the value for the parameter. For example: Query1.Params[0].AsString := Edit1.Text; or Query1.ParamByName('Country').AsString := 'Canada'; This allows us to write the SQL query once and then we only change the parameter as needed. Because we used AsString, the value of the parameter will be quoted in the SQL string. EXAMPLES -------- 1) Get the parts in decreasing order of net individual profitability SELECT parts.*, (ListPrice - Cost) AS profit FROM "parts.db" parts ORDER BY profit DESC 2) Get the parts that need immediate reposition to fulfil the orders. For each part, list its number, description, quantity needed, cost, total amount, vendor name and phone number. Sort the result by vendor name and part number. SELECT PartNo, Description, (OnOrder - OnHand) As Quantity, Cost, (Quantity * Cost) As Total, VendorName, Phone FROM "parts.db" parts JOIN "vendors.db" vendors ON parts.VendorNo = vendors.VendorNo WHERE OnOrder > OnHand ORDER BY VendorName, PartNo 3) Get the best selling part. SELECT PartNo, Description, SUM(Qty) As Quantity FROM "parts.db" parts JOIN "items.db" items ON parts.PartNo = items.PartNo GROUP BY PartNo, Description ORDER BY Quantity DESC The first record of this query corresponds to the best selling part. With other database engines you can use SELECT TOP 1 ... to get only the first record. 4) List all employees and their total sales in 1994 and 1995 SELECT employee.EmpNo, LastName, FirstName, SUM(ItemsTotal) AS Sales FROM employee LEFT JOIN orders ON employee.EmpNo = orders.EmpNo WHERE SaleDate BETWEEN "01/01/1994" AND "12/31/1995" GROUP BY employee.EmpNo, LastName, FirstName The WHERE clause could have also been for example: * WHERE SaleDate >= "01/01/1994" AND SaleDate <= "12/31/1995" * WHERE EXTRACT(YEAR FROM SaleDate) BETWEEN 1994 AND 1995 * WHERE EXTRACT(YEAR FROM SaleDate) IN (1994, 1995) 5) List all employees who have customers in Fiji SELECT employee.EmpNo, LastName, FirstName FROM (employee JOIN orders ON employee.EmpNo = orders.EmpNo) JOIN customer ON orders.CustNo = customer.CustNo WHERE customer.Country = "Fiji" 6) For each employee, total the sales to each of his/her customers SELECT employee.EmpNo, LastName, FirstName, Company, SUM(ItemsTotal) AS Sales FROM (employee JOIN orders ON employee.EmpNo = orders.EmpNo) JOIN customer ON orders.CustNo = customer.CustNo GROUP BY LastName, FirstName, employee.EmpNo, Company 7) Get all the orders of a customer whose number is to be supplied by the user: SELECT * FROM orders WHERE CustNo = :Custno ________________________________________________________________________ 4. LINKS * Top 100 Delphi web sites http://www.sandbrooksoftware.com/TS/index.shtml * All the best from James M Sandbrook - Delphi Programming Source Code. Free components, downloads, articles, examples etc. http://www.sandbrooksoftware.com/DPSC/index.shtml * DelphiLand - Online tutorials for the novice Delphi programmer. All lessons and source code can also be downloaded. http://www.festra.com/eng/index.html ________________________________________________________________________ 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.sandbrooksoftware.com/cgi-bin/TopSite2/rankem.cgi?id=latium http://news.optimax.com/delphi/links/links.exe/click?id=70C517ECAE6E http://www.programmingpages.com/?r=latiumsoftwarecomenpascal http://www.top219.org/cgi-bin/vote.cgi?delphi&83 http://top100borland.com/in.php?who=20 http://top200.jazarsoft.com/delphi/rank.php3?id=latium http://213.65.224.200/cgi-bin/toplist.cgi/hits?Id=80 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/download/p0008.zip ________________________________________________________________________ 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? eds2004 @ latiumsoftware.com ________________________________________________________________________ Latium Software http://www.latiumsoftware.com/en/index.php Copyright (c) 2000 by Ernesto De Spirito. All rights reserved. ________________________________________________________________________ |
The full source code examples of this issue are available for download.
 |
Errors? Omissions? Comments? Please contact us!
