db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Paging Result Sets
Date Fri, 02 May 2008 14:27:52 GMT
Six Fried Rice wrote:
> On Apr 28, 2008, at 8:02 AM, Rick Hillegas wrote:
>
>> The new 10.4 release provides support for the sql standard ROW_NUMBER 
>> function, which you can use for paging through query results. You can 
>> find this function documented in the ROW_NUMBER subsection of the 
>> "Built-in functions" section of the 10.4 Reference Manual.
>
> Thanks for the info. I have another developer working on this part, 
> and he reports that, ie, this query:
>
>> select * from (select row_number() over() as rownum, 
>> table_occurrence.* from table_occurrence) as temp where rownum>=1 and 
>> rownum<=10
>
> Works as expected when executed through the IJ console. But when 
> executed through the JDBC application, he gets this error:
>
>> 1147 FATAL [http-8080-Processor25] 
>> com.sixfriedrice.lumen.ddrserver.utility.BuildQuery     - Error in 
>> query :java.sql.SQ
>> LSyntaxErrorException: Syntax error: Encountered "(" at line 1, 
>> column 40.
>
> Does that make sense to anyone?
>
> Thanks,
>
> Geoff
Hi Geoff,

I'm afraid this does not make sense to me. I have tried this query in 3 
situations

1) under ij
2) from a java program connecting to an embedded Derby database
3) from a java program connecting over the network to a Derby database 
hosted in a Derby server

The query works for me in all situations. I suspect that your query 
generator is building bad query text. If you look in derby.log you 
should be able to find the query text which Derby is rejecting. That may 
help you track down the problem.

Hope this helps,
-Rick

Mime
View raw message