From: Date: December 25 2006 12:30am
Subject: ssl causes "PROCEDURE can't return a result set in the given context"
List-Archive: http://lists.mysql.com/java/9013
Message-Id: <20061224233032.C96011A818F@isis.cs3-inc.com>

I've seen lots of complaints about this error but no solutions.
Here's what I would call at very least an interesting result.
I hope the developers read this.  (How can I cause them to do so?)

I'm using mysql-connector-java-5.0.4, server 5.0.18-log

First my source, straight out of the mysql-connector-java doc:
====
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.CallableStatement;
  import java.sql.Types;
  import java.sql.ResultSet;

  class test1 {
   public static void main(String[] args) throws Exception{
	   try {
	   Class.forName("com.mysql.jdbc.Driver");
	   Connection conn = DriverManager.getConnection(args[0]);
	   CallableStatement cStmt = 
		   conn.prepareCall("{call demoSp(?, ?)}");
	   cStmt.setString(1, "abcdefg");
	   cStmt.registerOutParameter(2, Types.INTEGER);
	   cStmt.setInt("inOutParam", 1);
	   boolean hadResults = cStmt.execute();
	   while (hadResults) {
		  ResultSet rs = cStmt.getResultSet();
		  System.out.println("next result set");
		  while(rs.next())
			  System.out.println(rs.getObject(1).toString());
		  hadResults = cStmt.getMoreResults();
	   }
	   int outputValue = cStmt.getInt(2);
	   System.out.println("output "+outputValue);
	   } catch (SQLException ex) {
		  ex.printStackTrace();
	   }
   }	 
  }
====

Notice that I use arg[0] for the url.
(sanitizing the command line - the original is much longer)
  # java test1 "jdbc:mysql://myhost/mydb?user=root"
  next result set
  121
  155
  225
  231
  233
  next result set
  abcdefg
  next result set
  zyxwabcdefg
  output 2
(I added a select to the example procedure from the doc to see that
it would work with an rs that contained more than one result.)

Now I try to use ssl:
  # java test1 "jdbc:mysql://myhost/mydb?user=root&useSSL=true&requireSSL=true"
java.sql.SQLException: PROCEDURE mydb.demoSp can't return a result set in the given context
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:752)
	at test1.main(test1.java:18)

I get the same result without the requireSSL.
I can run the mysql command with ssl and execute the same queries:
  # mysql --host=myhost --user=root --ssl-ca=...
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 2229 to server version: 5.0.18-log
  mysql> \s
  --------------
  ...
  SSL:			Cipher in use is DHE-RSA-AES256-SHA
  ...
now just copying the commands I see in the log from the successful
run above:
  mysql> set @aaa=binary'1';
  Query OK, 0 rows affected (0.03 sec)

  mysql> call demoSP("asdfg",@aaa);
  ...
  5 rows in set (0.04 sec)

  +------------+
  | inputParam |
  +------------+
  | asdfg      |
  +------------+
  1 row in set (0.05 sec)

  +----------------------------+
  | CONCAT('zyxw', inputParam) |
  +----------------------------+
  | zyxwasdfg                  |
  +----------------------------+
  1 row in set (0.06 sec)

  Query OK, 0 rows affected (0.08 sec)

This shows that it's at least possible to issue the commands and
get back the right results, even using ssl.  Just off hand I have
trouble imagining what ssl could have to do with this problem.