ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anoop kumar V <anoopkum...@gmail.com>
Subject Re: Execute multiple statements (pagination query port from oracle to mysql)
Date Mon, 24 Aug 2009 15:10:17 GMT
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.Poitras@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.Poitras@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:anoopkumarv@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.Poitras@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:anoopkumarv@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 <anoopkumarv@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-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>

Mime
View raw message