![\"\"](\"http:\/\/borkedcode.com\/wp\/wp-content\/uploads\/2017\/07\/Basic-Data-Architecture.jpg\")
<\/a><\/p>\n<\/p>\n
<\/p>\n
<\/p>\n
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.\u00a0 We aren\u2019t doing that, so we want something that encapsulates this sort of function and makes it pretty painless to do.<\/p>\n
Fortunately, Delphi was designed almost from the outset to do just that \u2013 and not only to make it painless, but fun.\u00a0 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.\u00a0 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 \u201cPowerBuilder\u201d did, and eventually Microsoft figured out a way).<\/p>\n
As I mentioned, when Delphi first shipped it had components that wrapped the Borland Database Engine inside it.\u00a0 Since then, quite a few additional \u2018engines\u2019 have been added (I\u2019ll use \u201cengine\u201d to term the library that offers up multiple drivers), expanding Delphi\u2019s potential database access even further.\u00a0 The BDE is still in there, but it is rather old and doesn\u2019t shine quite the way it used to \u2013 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.<\/p>\n
In addition to BDE, the following engines are included:<\/p>\n
Interbase Express<\/strong> \u2013 connectors specific to Interbase, a SQL Server offered by Borland\/Embarcadero\/Idera (I can\u2019t keep straight which company kept what part).\u00a0 Interbase is a fast and compact database that is particularly good for bundling in with applications, though expensive in its deployment costs.<\/p>\n dbExpress<\/strong> \u2013 this is an engine that surfaces drivers for Sybase\u2019s SQL Anywhere, regular Sybase, DB2, Firebird (an open-source fork of Interbase), \u201cIBLite\u201d (an even-more-compact version of Interbase), Informix, Interbase, Microsoft SQL, ODBC, Oracle, and SQLite.\u00a0 It also offers a connection to DataSnap, which is a programming framework for making multi-tiered applications.<\/p>\n DBGo<\/strong> \u2013 components that harness ADO (a successor of sorts to ODBC).<\/p>\n FireDAC<\/strong> \u2013 FireDAC is a modern iteration of the multi-access engines produced in prior generations of programming tools.\u00a0 It presents a common interface to dozens of different data repositories and storage methods.\u00a0 Among them are:<\/p>\n So\u2026as you can see, FireDAC isn\u2019t joking around.\u00a0 It connects to a LOT of data sources.\u00a0 If yours isn\u2019t listed there, it can probably still be reached through an ODBC driver.\u00a0 (Of course, if it isn\u2019t in there, it probably isn\u2019t worth programming for J.)<\/p>\n Within Delphi, data access is done through a series of components (not necessarily VCL ones, but they all work roughly the same).\u00a0 First, a connector component representing the program\u2019s 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.\u00a0 Finally, components responsible for passing that data into visual formats are used, converting the data into information.<\/p>\n In the 10.2 (\u201cTokyo\u201d) build of RAD Studio, the BDE has been removed \u2013 it was deprecated long ago, and finally has been pulled.\u00a0 If you\u2019re maintaining an old version of code that does still contain these, you can retrieve an installer from Embarcadero\u2019s site (here: https:\/\/cc.embarcadero.com\/Item\/30752<\/a>), but that\u2019s the only case where I\u2019d recommend you do so.\u00a0 For future use, it\u2019s best to get yourself into one of the more current sets of components.<\/p>\n For starters, let\u2019s take one of the simpler ones, dbExpress, and connect it to a Microsoft SQL Server installation.\u00a0 As it happens, I have a dev edition of MS SQL here on my laptop, so we\u2019ll start with that one.\u00a0 You\u2019re 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\u2019re going to put a full server on your machine).\u00a0 If you\u2019re getting into software development, I\u2019d really recommend you buy a license of the MS SQL Developer Edition (available here:\u00a0 https:\/\/www.microsoft.com\/en-us\/sql-server\/application-development<\/a>).\u00a0 It\u2019s a fully-functional server, and is fantastic for working out issues prior to testing against a real server.<\/p>\n Let\u2019s start a new project.\u00a0 You had the basics of this in my last Delphi article, so go ahead and roll one out.\u00a0 A blank form is just fine.\u00a0 I\u2019ll do one here too, a VCL forms app for simplicity\u2019s sake.\u00a0 I\u2019ll target Win64 again as I did previously.<\/p>\n When Delphi was first launched in 1995, people used it a LOT for database access.\u00a0 However, even though the data access components are really small, they tend to collect quickly and can really clutter up your designer.\u00a0 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.\u00a0 Borland (the original maker of Delphi) recognized this as a pain point right away and in Delphi 2 released what is called a \u201cdata module\u201d \u2013 a non-visible form (so it wouldn\u2019t use as many system resources) which can host all manner of non-visual components like data access stuff, API components, and so on.\u00a0 That\u2019s what we\u2019ll do here too.<\/p>\n Once your project is ready, and you can see your designer with Form1 loaded, go to the File menu.\u00a0 In there choose File > New > Other\u2026 and in the dialog that appears, select \u201cDelphi Files\u201d from the tree view on the left.\u00a0 The right pane will have a list of choices, one of which is \u201cData Module\u201d.\u00a0 Select that and confirm by clicking \u201cOK\u201d.<\/p>\n Thar she blows!<\/p><\/div>\n Notice your Project Manager now shows you have \u201cunit1.pas\u201d and \u201cunit2.pas\u201d as part of your project.\u00a0 Unit1 is your main form, and Unit2 is the datamodule.\u00a0 You should probably save and name your files now, to stay in the habit J.\u00a0 Go ahead, I\u2019ll wait.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n Our next step is to designate a driver for this component \u2013 choose \u201cMSSQL\u201d.<\/p>\n By doing this, the component will fill up its \u201cParams\u201d section with a series of values that it will need to operate.\u00a0 Most of these you won\u2019t have to touch or bother with.\u00a0 The two you will need to set are \u201cHostName\u201d and \u201cDatabase\u201d \u2013 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.\u00a0 For hostname, I could give [MACHINENAME]\\[INSTANCENAME]<\/p>\n So it would look like \u201cMYSYSTEM\\SQLONE\u201d or similar.\u00a0 Since I\u2019m running locally, I\u2019m going to sub \u201c.\u201d for my machine name, so my Servername parameter reads as follows:<\/p>\n .\\THEOSQL<\/p>\n 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\u2019m using a home-grown named \u201cSampleData\u201d).<\/p>\n I\u2019m also going to change my \u201cMaxBlobSize\u201d for my own purposes \u2013 don\u2019t worry about this.\u00a0 Leave yours as -1.\u00a0 If you know what this is for, you can deal with it on your own terms, otherwise it\u2019s not important for this lesson.<\/p>\n Params, check.<\/p><\/div>\n Once your params are set, you can test them by changing the \u201cConnected\u201d property to \u201ctrue\u201d.\u00a0 You\u2019ll be prompted for a name and password (you did remember to store your login credentials somewhere, didn\u2019t you?), and if you give proper credentials, it\u2019ll change to true.\u00a0 That confirms that you have a live connection to your database.<\/p>\n Once you\u2019ve confirmed this, go ahead and set it back to \u201cfalse\u201d.\u00a0 Leaving a connection on in the designer is setting yourself up for a few problems later, and it\u2019s better to handle it in the program at run-time.\u00a0 We\u2019ll get back to this shortly when I show you how to get live data showing up in your app.<\/p>\n So\u2026we have a connection to the database, but we don\u2019t yet have real data.\u00a0 Let\u2019s set that up next.<\/p>\n I made up some data in SQL Server, so we’ll have something to look at.<\/p><\/div>\n For this, we need a dataset<\/em> component.\u00a0 Where a \u201cconnection\u201d represents a channel to the database server, a \u201cdataset\u201d 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. \u2013 basically anything that can be considered to have actual data in it).\u00a0 In the case of dbExpress, this means a TSQLDataSet, TSQLQuery, TSQLStoredProc, TSQLTable, or TSimpleDataSet.\u00a0 Since we\u2019re dealing with MS SQL, let\u2019s keep it straightforward and use a TSQLQuery.\u00a0 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.<\/p>\n Grab a TSQLQuery and drop it on the datamodule.\u00a0 Rename it from \u201cSQLQuery1\u201d to something more meaningful, like \u2018qryProducts\u2019 (in my case, that\u2019s what I\u2019m doing, because I\u2019ve got some sample data in a \u201cproducts\u201d table).<\/p>\n Check out the properties of your query object.\u00a0 There\u2019s a couple of interesting, and a couple of necessary, elements here.<\/p>\n On the necessary front, \u201cSQLConnection\u201d needs to be set \u2013 because your query needs to know which database to ask for its information.\u00a0 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\u2019s easier to manage in code with two separate connection definitions).\u00a0 In our case there\u2019s only one, so click the drop-down in that property and select our connection.<\/p>\n The next and final \u201cnecessary\u201d one is the SQL property.\u00a0 This is a \u201cTStrings\u201d object, which just means it is a list of string values.\u00a0 That list can be a multi-line SQL statement, but we won\u2019t need more than one for this.\u00a0 We\u2019re going to open up the strings editor (click on the ellipsis button in the property), and enter the following SQL statement:<\/p>\n Select * from Products<\/p>\n You can now test this query, by changing the \u201cActive\u201d property from False to True.\u00a0 Again, you\u2019ll 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).\u00a0 Once it goes true, set it back to false and set the connection\u2019s \u201cconnected\u201d property back to false as well, because it won\u2019t do that all by itself.<\/p>\n All set with the query…<\/p><\/div>\n At this stage we\u2019ve got a connection that can go live, and we\u2019re retrieving data \u2013 so if all we wanted to do was manipulate the data or check a value with our program, we\u2019d be good to go.\u00a0 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.<\/p>\n dbExpress is a little quirky, in that it operates on \u201cunidirectional\u201d datasets \u2013 as implied by this sort, it\u2019s a one-way thing.\u00a0 The DBGrid, which we\u2019re going to use shortly, requires a two-way connector.\u00a0 So to get around this, we\u2019re going to insert a little \u201cspoof\u201d on it by pulling our results into a locally-held two-way dataset, called a client dataset.<\/p>\n Although for the purposes of this writeup we\u2019re tricking the dbExpress stuff this way, I need to point out that in a real-world situation the feature that we\u2019re bypassing like this is actually insanely useful.\u00a0 The ClientDataset is designed for creating n-tier<\/em> applications.\u00a0 In the early days, apps were generally \u201cdesktop\u201d and \u201cclient-server\u201d, with workloads either entirely on the single user\u2019s PC, or split between a client and a server.\u00a0 Towards 1997-2000, a revolution happened that added a third option:\u00a0 distributed computing.\u00a0 We look at it now as just the norm, but at the time it was brand new and a very big deal.\u00a0 N-tier<\/em> means splitting your app\u2019s work up among multiple computers (hopefully in a logical fashion) so that more work could be done faster by the app.\u00a0 This later morphed into a wide variety of distributed architectures (like \u201cService Oriented,\u201d etc.), but the premise here is that you\u2019d 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 \u201csuitcase\u201d model for the data.\u00a0 When connectivity is re-established, the briefcase ships its changes (called a \u201cdelta packet\u201d) back to the server for handling.<\/p>\n That\u2019s what the ClientDataset does.\u00a0 Very cool component.<\/p>\n Let\u2019s get back to business, though \u2013 to feed data to a ClientDataSet, you need a DatasetProvider.\u00a0 Drop one on your datamodule, and set its name to something that will make sense to you (like \u201cdspProducts\u201d or something).\u00a0 Next set its DataSet property to your query.\u00a0 As you can probably guess, the \u201cDatasetProvider\u201d provides a DataSet to ClientDatasets.\u00a0 Which, surprisingly enough, is what we need next.\u00a0 Go ahead and stick one on the datamodule and set its name to \u201ccdsProducts\u201d.\u00a0 Next set its \u201cProviderName\u201d property to the name of your DatasetProvider, either typing it or via drop-down.<\/p>\n “She might not look like much, but she’s got it where it counts…”<\/p><\/div>\n Lastly\u2026<\/p>\n Delphi doesn\u2019t include display elements in its datasets, because the philosophy behind a lot of Delphi programming is \u201cIf you don\u2019t need it, don\u2019t include it.\u201d\u00a0 Datasets are for retrieval and manipulation of data, not its display.\u00a0 To add the ability to display to the mix, you need a component called a TDataSource.<\/p>\n Grab one from the Tool Palette and drop it on the main form of your app.\u00a0 Rename it to \u201cdsProducts\u201d.\u00a0 The job of this component is to relay the data from your datasets to visible data controls on your forms.\u00a0 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 \u2013 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.\u00a0 We won\u2019t get into that here, but just be aware that\u2019s what that component is good for.<\/p>\n Since our main form \u201cuses\u201d the datamodule, it will have visibility on what components are available there \u2013 namely the Query we put on it a few minutes ago.\u00a0 If you go to the Data Source\u2019s \u201cDataSet\u201d property and choose the drop-down, you should see the Query from the datamodule listed there.\u00a0 Select it and let\u2019s move on.<\/p>\n Next thing, let\u2019s keep it basic, will be a DBGrid.\u00a0 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\u2019re going to just make it a clear window on the data).<\/p>\n Slap a DBGrid onto your form, and assign its dataset property to the dataset you created a few moments ago.\u00a0 That\u2019s really all you have to do.<\/p>\n Slap-bang on the form<\/p><\/div>\n Ready to test something cool?\u00a0 Set the ClientDataSet\u2019s \u201cactive\u201d property to \u201cTrue\u201d.\u00a0 If everything is wired up properly, the DBGrid will populate with data from your table \u2013 in the designer!\u00a0 It\u2019s able to do that because Delphi\u2019s IDE is, itself, a running Delphi application.\u00a0 This was a huge <\/em>development back when it launched, and for many years afterwards, because there wasn\u2019t any other dev tool that could pull that little trick off.\u00a0 And when you\u2019re building a data-driven user interface, there is nothing better than viewing it with real results.<\/p>\n Ta-da! Yeah, baby!<\/p><\/div>\n 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\u2019s \u201cConnected\u201d property back to False.<\/p>\n Finally, while we\u2019re in here, let\u2019s put a button next to the grid for turning the data on and off.\u00a0 I\u2019m going to show you something a little bit fancier than our standard controls in how we\u2019re going to write code around that, as well.\u00a0 Instead of writing a control that directly grabs the query and turns it on, we\u2019re going to follow the chain of references in the components.<\/p>\n Once your button is on the form, double-click it to create an \u201cOnClick\u201d event handler.<\/p>\n In that handler, write the following code:<\/p>\n if grdData.DataSource.DataSet.Active then<\/p>\n begin<\/p>\n dmMain.MSSQLConnection.Close;<\/p>\n btnDataToggle.Caption := ‘Open Me’;<\/p>\n end<\/p>\n else begin<\/p>\n grdData.DataSource.DataSet.Open;<\/p>\n btnDataToggle.Caption := ‘Close Me’;<\/p>\n end;<\/p>\n What this translates into is that we are looking at the grid\u2019s datasource, checking its dataset, and if that dataset is currently \u201cActive\u201d (open), we close its connection, closing the dataset too.\u00a0 If it happens to be closed, we open it.\u00a0 In either case, we change the text of the button to represent what pressing it again will do.<\/p>\n You\u2019re all set now \u2013 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.<\/p>\n Play around with the various kinds of controls here \u2013 there are a great many data-aware elements you can goof around with.\u00a0 For me, I\u2019m going to go for a while, and next time I\u2019ll write up some examples of other methods of accessing data \u2013 ADO and FireDAC.<\/p>\n Until then, have fun!<\/p>\n <\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Delphi Hi everyone! Last time we talked Delphi, we talked a bit about frameworks and we built your first Delphi app, a \u201cHello World\u201d for Windows.\u00a0 This time around, we\u2019re going to talk about one of the most common uses … Continue reading \n
<\/a><\/a>Saved it?\u00a0 Okay, great.\u00a0 Notice the Data Module looks like a blank form, but it has no title bar, no icon, etc.\u00a0 That\u2019s because it will never appear visually within your application.\u00a0 Your visual form will use this Data Module, referencing it so that it can get a grip on the components present within it.\u00a0 To do this, return to your main form, and from the menus choose File > Use Unit.\u00a0 You\u2019ll see a list with your datamodule in it.\u00a0 Double-click and you\u2019re on your way.<\/p>\n<\/a>First thing we\u2019re going to want is to go to the Tool Palette and open up the \u201cdbExpress\u201d group.\u00a0 The starter is the TSQLConnection, which will represent a persistent connection to our database.\u00a0 Grab one and drop it on your Data Module.\u00a0 The new connection will default to a name of \u201cSQLConnection1\u201d \u2013 go ahead and rename it to \u201cMSSQLConnection\u201d.<\/p>\n<\/a>it the full name of my SQL instance (I\u2019m assuming you went and picked up the Dev edition of MS SQL I mentioned above), but since I\u2019ve installed it on my development machine, I can use \u201c.\u201d as the machine name.\u00a0 Each instance of SQL Server gets its own name too, so that is a two-parter.\u00a0 It will look like this:<\/p>\n<\/a><\/a><\/a><\/a><\/a><\/a>