Delphi How To: Creating Calculation Field

Wednesday, June 11, 2008

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 :


Table 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


Application Designtime


Runtime


Application Runtime



Download Sourcode here

SQL BETWEEN: Searching Between Two Specified Values (Range)

Monday, June 9, 2008

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

Tuesday, June 3, 2008

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)

Recent Comments

Tags Cloud

Blogumulus by Roy Tanck and Amanda Fazani