ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anoop kumar V <anoopkum...@gmail.com>
Subject Re: Execute multiple statements (pagination query port from oracle to mysql)
Date Mon, 24 Aug 2009 14:07:25 GMT
Those are not quotes in the sql, those are the quotes required for the
string identifier.

My question is how can one use such multiple statements within the ibatis
sqlmap:

set @sql = concat( "select
             iams_id as iamsId
            ,division_name as divisionName
            ,region_name as regionName
            ,isactive as isActive
           from user_approvers
       limit ", #from#, ",", (#from#-#to#+1) );
    prepare stmt from @sql;
    execute stmt;
    drop prepare stmt;

Is this supported, as it is a combination of DDL and DML statements...?

Thanks,
Anoop


On Mon, Aug 24, 2009 at 9:39 AM, Poitras Christian <
Christian.Poitras@ircm.qc.ca> wrote:

> MySQL doesn't need quotes around limit.
> It should be limit #from# (#from#-#to#+1)
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> Christian
>
> -----Original Message-----
> From: Anoop kumar V [mailto:anoopkumarv@gmail.com]
> Sent: Monday, August 24, 2009 9:13 AM
> To: user-java@ibatis.apache.org
> Subject: Re: Execute multiple statements (pagination query port from oracle
> to mysql)
>
> Anyone? Please help.
>
> On 8/21/09, Anoop kumar V <anoopkumarv@gmail.com> wrote:
> > This is an attempt to convert the pagination query using rownum in
> > Oracle to an equivalent pagination query using limit in mysql.
> >
> > I have an existing application that uses an Oracle database in
> production.
> > I
> > am trying to run the same application using mysql on my laptop. The
> > entire application and sql statements work perfectly fine, except for
> > those parts and sql's that use pagination. The pagination is achieved
> > using oracle's rownum feature and takes as input #from# and #to#.
> >
> > I understand that there is no equivalent in mysql except for the limit
> > clause. So after some effort I have been able to get the same results
> > in mysql using a set of sql statements that accept the #from# and #to#
> > input parameters.
> >
> > So just to illustrate, one of my oracle pagination queries was
> > something like (as an entry in my sqlmap):
> > **********************
> >   <select id="getUserApprovers" resultClass="ad.UserApprover"
> > parameterClass="java.util.Map">
> >     select         iams_id as iamsId        ,division_name as
> > divisionName        ,region_name as regionName        ,isactive as
> isActive
> >     from (
> >           select              iams_id
> > ,division_name              ,region_name              ,isactive
> > ,row_number() over (order by division_name, region_name) rn          from
> > user_approvers )
> >     where rn between #from# and #to#
> >     order by rn
> > </select>
> > **********************
> > Converted, the same sql in mysql was something like the following:
> > **********************
> >   <select id="getUserApprovers" resultClass="ad.UserApprover"
> > parameterClass="java.util.Map">
> >     set @sql = concat( "select
> >              iams_id as iamsId
> >             ,division_name as divisionName
> >             ,region_name as regionName
> >             ,isactive as isActive
> >            from user_approvers
> >        limit ", #from#, ",", (#from#-#to#+1) );
> >     prepare stmt from @sql;
> >     execute stmt;
> >     drop prepare stmt;
> >   </select>
> > **********************
> >
> > The mysql sql seems to work just like I want when I try it at the
> > mysql prompt.
> > When I tried it in the sqlmap I got errors like this:
> > **********************
> > Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You
> > have an error in your SQL syntax; check the manual that corresponds to
> your MySQL
> > server version for the right syntax to use near ';     prepare stmt from
> > @sql;     execute stmt;     drop prepare stmt' at line 1
> > **********************
> > what is the correct way of using it in the sqlmap? Should I use
> > executeBatch? I do not want to have to change my application logic to
> > make it work with both oracle and mysql - is there any other way? And
> > of course I do not want to install oracle on my laptop either.
> >
> > Any help is much appreciated.
> >
> > Thanks,
> > Anoop
> >
>
>
> --
>
> Thanks,
> Anoop
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>

Mime
View raw message