Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (athena.apache.org: domain of larry.meadors@gmail.com
 designates 66.249.90.181 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:reply-to:sender:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
        b=B0orW8UsV8295XsnuydTwb+LeJkbbXGN9rDKVcuaBUiwoHkKsdnxmQkwh1zgaF5ZmNDWIS6ZkFLsfhVwQ9yYgALDEEeJ/yWUL7ziqvEbQAW35r4waVOo1yafGw8pbArdZjdlO0LwR5VIFXgBxGGWDgfW28PeRGQENMOE9/hv5Ow=
Message-ID: <a10a823c0708231057y7d25d2cemb1b894de1becfb14@mail.gmail.com>
Date: Thu, 23 Aug 2007 11:57:25 -0600
From: "Larry Meadors" <lmeadors@apache.org>
Reply-To: lmeadors@apache.org
Sender: larry.meadors@gmail.com
To: user-java@ibatis.apache.org
Subject: Re: Fetching ids from oracle and postgresql sequences
In-Reply-To: <000101c7e59f$5fd0d1f0$0301a8c0@VAIOSZ330>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <46CDAB65.6080305@genedata.com>
	 <000101c7e59f$5fd0d1f0$0301a8c0@VAIOSZ330>

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
>
>
>
>
>
>
>