execute() with stored procedures that don't return a ResultSet

[Faw]

Ok, I checked all my versions everywhere to see if I had the right
ones before posting this (unlike the previous time)

My H2 server is running 1.3.151, and the jar for the JDBC driver is
the same.

Now my problem is that when I call a stored procedure that "does not"
return a resultset I'm getting one. According to the docs:

The execute method returns a boolean to indicate the form of the first
result. You must call either the method getResultSet or getUpdateCount
to retrieve the result; you must call getMoreResults to move to any
subsequent result(s).

This is the function I'm using to test it.

public static void test() throws SQLException{
boolean retCode;
try{
Class.forName("org.h2.Driver");
}catch (ClassNotFoundException e){
e.printStackTrace();
}
ResultSet rs;
ResultSetMetaData rsmd;
Connection conn = DriverManager.getConnection("jdbc:h2:~/temp/test");
System.out.println("version: " +
conn.getMetaData().getDatabaseProductVersion());
//
conn.createStatement().execute("drop alias test_procedure");
conn.createStatement().execute(
"create alias test_procedure as $$" +
"void return_nothing(Connection c, String x) throws
SQLException { }$$");
CallableStatement c = conn.prepareCall("{call
msim_getDictionary(?)}");
c.setString(1, "Hello");
retCode=c.execute();
System.out.println("execute returns "+retCode); // returns true
should be false
rs = c.getResultSet(); // this should give an exception
rsmd=rs.getMetaData(); // so should this
for(int i=0;i<rsmd.getColumnCount();i++){
System.out.println(String.valueOf(i+1)+"."+rsmd.getColumnName(i+1));
}
System.out.println("isBeforeFirst: " + rs.isBeforeFirst());
rs.next();
System.out.println(rs.getInt(1));
conn.close();
}

[Thomas Mueller]

Hi,

For H2, the statement "CALL" actually does return a result set. So I
wouldn't call it a 'bug'. However, it might be incompatible with other
databases. With which other database do you compare this?

Regards,
Thomas

[Fawzib Rojas]

I'm sure its a bug. Every database and JDBC driver I've used work the same way (MySQL, Derby, MSSQL, Sybase). CALL should only returns a ResultSet if the procedure returns one. Just check the documentation for execute(), getMoreResults(), getResultSet() and getUpdateCount()

According to the JDBC documentation:

execute() - The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).  Returns: true if the next result is a ResultSet object; false if it is an update count or there are no more results, so a standard way to process a request would be:

    retCode=stmt.execute();
    // find first resultset, skip all update counts until we
    // find a resultset or no more results are found
    while(retCode==false && stmt.getUpdateCount()!=-1){
      retCode=getMoreResults();
    }
    if(retCode==true){
      // we found a resultset, query was a select or a procedure
      // that returned a resultset
      rs=getResultSet();
    }else{
      // no results sets from this query, either an update,insert,delete
      // or a procedure doing one of those commands
      rs=null;
    }

So if a procedure is in the format "ResultSet myProcedure(Connection)" it should return a ResultSet (execute() should return true), if its in the format "void myProcedure(Connection)" it should not (execute() should result false), right now H2 returns a ResultSet with the field name as the proc name, and the value as NULL.

[Thomas Mueller]

Hi,

> I'm sure its a bug.

In your view, yes, and I understand your view. My previous boss once
said the definition of a bug is: it's where the documentation and the
implementation don't match. In H2 it is not documented that the CALL
statement returns a result set at
http://h2database.com/html/grammar.html#call - but the CALL statement
always did return a result set (with one row, or with many rows).
Changing this behavior would probably break existing applications that
rely on this current behavior.

For H2, you can still use CallableStatement, call c.execute(), and
then call c.getInt().

I understand the behavior of H2 doesn't match the behavior of other
databases, but I'm afraid it's not something that I can change
currently. I will add a feature request, and patches are welcome of
course.

Regards,
Thomas

[steve.ebersole]

Hi Thomas,

It depends on the documentation you refer to.  As the implementor of an API (JDBC) you also need to adhere to its documented behavior.  I appreciate that H2 cannot do this now, but to say it is not a bug is a little disingenuous.  The documentation for java.sql.Statement#executeUpdate and java.sql.Statement#getUpdateCount are quite clear, and H2 currently violates them.

Sorry to revive such an old thread, but this is hitting me in trying to test support for stored procedures in Hibernate.  FWIW

[Thomas Mueller]

Hi,

Sorry I don't understand, the thread was about "call" statements returning a result set or not, but you say Statement.executeUpdate and Statement.getUpdateCount violating the JDBC API, which sounds like something different.

What is the problem in your case?

Regards,

Thomas

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

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[steve.ebersole]

I guess it is more that in H2 there is not way to define a procedure that acts as an 'executeUpdate' target.  Sorry, I should have been more clear.  In JDBC CallableStatement extends Statement.  So CallableStatement also has executeUpdate and getUpdateCount methods that it inherits from Statement.  I guess those are essentially non-functioning in H2 since there is (as far as I can tell) no way to define a procedure that "returns" affected row counts.

[Thomas Mueller]

Hi,

OK I see the problem. The SQL statement "call" for H2 always returned a result set (that's historical, simply changing it would break applications).

However, also since a long time, there is a hack in that if you call Statement.executeUpdate and PreparedStatement.executeUpdate with a "call" SQL statement, and if the "call" statement returns an int, then the int is returned from the executeUpdate(). But it's hack really, as "call" technically is a query. If you call Statement.executeUpdate("call 2+3"), the result is 10. If you call Statement.executeQuery("call 2+3"), the result is one row. The same with PreparedStatement.

For some reason, this hack doesn't work for CallableStatement however. I think this was made for compatibility (I forgot).

So yes, right now, you can't use CallableStatement.executeUpdate() with the SQL statement of type "call".

Regards,

Thomas

[steve.ebersole]

On Wednesday, July 31, 2013 5:19:40 PM UTC-5, Thomas Mueller wrote:

So yes, right now, you can't use CallableStatement.executeUpdate() with the SQL statement of type "call".

I don't understand this part.  Are there other "SQL statement types" in H2 that a CallableStatement can represent?

[Thomas Mueller]

Hi,

> Are there other "SQL statement types" in H2 that a CallableStatement can represent?

Sure. Within H2, you can execute all SQL statements (select, update, create,...) with a CallableStatement. And you can execute any SQL statement with PreparedStatement and Statement.

Regards,

Thomas

--