Hello, it's me again with the ARRAY types! :-)
I have encountered some issues when ARRAY types are used in stored
procedures with H2 1.3.153. I'll try to describe them as
systematically as possible:
Issue 1: I can declare stored procedures that I cannot really call
######################################
This is what I can declare:
--------------------------------------------
CREATE ALIAS f_arrays1 AS $$
Integer[] f_arrays1(Integer[] in_array) {
return in_array;
}
$$;
--------------------------------------------
But I cannot seem to call that procedure with JDBC.
--------------------------------------------
PreparedStatement stmt = connection.prepareStatement("select
f_arrays1(?) from dual");
stmt.setObject(1, new Integer[] { 1, 2 });
ResultSet rs = stmt.executeQuery();
--------------------------------------------
The above statement's execution will indicate this:
--------------------------------------------
org.h2.jdbc.JdbcSQLException: Feature not supported:
"[Ljava.lang.Integer;";
--------------------------------------------
Issue 2: The return type is irrelevant to H2
######################################
Interestingly, only the parameter causes trouble, not the return
value. This I can declare as well:
--------------------------------------------
CREATE ALIAS f_arrays1 AS $$
Integer[] f_arrays1(Object[] in_array) {
return new Integer[] { (Integer)in_array[0],
(Integer)in_array[1] };
}
$$;
--------------------------------------------
And I can call the above function using a PreparedStatement as such:
--------------------------------------------
PreparedStatement stmt = connection.prepareStatement("select
f_arrays1(?) from dual");
stmt.setObject(1, new Integer[] { 1, 2 });
ResultSet rs = stmt.executeQuery();
System.out.println(rs.getObject(1).getClass());
System.out.println(Arrays.asList((Object[])rs.getObject(1)));
--------------------------------------------
But the fact that I return Integer[] is ignored by H2. The output of
the above is:
--------------------------------------------
class [Ljava.lang.Object;
[1, 2]
--------------------------------------------
We've had this discussion before. I guess this is a general thing with
H2...
http://groups.google.com/group/h2-database/browse_thread/thread/42e38afa682d4fc2/98b4c6349bee31e9
Issue 3: CallableStatements cannot return ARRAY types
######################################
This will not work correctly with H2:
CallableStatement call = connection.prepareCall("{ ? = call
f_arrays1(?) }");
call.setObject(2, new Integer[] { 2, 1 });
call.registerOutParameter(1, Types.ARRAY);
call.execute();
System.out.println(call.getObject(1).getClass());
System.out.println(call.getObject(1));
--------------------------------------------
class java.lang.Integer
2
--------------------------------------------
As you can see, the call doesn't return the ARRAY correctly, but
instead, it returns the first element of the array.
Summary
######################################
1. I think there should be a way to call functions with Integer[]
parameters (or similar), since they can be declared without any
trouble.
2. You already put that on the roadmap for ARRAY types in tables...
3. That looks like a bug to me. Or am I misusing the
CallableStatement?
I hope this report helps!
Cheers
Lukas
--
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.
