Anyone? Please help. On 8/21/09, Anoop kumar V <anoopkum...@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-unsubscr...@ibatis.apache.org For additional commands, e-mail: user-java-h...@ibatis.apache.org
