Last time we talked Delphi, we talked a bit about frameworks and we built your first Delphi app, a “Hello World” for Windows. This time around, we’re going to talk about one of the most common uses for programming tools in business environments – data access.
Let’s get down to brass tacks on this first – what is data access? Put simply, “data access” means the ability of a program to read and write persistent information – info that will be kept (hopefully safely) while the program is turned off, and can be retrieved when turned on again, or when asked for by another program. Generally, we do this with databases – things like Oracle, MS SQL, MS Access, etc.
It is also worth making a differentiation between “data” and “information”. In this context, I’m going to use “data” to represent raw data, the kind of thing that might be useful to a program, but generally doesn’t mean anything to a human who doesn’t know the insides of the computer. “Information,” on the other hand, I’m going to use to represent the stuff that we can put up in front of a user and have a reasonable chance of being understood.
When Delphi was first launched in 1995, it shipped with a series of VCL components that wrapped up the “Borland Database Engine” (BDE for short), which was already a healthy set of connectors to various databases like Paradox, DBase, DB2, Microsoft SQL Server, Oracle, and a few others (if you count ODBC – “Open DataBase Connectivity”, a Microsoft library that enabled vendors to write a connector to ODBC and have coders connect through that – you could have dozens or hundreds of possibilities). Each of these database types was connected by a library commonly referred to as a “driver”. So if you had a “database driver” for a specific database, you could access that type of server or group of files.
The basic premise of most data access methods is that a program binds to a general data-access layer library. That library may have one or more drivers (specialist libraries that know how to connect to a specific database type), and each driver knows how to operate with its own specific data repository (be it a SQL server of some sort, or a file-based database like FireFox, Paradox, etc.).
Conceptually, it could look something like this:
The job of the programming tool is to make this diagram basically invisible to the programmer (unless he/she is specifically wanting to write code that does this kind of work. We aren’t doing that, so we want something that encapsulates this sort of function and makes it pretty painless to do.
Fortunately, Delphi was designed almost from the outset to do just that – and not only to make it painless, but fun. Delphi was the first programming tool to offer its programmers the ability to see live data in its designers as well, which was extremely valuable in making sure one was coding correctly. No one else had ever done it like this, and it was several years before anyone else could offer something similar (a short-lived and extremely bad programming tool called “PowerBuilder” did, and eventually Microsoft figured out a way).
As I mentioned, when Delphi first shipped it had components that wrapped the Borland Database Engine inside it. Since then, quite a few additional ‘engines’ have been added (I’ll use “engine” to term the library that offers up multiple drivers), expanding Delphi’s potential database access even further. The BDE is still in there, but it is rather old and doesn’t shine quite the way it used to – it has been deprecated, which in general means it is no longer supported and no new code is being done to improve or expand it.
In addition to BDE, the following engines are included:
Interbase Express – connectors specific to Interbase, a SQL Server offered by Borland/Embarcadero/Idera (I can’t keep straight which company kept what part). Interbase is a fast and compact database that is particularly good for bundling in with applications, though expensive in its deployment costs.
dbExpress – this is an engine that surfaces drivers for Sybase’s SQL Anywhere, regular Sybase, DB2, Firebird (an open-source fork of Interbase), “IBLite” (an even-more-compact version of Interbase), Informix, Interbase, Microsoft SQL, ODBC, Oracle, and SQLite. It also offers a connection to DataSnap, which is a programming framework for making multi-tiered applications.
DBGo – components that harness ADO (a successor of sorts to ODBC).
FireDAC – FireDAC is a modern iteration of the multi-access engines produced in prior generations of programming tools. It presents a common interface to dozens of different data repositories and storage methods. Among them are:
- MS Access
- MS Excel
- dbExpress drivers
- DataSnap servers
- Firebird (embedded and normal)
- MySQL (embedded and normal)
- MS SQL
- MS SQL Azure
- MS SQL CE
- InterBase ToGo
- Advantage Database
- Sybase SQL Anywhere
- Microfocus Cobol
So…as you can see, FireDAC isn’t joking around. It connects to a LOT of data sources. If yours isn’t listed there, it can probably still be reached through an ODBC driver. (Of course, if it isn’t in there, it probably isn’t worth programming for J.)
Within Delphi, data access is done through a series of components (not necessarily VCL ones, but they all work roughly the same). First, a connector component representing the program’s access to the database server or location is used, and after that one or many components representing the various bundles of data within that location are set up to enable the program to read and write to them. Finally, components responsible for passing that data into visual formats are used, converting the data into information.
In the 10.2 (“Tokyo”) build of RAD Studio, the BDE has been removed – it was deprecated long ago, and finally has been pulled. If you’re maintaining an old version of code that does still contain these, you can retrieve an installer from Embarcadero’s site (here: https://cc.embarcadero.com/Item/30752), but that’s the only case where I’d recommend you do so. For future use, it’s best to get yourself into one of the more current sets of components.
For starters, let’s take one of the simpler ones, dbExpress, and connect it to a Microsoft SQL Server installation. As it happens, I have a dev edition of MS SQL here on my laptop, so we’ll start with that one. You’re going to need to install at least the MS SQL client software on your system before we get started (the client is also included in the server installation if you’re going to put a full server on your machine). If you’re getting into software development, I’d really recommend you buy a license of the MS SQL Developer Edition (available here: https://www.microsoft.com/en-us/sql-server/application-development). It’s a fully-functional server, and is fantastic for working out issues prior to testing against a real server.
Let’s start a new project. You had the basics of this in my last Delphi article, so go ahead and roll one out. A blank form is just fine. I’ll do one here too, a VCL forms app for simplicity’s sake. I’ll target Win64 again as I did previously.
When Delphi was first launched in 1995, people used it a LOT for database access. However, even though the data access components are really small, they tend to collect quickly and can really clutter up your designer. As a solution in Delphi 1, most programmers just added a new form to the project and put all their data components on it to avoid getting their UI out of control. Borland (the original maker of Delphi) recognized this as a pain point right away and in Delphi 2 released what is called a “data module” – a non-visible form (so it wouldn’t use as many system resources) which can host all manner of non-visual components like data access stuff, API components, and so on. That’s what we’ll do here too.
Once your project is ready, and you can see your designer with Form1 loaded, go to the File menu. In there choose File > New > Other… and in the dialog that appears, select “Delphi Files” from the tree view on the left. The right pane will have a list of choices, one of which is “Data Module”. Select that and confirm by clicking “OK”.
Notice your Project Manager now shows you have “unit1.pas” and “unit2.pas” as part of your project. Unit1 is your main form, and Unit2 is the datamodule. You should probably save and name your files now, to stay in the habit J. Go ahead, I’ll wait.
Saved it? Okay, great. Notice the Data Module looks like a blank form, but it has no title bar, no icon, etc. That’s because it will never appear visually within your application. Your visual form will use this Data Module, referencing it so that it can get a grip on the components present within it. To do this, return to your main form, and from the menus choose File > Use Unit. You’ll see a list with your datamodule in it. Double-click and you’re on your way.
First thing we’re going to want is to go to the Tool Palette and open up the “dbExpress” group. The starter is the TSQLConnection, which will represent a persistent connection to our database. Grab one and drop it on your Data Module. The new connection will default to a name of “SQLConnection1” – go ahead and rename it to “MSSQLConnection”.
Our next step is to designate a driver for this component – choose “MSSQL”.
By doing this, the component will fill up its “Params” section with a series of values that it will need to operate. Most of these you won’t have to touch or bother with. The two you will need to set are “HostName” and “Database” – the host name will be the name of the server to which you are attaching, and the database is the actual name of the database on that server. For hostname, I could give it the full name of my SQL instance (I’m assuming you went and picked up the Dev edition of MS SQL I mentioned above), but since I’ve installed it on my development machine, I can use “.” as the machine name. Each instance of SQL Server gets its own name too, so that is a two-parter. It will look like this:
So it would look like “MYSYSTEM\SQLONE” or similar. Since I’m running locally, I’m going to sub “.” for my machine name, so my Servername parameter reads as follows:
The Database parameter is quite literally the name of the database you intend to connect to (Adventureworks is the sample data that MS has always shipped with their product, so you can test with that, but I’m using a home-grown named “SampleData”).
I’m also going to change my “MaxBlobSize” for my own purposes – don’t worry about this. Leave yours as -1. If you know what this is for, you can deal with it on your own terms, otherwise it’s not important for this lesson.
Once your params are set, you can test them by changing the “Connected” property to “true”. You’ll be prompted for a name and password (you did remember to store your login credentials somewhere, didn’t you?), and if you give proper credentials, it’ll change to true. That confirms that you have a live connection to your database.
Once you’ve confirmed this, go ahead and set it back to “false”. Leaving a connection on in the designer is setting yourself up for a few problems later, and it’s better to handle it in the program at run-time. We’ll get back to this shortly when I show you how to get live data showing up in your app.
So…we have a connection to the database, but we don’t yet have real data. Let’s set that up next.
For this, we need a dataset component. Where a “connection” represents a channel to the database server, a “dataset” represents a channel to a specific package of data (which might be the contents of a table, the output of a SQL query, view, or stored procedure, etc. – basically anything that can be considered to have actual data in it). In the case of dbExpress, this means a TSQLDataSet, TSQLQuery, TSQLStoredProc, TSQLTable, or TSimpleDataSet. Since we’re dealing with MS SQL, let’s keep it straightforward and use a TSQLQuery. This component represents a query you write and store inside the component, and when it is opened, it fires this query off to the server, then makes the response from the server available to your app.
Grab a TSQLQuery and drop it on the datamodule. Rename it from “SQLQuery1” to something more meaningful, like ‘qryProducts’ (in my case, that’s what I’m doing, because I’ve got some sample data in a “products” table).
Check out the properties of your query object. There’s a couple of interesting, and a couple of necessary, elements here.
On the necessary front, “SQLConnection” needs to be set – because your query needs to know which database to ask for its information. Some apps connect to multiple database servers, or in different ways to the same one (for example, as an admin or as a user) and that would mean multiple connection objects (potentially one object with multiple settings that change at runtime, but it’s easier to manage in code with two separate connection definitions). In our case there’s only one, so click the drop-down in that property and select our connection.
The next and final “necessary” one is the SQL property. This is a “TStrings” object, which just means it is a list of string values. That list can be a multi-line SQL statement, but we won’t need more than one for this. We’re going to open up the strings editor (click on the ellipsis button in the property), and enter the following SQL statement:
Select * from Products
You can now test this query, by changing the “Active” property from False to True. Again, you’ll be prompted by the program for a username and password (because the query will automatically open the connection, and the connection will want to authenticate you). Once it goes true, set it back to false and set the connection’s “connected” property back to false as well, because it won’t do that all by itself.
At this stage we’ve got a connection that can go live, and we’re retrieving data – so if all we wanted to do was manipulate the data or check a value with our program, we’d be good to go. However, we want to actually show off the information a little bit, so we need some data controls on the main form of our app.
dbExpress is a little quirky, in that it operates on “unidirectional” datasets – as implied by this sort, it’s a one-way thing. The DBGrid, which we’re going to use shortly, requires a two-way connector. So to get around this, we’re going to insert a little “spoof” on it by pulling our results into a locally-held two-way dataset, called a client dataset.
Although for the purposes of this writeup we’re tricking the dbExpress stuff this way, I need to point out that in a real-world situation the feature that we’re bypassing like this is actually insanely useful. The ClientDataset is designed for creating n-tier applications. In the early days, apps were generally “desktop” and “client-server”, with workloads either entirely on the single user’s PC, or split between a client and a server. Towards 1997-2000, a revolution happened that added a third option: distributed computing. We look at it now as just the norm, but at the time it was brand new and a very big deal. N-tier means splitting your app’s work up among multiple computers (hopefully in a logical fashion) so that more work could be done faster by the app. This later morphed into a wide variety of distributed architectures (like “Service Oriented,” etc.), but the premise here is that you’d have a server responsible for hosting persistent data, an app that ran apart from it but which was responsible for retrieving that data (and perhaps performed validations on data sent back to it, etc.), and a client app that not only showed and manipulated that data, but also was able to run in a disconnected environment on a “suitcase” model for the data. When connectivity is re-established, the briefcase ships its changes (called a “delta packet”) back to the server for handling.
That’s what the ClientDataset does. Very cool component.
Let’s get back to business, though – to feed data to a ClientDataSet, you need a DatasetProvider. Drop one on your datamodule, and set its name to something that will make sense to you (like “dspProducts” or something). Next set its DataSet property to your query. As you can probably guess, the “DatasetProvider” provides a DataSet to ClientDatasets. Which, surprisingly enough, is what we need next. Go ahead and stick one on the datamodule and set its name to “cdsProducts”. Next set its “ProviderName” property to the name of your DatasetProvider, either typing it or via drop-down.
Delphi doesn’t include display elements in its datasets, because the philosophy behind a lot of Delphi programming is “If you don’t need it, don’t include it.” Datasets are for retrieval and manipulation of data, not its display. To add the ability to display to the mix, you need a component called a TDataSource.
Grab one from the Tool Palette and drop it on the main form of your app. Rename it to “dsProducts”. The job of this component is to relay the data from your datasets to visible data controls on your forms. This control is a bit limited in its scope, but it does have several useful features that when you get into programming seriously, will be extremely handy to have around – in particular, when a user of your app makes changes to the data in a form, you can insert routines that can look over the changes they are about to make, and perhaps abort them or pause the user if what they are about to enter is questionable or invalid. We won’t get into that here, but just be aware that’s what that component is good for.
Since our main form “uses” the datamodule, it will have visibility on what components are available there – namely the Query we put on it a few minutes ago. If you go to the Data Source’s “DataSet” property and choose the drop-down, you should see the Query from the datamodule listed there. Select it and let’s move on.
Next thing, let’s keep it basic, will be a DBGrid. A Grid is just a row-by-row display of all the columns in your dataset (the grid itself has a lot of customization features to it as well, but for now we’re going to just make it a clear window on the data).
Slap a DBGrid onto your form, and assign its dataset property to the dataset you created a few moments ago. That’s really all you have to do.
Ready to test something cool? Set the ClientDataSet’s “active” property to “True”. If everything is wired up properly, the DBGrid will populate with data from your table – in the designer! It’s able to do that because Delphi’s IDE is, itself, a running Delphi application. This was a huge development back when it launched, and for many years afterwards, because there wasn’t any other dev tool that could pull that little trick off. And when you’re building a data-driven user interface, there is nothing better than viewing it with real results.
Go ahead and set the Active property of both the ClientDataSet and your Query to false again (the CDS will have switched it on) and the Connection’s “Connected” property back to False.
Finally, while we’re in here, let’s put a button next to the grid for turning the data on and off. I’m going to show you something a little bit fancier than our standard controls in how we’re going to write code around that, as well. Instead of writing a control that directly grabs the query and turns it on, we’re going to follow the chain of references in the components.
Once your button is on the form, double-click it to create an “OnClick” event handler.
In that handler, write the following code:
if grdData.DataSource.DataSet.Active then
btnDataToggle.Caption := ‘Open Me’;
btnDataToggle.Caption := ‘Close Me’;
What this translates into is that we are looking at the grid’s datasource, checking its dataset, and if that dataset is currently “Active” (open), we close its connection, closing the dataset too. If it happens to be closed, we open it. In either case, we change the text of the button to represent what pressing it again will do.
You’re all set now – you can take that executable you just built and use it on pretty much any PC that has a SQL Server client on it, and a valid link back to your chosen server.
Play around with the various kinds of controls here – there are a great many data-aware elements you can goof around with. For me, I’m going to go for a while, and next time I’ll write up some examples of other methods of accessing data – ADO and FireDAC.
Until then, have fun!