Never mind. I got it to work..
I had to really trim down the entire statement:
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;
But I am not able to use it as a sqlmapped statement in iBatis, but that is
a separate problem for a different user list.. but you gave me the idea so
far and it works. Thanks very much.
Thanks,
Anoop
On Fri, Aug 21, 2009 at 8:26 PM, Anoop kumar V <[email protected]>wrote:
> I am having trouble executing what you have sent. Below is output....
>
> mysql> set @sql = concat( "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
> "> from user_approvers )
> "> order by rn limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1248 (42000): Every derived table must have its own alias
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql> set @sql = concat( "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
> "> from user_approvers ) a
> "> order by rn limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1054 (42S22): Unknown column 'rn' in 'order clause'
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql> set @sql = concat( "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
> "> from user_approvers ) a
> "> limit ", 10, ",", (20-10+1) );
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> prepare stmt from @sql;
> ERROR 1064 (42000): 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 'limit 10,11' at line 13
> mysql> execute stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> EXECUTE
> mysql> drop prepare stmt;
> ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to
> DEALLOCATE PREPARE
> mysql>
> mysql>
>
> Thanks,
> Anoop
>
>
>
> On Fri, Aug 21, 2009 at 7:22 PM, Peter Brawley <
> [email protected]> wrote:
>
>> I think you'd need to use Prepare, eg replace the query with ...
>>
>> set @sql = concat( "select
>> user_id as iamsId
>> ,division_name as divisionName
>> ,region_name as regionName
>> ,isactive as isActive
>> from (
>> select
>> user_id
>> ,division_name
>> ,region_name
>> ,isactive
>> from user_approvers )
>> order by rn limit ", #from, ",", (#to-#from+1) );
>> prepare stmt from @sql;
>> execute stmt;
>> drop prepare stmt;
>>
>>
>> PB
>>
>> -----
>>
>> Anoop kumar V wrote:
>>
>> Thanks very much Peter.
>>
>> But I think I did figure that much. What I am lacking is the integration
>> of that logic into the sql.
>>
>> The current sql (made for oracle) is like this - I can change it all I
>> want because of the sql map which is configurable...
>>
>> select
>> user_id as iamsId
>> ,division_name as divisionName
>> ,region_name as regionName
>> ,isactive as isActive
>> from (
>> select
>> user_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
>>
>> I can change everything but the parameters to the sql: #from# and #to#.
>> These come from the application logic and is user enterred (not directly,
>> but through pagination etc - you get the idea)
>>
>> I tried things like the following (to get rows from 11 to 20):
>> select * from user_approvers limit 10, 20-10;
>>
>> Also tried assigning variables.. still no go.
>>
>> Thanks,
>> Anoop
>>
>>
>> On Fri, Aug 21, 2009 at 6:44 PM, Peter Brawley <
>> [email protected]> wrote:
>>
>>> >Is there anyway the SELECT query can be forced to use the "from" and "to"
>>> >rownum parameters?
>>>
>>>
>>> 1st LIMIT arg = OracleFromArg
>>> 2nd LIMIT arg = OracleToArg - OracleFromArg + 1
>>>
>>> so 'from 11 to 20' becomes LIMIT 11,10.
>>>
>>> PB
>>>
>>> -----
>>>
>>> Anoop kumar V wrote:
>>>
>>> Hi All,
>>>
>>> I am facing a problem in porting an application written for oracle to run on
>>> mysql.
>>>
>>> The application uses a sqlmap (ibatis) at the heart of which is basically a
>>> file that defines all sql's used in the application. It is very well
>>> organized this way. The application uses Oracle as the database. The problem
>>> is that for pagination purposes the sql's written use rownum and accept 2
>>> arguments - the "from" rownum and the "to" rownum.
>>>
>>> I am trying to run the same application on my laptop that runs mysql. I have
>>> migrated all data and all the sql queries work perfectly except the one that
>>> use pagination and the rownum.
>>>
>>> I know in mysql there is support for sql using the LIMIT clause, but the
>>> LIMIT seems to take 2 arguments, the first one being the start rownum and
>>> the second being the number of rows to output. I need the second to be the
>>> "to" rownum. I have done a lot of googling, but apart from just putting a
>>> rownum for the sql output there was no real usages for pagination purposes.
>>>
>>> I cannot use the LIMIT as it is in mysql, because that would mean I would
>>> have to change the application logic which I do not want to do. I also do
>>> not want to install Oracle on my laptop, just too heavy.
>>>
>>> I have found this to work except I am not sure how to pass a where clause
>>> for the rownum part:
>>>
>>> SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
>>> user_approvers t
>>> I was trying something like:
>>>
>>> SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
>>> user_approvers t where r.rownum between 10, 20;
>>> or even
>>> SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
>>> user_approvers t where r.rownum=1;
>>>
>>> I get the error:
>>> ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause'
>>>
>>> Is there anyway the SELECT query can be forced to use the "from" and "to"
>>> rownum parameters?
>>>
>>> Thanks a lot for any help,
>>> Anoop
>>>
>>>
>>>
>>> ------------------------------
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - www.avg.com
>>> Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09
>>> 06:04:00
>>>
>>>
>>>
>>>
>> ------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09
>> 06:04:00
>>
>>
>>
>>
>
