Problem passing objects into user defined function

[Mike Goodwin]

Hi,

I wanted to define a function that takes a variable number of object arguments

CREATE ALIAS STRUCT FOR "x.Library.struct"

static public Object struct(Object... args) throws SQLException{
   ...
}

The function expects an even number arguments key,val,key,val ..., where every even argument is a string constant. Unfortunately it seems that when an argument is not explicitly a java.lang.String in the method signature h2 attempts to interpret it as a hexadecimal value.

 org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number of characters: c; SQL statement:
                                    SELECT STRUCT('c',t.c,'C',UCASE(t.c))) FROM t [90003-115]
                                        at org.h2.message.Message.getSQLException(Message.java:105)
                                        at org.h2.message.Message.getSQLException(Message.java:116)
                                        at org.h2.message.Message.getSQLException(Message.java:75)
                                        at org.h2.util.ByteUtils.convertStringToBytes(ByteUtils.java:80)
                                        at org.h2.value.Value.convertTo(Value.java:756)
                                        at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:270)
                                        at org.h2.expression.JavaFunction.getValue(JavaFunction.java:38)
                                        at org.h2.expression.JavaAggregate.updateAggregate(JavaAggregate.java:175)
                                        at org.h2.expression.Alias.updateAggregate(Alias.java:78)
                                        at org.h2.command.dml.Select.queryGroup(Select.java:337)
                                        at org.h2.command.dml.Select.queryWithoutCache(Select.java:556)
                                        at org.h2.command.dml.Query.query(Query.java:234)
                                        at org.h2.command.CommandContainer.query(CommandContainer.java:82)
                                        at org.h2.command.Command.executeQueryLocal(Command.java:142)
                                        at org.h2.command.Command.executeQuery(Command.java:123)
                                        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:98)


As far as i can see there is no way to make this work without patching h2, which is undesirable. The crux of the problem I believe is when h2 infers the sql signature from the java signature, there is not enough information so certain sql signatures are not possible. That is simply changing things to fix my problem would just break things for someone else. It seems to me that for functions to be defineable in a completely general way then it needs to be possible to define them, as well as by the current method of using a static function,  by implementing an interface - much like the way that aggregate functions are defined. I will implement this if it is wanted...

What is the best way to procede?

thanks!

mike

[Mike Goodwin]

Hi,

In the end I worked around this problem using the sql array construction (a,b,c...) and omitting the string constants (not sure the 2nd step was necessary). So its not a current issue for me. Still I think the user function declaration api is not completely general as it is at the moment.

thanks,

mike

[Thomas Mueller]

Hi,

> I wanted to define a function that takes a variable number of object
> arguments

The database thinks you try pass a serialized object, so it tries to
convert the value to a byte array, and then deserialize it into a
java.lang.Object (data type 'OTHER' - see also
http://www.h2database.com/html/datatypes.html#othertype ).

What you want in your case is to _not_ convert the value. This is
currently not supported, and I wonder if it can be supported when
using static methods (and no hacks). Also, the return type is Object.
I guess that means the returned data type is unknown until the method
is called? One workaround is to pass an Object array and return an
Object array. Example:

private void testDynamicArgumentAndReturn() throws SQLException {
Connection conn = getConnection("functions");
Statement stat = conn.createStatement();
ResultSet rs;
stat.execute("create alias dynamic deterministic for
\""+getClass().getName()+".dynamic\"");
setCount(0);
rs = stat.executeQuery("call dynamic(('a', 1))[0]");
rs.next();
String a = rs.getString(1);
assertEquals("a1", a);
stat.execute("drop alias dynamic");
conn.close();
}

public static Object[] dynamic(Object[] args) {
StringBuilder buff = new StringBuilder();
for (Object a : args) {
buff.append(a);
}
return new Object[] { buff.toString() };
}

Could you explain what the method does?

> there is not enough information
> so certain sql signatures are not possible.

That's true.

> It seems to me that for functions to be defineable in a completely general way then it
> needs to be possible to define them, as well as by the current method of
> using a static function,  by implementing an interface - much like the way
> that aggregate functions are defined.

Yes, that may be a solution. How do you suggest should the Function
interface look like?

Regards,
Thomas

[bob mcgee]

Hey,
I've run into some confusions as well about type handling in user-
defined functions, and from this post and others, I'm probably not the
only one.
Would it be possible to add more to the documentation on the subject
about user-defined functions? Just a little matrix of accepted
inputs and implicit conversions, plus a few more words on user
ResultSets would make it much easier for people to work out user-
function glitches & optimize their procedures.

The table could just be 3 columns: H2 type, Java types/primitives
accepted (no conversion needed, fastest), Java types/primitives
implicitly converted and how it is converted. Examples for the last:
VARCHAR accepts byte[], by converting to hex chars, TimeStamp will
try to parse a String, maybe accepts a Calendar or Date, but not a
long System.currentTimeMillis() value.

Questions this would help with:
Can/should I pass java.sql.Blob or byte[] or input streams to
something expecting a BLOB or BINARY type?
What about functions needing to take BLOB as input?
Which objects can be used with functions expecting TIME/DATE/TIMESTAMP
objects (besides the java.sql.TimeStamp, etc)?
Which of the java.lang.* and java.sql.* Objects will be implicitly
converted to something besides OTHER if possible?
How can I build & return a more full-featured or disk-paged ResultSet
from a procedure, for accumulating large results from a stored
procedure operating on tables, which may not fit in RAM?

Thank you,
Bob McGee

On Jul 8, 12:24 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Mike Goodwin]

Hi Thomas,

>Could you explain what the method does?

The particular function was to create what i've been calling a 'struct', which means a map with a fixed set of (string) keys. Actually as it happens i don't need the string information in the sql, so i can get away with using an array, so similar to your implementation ... though its not obvious (to me) that types in an array are not treated the same.

>The database thinks you try pass a serialized object, so it tries to
>convert the value to a byte array, and then deserialize it into a
>java.lang.Object (data type 'OTHER' - see also
>http://www.h2database.com/html/datatypes.html#othertype ).

Does anyone ever actually want to receive an serialised object as an argument? And if so perhaps they should put byte[] in the signature?

How does java.sql.Type.JAVA_OBJECT fit into this, how is it different to OTHER? (http://java.sun.com/j2se/1.3/docs/api/java/sql/Types.html)

Another approach might be to allow methods to take the h2 value type as arguments and as the return. Then the method could do its own conversions... i don't think this is a great idea for a public api, but i'm just throwing the idea in there. (I guess it would be more efficient in some cases, avoiding unnecessary conversions).

For a java interface, I'm not sure. Something could be done passing in and out java.sql.Type, but its a bit clumsy. To be honest i don't have a clear idea on the sql standard and implementation issues.

thanks,

mike

On Wed, Jul 8, 2009 at 5:24 AM, Thomas Mueller <thomas.to...@gmail.com> wrote:

Hi,

> I wanted to define a function that takes a variable number of object
> arguments

What you want in your case is to _not_ convert the value. This is
currently not supported, and I wonder if it can be supported when
using static methods (and no hacks). Also, the return type is Object.
I guess that means the returned data type is unknown until the method
is called? One workaround is to pass an Object array and return an
Object array. Example:

private void testDynamicArgumentAndReturn() throws SQLException {
   Connection conn = getConnection("functions");
   Statement stat = conn.createStatement();
   ResultSet rs;
   stat.execute("create alias dynamic deterministic for
\""+getClass().getName()+".dynamic\"");
   setCount(0);
   rs = stat.executeQuery("call dynamic(('a', 1))[0]");
   rs.next();
   String a = rs.getString(1);
   assertEquals("a1", a);
   stat.execute("drop alias dynamic");
   conn.close();
}

public static Object[] dynamic(Object[] args) {
   StringBuilder buff = new StringBuilder();
   for (Object a : args) {
       buff.append(a);
   }
   return new Object[] { buff.toString() };
}

> there is not enough information
> so certain sql signatures are not possible.

[Thomas Mueller]

Hi,

> I've run into some confusions as well about type handling in user-
> defined functions, and from this post and others, I'm probably not the
> only one.
> Would it be possible to add more to the documentation on the subject
> about user-defined functions?

Yes, what about if I add the following here:
http://www.h2database.com/html/features.html#user_defined_functions

"SQL types are mapped to Java classes and vice-versa as in the JDBC
API. For details, see <a href="datatypes.html">Data Types</a>. There
are two special cases: java.lang.Object is mapped to OTHER (a
serialized object). Therefore, Object can not be used to match all SQL
types. The second special case is Object[]: Arrays of any class are
mapped to ARRAY."

I will expand the data type documentation to include mappings to Java
types, such as:

DECIMAL: Mapped to java.math.BigDecimal.
BINARY: Mapped to byte[].
OTHER: Mapped to java.lang.Object (or any subclass).
BLOB: Mapped to java.sql.Blob (java.io.InputStream is also supported).
ARRAY: Mapped to java.lang.Object[] (arrays of any non-primitive type
are also supported).

> a few more words on user
> ResultSets

This is already documented at
http://www.h2database.com/html/features.html#user_defined_functions -
"Functions returning a Result Set".

> Can/should I pass java.sql.Blob or byte[] or input streams to
> something expecting a BLOB or BINARY type?

java.sql.Blob for BLOB, byte[] for BINARY.

> What about functions needing to take  BLOB as input?

I don't understand the question.

> Which objects can be used with functions expecting TIME/DATE/TIMESTAMP
> objects (besides the java.sql.TimeStamp, etc)?

See above.

> Which of the java.lang.* and java.sql.* Objects will be implicitly
> converted to something besides OTHER if possible?

See above.

> How can I build & return a more full-featured or disk-paged ResultSet
> from a procedure, for accumulating large results from a stored
> procedure operating on tables, which may not fit in RAM?

SimpleResultSet supports streaming, unfortunately the constructor
SimpleResultSet(SimpleRowSource source) is not documented curently at
http://www.h2database.com/javadoc/org/h2/tools/SimpleResultSet.html -
I will fix that.

Regards,
Thomas

[bob mcgee]

Thomas,

I think adding all of that into the documentation at the appropriate
places would be a
big start in helping users looking to users looking to get started
with Triggers/Stored procs.

I'm not sure if that listing is wholly complete though. I know it
excludes the string parsing
conversions (I've seen a couple) and implicit type conversions
applied within SQL-only
statements. For examble, implicit CLOB <--> VARCHAR conversion (I
believe) has allowed me
to use PreparedStatement.setString for CLOBs. Most of those are
common sense,
but some are not (thank goodness the UUID quirks were straightened
out).

I'm kind of surprised that BLOBs can't accept byte[], or maybe that
results in a BINARY, which is
implicitly converted to a BLOB type if needed?

I'm emailing you about maybe adding to the documentation in this area
myself if you are interested.

On Jul 12, 4:07 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> > How can I build & return a more full-featured or disk-paged ResultSet
> > from a procedure, for accumulating large results from a stored
> > procedure operating on tables, which may not fit in RAM?
>
> SimpleResultSet supports streaming, unfortunately the constructor
> SimpleResultSet(SimpleRowSource source) is not documented curently athttp://www.h2database.com/javadoc/org/h2/tools/SimpleResultSet.html-
> I will fix that.

Wow, okay, that would be really good to know and have in
documentation. Sooner or later I am going to figure out how much of
the ResultSet API it supports.
Here's hoping it gets server-side cursors when they are implemented!

Cheers,
Bob McGee

[Thomas Mueller]

Hi,

> I know it
> excludes the string parsing
> conversions (I've seen a couple)  and implicit type conversions
> applied within SQL-only
> statements.

The best place to document this is probably the CAST(..) function. I
will extend the documentation of CAST:

"Converts a value to another data type. When converting a text to a
number, the default Java conversion
rules are used (prefixes 0x or # for hexadecimal numbers, prefix 0 for
octal numbers)."

That's a start, but much more is required.

> I'm kind of surprised that BLOBs can't accept byte[]

It can (data is converted automatically).

Regards,
Thomas