michael schurter

When Synthesys first decided to go with PostgreSQL as the database for a large web application, I was very excited to get away from our usual reliance on MySQL and move to a real database platform.

Once we figured out the whole MySQL auto_increment vs. PostgreSQL SERIAL, the first thing we really missed in PostgreSQL was the REPLACE statement. I understand why its missing, and it is kind of a hack of a SQL statement (its not in a SQL standard).

Turns out in the SQL 2003 Standard there is a statement similar to REPLACE called MERGE. Despite PostgreSQL’s excellent support for standards, they have not implemented MERGE. I would guess it has something to do with the ambiguity of the statement.

At any rate I still love working with PostgreSQL, and for what its worth Npgsql is an excellent PostgreSQL connector for Mono/.Net. (Even in RC/Beta state)

At Synthesys we’re using PostgreSQL as the database for a large web application.� For the time being we’re writing our reports in Crystal Reports, although we’re hoping to move to a free platform in the future.

Unfortunately we only have Crystal Reports XI Standard Edition which evidently doesn’t support ODBC connections.� Crystal wants you to buy the Professional Edition to get that kind of enterprise functionality.

Our first attempt to get the two talking consisted of using Microsoft Access’s External Table Linking capability.� While this was horrifying in concept, it actually worked beautifully until you needed to sort by a PostgreSQL text field.� Microsoft Access read text fields as memo fields, and you can’t sort by memo fields.

So back to the drawing board.� Our next attempt was to use PgOleDb in hopes that Crystal allowed that.� For some reason Crystal allows OLE connections, but not ODBC.� However, whenever we tried to add a table to a report, we received an OLE/ADO error.� Have I mentioned I hate Window’s data access technologies?� Well I do.

Our final attempt consisted of connecting to ODBC through OLE.� I don’t know why this is possible, but it is.� Not only is it possible, but it works beautifully.

For some bizarre reason you can connect to a PostgreSQL database from Crystal Reports XI Standard Edition if you use an OLE wrapper around a PostgreSQL ODBC connection.

Many thanks to psqlODBC, and no thanks to Crystal.

I’ve been keeping up a good pace of bug fixing in OpenIT thanks to user bug reports. I’m really getting bitter about using MySQL 4.0, especially after we’ve started using PostgreSQL for a new project at Synthesys.

Views? Foreign Key Constraints? Cascading UPDATEs and DELETEs? I’ve had to implement these in PHP! Lord have mercy on my soul. While I appreciate how easy MySQL is to install and use, after using PostgreSQL for just a short while I’m ready to drop MySQL and never look back.