Bug #69298 Different outcome from DatabaseMetaData.getFunctions() when using I__S
Submitted: 21 May 2013 18:11 Modified: 15 Jul 2013 16:06
Reporter: Filipe Silva Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.25 OS:Linux
Assigned to: Filipe Silva CPU Architecture:Any

[21 May 2013 18:11] Filipe Silva
The methods getFunctions(...) and getFunctionColumns(...) from DatabaseMetaData, have different results when the Connection property useInformationSchema=true is used.

If not using the I__S, these methods return information about all existing functions and procedures, while when using I__S, they return only information about functions.

A similar problem exists with the methods getProcedures() and getProcedureColumns() which always return information about functions and procedures, however, as since JDBC4 there are methods specific to return only functions related information, those should return only procedures related information.


How to repeat:
Create a function and a procedure in the server.

Create a small java program to output the results from the referred methods and:

1. Execute it with a plain connection;
2. Execute it with a connection having the property useInformationSchema=true.

Compare the results.

Suggested fix:
[15 Jul 2013 16:06] Daniel So
Added entry to Connector/J 5.1.25 changelog:

The DatabaseMetaData methods of getFunctions and getFunctionColumns returned information for both stored functions and procedures when value of the connection option useInformationSchema was false. On the other hand, getProcedures and getProcedureColumns always return information for both stored procedures and functions. The behaviours of the four functions are inconsistent with the idea introduced since JDBC4 to separate the “get” functions for stored functions and procedures. The functions are modified as follows:

- getFunctions and getFunctionColumns now only return information for stored functions (not for stored procedures), irrespective of the value of useInformationSchema.

- For getProcedures and getProcedureColumns, in order to maintain backward compatibility with pre-JDBC4 implementations, a new connection option, getProceduresReturnsFunctions, is created, whose default value of 'true' makes getProcedures and getProcedureColumns return information for both stored procedures and functions. Setting the option to false makes the functions return only information for stored procedures.