MySQL Bugs: #3539: getProcedures() does not return any procedures in result set

Bug #3539	getProcedures() does not return any procedures in result set
Submitted:	22 Apr 2004 11:14	Modified:	27 Apr 2004 10:21
Reporter:	Karl Niemeier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.1-alpha	OS:	Windows (Windows XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
MySQL Version: 5.0.0-alpha-nt
Connector/J: mysql-connector-java-3.1.1-alpha ( $Date: 2004/02/06 17:15:32 $, $Revision: 1.27.4.29 $ )

DatabaseMetaData.getProcedures() always returns an empty result set.

How to repeat:
Run the following SQL script:

delimiter //
drop procedure if exists xt//
create procedure xt(out x int)
begin
set x=42;
end
//

Now, run the following Java code:
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("put your connection string here");
    DatabaseMetaData meta = conn.getMetaData();
    String db = null; // or set to "mysql"
    try {
      ResultSet mdrs = meta.getProcedures(db, null, "xt");
      while(mdrs.next()){
        System.out.println("found proc: " + mdrs.getString(3));
      }
      System.out.println("------------------------------------------");
      mdrs = meta.getProcedureColumns(db, null, "xt", "%");
      while(mdrs.next()){
        String procName = mdrs.getString(3);
        System.out.println("Procedure: "+ procName);
        String parmName = mdrs.getString(4);
        System.out.println("PARAMETER:" + parmName);
        System.out.println("------------------------------------------");
      }
    } catch (SQLException e) {;}

Observed: The call to getProcedureColumns works and confirms that there is a procedure named "xt" but the result set returned by getProcedures() is empty. The 3 parameters to getProcedures have the same value as the first 3 parameters to getProcedureColumns. Therefore, if getProcedureColumns is finding columns with these values, getProcedures should find the procedure.

Please test with the nightlies of Connector/J 3.1.x, as this code has been modified and fixed, and the issue doesn't seem to be repeatable with the latest version. See http://downloads.mysql.com/snapshots.php

I re-tested with mysql-connector-java-3.1-nightly-20040426 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ ) and still see the same problem. I'm not sure what additional info I can add other than to clarify that I'm running WinXP Pro 2002.

This is now fixed in the source repository, and will be included with Connector/J 3.1.2...See http://downloads.mysql.com/snapshots.php to test the nightly snapshot with these fixes applied after 00:00 GMT today.