Hello Bernd, My current way of doing it (far from perfect I must admit) is to manage one sqlmaps xml file per supported DB. All statements have the same name. I know this makes a lot of redundant SQL but it makes it easier not only to manage each DBMS way to manage sequences/identities and also other differences in the SQL language (eg, I don't know now, but last time I checked, Oracle still did not support standard SQL outer joins but had its own syntax for that). Finally it allows specific optimization (only if required, I don't advise to make SQL optimization systematic, but only on-demand), which is most often specific to each DBMS. In my situation (only a few dozen statements for a small DB model), this fits perfectly. However, with large DB models, hence high number of statements this could become more difficult to manage.
Besides this, that approach differs from yours mainly in the fact that there is only one Java code location where the choice must be handled: that's where the SqlMapsClient is initialized, nowhere else. Of course, it is also possible (but I did not try it) to have a common xml file for common sql queries and only have separate files for specific queries; that is OK when you know beforehand which DBMS you will have to support during the whole lifecycle of your system. However, if you intend to add support to other DBMS in the future then this approach will probably become messy (because previously common SQL for 2 DBMS A & B, might not be common anymore when you put DBMS C into the picture...) Cheers Jean-Francois -----Original Message----- From: Bernd Kappler [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 10:45 PM To: [email protected] Subject: Fetching ids from oracle and postgresql sequences Hi, we have an application that should work with oracle and postgresql as a database backend. Unfortunately, the syntax for getting values from a sequence is slightly different for both databases and we therefore cannot use the same sql for both. Has somebody solved this problem already? Where would be the best place to do the switch? Would you recommend to create two different statements and do the switching in the java code, i. e. something like <select id="getNextFromSequence-Postgresql"> ... </select> <select id="getNextFromSequence-Oracle"> ... </select> and in java if (sqlMapClient.getCurrentConnection() instanceof OracleConnection) { sqlMapClient.queryForObject("getNextFromSequence-Oracle"); } else { sqlMapClient.queryForObject("getNextFromSequence-Postgresql"); } or is there a clever way to do that with dynamic sql? Best regards Bernd
