(no subject)

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

(no subject)

list+org.hsqldb.user
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: (no subject)

Fred Toussi-2
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, [hidden email] 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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: (no subject)

list+org.hsqldb.user
Hello!

On 2016-09-08T18:27:37 +0100
Fred Toussi <[hidden email]> wrote:

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

Ok, thanks.

>
> Please read the Guide for the supported syntax of CREATE PROCEDURE and
> CREATE FUNCTION.
>
> With CREATE FUNCTION, you need to use RETURN TABLE(SELECT ...)

I'm not sure if you meant that the syntax I gave is wrong...

  CREATE FUNCTION getX(IN in_x INTEGER)
    RETURNS TABLE (x INTEGER)
      READS SQL DATA RETURN TABLE (SELECT t.x FROM WHERE t.x = in_x
       LIMIT 1);

It seems correct. Is the (fixed) bug masking what would have been
a syntax error?

M

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

attachment0 (837 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: (no subject)

Fred Toussi-2
Your new version of CREATE FUNCTION looks correct.

The bug relates to using a function with CALL. It worked fine with
procedures.

Fred

On Thu, Sep 8, 2016, at 18:48, [hidden email] wrote:

> Hello!
>
> On 2016-09-08T18:27:37 +0100
> Fred Toussi <[hidden email]> wrote:
>
> > I think this has been fixed in SVN /base/trunk
>
> Ok, thanks.
>
> >
> > Please read the Guide for the supported syntax of CREATE PROCEDURE and
> > CREATE FUNCTION.
> >
> > With CREATE FUNCTION, you need to use RETURN TABLE(SELECT ...)
>
> I'm not sure if you meant that the syntax I gave is wrong...
>
>   CREATE FUNCTION getX(IN in_x INTEGER)
>     RETURNS TABLE (x INTEGER)
>       READS SQL DATA RETURN TABLE (SELECT t.x FROM WHERE t.x = in_x
>        LIMIT 1);
>
> It seems correct. Is the (fixed) bug masking what would have been
> a syntax error?
>
> M
> ------------------------------------------------------------------------------
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> Email had 1 attachment:
> + Attachment1.2
>   1k (application/pgp-signature)

------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...