ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Fetching ids from oracle and postgresql sequences
Date Thu, 23 Aug 2007 17:57:25 GMT
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 <jfpoilpret@yahoo.fr> 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:Bernd.Kappler@genedata.com]
> Sent: Thursday, August 23, 2007 10:45 PM
> To: user-java@ibatis.apache.org
> 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
>
>
>
>
>
>
>

Mime
View raw message