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.poit...@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: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
>
>
