Description:
Hi,
I was using MySQL JDBC driver 5.0.8. In that case if I try to call DatabaseMetaData.getProcedureColumns() for a invalid/non-existing stored procedure, an SQLException will be thrown.
This week we moved to 5.1.15 JDBC driver. Then onwards DatabaseMetaData.getProcedureColumns() is not throwing SQLException if procedureName is non existing.
My questions
1. Is it a behavioral change or is it a defect? Whats the expected behavior?
2. How we will distinguish between "valid procedure without any parameters" and "invalid stored procedures" when we use it in DatabaseMetaData.getProcedureColumns()
MySQL server : 5.0.67/5.1.46/5.5
OS:Windows
How to repeat:
public class TestMySQLJDBC {
private final static String jdbcDriver ="com.mysql.jdbc.Driver";
private final static String connectionUrl ="jdbc:mysql://cin0389:3306/testutf8";
private final static String user = "root";
private final static String password = "password";
private final static String database = "testutf8";
private final static String invalidStoredProcedure = "INVALID";
private final static String validStoredProcedureWithoutParam = "TESTPROC";
private Connection dbConnection;
void createConnection() throws Exception{
Class.forName(jdbcDriver);
dbConnection = DriverManager.getConnection(connectionUrl, user, password);
}
void getMetadataForInvalid() throws Exception{
DatabaseMetaData metadata = null;
ResultSet rs = null;
boolean recordsPresent = false;
try{ // trying metadata of invalid stored procedure
metadata = dbConnection.getMetaData();
rs =metadata.getProcedureColumns(database, null, invalidStoredProcedure, null);
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
while(rs.next()){
recordsPresent = true;
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
if(!recordsPresent){
System.out.println("Nothing is in resultset");
}
} finally{
if(rs != null){
try{
rs.close();
} catch(SQLException sqle){
//ignore;
}
}
}
}
void closeConnection(){
try{
if(dbConnection != null){
dbConnection.close();
}
} catch(SQLException sqle){
//ignore
}
}
public static void main(String [] args){
TestMySQLJDBC myTest = new TestMySQLJDBC();
try {
myTest.createConnection();
myTest.getMetadataForValid();
} catch(Exception e){
System.out.println(e.getMessage());
} finally{
myTest.closeConnection();
}
}
}
Execute this using 5.0.8 driver and 5.1.15 driver. There is a change in way it behaves
Description: Hi, I was using MySQL JDBC driver 5.0.8. In that case if I try to call DatabaseMetaData.getProcedureColumns() for a invalid/non-existing stored procedure, an SQLException will be thrown. This week we moved to 5.1.15 JDBC driver. Then onwards DatabaseMetaData.getProcedureColumns() is not throwing SQLException if procedureName is non existing. My questions 1. Is it a behavioral change or is it a defect? Whats the expected behavior? 2. How we will distinguish between "valid procedure without any parameters" and "invalid stored procedures" when we use it in DatabaseMetaData.getProcedureColumns() MySQL server : 5.0.67/5.1.46/5.5 OS:Windows How to repeat: public class TestMySQLJDBC { private final static String jdbcDriver ="com.mysql.jdbc.Driver"; private final static String connectionUrl ="jdbc:mysql://cin0389:3306/testutf8"; private final static String user = "root"; private final static String password = "password"; private final static String database = "testutf8"; private final static String invalidStoredProcedure = "INVALID"; private final static String validStoredProcedureWithoutParam = "TESTPROC"; private Connection dbConnection; void createConnection() throws Exception{ Class.forName(jdbcDriver); dbConnection = DriverManager.getConnection(connectionUrl, user, password); } void getMetadataForInvalid() throws Exception{ DatabaseMetaData metadata = null; ResultSet rs = null; boolean recordsPresent = false; try{ // trying metadata of invalid stored procedure metadata = dbConnection.getMetaData(); rs =metadata.getProcedureColumns(database, null, invalidStoredProcedure, null); System.out.println(rs.getString(1)); System.out.println(rs.getString(2)); System.out.println(rs.getString(3)); while(rs.next()){ recordsPresent = true; System.out.println(rs.getString(1)); System.out.println(rs.getString(2)); System.out.println(rs.getString(3)); } if(!recordsPresent){ System.out.println("Nothing is in resultset"); } } finally{ if(rs != null){ try{ rs.close(); } catch(SQLException sqle){ //ignore; } } } } void closeConnection(){ try{ if(dbConnection != null){ dbConnection.close(); } } catch(SQLException sqle){ //ignore } } public static void main(String [] args){ TestMySQLJDBC myTest = new TestMySQLJDBC(); try { myTest.createConnection(); myTest.getMetadataForValid(); } catch(Exception e){ System.out.println(e.getMessage()); } finally{ myTest.closeConnection(); } } } Execute this using 5.0.8 driver and 5.1.15 driver. There is a change in way it behaves