Making XMPie work with PostGRE SQL 8.3 backend

For all the good things about XMPie, one of my niggles has been the lack of support for PostGRE SQL. I guess thats pretty understanderble; most of there client base use SQL server and other such MS products to some degree. There are times when id really like to use PostGre SQL as the backend and it can be a bit annoying. Anyway…thats another story…. for now were just going to look at implementing the OLEDB conectivity onto the servers to make it talk to our datasource.

Before we begin please note that it is critical to make sure that your PostgreSQL server can accept connections from your host IP. Otherwise you will get an error detailing that windows could not initialize the OLEDB provider. So, for those not familiar with PGSQL, check your postgresql.conf and pg_hba.conf….

Step 1

Ok, firstly you will need to install the OLEDB driver onto the server (and all extension serves where applicible)…

Download the OLEDB driver files from here - its currently in beta so do bear that in mind. Once downloaded, unzip it and copy libpq.dll and PgOleDb.dll to your windows\system32 folder.

Next, to register those new files as sources, go Start > Run and type

regsvr32 PGOLEDB.DLL

All being well you should see a sucsess box - if not, then i guess check your spelling against the DLL :)

Step 2

Now, provided you got your working OLEDB driver up and running in the previous step (be sure to test this with a UDL script) you should be able to add the database into the xmpie dashboard using a dsn something like this:

<code>Provider=PostgreSQL OLE DB Provider;Password=<pass>;
User ID=root;Data Source=127.0.0.1;Location=demo;Extended Properties=&#8221;&#8221;
</code>

I am going to be doing some testing to see if this is any quicker than SQL Server (fingers crossed it will be) - stay tunned readers!

Give me a shout if you dont seem to be able to make this work (for whatever reason)