Bug #73775 DBMD.getProcedureColumns()/.getFunctionColumns() fail to filter by columnPattern

• Submitted: 29 Aug 2014 18:10
• Modified: 18 Apr 2017 21:53
• Reporter: Filipe Silva
• Status: Closed
• Category: Connector / J
• Severity: S1 (Critical)
• OS: Any
• Assigned to: Filipe Silva
• CPU Architecture: Any

[29 Aug 2014 18:10] Filipe Silva

Description:
Consulting DatabaseMetaData.getProcedureColumns() or DatabaseMetaData.getFunctionColumns() with a columnNamePattern filter doesn't work unless 'useInformationSchema=true' property is set.

But even if 'useInformationSchema=true' is used, the result always contains the "empty" column corresponding to the function return, no matter the columnNamePattern used.

How to repeat:
stmt.execute("DROP PROCEDURE IF EXISTS common_name");
stmt.execute("CREATE PROCEDURE common_name (INOUT param1 CHAR(20), IN param2 CHAR(20)) BEGIN  SELECT CONCAT(param1, param2) INTO param1;  END");

stmt.execute("DROP FUNCTION IF EXISTS common_name");
stmt.execute("CREATE FUNCTION common_name (param1 CHAR(20), param2 CHAR(20)) RETURNS CHAR(40) DETERMINISTIC RETURN CONCAT(param1, param2)");

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs;

System.out.println("Procs 1 ********");
rs = dbmd.getProcedureColumns(null, "", "%", "col2");
while (rs.next()) {
    System.out.println(rs.getString(3) + " -- " + rs.getString(4));
}

System.out.println("Procs 2 ********");
rs = dbmd.getProcedureColumns(null, "", "%", "col%");
while (rs.next()) {
    System.out.println(rs.getString(3) + " -- " + rs.getString(4));
}

System.out.println("Funcs 1 ********");
rs = dbmd.getFunctionColumns(null, "", "%", "col2");
while (rs.next()) {
    System.out.println(rs.getString(3) + " -- " + rs.getString(4));
}

System.out.println("Funcs 2 ********");
rs = dbmd.getFunctionColumns(null, "", "%", "col%");
while (rs.next()) {
    System.out.println(rs.getString(3) + " -- " + rs.getString(4));
}

Suggested fix:
Fix StringUtils.wildCompare().
Fix query that consults the information_schema regarding the procedure/function columns info

[18 Apr 2017 21:53] Daniel So

Posted by developer:
 
Added the following entry to the Connecotr/J 5.1.42 changelog:

"DatabaseMetaData.getProcedureColumns() and DatabaseMetaData.getFunctionColumns() did not return expected results. This was due to the errors with the matching algorithm for the column names, which have now been fixed. Notice that, however, the effects of the connection parameter getProceduresReturnsFunctions on the two methods when JDBC 4 is used remain unchanged."