Result set returning functions wrapped with parentheses

[jomarlla]

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

[Thomas Mueller]

Hi,

> 2) The sentence "select getVector(3);"   returns:
> ((0), (1), (2))

Yes, it returns one row with one value. The value contains the result set.

> 3) The sentence "select X from getVector(3);" works in the way that I want.

In this case the function called as it would be a table.

> "select getVector(value) from example;"

> "select * from (select getVector(value) from example) as foo;"

This both interprets the getVector result as a value (that contains
the result set).

> The result I was expecting to get (like in PostgreSQL) would be a
> table made up with 6 rows:
> 0
> 1
> 2
> 0
> 1

I'm afraid this is not supported in H2, at least not in this way.
Could you post a PostgreSQL example if you have one?

But I'm sure the problem can be solved in H2. Example:
http://h2database.com/p.html#ba5c002e6f13a9e55ae33ed3c0725550

drop all objects;

create table example (value integer);
insert into example (value) values (3);
insert into example (value) values (2);

create alias getVector as $$
import java.sql.*;
import org.h2.tools.SimpleResultSet;
@CODE

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;
}

$$;
create alias getCombinedResult as $$
import java.sql.*;
import org.h2.tools.SimpleResultSet;
@CODE
ResultSet getCombinedResult(Connection conn,
String outer,
String each) throws SQLException {
SimpleResultSet result = null;
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(outer);
PreparedStatement prep = conn.prepareStatement(each);
String url = conn.getMetaData().getURL();
while (rs.next()) {
int oc = rs.getMetaData().getColumnCount();
for (int i = 0; i < oc; i++) {
prep.setString(i + 1, rs.getString(i + 1));
}
ResultSet rs2 = prep.executeQuery();
while(rs2.next()) {
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
if (result == null) {
result = new SimpleResultSet();
for (int i = 0; i < columnCount; i++) {
result.addColumn(
meta.getColumnLabel(i + 1),
meta.getColumnType(i + 1),
meta.getPrecision(i + 1),
meta.getScale(i + 1)
);

}
if (url.equals("jdbc:columnlist:connection")) {

return result;
}
}
Object[] row = new Object[columnCount];
for (int i = 0; i < columnCount; i++) {
row[i] = rs2.getObject(i + 1);
}
result.addRow(row);
}
}
return result;
}
$$;
call getCombinedResult(
'select value from example',
'call getVector(?1)');

Regards,
Thomas

[jomarlla]

Hi Thomas,
Thanks for your fast and detailed answer.

The example in postgres (using plpgsql) would be:

CREATE OR REPLACE FUNCTION getVector(integer) RETURNS SETOF integer AS
$$
DECLARE
i integer;
BEGIN
FOR i IN 1..$1 LOOP
RETURN NEXT i-1;
END LOOP;
RETURN;
END
$$
LANGUAGE 'plpgsql';

------------ example

select * from getVector(3);
getvector
-----------
0
1
2

create table example (value integer);
insert into example (value) values (3);
insert into example (value) values (2);

select getVector(value) from example;
getvector
-----------

0
1
2
0
1

Even though with your answer it would be possible to get this behave
in H2:

> call getCombinedResult(
> 'select value from example',
> 'call getVector(?1)');

it is kind of difficult for a normal user to use it compare with the
previous postgres example, and I think it would be kind of tricky
to use it in a more complex SQL sentences.

are you planning to develop this behave in a future H2 release? or the
only alternative is to use it the way you told me with
'getCombinedResult'?

Thanx,
Jose Martinez-Llario


On Jun 6, 10:49 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

>> call getCombinedResult(
>>   'select value from example',
>>   'call getVector(?1)');
>
> it is kind of difficult for a normal user to use it compare with the
> previous postgres example, and I think it would be kind of tricky
> to use it in a more complex SQL sentences.

Yes. However, I don't really understand the PostgreSQL solution
either. It's like a join, but it doesn't look like a join. It affects
(breaks?) "group by" as well:

select value, getVector(value), count(*) from example group by value;
value getvector count
3 0 1
3 1 1
3 2 1
2 0 1
2 1 1

> are you planning to develop this behave in a future H2 release?

No, I'm sorry. I currently don't see it as an important use case.

> or the only alternative is to use it the way you told me with
> 'getCombinedResult'?

Yes.

Regards,
Thomas

[Jose Carlos Martinez Llario]

Thanks Thomas for the answers. I will use your approach.
Regards,
Jose

--
------------------------------
Jos� Carlos Mart�nez Llario

Producci�n Cartogr�fica y SIG.
Dpto. Ingenier�a Cartogr�fica.
Univ. Polit�cnica de Valencia.

E-mail: joma...@cgf.upv.es
Telf: 963877007 ext. 75599
------------------------------