Connecting to PostgreSQL through dbExpress ODBC driver

Delphi XE2 and C + + Builder XE2  introduce the new dbExpress ODBC driver, which enable FireMonkey and VCL applications to connect to any database that provides a ODBC driver.

In order to show how it works, I created a FireMonkey project sample that connect to a PostgreSQL database.

As first step, you must install the PostgreSQL ODBC driver on the machine that will run the application. Second, applications compiled as 32-bit, requires the 32-bit ODBC driver, 64-bit applications will require the 64-bit ODBC driver.

Onde installed the driver, create and configure a User DSN or System DSN on the ODBC Data Source Administrator (odbcad32.exe):

Instructions for windows 64-bit

  • For 32-bit applications use c:\Windows\SysWOW64\odbcad32.exe
  • For 64-bit applications use o c:\Windows\system32\odbcad32.exe

Instructions for windows 32-bit

  • Use  c:\Windows\system32\odbcad32.exe

The next step is to create an dbExpress ODBC alias on Data Explorer, where the Database Name parameter is the name of the Data Source created earlier, in my example the Data Source is called PostgreSQL35W. The database, username and password was already defined in the Data Source, but you can override the username and password parameters through the SQLConnection.

Assuming you already know how to use the SQLConnection, DataSetProvider and ClientDataSet or SimpleDataSet ,and DataSource components, just connect all  the components as you usually do. The SQLConnection will look like this:

  object SQLConnection1: TSQLConnection
    ConnectionName = 'PostgreSQLOdbc'
    DriverName = 'ODBC'
    LoginPrompt = False
    Params.Strings = (
      'drivername=ODBC'
      'Database=PostgreSQL35W')
  end

You can use the dbExpress framework when connected to a database through dbExpress ODBC driver. In XE2 the ODBC driver is compatible with Windows, we are evaluating the possibility to support this driver on Mac.

The figure below shows the FireMonkey application connected to PostgreSQL via dbExpress ODBC driver. Now you can connect to any other other database not supported natively by dbExpress.

Download the source code here.

9 Responses to Connecting to PostgreSQL through dbExpress ODBC driver

  1. Reply Lars Frische says:

    Looking at the currencies for the countries of the euro-zone your database seems to be a bit outdated. However, in these days and ages it might just as well be prophetic:-(

  2. Reply des says:

    Hi, I’m new to XE2 and I usually connected to my PgSQL through a DAC.

    I followed your example but when I go in the Data Explorer window I can’t find the system DSN I created.

    I tried adding manually a new connection via the Data Explorer but I still can’t connect.

    The error I get is “[Microsoft][Driver Manager ODBC] Connection not open”

    I tried with a simple MDB file but I still get the same error, so I think it’s not about the pgSQL ODBC drivers.

    Can you give me any advice?

    Thanks

  3. Reply des says:

    Yes sure, that’s what I did.

    I created my “postgreTest” DSN and went and refreshed the Data Explorer, but it wasn’t listed.

    That’s when I tried manually creating an object with the same name in the Data Explorer.

    I mean, in the second image, is “PostgreSQLOdbc” supposed to appear automatically?

  4. Reply des says:

    May I Add, I’m trying this on a trial installation of Delphi XE2, could this be a problem? Some kind of limitation?

  5. Reply Lena says:

    Thank you very much!

  6. Reply PhuongNguyen says:

    Hi,I using Rad XE3, I tried adding manually a new connection via the Data Explorer but I still can’t connect to postgres,
    I have error:[ilink32 Error] Error: Unresolved external ‘SQLGetInfoW’ referenced from C:\PROGRAM FILES\EMBARCADERO\RAD STUDIO\10.0\LIB\WIN32\DEBUG\DBXODBCDRIVER.LIB|Data.DBXOdbcMetaDataReader

    Pls help me

Leave a Reply to Andreano Lanusse Cancel reply

Your email address will not be published. Required fields are marked *