Re: Paging Result Sets

[Six Fried Rice <te...@sixfriedrice.com>]

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

Re: Paging Result Sets

[Rick Hillegas <Ri...@Sun.COM>]

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

Re: Paging Result Sets

[Six Fried Rice <te...@sixfriedrice.com>]

On May 2, 2008, at 8:01 AM, Knut Anders Hatlen wrote:

> That's the error you'll get if you execute the query in a Derby  
> release that doesn't support the ROW_NUMBER function (e.g., Derby  
> 10.3). So I would have double checked that the JDBC application in  
> fact uses Derby 10.4. derby.log should tell you which version was  
> used.

Sorry it took me so long to reply...

Thanks to all for the responses. You are correct, just a little Jar  
file madness. Once we *really* put the 10.4 jar in place, things  
worked swimmingly.

Thanks again!

Geoff

Re: Paging Result Sets

[Knut Anders Hatlen <Kn...@Sun.COM>]

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?
Hi Geoff,

That's the error you'll get if you execute the query in a Derby release 
that doesn't support the ROW_NUMBER function (e.g., Derby 10.3). So I 
would have double checked that the JDBC application in fact uses Derby 
10.4. derby.log should tell you which version was used.

-- 
Knut Anders