Simple DBX4 Console App Example
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.

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.



Comments
-
Jeyhun Mammadov Sunday, 29 July 2007
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. -
Robert Love Monday, 30 July 2007
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. -
Rasmus Wätjen Monday, 30 July 2007
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. -
Pawel Glowacki Tuesday, 14 August 2007
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". -
Please login first in order for you to submit comments
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"?