Hi everyone,
I am developing some H2 stored procedures with result set returning
function and I am getting a weird result.
H2 stored procedure example:
1) This is the function I am using as a example:
public static ResultSet getVector(Connection conn, Integer size)
throws SQLException {
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("X", Types.INTEGER, 10, 0);
String url = conn.getMetaData().getURL();
if (url.equals("jdbc:columnlist:connection")) {
return rs;
}
for (int s = size.intValue(), x = 0; x < s; x++) {
rs.addRow(x);
}
return rs;
}
2) The sentence "select getVector(3);" returns:
((0), (1), (2))
(1 row, 1 ms)
As you can say the result is wrapped with parenthesis
3) The sentence "select X from getVector(3);" works in the way that I
want. That is, the result is NOT
wrapped with parentheses and it is made up with 3 rows.
X
0
1
2
(3 rows, 1 ms)
4) The problem is when Iam using a column from a table as the argument
function:
create table example (value integer);
insert into example (value) values (3);
insert into example (value) values (2);
THEN I ALWAYS get the result wrapped with parentheses:
"select getVector(value) from example;" returns:
((0), (1), (2))
((0), (1))
(2 rows, 1 ms)
I have tried with subqueries in different ways but without success:
"select * from (select getVector(value) from example) as foo;"
returns:
((0), (1), (2))
((0), (1))
(2 rows, 1 ms)
The result I was expecting to get (like in PostgreSQL) would be a
table made up with 6 rows:
0
1
2
0
1
Someone knows how can I use a result set returning function properly?
This is really important issue to finish a full open source project
developed with H2 and which I want to
release in the next months.
Thanx a lot for the answers,
Jose Martinez-Llario
--
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.
