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 13:12:49 GMT
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


Mime
View raw message