Delphi How To: Creating Calculation Field

In this Delphi Tutorial i will explain how to create a calculation field on Delphi DBAware component and show it on the DBGrid. Calculation Field is an additional field that we can create to do automatic calculation at OnCalcFields event.

For example you have a table called SalesDetail that have the following structure :




if you want your Application to do automatic calculation for the total price (UnitPrice * Quantity) as you insert or edit Data, you can follow this steps :

1. Creating Calculation Field On TADOTable Component

• Add TADOTable Component to a Form


• Set its ConnectionString and TableName properties so its connected to your Database


• Right click on the TADOTable component and choose Fields Editor... the Fields Editor will appear


• Right click on the Fields Editor and choose Add All Fields to include all field in the Table


• Right Click on Fields Editor and choose New Field... the New Field Dialog will appear


• Enter a name for this new field in the Name textbox for example CTotal


• Choose Number Data Type (Integer, Float, Largeint, etc depend on posible value range you will need) on Type combobox


• Choose Calculated in the Field Type Section and then click the OK button







• Now you have created a calculation Field called CTotal

2. Adding Code to your Application to do Automatic Calculation

• Add a DataSource component to the form and connect it to your TTable or TADOTable Component.


• Add a DBGrid component to the form and connect it to your DataSource Component


• set the AutoCalcFields Properties of TADOTable Component to True.


• To do automatic calculation as you insert or edit Data, add this code on OnCalcFields event of TADOTable component



AdoTable1.FieldValues['CTotal'] :=

AdoTable1.FieldValues['UnitPrice'] * AdoTable1.FieldValues['Qty'];


Designtime

Runtime

Download Sourcode here

SQL BETWEEN: Searching Between Two Specified Values (Range)

Syntax

expression [ NOT ] BETWEEN lower_value AND higher_value

The SQL BETWEEN Operator are used to display rows between two specified values. The lower value are placed before AND operator and the higher value are placed after AND operator.

For Example you have a table called Articles that contain the following Data:

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00
5         SQL HAVING Example                 50 2008-03-19 15:33:00

Using SQL Between on Numeric Data Type Field

The following SQL Statement example will return Articles that having PageView Between 50 and 100:


Select ArticleID,Title,PageView,PublishDate

from Articles

where PageView Between 50 and 100


Query Result:

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
5         SQL HAVING Example                 50 2008-03-19 15:33:00

Using SQL Between on Date Data Type Field

The following SQL Statement example will return Articles published between 29th May 2008 and 8th June 2008 :


Select ArticleID,Title,PageView,PublishDate

from articles

where PublishDate between '29-may-2008' and '08-jun-2008'


Query Result

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
2         SQL BETWEEN Example                84 2008-06-07 15:23:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00

you can use SQL Statement bellow if you work with a MS Access Database. it will produce the same result


Select ArticleID,Title,PageView,PublishDate

from articles

where PublishDate between #01/06/2008# and #08/06/2008#

To display rows outside the range you specified, you must omits NOT operator before BETWEEN operator. Example :


Select ArticleID,Title,PageView,PublishDate

from Articles

where PageView Not Between 50 and 100


Query Result

ArticleID Title                     PageView    PublishDate
--------- ------------------------- ----------- -------------------
1         SQL JOIN Example                   43 2008-06-04 15:18:00
3         SQL Where Example                   0 2008-06-07 15:27:00
4         SQL: Find Duplicate Values        102 2008-05-29 15:30:00

Using SQL Outer Join

When you use SQL JOIN/INNER JOIN, the result are rows that only matches in both table based on Join condition you specified in the FROM clause. If you need to display all rows from at least one of the tables mentioned in the FROM clause, you can use SQL OUTER JOIN statement. There are 3 types of SQL Outer Join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN : Left Outer Join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN : A right outer join will return All rows from the right table and Null values are returned when there are no matching row in the left table.

FULL JOIN or FULL OUTER JOIN : A full outer join returns all rows in both the left and right tables. Null values given for any rows that has no match in both tables.

For example you have the following tables and data in your database:

Data in the Vendors table:

VendorID  VendorName
--------- ------------------------------
V0001     Microsoft Corporation
V0002     Oracle Corporation
V0003     MySQL AB
V0004     PremiumSoft CyberTech Ltd.
V0005     Mentat Technologies
V0006     FastReport Inc.
V0007     CoreLab

Data in the Products table:

ProductID  VendorID  Description
---------- --------- -----------------------------------------
SDB0001    V0001     MS SQL Server Database 2005
SDB0002    V0002     Oracle Database 10g Enterprise
SDB0003    V0003     MySQL 5.0 Enterprise Edition
SDBA0001   V0004     Navicat For MySQL Administration
SDBA0002   V0005     Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   V0005     Dreamcoder 4.1 For MySQL Profesional
SDBA0004   V0005     Dreamcoder 4.1 For MS SQL Server Database
SDBA0005   V0008     PostgreSQL Maestro
SDBA0006   V0008     Firebird Maestro
SDBA0007   V0008     SQLite Maestro

You noticed that Vendor V0008 doesn't exist in the Poducts table and V0006, V0007 doesn't exist in the Vendors Table. This rows will not be displayed if you use a regular SQL JOIN/INNER JOIN (where Vendors.VendorID=Products.VendorID). How to display all rows from the Vendors table although it doesnt exist on the Products table or the reverse?.

Left Join or Left Outer Join Example:

The Following SQL Statement will display all rows from the Vendors table and will display only rows from the Products table that match to the Vendors table based on VendorID field.

select Vendors.VendorID,products.ProductID,products.Description

from vendors LEFT JOIN products

on vendors.vendorid=products.vendorid

Query Result:

VendorID  ProductID  Description
--------- ---------- -----------------------------------------
V0001     SDB0001    MS SQL Server Database 2005
V0002     SDB0002    Oracle Database 10g Enterprise
V0003     SDB0003    MySQL 5.0 Enterprise Edition
V0004     SDBA0001   Navicat For MySQL Administration
V0005     SDBA0002   Dreamcoder 4.1 For MySQL Enterprise
V0005     SDBA0003   Dreamcoder 4.1 For MySQL Profesional
V0005     SDBA0004   Dreamcoder 4.1 For MS SQL Server Database
V0006     NULL       NULL
V0007     NULL       NULL

Right Join or Right Outer Join Example::

The Following SQL Statement will display all rows from the Products table and display only rows from the Vendors table that match to the Products table based on VendorID field.


select Vendors.VendorID,products.ProductID,products.Description

from vendors RIGHT JOIN products

on vendors.vendorid=products.vendorid

Query Result:

VendorID  ProductID  Description
--------- ---------- -----------------------------------------
V0001     SDB0001    MS SQL Server Database 2005
V0002     SDB0002    Oracle Database 10g Enterprise
V0003     SDB0003    MySQL 5.0 Enterprise Edition
V0004     SDBA0001   Navicat For MySQL Administration
V0005     SDBA0002   Dreamcoder 4.1 For MySQL Enterprise
V0005     SDBA0003   Dreamcoder 4.1 For MySQL Profesional
V0005     SDBA0004   Dreamcoder 4.1 For MS SQL Server Database
NULL      SDBA0005   PostgreSQL Maestro
NULL      SDBA0006   Firebird Maestro
NULL      SDBA0007   SQLite Maestro

Full Join or Full Outer Join Example::

The Following SQL Statement will display all rows from both Vendors and Products table.


select Vendors.VendorID,products.ProductID,products.Description

from vendors FULL JOIN products

on vendors.vendorid=products.vendorid

Query Result:

VendorID  ProductID  Description
--------- ---------- -----------------------------------------
V0001     SDB0001    MS SQL Server Database 2005
V0002     SDB0002    Oracle Database 10g Enterprise
V0003     SDB0003    MySQL 5.0 Enterprise Edition
V0004     SDBA0001   Navicat For MySQL Administration
V0005     SDBA0002   Dreamcoder 4.1 For MySQL Enterprise
V0005     SDBA0003   Dreamcoder 4.1 For MySQL Profesional
V0005     SDBA0004   Dreamcoder 4.1 For MS SQL Server Database
NULL      SDBA0005   PostgreSQL Maestro
NULL      SDBA0006   Firebird Maestro
NULL      SDBA0007   SQLite Maestro

SQL JOIN : Retrieving Data from many Tables

Joins are created when we display Data in queries that using more than one tables. we can connect a row in one table to another row in other table using the same value of both rows. Usualy this relation represented as Primary Key and Foreign Key relationship.

Joins allow an association between a field in one table or view with a field of the same data type in another table or view. Joins show how the data in one table relates to the data in another table. By using joins, data from two or more tables or queries based on logical relationships among the tables can be retrieved. Joins can be added, edited, or deleted to retrieve the data from the database.

For Example you have two tables called Products and Vendors that contain the following Data:

Data in table Vendors:

VendorID  VendorName                  URL
--------- --------------------------- --------------------------
V0001     Microsoft Corporation       http://www.microsoft.com
V0002     Oracle Corporation          http://www.oracle.com
V0003     MySQL AB                    http://www.mysql.com
V0004     PremiumSoft CyberTech Ltd.  http://www.navicat.com
V0005     Mentat Technologies         http://www.mentattech.com

Data in table Products:

ProductID  VendorID  Description
---------- --------- -----------------------------------------
SDB0001    V0001     MS SQL Server Database 2005
SDB0002    V0002     Oracle Database 10g Enterprise
SDB0003    V0003     MySQL 5.0 Enterprise Edition
SDBA0001   V0004     Navicat For MySQL Administration
SDBA0002   V0005     Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   V0005     Dreamcoder 4.1 For MySQL Profesional
SDBA0004   V0005     Dreamcoder 4.1 For MS SQL Server Database

Bellow is SQL JOIN Statement example to display ProductID, VendorName, and Description by Joining two tables above


select products.ProductID,vendors.VendorName,products.Description

from vendors, products

where vendors.vendorid=products.vendorid

Query Result:

ProductID  VendorName                 Description
---------- -------------------------- -----------------------------------------
SDB0001    Microsoft Corporation      MS SQL Server Database 2005
SDB0002    Oracle Corporation         Oracle Database 10g Enterprise
SDB0003    MySQL AB                   MySQL 5.0 Enterprise Edition
SDBA0001   PremiumSoft CyberTech Ltd. Navicat For MySQL Administration
SDBA0002   Mentat Technologies        Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   Mentat Technologies        Dreamcoder 4.1 For MySQL Profesional
SDBA0004   Mentat Technologies        Dreamcoder 4.1 For MS SQL Server Database

Or you can use this form of SQL JOIN, will produce the same result


select products.ProductID,vendors.VendorName,products.Description

from vendors inner join products

on vendors.vendorid=products.vendorid

Query Result:

ProductID  VendorName                  Description
---------- --------------------------- -----------------------------------------
SDB0001    Microsoft Corporation       MS SQL Server Database 2005
SDB0002    Oracle Corporation          Oracle Database 10g Enterprise
SDB0003    MySQL AB                    MySQL 5.0 Enterprise Edition
SDBA0001   PremiumSoft CyberTech Ltd.  Navicat For MySQL Administration
SDBA0002   Mentat Technologies         Dreamcoder 4.1 For MySQL Enterprise
SDBA0003   Mentat Technologies         Dreamcoder 4.1 For MySQL Profesional
SDBA0004   Mentat Technologies         Dreamcoder 4.1 For MS SQL Server Database

MySQL Connector Download

MySQL provide standard database driver connectivity for using MySQL with applications and tools that are compatible with industry standards ODBC and JDBC. This enables MySQL to work easily with standard development tools on Windows, Linux, Macintosh and Unix platforms. Any system that works with ODBC or JDBC can use MySQL.

• Connector/ODBC: Standardized database driver Windows, Linux, Mac OS X, and Unix platforms.


• Connector/J: Standardized database driver for Java platforms and development.


• Connector/Net: Standardized database driver for .NET platforms and development.


• Connector/MXJ: MBean for embedding the MySQL server in Java applications.


• MySQL native driver for PHP - mysqlnd: The MySQL native driver for PHP is an additional, alternative way to connect from PHP 6 to the MySQL Server 4.1 or newer.

MySQL Connector/ODBC 5.1

Version 5.1 of the MyODBC driver is now released by MySQL AB and available for download. An ODBC (Open Database Connectivity) driver enables client programs to access a database or data source. The new version 5.1 MySQL ODBC driver is a greatly enhanced successor version to the existing MyODBC 3.51, rewritten to follow the ODBC 5.1x specifications. It provides complete and up-to-date ODBC functionality when accessing any MySQL database. This latest version is available under the GPL license as well as under commercial license.

MySQL Connector/Net 5.1

Connector/Net 5.1 is the most exciting release of MySQL's fully managed ADO.Net provider to date. Some of the most user-requested features have been included. For example:

• Support for fully commented stored procedures.


• Support for Compact Framework 2.0.


• Support for ASP.Net 2.0 Membership and Role systems.

This release also represents a change in the product packaging. In the past, MySQL AB provided separate downloads for MySQL AB Visual Studio integration product. this product has been merge into a single installer For a better "out of box" experience.

NOTE: By downloading the software from this page, you acknowledge that the software available from here is licensed under the GPL. We advise that you review the GPL before downloading. If you need commercial, non-GPL, licenses, you can order them online.

MySQL Connector/J 5.1

MySQL Connector/J is the official JDBC driver for MySQL. Connector/J 5.1 includes support for JDBC-4.0 functionality, including:

• Ease-of-development features including auto-registration with the DriverManager via the service provider mechanism, standardized Connection validity checks and categorized SQLExceptions based on recoverability/retry-ability and class of the underlying error.


• Unwrapping to interfaces that include vendor extensions


• Support for XML processing via JAXP interfaces to DOM, SAX and StAX


• Support for setting per-connection client information (which can be viewed in the comments section of a query via "SHOW PROCESSLIST" on a MySQL server, or can be extended to support custom persistence of the information via a public interface)


• Support for JDBC-4.0 NCHAR, NVARCHAR and NCLOB types

MySQL native driver for PHP - mysqlnd

The MySQL native driver for PHP is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or newer. It is a replacement for the libmysql, the MySQL Client Library. From now on you can use ext/mysqli either together with libmysql as you did in the past or with mysqlnd.

MySQL AB have no plans to remove libmysql support from ext/mysqli, which would break existing applications. They just add a new, superior alternative to their PHP offerings.

MySQL Visual Studio Plugin 1.02

MySQL Tools for Visual Studio is a package that integrates seamlessly into Visual Studio 2005. This package is currently available under a commercial, no-charge binary license but we are planning to release the source code under an approved open source license within the next few weeks.

The package provides the following features:

• DDEX capability


• Database object manipulation (tables, stored procedures, etc)

Publisher : MySQL AB

Download MySQL Connector/ODBC 5.1 (Windows MSI Installer) : Here (4.1 MB)

Download MySQL Connector/ODBC 5.1 (Linux non RPM packages) : Here (4.3 MB)

Download MySQL Connector/Net 5.1 (sources): Here (3.7 MB)

Download Connector/J 5.1 (ZIP): Here (8.4 MB)

Download MySQL native driver for PHP - mysqlnd Sourcode (tar.gz): Here

Download MySQL Visual Studio Plugin 1.02 (ZIP): Here (650 KB)