FOLDOC gives the definition of a database as "one or more large structured sets of persistent data, usually associated with software to update and query the data". A relational database "allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organised in tables. A table is a collection of rows or records and each row in a table contains the same fields." Be prepared for a lot of database terminology!
We have given examples of accessing a single table in a database in our section on the TDBGrid and other data-aware controls. These examples used a Microsoft Access database. In this tutorial we will explain how to set up and query a Firebird relational database. Firebird (formerly supplied with Delphi as Interbase hence the component name IBConnection) is available as a free download. It has many advanced features and you can use it either as a server or embedded in your own application.
SELECT Surname, Forename FROM Employee ORDER BY Surname; SELECT Surname FROM Employee WHERE ID = 1001;
A database under the control of a Database Management System (DBMS) offers many advantages for ease of use in a commercial application. The Database Management System (DBMS) handles security so that you can grant different rights to different groups of users on different data items. It helps to maintain the integrity of the data by the use of constraints. The DBMS controls the structure of the data so that different applications can access it. Certain changes can be made without breaking applications that use it. In a relational database there should be no redundancy (repeated occurrences of the same data). With multiple instances of a data item integrity is compromised; one instance might be updated before another or one might not be updated at all.
You can edit data in a suitable control linked to a dataset derived from a database and post the data to the dataset. In itself, this will not update the underlying Firebird database. To make changes permanent you must commit the data. The constraints that you build into the database (such as a range check for age) will operate only when you attempt to commit the data. If you do not want the invalid data to be displayed at all you need to write your own validation code for data entry.
We describe the planning of a simple database, the setting up of the Firebird DBMS, the creation and populating of a database and then ways that you can access it by different applications to query the data. Written reports are an essential feature of most databases. We demonstrate the use of the LazReport component in the section entitled Creating and Printing a Report. The use of parameters may improve the performance of a database. Firebird supports prepared statements, which means that the statement is prepared and cached in the database. A prepared statement can be used more than once and does not require parsing every time it is used; only the parameters are changed. See the final section for a demonstration.
The Database topic is vast and important, but there should be enough here to help you to get started with practical work on databases. See afterwards how to use Smart Mobile Studio to develop a web page that accesses data in a MySQL database and how to get started with a PostgsreSQL database. See our Overview of Access to Databases for links to many further pages on databases on this website.
We hope that you avoid pitfalls by following our advice, but you should still be prepared to cope with a whole new assortment of error messages! Follow the links below to the sections of the tutorial. Good luck!