Connecting to PostgreSQL through dbExpress ODBC driver

Let's share the knowledge with your friends

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.


Let's share the knowledge with your friends
9 replies
  1. des
    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

    Reply
  2. des
    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?

    Reply
  3. des
    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?

    Reply
  4. PhuongNguyen
    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

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.