ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean-Francois Poilpret" <jfpoilp...@yahoo.fr>
Subject RE: Fetching ids from oracle and postgresql sequences
Date Thu, 23 Aug 2007 16:05:03 GMT
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