Simple DBX4 Console App Example
Just two days ago Dr.Bob posted a very interesting article "Delphi 2007 and DBX4", which reminded me to post source code from my recent European Web Seminar Developer Days "DBX4 - Database Development After BDE" session.
The new DBX4 database access architecture introduced in Delphi 2007 for Win32 is very elegant. On the VCL component level it looks like almost nothing has been changed. "TSQLConnection", "TSQLDataSet" and other familiar component names are still there on the "dbExpress" Tool Palette tab. However closer inspection will reveal subtle changes in VCL DBX components properties and methods. Dr.Bob mentions changes in signatures of methods related to transaction support. Instead of using a longword "TransactionID" to identify transactions, we can now use "DBXTransaction" object, which makes for a much cleaner solution.
Probably the most interesting new property added to the "TSQLConnection" component is "DBXConnection", which provides access to the new underlying DBX4 (sometimes called "TDBX") architecture. "TDBXConnection" class is defined in the "DBXCommon" unit, which also defines "TDBXCommand" and "TDBXReader" classes that form the backbone of DBX4 data access. The following slide shows their relationships.
"TDBXConnection" class contains "CreateCommand" method that returns an instance of "TDBXCommand" class. Every connection can have zero or more commands. "TDBXCommand" in turn provides "ExecuteQuery" method that returns an instance of "TDBXReader" class that is used for accessing the results of the command. So every command can have zero or more readers. The trace output generated by DBXTrace delegate driver uses a special numbering scheme to quickly find out which reader belongs to which command, and which command belongs to which connection. So for example "ReaderC1_2_1" is the first reader, of the second command, of the first connection.
One of the most innovative features of the DBX4 driver framework are custom command types. The default command type is "Dbx.SQL" (defined in "TDBXCommandTypes" class), but developers are free to add their own custom command types and descend from TDBXCommand class. In this way it is very straightforward to extend the DBX4 framework to include some non-standard command types. For example Delphi 2007 comes with "TDBXPool" delegate driver that provides "GetPools" command that returns a reader with a row for each connection pool that is currently active.
This opens a new world of possibilities. For example it is not difficult to think about a command type that would return file system information, or provide access to aggregated data from a multidimensional OLAP cube. It is only our imagination that limits the new and creative ways of using the DBX4 framework:-)
Inspired by Andreano Lanusse example, I wanted to write a simplistic DBX4 application that would only execute a SQL SELECT statement and display a result set. The idea here is to make the code as generic as possible. Specifically I did not want to hardcode a SQL statement, and consequently not hardcode column count and column names. The username and password are also not hardcoded and are read from connection properties. Before running this program you need to create a database connection in Data Explorer and put the name of this connection into the "CONNECTION_NAME" constant. The last thing to do is to specify a SELECT SQL statement as a value of "SQL_SELECT_COMMAND" constant. This of course will depend on table and column names in the database specified in "CONNECTION_NAME". In the example below I’m using CodeGear InterBase 2007 sample "EMPLOYEE" database.
The source code of the "DBX4SimpleExample" project can be downloaded from here.
Share This | Email this page to a friend
Posted by Pawel Glowacki on July 27th, 2007 under Uncategorized |12 Responses to “Simple DBX4 Console App Example”
Leave a Comment
Server Response from: dnrh2.codegear.com
July 27th, 2007 at 6:01 pm
Interesting article. I ran the sample code and it works fine for the EMPLOYEE database. I tried it on a MySQL database and got the output
Feature not Implemented
where the data would be. Does this mean that the MySQL drivers don’t implement the "Reader"?
July 30th, 2007 at 6:48 am
It would be better to write
aConnFactory:=TDBXConnectionFactory.Create;
insted of
aConnFactory:=TDBXConnectionFactory.GetConnectionFactory;
Because GetConnectionFactory means ConnectionFactory is already created. But we don’t see it from the source. So it’s complicated.
People using Delphi to write programs used to understand what is going from the source. Delphi is prefered to other programming languages for it’s understandability.
July 30th, 2007 at 1:34 pm
David,
The driver implements reader, it may not implement GetAnsiString for the column your trying to access.
Jeyhum,
GetconnectionFactory returns a single object. i.e. Singleton. So you can GetConnectionFactory N number of times without having to worry about there ever being more than one instance.
July 30th, 2007 at 3:14 pm
Jeyhun,
it looks like TDBXConnectionFactory is a Singleton. That means you should never use the constructor directly.
I wonder why the TDBXConnectionFactory.Create is public, it should be private or protected for a "true" Singleton, so that unwitting programmers would not use it.
August 1st, 2007 at 3:09 pm
This looks pretty straightforward but if you run with FASTMM4 it reports a memory leak on close down. Is this a problem with the demo or within TDBXConnection itself?
Regards, Paul
August 3rd, 2007 at 6:35 am
The not implemented error message has been improved in the highlander time frame to indicate what type the column expects to be accessed as. Eventually we want to implement "As" methods to perform type coercion as needed.
August 3rd, 2007 at 6:36 am
Typically most apps just use the TDBXConnectionFactory.GetConnectionFactory singleton that loads connections from the dbx*.ini files. The constructor was left in to allow for other implementations.
August 9th, 2007 at 9:46 pm
thanks for the news update!
August 12th, 2007 at 8:05 pm
Just back from the holiday in Poland. (Great sun and beautiful sea:-) That’s why I was not responding to your comments. I appreciate all of them:-)
More to come tommorrow!
Go DELPHI!
August 14th, 2007 at 3:37 pm
Paul,
I wonder why you see the memory leak. Could you provide some more info that you can see from fastmm output?
The idea behind this code was to make it as simple as possible, but I see that there should be nested try..finally blocks to make sure that destructors at the end of the try..except block are called.
The program itself should probably also check if the field is not nil before calling "GetAnsiString".
November 7th, 2007 at 5:41 pm
Thank you for this article, I appreciate it even more because it is not so common to find those kind of things on the net. Thnx!
May 9th, 2008 at 11:01 am
Very nice and interesting article.