January 14th, 2003, 04:41 PM
java method to convert non-ANSI ORACLE sql to Postgres usable ANSI SQL???
i am faced with a decision of either a massive code overhaul or a nasty method/program to convert my Oracle based queries into a Oracle9i / Postgresql friendly manner.
all my code was initially written against oracle7, oracle8i using sql queries that ran in these databases, for clients that used them. honestly, there is over a couple hundred thousand lines of code spread out through over 500+ java programs (using tags, with TLD's). a couple hundred of these tags/programs have the sql embedded with them. they have OUTER joins in them and based on ORACLE back then, it did not support the ANSI sql logic (using 'left outer join on'), so the outer join code is table.column(+)=table2.column.....
ORACLE is expensive, so alot of people want to use my code but want to use postgres as their database. i would like 1 set of code that could use either database, assuming the databases are set up exactly the same (already done). ORACLE9i now supports ANSI sql. So, I can go through the 500+ programs and change every single OUTER JOIN to ANSI sql, which could honestly take me months... also changing stuff like sequences (how they are called) and 'sysdate' to 'now()' and stuff like that....
OR - a java method that could take a NON-ANSI query and convert it to an ANSI QUERY and i could run the queries through this converter at runtime depending on the environment my client is running.
this way, all the code could be written as it always has been, and the converter could take care of all query changes.
the converter method is also preferential because if another difference is found that needs changed, it could be changed in the converter method 1 time, rather than the 500 programs, which have to be re-compiled and what not....
SO, my question is... has anyone heard of something like this method already existing?????