That is something new I never knew. I will try that and let you know.. Thanks, Anoop
On Mon, Aug 24, 2009 at 10:31 AM, Poitras Christian < christian.poit...@ircm.qc.ca> wrote: > 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> 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 >> >> >
