Hi,
When i prepare a statement with 'WITH LINK' in the statement - no
results are returned, while if i execute the same statement with
'hard' values in it and execute directly - it returns the expected
result set.
First setup the database like this - mimic a hierarchy basically:
CREATE TABLE MYTYPES
(
NAME VARCHAR2(128) NOT NULL
,TYPETEXT TEXT
,EXTENDS VARCHAR2(128)
);
CREATE TABLE MYOBJECTS (
ID NUMBER(11) NOT NULL
,PARENTID NUMBER(11)
,TYPE VARCHAR2(128) NOT NULL
,STATUS VARCHAR2(1)
);
INSERT INTO myTypes VALUES ('RootType', 'blahRoot', NULL);
INSERT INTO myTypes VALUES ('RootBase', 'blahBase', 'RootType');
INSERT INTO myTypes VALUES ('RandomType', 'blahRandom', 'RootBase');
INSERT INTO myObjects VALUES (0, null, 'RootType', 'C');
INSERT INTO myObjects VALUES (1, 0, 'RootBase', 'C');
INSERT INTO myObjects VALUES (37, 0, 'RandomType', 'C');
Then in java - run both statements (the same - or maybe not under the
hood), one a prepared statement version and the other a directly
executed sql string:
import java.sql.*;
public class TestWithLink {
public static void main(String[] a)
throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.
getConnection("jdbc:h2:~/TestDB", "whatever", "whatever");
conn.setAutoCommit(true);
String paramStmt = "WITH LINK( name, extends, LEVEL ) AS
( SELECT name, extends, 0 FROM myTypes where name = ? UNION ALL SELECT
myTypes.name, myTypes.extends, LEVEL + 1 FROM LINK INNER JOIN myTypes
ON LINK.name = myTypes.extends ) SELECT O.ID FROM myObjects O JOIN
LINK L ON O.type = L.name WHERE O.status='C' AND O.ParentId=?";
String hardStmt = "WITH LINK( name, extends, LEVEL ) AS
( SELECT name, extends, 0 FROM myTypes where name = 'RandomType' UNION
ALL SELECT myTypes.name, myTypes.extends, LEVEL + 1 FROM LINK INNER
JOIN myTypes ON LINK.name = myTypes.extends ) SELECT O.ID FROM
myObjects O JOIN LINK L ON O.type = L.name WHERE O.status='C' AND
O.ParentId=0";
// Way 1 - doesn't work
PreparedStatement prepStmt = conn.prepareStatement(paramStmt);
prepStmt.setString(1, "RandomType");
prepStmt.setInt(2, 0);
ResultSet rs1 = prepStmt.executeQuery();
if(rs1.next())
System.out.println("rs1 found something");
else
System.out.println("rs1 found nothing");
// Way 2 - works but...
ResultSet rs2 = conn.createStatement().executeQuery(hardStmt);
if(rs2.next())
System.out.println("rs2 found something");
else
System.out.println("rs2 found nothing");
conn.close();
}
}
Is this a bug with 'WITH LINK' within a prepared statement?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
