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:anoopkum...@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 <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 --------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org For additional commands, e-mail: user-java-h...@ibatis.apache.org
