INOUT stored procedure parameters

[steve.ebersole]

Following along with http://www.h2database.com/html/features.html#user_defined_functions I am trying to create a stored procedure that will "return" some results.  The link I mentioned makes no real distinction between functions and procedures, so I am assuming that procedures simply have a void return? 

Ultimately, I am uncertain how to handle OUT/INOUT parameters. Is that possible in H2?

[Noel Grandin]

H2 does not implement in/out parameters, you'd either need to
(a) pass some kind of carrier object into the method that could be updated,
(b) return your data in a carrier object
(c) return your data in a result set

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/XbnFQg-ZES4J.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[steve.ebersole]

Thanks for the reply Noel.  That's too bad.  Oh well I'll figure something out.

Relatedly, I am running into a problem trying to get this working.  I have the following:

return "CREATE ALIAS findUser AS $$\n" +
                                "ResultSet findUser() {\n" +
                                "    org.h2.tools.SimpleResultSet rs = new org.h2.tools.SimpleResultSet();\n" +
                                "    rs.addColumn(\"ID\", Types.INTEGER, 10, 0);\n" +
                                "    rs.addColumn(\"NAME\", Types.VARCHAR, 255, 0);\n" +
                                "    rs.addRow(1, \"Steve\");\n" +
                                "    return rs;\n" +
                                "} $$";

But this keeps giving me the error:

08:57:37,103 ERROR SchemaExport:425 - HHH000389: Unsuccessful: CREATE ALIAS findUser AS $$
ResultSet findUser() {
    org.h2.tools.SimpleResultSet rs = new org.h2.tools.SimpleResultSet();
    rs.addColumn("ID", Types.INTEGER, 10, 0);
    rs.addColumn("NAME", Types.VARCHAR, 255, 0);
    rs.addRow(1, "Steve");
    return rs;
} $$
08:57:37,105 ERROR SchemaExport:426 - Syntax error in SQL statement "/org/h2/dynamic/FINDUSER.java:9: package org.h2.tools does not exist
    org.h2.tools.SimpleResultSet rs = new org.h2.tools.SimpleResultSet();
                ^
/org/h2/dynamic/FINDUSER.java:9: package org.h2.tools does not exist
    org.h2.tools.SimpleResultSet rs = new org.h2.tools.SimpleResultSet();

I verified that the version of H2 I am using (1.2.145) does in fact have this class.  Any idea what is going on there?  That snippet is taken almost verbatim from the user guide.  I tried both with the import and then FQN references.

[Thomas Mueller]

Hi,

I'm not sure, but I guess it's a JVM / classpath problem. Which JVM do you use?

The database tries to compile the class using the class "com.sun.tools.javac.Main" if available (which means the H2 jar file is most likely in the classpath). If it is not available, then it will try using a new process, "javac -sourcepath ... ", in which case the H2 jar file most likely isn't available. I guess it should use javax.tools.JavaCompiler if available...

Of could you could use the "old style" user defined function, in which case the user defined function needs to be in the classpath of the database engine:

    create alias findUser for "com.acme.udf.FindUser"

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/H5srCLpDQFYJ.

[steve.ebersole]

As you point out, yes H2 is on *my* classpath and it is able to see com.sun.tools.javac.Main (otherwise we'd see no compiler output).

IIUC the problem is really the classpath given to com.sun.tools.javac.Main.  Or most likely the lack thereof.  I dont think calls to com.sun.tools.javac.Main inherit the classpath of the caller (aside maybe from any classpath set as environmental variables). 

I am using the Oracle Java 7 JDK for Linux.

[Thomas Mueller]

Hi,

Hm, I wonder what is the easiest way to reproduce the problem...

> IIUC the problem is really the classpath given to com.sun.tools.javac.Main.
> Or most likely the lack thereof.

Yes, most likely. Do you use a special classloader environment (a web
server, OSGi,...)?

> I am using the Oracle Java 7 JDK for Linux.

OK, but I don't think this alone is the problem.

Regards,
Thomas

[steve.ebersole]

On Tuesday, June 26, 2012 2:09:40 PM UTC-5, Thomas Mueller wrote:

Hi,

Hm, I wonder what is the easiest way to reproduce the problem...

To be honest, now that you have explained how this works, unless you are setting up a classpath environmental variable I do not see how this would ever work.  Unless I have complete misunderstanding of how com.sun.tools.javac.Main works
 

> IIUC the problem is really the classpath given to com.sun.tools.javac.Main.
> Or most likely the lack thereof.

Yes, most likely. Do you use a special classloader environment (a web
server, OSGi,...)?

I am not using a special classloader.  This is a new test class I am developing as part of the Hibernate test suite and which I am trying to run from my IDE (IntelliJ).

> I am using the Oracle Java 7 JDK for Linux.

OK, but I don't think this alone is the problem.

No, nor do I.  But you asked which JVM I use, so I said ;)

[steve.ebersole]

The synopsis for the -classpath option passed to javac (and therefore available to com.sun.tools.javac.Main as well) states that if not set and there is no CLASSPATH environmental variable, then the current working directory is used.  I wonder if that affects your tests in a false-positive manner?