You should allow multiple queries if you haven't done so. allowMultiQueries in http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
Christian ________________________________ From: Poitras Christian [mailto:christian.poit...@ircm.qc.ca] Sent: Monday, August 24, 2009 10:16 AM To: 'user-java@ibatis.apache.org' Subject: RE: Execute multiple statements (pagination query port from oracle to mysql) Oups... read a little to fast... I am not sure if this type of syntax is supported... You should look more into MySQL documentation. ________________________________ From: Anoop kumar V [mailto:anoopkum...@gmail.com] Sent: Monday, August 24, 2009 10:07 AM To: user-java@ibatis.apache.org Subject: Re: Execute multiple statements (pagination query port from oracle to mysql) 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<mailto: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<mailto:anoopkum...@gmail.com>] Sent: Monday, August 24, 2009 9:13 AM To: user-java@ibatis.apache.org<mailto: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<mailto: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<mailto:user-java-unsubscr...@ibatis.apache.org> For additional commands, e-mail: user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org> --------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org<mailto:user-java-unsubscr...@ibatis.apache.org> For additional commands, e-mail: user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org>
