Re: (no subject)

I think this has been fixed in SVN /base/trunk

Please read the Guide for the supported syntax of CREATE PROCEDURE and
CREATE FUNCTION.

With CREATE FUNCTION, you need to use RETURN TABLE(SELECT ...)

Alternatively you can use CREATE PROCEDURE and return the data by
declaring a CURSOR and opening the CURSOR in the body of the procedure.

Fred

On Thu, Sep 8, 2016, at 17:22, [email protected] wrote:
> Hi.
> 
> The following program causes a NullPointerException:
> 
> Exception in thread "main" java.lang.NullPointerException
> 	at org.hsqldb.StatementProcedure.getParametersMetaData(Unknown Source)
> 	at org.hsqldb.result.Result.newPrepareResponse(Unknown Source)
> 	at org.hsqldb.Session.execute(Unknown Source)
> 	at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
> 	at org.hsqldb.jdbc.JDBCCallableStatement.<init>(Unknown Source)
> 	at org.hsqldb.jdbc.JDBCConnection.prepareCall(Unknown Source)
> 	at TestCall.main(TestCall.java:69)
> 
> The problem occurs at the last prepareCall() line:
> 
>   try (final CallableStatement st = c.prepareCall(text.toString())) {
> 
> This is a small contrived example taken from a larger program. The
> problem
> seems to be when attempting to call a function declared with CREATE
> FUNCTION.
> 
> Am I doing something wrong?
> 
> --8<--
> 
> import org.hsqldb.jdbc.JDBCDataSource;
> 
> import java.sql.CallableStatement;
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.util.Properties;
> 
> public final class TestCall
> {
>   private TestCall()
>   {
> 
>   }
> 
>   public static void main(final String[] args)
>     throws Exception
>   {
>     final String url = String.format("jdbc:hsqldb:file:%s",
>     "/tmp/example");
> 
>     final Properties props = new Properties();
>     props.setProperty("user", "SA");
>     props.setProperty("password", "");
>     props.setProperty("create", "true");
>     props.setProperty("shutdown", "false");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("sql.enforce_names", "true");
>     props.setProperty("sql.regular_names", "true");
>     props.setProperty("sql.enforce_refs", "true");
>     props.setProperty("sql.enforce_size", "true");
>     props.setProperty("hsqldb.applog", "3");
>     props.setProperty("hsqldb.sqllog", "3");
> 
>     final JDBCDataSource ds = new JDBCDataSource();
>     ds.setUrl(url);
>     ds.setProperties(props);
> 
>     final StringBuilder text = new StringBuilder(128);
>     text.append("CREATE CACHED TABLE t (x INTEGER);");
> 
>     try (final Connection c = ds.getConnection()) {
>       try (final PreparedStatement st =
>              c.prepareStatement(text.toString())) {
>         st.execute();
>       }
>     }
> 
>     text.setLength(0);
>     text.append("CREATE FUNCTION getX(IN in_x INTEGER)\n");
>     text.append("RETURNS TABLE (x INTEGER)\n");
>     text.append("READS SQL DATA RETURN TABLE(\n");
>     text.append("  SELECT\n");
>     text.append("    t.x\n");
>     text.append("  FROM t\n");
>     text.append("  WHERE t.x = in_x\n");
>     text.append("  LIMIT 1\n");
>     text.append(");");
> 
>     try (final Connection c = ds.getConnection()) {
>       try (final PreparedStatement st =
>       c.prepareStatement(text.toString())) {
>         st.execute();
>       }
>     }
> 
>     text.setLength(0);
>     text.append("CALL getX(?)\n");
> 
>     try (final Connection c = ds.getConnection()) {
>       try (final CallableStatement st = c.prepareCall(text.toString())) {
>         st.setInt(1, 23);
>         try (final ResultSet rs = st.executeQuery()) {
>           assert rs.next() == false;
>         }
>       }
>     }
>   }
> }
> 
> -->8--
> 
> M
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------