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.
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:-(
@Lars, LOL!!! That’s true
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
@Des, you have to create a DSN and set the database name in on your dbExpress connection to the name of the DSN
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?
May I Add, I’m trying this on a trial installation of Delphi XE2, could this be a problem? Some kind of limitation?
Thank you very much!
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
I don’t know why this error in XE3, did you try to contact Embarcadero technical support?