Another approach that work well is to include the sql for this, then define it as a property in your configuration (sqlmapconfig.properties).
Larry On 8/23/07, Jean-Francois Poilpret <[EMAIL PROTECTED]> wrote: > 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 > > > > > > >
