Bug #3540 getProcedureColumns doesn't work with wildcards for procedure name
Submitted: 22 Apr 2004 11:28 Modified: 27 Apr 2004 10:20
Reporter: Karl Niemeier Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.1-alpha OS:Windows (Windows XP Pro)
Assigned to: Mark Matthews CPU Architecture:Any

[22 Apr 2004 11:28] Karl Niemeier
Description:
MySql Server: 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 $ )

The procedureNamePattern parameter of getProcedureColumns produces a SQLException if the pattern contains a wildcard.

How to repeat:
First, 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:
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection("your connection stuff here");
      DatabaseMetaData meta = conn.getMetaData();
      System.out.println("Using xt to specify procedure");
      ResultSet mdrs = meta.getProcedureColumns(null, 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("------------------------------------------");
      }
      System.out.println("Using x% to specify procedure");
      mdrs = meta.getProcedureColumns(null, null, "x%", "%");
      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 first call to getProcedureColumns that specifies "xt" works fine. The second call to getProcedureColumns that specifies "x%" throws a SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURES LIKE 'x%'' at line 1"
[24 Apr 2004 8:54] Mark Matthews
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
[26 Apr 2004 15:27] Karl Niemeier
I still see the problem using mysql-connector-java-3.1-nightly-20040426 ( $Date: 2004/02/21 17:43:42 $, $Revision: 1.27.4.31 $ ).
[27 Apr 2004 10:20] Mark Matthews
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.