Simple DBX4 Console App Example

Posted by on in Blogs
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.


About
Gold User, Rank: 9, Points: 364
Crazy about Delphi Programming!

Comments

  • Guest
    David Plock Friday, 27 July 2007

    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"?



  • Guest
    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.

  • Guest
    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.



  • Guest
    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.

  • Guest
    Paul Hughes Wednesday, 1 August 2007

    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

  • Guest
    Steve Shaughnessy Thursday, 2 August 2007

    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.

  • Guest
    Steve Shaughnessy Thursday, 2 August 2007

    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.

  • Guest
    Pozycjonowanie Thursday, 9 August 2007

    thanks for the news update!

  • Guest
    Pawel Glowacki Sunday, 12 August 2007

    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!

  • Guest
    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".

  • Guest
    tłumaczenia angielski Wednesday, 7 November 2007

    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!

  • Guest
    fussball Wednesday, 11 March 2009

    Gute Arbeit hier! Gute Inhalte.

  • Guest
    sohbet Sunday, 17 May 2009

    thanks for this article.. Sometimes many pictures may draw off the attention.

  • Guest
    Pawel Glowacki Tuesday, 19 May 2009

    @sohbet, thanks for the comment. It was actually a problem with image URLs...

  • Guest
    Pawel Glowacki Tuesday, 19 May 2009

    BTW: In Delphi 2009 the equivalent code looks much simpler...

  • Guest
    Ustrzyki noclegi Thursday, 11 June 2009

    thanks this is very nice site thank you for this!

  • Guest
    sohbet Sunday, 14 June 2009

    I am Very thank full the owner of this blog. Becouse of this blog is very imformative for me.. And I ask u some thiing You make more this type blog where we can get more knowledge. and any one tell me how can I find this type blog.

  • Guest
    BHP Warszawa Monday, 6 December 2010

    Good work – good idea – thanks very much.

  • Guest
    Wrocław psycholog Monday, 6 December 2010

    Przydatna stronka.dzieki

  • Guest
    Biber Hapı Lw6090 Zayıflama H4P1 Friday, 18 March 2011

    thank you very much

  • Please login first in order for you to submit comments

Check out more tips and tricks in this development video: