You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by Olivier Ziller GMail Lists <ol...@gmail.com> on 2005/10/07 08:55:18 UTC

Mutliple database vendor

hello,

my application have to work both with MySql and Oracle.
Most of my queries work on both database but a few are specific. 
Especially those with selectKey...

What i've done is to make 3 files for statements : one for generic 
statements, one for oracle and one for mysql...

in the mysql file i will have something like :

    <insert id="insertErreurObjet" 

        parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >

        insert into FUN_ERR_OBJ values (#numErrObj#, #numObj#, #codGi#, #numLang#, 

        #codInf#, #typErrObj#, #libErrObj#) 

        <selectKey keyProperty="numErrObj" resultClass="int">

            select LAST_INSERT_ID()

        </selectKey>

    </insert>


and in the oracle one, something like :

    <insert id="insertErreurObjet" 

        parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >

        <selectKey keyProperty="numErrObj" resultClass="int">

            select FUN_SEQ_ERR_OBJ.nextval from dual

        </selectKey>

        insert into FUN_ERR_OBJ values (#numErrObj:INTEGER#, #numObj:INTEGER#, #codGi:VARCHAR#, #numLang:INTEGER#, 

        #codInf:VARCHAR#, #typErrObj:VARCHAR#, #libErrObj:VARCHAR#) 

    </insert>


i do not like this solution very much because my statements are very 
similar except the way to generate a key...

could you tell me if there is a better way to do that?

regards

Re: Can I get an OracleCallableStatement from getConnection()?

Posted by Larry Meadors <lm...@apache.org>.

You are not being an idiot. :-D

The connection you get back is a proxied connection - that is how
iBATIS does logging of java.sql activity.

To find out how to get the real connection, someon will have to look
into the source. I do not know off-hand.

Larry

On 10/7/05, Richard Yee <ry...@cruzio.com> wrote:
> Hello,
> I'm using iBATIS DAO and the JdbcDaoTemplate in order to call an Oracle
> Stored procedure that returns and OracleType.CURSOR in an out parameter.
> I can't seem to do it b/c I'm getting a CallableStatement from the
> conn.prepareCall() method instead of an OracleCallableStatement. Let me
> know if I'm being an idiot.
>
> Any ideas?
>
> Thanks,
>
> Richard
>
>
>

Can I get an OracleCallableStatement from getConnection()?

Posted by Richard Yee <ry...@cruzio.com>.

Hello,
I'm using iBATIS DAO and the JdbcDaoTemplate in order to call an Oracle 
Stored procedure that returns and OracleType.CURSOR in an out parameter.
I can't seem to do it b/c I'm getting a CallableStatement from the 
conn.prepareCall() method instead of an OracleCallableStatement. Let me 
know if I'm being an idiot.

Any ideas?

Thanks,

Richard

Re: Mutliple database vendor

Posted by Larry Meadors <lm...@apache.org>.

Use this instead: <include refid="${dbVendor}_select" />

Larry


On 10/7/05, marc.heimann@prolifics.de <ma...@prolifics.de> wrote:
> <if $(database_vendor)=="ORACLE">
>         <include refid="oracle_select">
> <else>
>         <include refid="mysql_select">
> <endif>
>

Re: Antwort: Mutliple database vendor

Posted by Olivier Ziller GMail Lists <ol...@gmail.com>.

thanks marc, you made my day!



marc.heimann@prolifics.de a écrit :

>
> We have a similar problem in the very near future and my idea is to 
> use the include-feature to seperate the DB specific code.
> Have a look at the iBATIS Wiki at 
> http://opensource2.atlassian.com/confluence/oss/pages/viewpage.action?pageId=707 
>
>
>
>
> Looks something like this
>
> <sql id="MAselectList_fragment">
>       SELECT
>             *
>     FROM
>             tbl_mitarbeiter
>   </sql>        
>   <select id="leseMitarbeiter" resultMap="mitarbeiterResultMap" 
> parameterClass="int" resultClass="MA">
>         <include refid="MAselectList_fragment"/>
>     WHERE
>             fldPersNr = #value#
>      
>   </select>
>
>
> I might be brave enough to have a look at the parsing source code and 
> try to implement a conditional statement like
>
> <if $(database_vendor)=="ORACLE">
>         <include refid="oracle_select">
> <else>
>         <include refid="mysql_select">
> <endif>
>
> but I'm not sure if the current amount of workload allows me to do it 
> in a timely fashion.
>
> I have not yet evaluated if it is possible to include sql fragments 
> from another xml file, if so I suggest putting the DB specific 
> fragments in their own jars (one for Oracle, one for MySQL) and use 
> whichever you need.
>
>
> Cheers
> Marc
>
> Olivier Ziller GMail Lists <ol...@gmail.com> schrieb am 
> 07.10.2005 08:55:18:
>
> > hello,
> >
> > my application have to work both with MySql and Oracle.
> > Most of my queries work on both database but a few are specific.
> > Especially those with selectKey...
> >
> > What i've done is to make 3 files for statements : one for generic
> > statements, one for oracle and one for mysql...
> >
> > in the mysql file i will have something like :
> >
> >     <insert id="insertErreurObjet"
> >
> >         
> parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >
> >
> >         insert into FUN_ERR_OBJ values (#numErrObj#, #numObj#,
> > #codGi#, #numLang#,
> >
> >         #codInf#, #typErrObj#, #libErrObj#)
> >
> >         <selectKey keyProperty="numErrObj" resultClass="int">
> >
> >             select LAST_INSERT_ID()
> >
> >         </selectKey>
> >
> >     </insert>
> >
> >
> > and in the oracle one, something like :
> >
> >     <insert id="insertErreurObjet"
> >
> >         
> parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >
> >
> >         <selectKey keyProperty="numErrObj" resultClass="int">
> >
> >             select FUN_SEQ_ERR_OBJ.nextval from dual
> >
> >         </selectKey>
> >
> >         insert into FUN_ERR_OBJ values (#numErrObj:INTEGER#,
> > #numObj:INTEGER#, #codGi:VARCHAR#, #numLang:INTEGER#,
> >
> >         #codInf:VARCHAR#, #typErrObj:VARCHAR#, #libErrObj:VARCHAR#)
> >
> >     </insert>
> >
> >
> > i do not like this solution very much because my statements are very
> > similar except the way to generate a key...
> >
> > could you tell me if there is a better way to do that?
> >
> > regards


-- 
Olivier ZILLER
CRI Gestion
Universite NANCY2
25 rue Baron Louis
BP 454 NANCY CEDEX
tel: 03 54 00 11 12 / 06 85 74 89 77 / poste 604
fax : 03 83 30 05 65

Antwort: Mutliple database vendor

Posted by ma...@prolifics.de.

We have a similar problem in the very near future and my idea is to use 
the include-feature to seperate the DB specific code.
Have a look at the iBATIS Wiki at 
http://opensource2.atlassian.com/confluence/oss/pages/viewpage.action?pageId=707 




Looks something like this

<sql id="MAselectList_fragment">
      SELECT 
        *
    FROM 
        tbl_mitarbeiter
  </sql> 
  <select id="leseMitarbeiter" resultMap="mitarbeiterResultMap" 
parameterClass="int" resultClass="MA">
        <include refid="MAselectList_fragment"/>
    WHERE 
        fldPersNr = #value#
 
  </select>


I might be brave enough to have a look at the parsing source code and try 
to implement a conditional statement like 

<if $(database_vendor)=="ORACLE">
        <include refid="oracle_select">
<else>
        <include refid="mysql_select">
<endif>

but I'm not sure if the current amount of workload allows me to do it in a 
timely fashion.

I have not yet evaluated if it is possible to include sql fragments from 
another xml file, if so I suggest putting the DB specific fragments in 
their own jars (one for Oracle, one for MySQL) and use whichever you need.


Cheers
Marc

Olivier Ziller GMail Lists <ol...@gmail.com> schrieb am 
07.10.2005 08:55:18:

> hello,
> 
> my application have to work both with MySql and Oracle.
> Most of my queries work on both database but a few are specific. 
> Especially those with selectKey...
> 
> What i've done is to make 3 files for statements : one for generic 
> statements, one for oracle and one for mysql...
> 
> in the mysql file i will have something like :
> 
>     <insert id="insertErreurObjet" 
> 
> parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >
> 
>         insert into FUN_ERR_OBJ values (#numErrObj#, #numObj#, 
> #codGi#, #numLang#, 
> 
>         #codInf#, #typErrObj#, #libErrObj#) 
> 
>         <selectKey keyProperty="numErrObj" resultClass="int">
> 
>             select LAST_INSERT_ID()
> 
>         </selectKey>
> 
>     </insert>
> 
> 
> and in the oracle one, something like :
> 
>     <insert id="insertErreurObjet" 
> 
> parameterClass="fr.unire.portal.channels.fun.csof.beans.ErreurObjet" >
> 
>         <selectKey keyProperty="numErrObj" resultClass="int">
> 
>             select FUN_SEQ_ERR_OBJ.nextval from dual
> 
>         </selectKey>
> 
>         insert into FUN_ERR_OBJ values (#numErrObj:INTEGER#, 
> #numObj:INTEGER#, #codGi:VARCHAR#, #numLang:INTEGER#, 
> 
>         #codInf:VARCHAR#, #typErrObj:VARCHAR#, #libErrObj:VARCHAR#) 
> 
>     </insert>
> 
> 
> i do not like this solution very much because my statements are very 
> similar except the way to generate a key...
> 
> could you tell me if there is a better way to do that?
> 
> regards