Overview of Access to Databases

On this website we demonstrate access to Firebird databases in these languages and dialects:
  • Pascal/Object Pascal (in Lazarus and Delphi);
  • Smart Pascal;
  • Oxygene for Java;
  • Oxygene for .Net;
  • RemObjects C# for Java;
  • C#.
We also cover Microsoft Access, MySQL and SQLite databases and the datasets ClientDataset, MemDataset, BufDataset and TW3Dataset. This page provides links so that you can make comparisons more easily and decide which material could be most useful to you when you access databases from your own applications. You will be able to see how much of the code we were able to reuse because SQL was designed to be universal.

Planning a Database

Our Databases tutorial gives advice for planning a database to avoid redundancy of data. Although the tutorial continues with specific guidance for a Firebird implementation, this initial section is general for a relational database. It explains the use of primary and foreign keys, for example.

Firebird

Our Databases tutorial provides detailed instructions for downloading and installing Firebird then creating a Firebird database with isql and editing and searching it with a Lazarus application. The use of stored procedures is recommended for increased efficiency. We show you how to install and use the LazReport tool to produce and print a master-detail report.

Both Firebird and node-firebird are suitable for use on the Pi and we provide instructions for installation, examples of the use of Firebird with Lazarus (including date and blob fields) and demonstrations of extraction of selected data to a memo, grid and dataset on a web page by a Smart Pascal server and client. We also demonstrate how to access a Firebird database using:

Microsoft Access

Using a DBGrid in Delphi demonstrates how to use a DBGrid to display and edit data in an Access database. See the corresponding page for Lazarus. Updating a Microsoft Access Database using Lazarus demonstrates how to use a DBGrid, DBEdit and DBMemo to display and edit data in an Access database.

Accessing a MySQL Database on a Web Server

The Smart Pascal program Loading data from a MySQL database on the website uses these PHP scripts to retrieve the data from a MySQL database. We tested the program by installing an Apache web server and the MySQL database on a networked Raspberry Pi. Access to the same database is achieved relatively easily with Node.js. Our page Accessing Databases using C directs you to C programs that access a MySQL database and provides notes on trying them on a Raspberry Pi.

SQLite Databases

We have added to our main Lazarus/Delphi databases tutorial pages on SQLite for a PC and a Raspberry Pi. We demonstrate how to create and use a database with images stored as blobs and also a way of handling date fields.

The Smart Pascal client-server combination enables selected data from a SQLite database on a PC to be viewed remotely in a web page delivered by the Node.js server. We also demonstrate how to create and access an in-memory SQLite database on a web page using Smart Pascal.

Datasets

Our Databases tutorial concludes with the use of datasets stored in local files.

The Delphi example demonstrates two linked ClientDataSets being used to model a master-detail relationship. The data of each ClientDataSet is held in a local XML file so the contents can be inspected readily and edited "by hand" in a text editor if necessary.

In Lazarus we demonstrate the use of MemDatasets and BufDatasets (which have greater functionality).

Our Smart Pascal demonstration uses Version 0.1 the inbuilt TW3Dataset within Version 2.2 Beta 4 of Smart Mobile Studio.

Programming - a skill for life!

Overviews of site content and general information, tips and tricks for student programmers