Recursive query doesn't return values by PreparedStatement with parameter

[GoogleCodeExporter]

Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

Please see attached file

What is the expected output? What do you see instead?
Expected output:
1
2

Actual output:
<nothing>

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2: 1.3.154
OS: Win7, NTFS
JDK: 1.7.0-ea-b131

Do you know a workaround?
Don't use PreparedStatement parameter, just replace ? with 2 : 
"WHERE id = ?" -> "WHERE id = 2"

What is your use case, meaning why do you need this feature?
I need to retrieve list of all parent folders for file entry.

How important/urgent is the problem for you?
Currently this is a big performance impact because I have to do separate call 
for each parent and get results in the loop. I hope with recursive query it 
should work much quicker.

Please provide any additional information below.

Original issue reported on code.google.com by Sergey.Z...@gmail.com on 18 Apr 2011 at 4:49

Attachments:

• Main.java

[GoogleCodeExporter]

Hi,

This is a known limitation, it is documented at 
http://h2database.com/html/advanced.html#recursive_queries : "Parameters are 
only supported within the last SELECT statement (a workaround is to use session 
variables like @start within the table expression)"

I know it's a weird limitation, but it's not so easy to implement parameters 
correctly... I will increase the priority of this feature (feature request 
"Common Table Expression (CTE) / recursive queries: support parameters").

I will change this issue to 'in roadmap' because priorities are tracked there 
(I don't know a way to track them here).

Original comment by thomas.t...@gmail.com on 6 May 2011 at 10:28

• Changed state: InRoadmap