On Fri, 23 Feb 2007, David Fetter wrote: > On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote: > > Anyone know of any guidelines for writing SQL which works under > > Oracle witch will also work under postgress. This is to ensure that > > SQL written for an Oracle database can be migrated to postgress > > later. > > You've just bumped into the problem that while standard SQL exists, > only Mimer and possibly DB2 implement it. The presentation below > outlines your main choices for supporting more than one DB back-end, > and they're all expensive and troublesome to maintain. > > http://www.powerpostgresql.com/Downloads/database_depends_public.swf With all due respect to Josh's presentation, there's a lot more to the story than those couple of slides. (They were meant to be given, I'm sure, along with a talk in which the speaker provided most of the value.) And I don't think launching an attack on MySql is helpful to this dialogue, though I do understand the point Josh is making... There are other choices. For example, Science Tools, back in 1997, faced with the similar but slightly different problem of being a vendor supporting multiple RDBMSes for client data, could have taken the typical choice of managing different code branches for each of the RDBMSes it supports. Instead, we wrote an SQL dialect translator that presently supports five (and soon six) RDBMS platforms - and could probably support all the rest if only someone cared enough to configure them - and this translator is available to customers, not just embeded for the exclusive use of Science Tools' applications. You link your user-application code to our library and you can send it any version of SQL, either statically or dynamically, and it automatically translates into the correct dialect for the database engine you're connected to. It does both DDL and DML and it has command-line tools available, too, so you don't have to link your apps if you don't want to. Presently supported are: Postgres (of course!), Informix, DB2, Sybase, and also Oracle - yes, of course, them, too. (OpenIngres is undergoing testing right now for certification sometime this spring.) Are there things it misses? Yes, but not much. I'll take the wild guess that more than 80% of applications are completely and adequately served. It has pass-through capability so you can still get at engine-specific features, though it does completely side-step stored procedures as these are vastly harder to automate conversion of - we just do the SQL. When calling a DBMS from our library, we handle error recovery, database reconnection, optional DBMS independent journaling and even important aspects of security. When parsing DDL, it (optionally) throws warnings of incompatability, though, as a practical matter, most engines have now removed most of their older limitations that made this vital in their earlier versions. (We support versions of all five since about 1997 and, as there were so many small changes along the way, we provide a configuration mechanism where you can tell it the limitations of your version such as attribute length, maximum length of varchar, etc.) Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 rtroy@xxxxxxxxxxxxxxxx, http://ScienceTools.com/
