Nintex Workflow – Execute SQL to PostgreSQL on a 64-bit server
Time to get back into the blogging, if only to keep my mind fresh and to keep track of what I find in my geeking.
After some talks with a friend of mine last night, we decided to check out PostgreSQL, seeing as for a simple application, MSSQL Express was taking up way too much diskspace and memory.
So, I thought I’d try out Nintex Workflow and specifically the Execute SQL action with ODBC checked. The bonus here, being that I wouldn’t have to write code.
Installing PostgreSQL and it’s ODBC driver was really quick and painless. The odd thing I found, was that on my Windows Server 2008 64-bit, when I clicked on Adminstrative Tools->Data Sources (ODBC), the PostgreSQL driver did not appear in the Drivers tab. Which also meant, that I couldn’t create a DSN to my database.
Some hunting around, and I found that on Windows Server 2008, there is another ODBC Manager that is located in c:\Windows\SysWow64 and it is odbcad32.exe. This is the 32-bit version of the ODBC Manager, and the PostgreSQL driver appeared in there. I was now able to create a DSN and test the connection successfully.
I then configured the Execute SQL action in Nintex Workflow, but I still received the same error about “No data source….”. So, time to get back to development and write some C# code that uses the OdbcConnection class. Funnily enough, I received the same error. When I changed my build configured to x86 it worked.
PostgreSQL only has a 32-bit ODBC driver. If you have installed Nintex Workflow on a 64-bit Server, then it will <strong>not</strong> be able to access your database. I would assume that this would be the case for other databases that don’t have a 64-bit version of their ODBC driver. Hopefully Oracle and MySQL do, but I don’t have the time currently to investigate them.
I thought I might be able to recompile the PostgreSQL ODBC driver into 64-bit to see if that would work, but I’m having trouble find some header files. The hunt continues.