/*
Copyright (c) 2002, 2014, Oracle and/or its affiliates. All rights reserved.
The MySQL Connector/J is licensed under the terms of the GPLv2
<http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors.
There are special exceptions to the terms and conditions of the GPLv2 as it is applied to
this software, see the FLOSS License Exception
<http://www.mysql.com/about/legal/licensing/foss-exception.html>.
This program is free software; you can redistribute it and/or modify it under the terms
of the GNU General Public License as published by the Free Software Foundation; version 2
of the License.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this
program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth
Floor, Boston, MA 02110-1301 USA
*/
package testsuite.regression.jdbc4;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
import com.mysql.jdbc.ConnectionProperties;
import com.mysql.jdbc.Util;
import testsuite.BaseTestCase;
public class MetaDataRegressionTest extends BaseTestCase {
/**
* Creates a new MetaDataRegressionTest.
*
* @param name
* the name of the test
*/
public MetaDataRegressionTest(String name) {
super(name);
}
/**
* Runs all test cases in this test suite
*
* @param args
*/
public static void main(String[] args) {
junit.textui.TestRunner.run(MetaDataRegressionTest.class);
}
/**
* Tests fix for BUG#68307 - getFunctionColumns() returns incorrect "COLUMN_TYPE" information. This is a JDBC4
* feature.
*
* @throws Exception
* if the test fails.
*/
public void testBug68307() throws Exception {
createFunction("testBug68307_func", "(func_param_in INT) RETURNS INT DETERMINISTIC RETURN 1");
createProcedure("testBug68307_proc",
"(IN proc_param_in INT, OUT proc_param_out INT, INOUT proc_param_inout INT) SELECT 1");
// test metadata from MySQL
DatabaseMetaData testDbMetaData = conn.getMetaData();
checkFunctionColumnTypeForBug68307("MySQL", testDbMetaData);
checkProcedureColumnTypeForBug68307("MySQL", testDbMetaData);
// test metadata from I__S
Connection connUseIS = getConnectionWithProps("useInformationSchema=true");
testDbMetaData = connUseIS.getMetaData();
checkFunctionColumnTypeForBug68307("I__S", testDbMetaData);
checkProcedureColumnTypeForBug68307("I__S", testDbMetaData);
connUseIS.close();
}
private void checkFunctionColumnTypeForBug68307(String testAgainst, DatabaseMetaData testDbMetaData) throws Exception {
rs = testDbMetaData.getFunctionColumns(null, null, "testBug68307_%", "%");
while (rs.next()) {
String message = testAgainst + ", function <" + rs.getString("FUNCTION_NAME") + "."
+ rs.getString("COLUMN_NAME") + ">";
if (rs.getString("COLUMN_NAME") == null || rs.getString("COLUMN_NAME").length() == 0) {
assertEquals(message, DatabaseMetaData.functionReturn, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_in")) {
assertEquals(message, DatabaseMetaData.functionColumnIn, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_inout")) {
assertEquals(message, DatabaseMetaData.functionColumnInOut, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_out")) {
assertEquals(message, DatabaseMetaData.functionColumnOut, rs.getShort("COLUMN_TYPE"));
} else {
fail("Column '" + rs.getString("FUNCTION_NAME") + "." + rs.getString("COLUMN_NAME")
+ "' not expected within test case.");
}
}
}
private void checkProcedureColumnTypeForBug68307(String testAgainst, DatabaseMetaData testDbMetaData) throws Exception {
rs = testDbMetaData.getProcedureColumns(null, null, "testBug68307_%", "%");
while (rs.next()) {
String message = testAgainst + ", procedure <" + rs.getString("PROCEDURE_NAME") + "."
+ rs.getString("COLUMN_NAME") + ">";
if (rs.getString("COLUMN_NAME") == null || rs.getString("COLUMN_NAME").length() == 0) {
assertEquals(message, DatabaseMetaData.procedureColumnReturn, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_in")) {
assertEquals(message, DatabaseMetaData.procedureColumnIn, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_inout")) {
assertEquals(message, DatabaseMetaData.procedureColumnInOut, rs.getShort("COLUMN_TYPE"));
} else if (rs.getString("COLUMN_NAME").endsWith("_out")) {
assertEquals(message, DatabaseMetaData.procedureColumnOut, rs.getShort("COLUMN_TYPE"));
} else {
fail("Column '" + rs.getString("FUNCTION_NAME") + "." + rs.getString("COLUMN_NAME")
+ "' not expected within test case.");
}
}
}
/**
* Tests fix for BUG#44451 - getTables does not return resultset with expected columns.
*
* @throws Exception
* if the test fails.
*/
public void testBug44451() throws Exception {
String methodName;
List<String> expectedFields;
String[] testStepDescription = new String[] { "MySQL MetaData", "I__S MetaData" };
Connection connUseIS = getConnectionWithProps("useInformationSchema=true");
Connection[] testConnections = new Connection[] { conn, connUseIS };
methodName = "getClientInfoProperties()";
expectedFields = Arrays.asList("NAME", "MAX_LEN", "DEFAULT_VALUE", "DESCRIPTION");
for (int i = 0; i < testStepDescription.length; i++) {
DatabaseMetaData testDbMetaData = testConnections[i].getMetaData();
rs = testDbMetaData.getClientInfoProperties();
checkReturnedColumnsForBug44451(testStepDescription[i], methodName, expectedFields, rs);
rs.close();
}
methodName = "getFunctions()";
expectedFields = Arrays.asList("FUNCTION_CAT", "FUNCTION_SCHEM", "FUNCTION_NAME", "REMARKS", "FUNCTION_TYPE", "SPECIFIC_NAME");
for (int i = 0; i < testStepDescription.length; i++) {
DatabaseMetaData testDbMetaData = testConnections[i].getMetaData();
rs = testDbMetaData.getFunctions(null, null, "%");
checkReturnedColumnsForBug44451(testStepDescription[i], methodName, expectedFields, rs);
rs.close();
}
connUseIS.close();
}
private void checkReturnedColumnsForBug44451(String stepDescription, String methodName, List<String> expectedFields,
ResultSet resultSetToCheck) throws Exception {
ResultSetMetaData rsMetaData = resultSetToCheck.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
assertEquals(stepDescription + ", wrong column count in method '" + methodName + "'.", expectedFields.size(),
numberOfColumns);
for (int i = 0; i < numberOfColumns; i++) {
int position = i + 1;
assertEquals(stepDescription + ", wrong column at position '" + position + "' in method '" + methodName
+ "'.", expectedFields.get(i), rsMetaData.getColumnName(position));
}
rs.close();
}
/**
* Tests fix for BUG#69298 - Different outcome from DatabaseMetaData.getFunctions() when using I__S.
*
* @throws Exception
* if the test fails.
*/
public void testBug69298() throws Exception {
Connection testConn;
createFunction("testBug69298_func",
"(param_func INT) RETURNS INT COMMENT 'testBug69298_func comment' DETERMINISTIC RETURN 1");
createProcedure("testBug69298_proc", "(IN param_proc INT) COMMENT 'testBug69298_proc comment' SELECT 1");
// test with standard connection
assertFalse("Property useInformationSchema should be false",
((ConnectionProperties) conn).getUseInformationSchema());
assertTrue("Property getProceduresReturnsFunctions should be true",
((ConnectionProperties) conn).getGetProceduresReturnsFunctions());
checkGetFunctionsForBug69298("Std. Connection MetaData", conn);
checkGetFunctionColumnsForBug69298("Std. Connection MetaData", conn);
checkGetProceduresForBug69298("Std. Connection MetaData", conn);
checkGetProcedureColumnsForBug69298("Std. Connection MetaData", conn);
// test with property useInformationSchema=true
testConn = getConnectionWithProps("useInformationSchema=true");
assertTrue("Property useInformationSchema should be true",
((ConnectionProperties) testConn).getUseInformationSchema());
assertTrue("Property getProceduresReturnsFunctions should be true",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkGetFunctionsForBug69298("Prop. useInfoSchema(1) MetaData", testConn);
checkGetFunctionColumnsForBug69298("Prop. useInfoSchema(1) MetaData", testConn);
checkGetProceduresForBug69298("Prop. useInfoSchema(1) MetaData", testConn);
checkGetProcedureColumnsForBug69298("Prop. useInfoSchema(1) MetaData", testConn);
testConn.close();
// test with property getProceduresReturnsFunctions=false
testConn = getConnectionWithProps("getProceduresReturnsFunctions=false");
assertFalse("Property useInformationSchema should be false",
((ConnectionProperties) testConn).getUseInformationSchema());
assertFalse("Property getProceduresReturnsFunctions should be false",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkGetFunctionsForBug69298("Prop. getProcRetFunc(0) MetaData", testConn);
checkGetFunctionColumnsForBug69298("Prop. getProcRetFunc(0) MetaData", testConn);
checkGetProceduresForBug69298("Prop. getProcRetFunc(0) MetaData", testConn);
checkGetProcedureColumnsForBug69298("Prop. getProcRetFunc(0) MetaData", testConn);
testConn.close();
// test with property useInformationSchema=true & getProceduresReturnsFunctions=false
testConn = getConnectionWithProps("useInformationSchema=true,getProceduresReturnsFunctions=false");
assertTrue("Property useInformationSchema should be true",
((ConnectionProperties) testConn).getUseInformationSchema());
assertFalse("Property getProceduresReturnsFunctions should be false",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkGetFunctionsForBug69298("Prop. useInfoSchema(1) + getProcRetFunc(0) MetaData", testConn);
checkGetFunctionColumnsForBug69298("Prop. useInfoSchema(1) + getProcRetFunc(0) MetaData", testConn);
checkGetProceduresForBug69298("Prop. useInfoSchema(1) + getProcRetFunc(0) MetaData", testConn);
checkGetProcedureColumnsForBug69298("Prop. useInfoSchema(1) + getProcRetFunc(0) MetaData", testConn);
testConn.close();
}
private void checkGetFunctionsForBug69298(String stepDescription, Connection testConn) throws Exception {
DatabaseMetaData testDbMetaData = testConn.getMetaData();
ResultSet functionsMD = testDbMetaData.getFunctions(null, null, "testBug69298_%");
String sd = stepDescription + " getFunctions() ";
assertTrue(sd + "one row expected.", functionsMD.next());
// function: testBug69298_func
assertEquals(sd + "-> FUNCTION_CAT", testConn.getCatalog(), functionsMD.getString("FUNCTION_CAT"));
assertEquals(sd + "-> FUNCTION_SCHEM", null, functionsMD.getString("FUNCTION_SCHEM"));
assertEquals(sd + "-> FUNCTION_NAME", "testBug69298_func", functionsMD.getString("FUNCTION_NAME"));
assertEquals(sd + "-> REMARKS", "testBug69298_func comment", functionsMD.getString("REMARKS"));
assertEquals(sd + "-> FUNCTION_TYPE", DatabaseMetaData.functionNoTable, functionsMD.getShort("FUNCTION_TYPE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", functionsMD.getString("SPECIFIC_NAME"));
assertFalse(stepDescription + "no more rows expected.", functionsMD.next());
}
private void checkGetFunctionColumnsForBug69298(String stepDescription, Connection testConn) throws Exception {
DatabaseMetaData testDbMetaData = testConn.getMetaData();
ResultSet funcColsMD = testDbMetaData.getFunctionColumns(null, null, "testBug69298_%", "%");
String sd = stepDescription + " getFunctionColumns() ";
assertTrue(sd + "1st of 2 rows expected.", funcColsMD.next());
// function column: testBug69298_func return
assertEquals(sd + "-> FUNCTION_CAT", testConn.getCatalog(), funcColsMD.getString("FUNCTION_CAT"));
assertEquals(sd + "-> FUNCTION_SCHEM", null, funcColsMD.getString("FUNCTION_SCHEM"));
assertEquals(sd + "-> FUNCTION_NAME", "testBug69298_func", funcColsMD.getString("FUNCTION_NAME"));
assertEquals(sd + "-> COLUMN_NAME", "", funcColsMD.getString("COLUMN_NAME"));
assertEquals(sd + "-> COLUMN_TYPE", DatabaseMetaData.functionReturn, funcColsMD.getShort("COLUMN_TYPE"));
assertEquals(sd + "-> DATA_TYPE", Types.INTEGER, funcColsMD.getInt("DATA_TYPE"));
assertEquals(sd + "-> TYPE_NAME", "INT", funcColsMD.getString("TYPE_NAME"));
assertEquals(sd + "-> PRECISION", 10, funcColsMD.getInt("PRECISION"));
assertEquals(sd + "-> LENGTH", 10, funcColsMD.getInt("LENGTH"));
assertEquals(sd + "-> SCALE", 0, funcColsMD.getShort("SCALE"));
assertEquals(sd + "-> RADIX", 10, funcColsMD.getShort("RADIX"));
assertEquals(sd + "-> NULLABLE", DatabaseMetaData.functionNullable, funcColsMD.getShort("NULLABLE"));
assertEquals(sd + "-> REMARKS", null, funcColsMD.getString("REMARKS"));
assertEquals(sd + "-> CHAR_OCTET_LENGTH", 0, funcColsMD.getInt("CHAR_OCTET_LENGTH"));
assertEquals(sd + "-> ORDINAL_POSITION", 0, funcColsMD.getInt("ORDINAL_POSITION"));
assertEquals(sd + "-> IS_NULLABLE", "YES", funcColsMD.getString("IS_NULLABLE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", funcColsMD.getString("SPECIFIC_NAME"));
assertTrue(sd + "2nd of 2 rows expected.", funcColsMD.next());
// function column: testBug69298_func.param_func
assertEquals(sd + "-> FUNCTION_CAT", testConn.getCatalog(), funcColsMD.getString("FUNCTION_CAT"));
assertEquals(sd + "-> FUNCTION_SCHEM", null, funcColsMD.getString("FUNCTION_SCHEM"));
assertEquals(sd + "-> FUNCTION_NAME", "testBug69298_func", funcColsMD.getString("FUNCTION_NAME"));
assertEquals(sd + "-> COLUMN_NAME", "param_func", funcColsMD.getString("COLUMN_NAME"));
assertEquals(sd + "-> COLUMN_TYPE", DatabaseMetaData.functionColumnIn, funcColsMD.getShort("COLUMN_TYPE"));
assertEquals(sd + "-> DATA_TYPE", Types.INTEGER, funcColsMD.getInt("DATA_TYPE"));
assertEquals(sd + "-> TYPE_NAME", "INT", funcColsMD.getString("TYPE_NAME"));
assertEquals(sd + "-> PRECISION", 10, funcColsMD.getInt("PRECISION"));
assertEquals(sd + "-> LENGTH", 10, funcColsMD.getInt("LENGTH"));
assertEquals(sd + "-> SCALE", 0, funcColsMD.getShort("SCALE"));
assertEquals(sd + "-> RADIX", 10, funcColsMD.getShort("RADIX"));
assertEquals(sd + "-> NULLABLE", DatabaseMetaData.functionNullable, funcColsMD.getShort("NULLABLE"));
assertEquals(sd + "-> REMARKS", null, funcColsMD.getString("REMARKS"));
assertEquals(sd + "-> CHAR_OCTET_LENGTH", 0, funcColsMD.getInt("CHAR_OCTET_LENGTH"));
assertEquals(sd + "-> ORDINAL_POSITION", 1, funcColsMD.getInt("ORDINAL_POSITION"));
assertEquals(sd + "-> IS_NULLABLE", "YES", funcColsMD.getString("IS_NULLABLE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", funcColsMD.getString("SPECIFIC_NAME"));
assertFalse(sd + "no more rows expected.", funcColsMD.next());
}
private void checkGetProceduresForBug69298(String stepDescription, Connection testConn) throws Exception {
DatabaseMetaData testDbMetaData = testConn.getMetaData();
ResultSet proceduresMD = testDbMetaData.getProcedures(null, null, "testBug69298_%");
String sd = stepDescription + " getProcedures() ";
boolean isGetProceduresReturnsFunctions = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
if (isGetProceduresReturnsFunctions) {
assertTrue(sd + "1st of 2 rows expected.", proceduresMD.next());
// function: testBug69298_func
assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", proceduresMD.getString("PROCEDURE_NAME"));
assertEquals(sd + "-> REMARKS", "testBug69298_func comment", proceduresMD.getString("REMARKS"));
assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureReturnsResult,
proceduresMD.getShort("PROCEDURE_TYPE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", proceduresMD.getString("SPECIFIC_NAME"));
assertTrue(sd + "2nd of 2 rows expected.", proceduresMD.next());
} else {
assertTrue(sd + "one row expected.", proceduresMD.next());
}
// procedure: testBug69298_proc
assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), proceduresMD.getString("PROCEDURE_CAT"));
assertEquals(sd + "-> PROCEDURE_SCHEM", null, proceduresMD.getString("PROCEDURE_SCHEM"));
assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", proceduresMD.getString("PROCEDURE_NAME"));
assertEquals(sd + "-> REMARKS", "testBug69298_proc comment", proceduresMD.getString("REMARKS"));
assertEquals(sd + "-> PROCEDURE_TYPE", DatabaseMetaData.procedureNoResult,
proceduresMD.getShort("PROCEDURE_TYPE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_proc", proceduresMD.getString("SPECIFIC_NAME"));
assertFalse(stepDescription + "no more rows expected.", proceduresMD.next());
}
private void checkGetProcedureColumnsForBug69298(String stepDescription, Connection testConn) throws Exception {
DatabaseMetaData testDbMetaData = testConn.getMetaData();
ResultSet procColsMD = testDbMetaData.getProcedureColumns(null, null, "testBug69298_%", "%");
String sd = stepDescription + " getProcedureColumns() ";
boolean isGetProceduresReturnsFunctions = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
if (isGetProceduresReturnsFunctions) {
assertTrue(sd + "1st of 3 rows expected.", procColsMD.next());
// function column: testBug69298_func return
assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), procColsMD.getString("PROCEDURE_CAT"));
assertEquals(sd + "-> PROCEDURE_SCHEM", null, procColsMD.getString("PROCEDURE_SCHEM"));
assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", procColsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + "-> COLUMN_NAME", "", procColsMD.getString("COLUMN_NAME"));
assertEquals(sd + "-> COLUMN_TYPE", DatabaseMetaData.procedureColumnReturn,
procColsMD.getShort("COLUMN_TYPE"));
assertEquals(sd + "-> DATA_TYPE", Types.INTEGER, procColsMD.getInt("DATA_TYPE"));
assertEquals(sd + "-> TYPE_NAME", "INT", procColsMD.getString("TYPE_NAME"));
assertEquals(sd + "-> PRECISION", 10, procColsMD.getInt("PRECISION"));
assertEquals(sd + "-> LENGTH", 10, procColsMD.getInt("LENGTH"));
assertEquals(sd + "-> SCALE", 0, procColsMD.getShort("SCALE"));
assertEquals(sd + "-> RADIX", 10, procColsMD.getShort("RADIX"));
assertEquals(sd + "-> NULLABLE", DatabaseMetaData.procedureNullable, procColsMD.getShort("NULLABLE"));
assertEquals(sd + "-> REMARKS", null, procColsMD.getString("REMARKS"));
assertEquals(sd + "-> COLUMN_DEF", null, procColsMD.getString("COLUMN_DEF"));
assertEquals(sd + "-> SQL_DATA_TYPE", 0, procColsMD.getInt("SQL_DATA_TYPE"));
assertEquals(sd + "-> SQL_DATETIME_SUB", 0, procColsMD.getInt("SQL_DATETIME_SUB"));
assertEquals(sd + "-> CHAR_OCTET_LENGTH", 0, procColsMD.getInt("CHAR_OCTET_LENGTH"));
assertEquals(sd + "-> ORDINAL_POSITION", 0, procColsMD.getInt("ORDINAL_POSITION"));
assertEquals(sd + "-> IS_NULLABLE", "YES", procColsMD.getString("IS_NULLABLE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", procColsMD.getString("SPECIFIC_NAME"));
assertTrue(sd + "2nd of 3 rows expected.", procColsMD.next());
// function column: testBug69298_func.param_func
assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), procColsMD.getString("PROCEDURE_CAT"));
assertEquals(sd + "-> PROCEDURE_SCHEM", null, procColsMD.getString("PROCEDURE_SCHEM"));
assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_func", procColsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + "-> COLUMN_NAME", "param_func", procColsMD.getString("COLUMN_NAME"));
assertEquals(sd + "-> COLUMN_TYPE", DatabaseMetaData.procedureColumnIn, procColsMD.getShort("COLUMN_TYPE"));
assertEquals(sd + "-> DATA_TYPE", Types.INTEGER, procColsMD.getInt("DATA_TYPE"));
assertEquals(sd + "-> TYPE_NAME", "INT", procColsMD.getString("TYPE_NAME"));
assertEquals(sd + "-> PRECISION", 10, procColsMD.getInt("PRECISION"));
assertEquals(sd + "-> LENGTH", 10, procColsMD.getInt("LENGTH"));
assertEquals(sd + "-> SCALE", 0, procColsMD.getShort("SCALE"));
assertEquals(sd + "-> RADIX", 10, procColsMD.getShort("RADIX"));
assertEquals(sd + "-> NULLABLE", DatabaseMetaData.procedureNullable, procColsMD.getShort("NULLABLE"));
assertEquals(sd + "-> REMARKS", null, procColsMD.getString("REMARKS"));
assertEquals(sd + "-> COLUMN_DEF", null, procColsMD.getString("COLUMN_DEF"));
assertEquals(sd + "-> SQL_DATA_TYPE", 0, procColsMD.getInt("SQL_DATA_TYPE"));
assertEquals(sd + "-> SQL_DATETIME_SUB", 0, procColsMD.getInt("SQL_DATETIME_SUB"));
assertEquals(sd + "-> CHAR_OCTET_LENGTH", 0, procColsMD.getInt("CHAR_OCTET_LENGTH"));
assertEquals(sd + "-> ORDINAL_POSITION", 1, procColsMD.getInt("ORDINAL_POSITION"));
assertEquals(sd + "-> IS_NULLABLE", "YES", procColsMD.getString("IS_NULLABLE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_func", procColsMD.getString("SPECIFIC_NAME"));
assertTrue(sd + "3rd of 3 rows expected.", procColsMD.next());
} else {
assertTrue(sd + "one row expected.", procColsMD.next());
}
// procedure column: testBug69298_proc.param_proc
assertEquals(sd + "-> PROCEDURE_CAT", testConn.getCatalog(), procColsMD.getString("PROCEDURE_CAT"));
assertEquals(sd + "-> PROCEDURE_SCHEM", null, procColsMD.getString("PROCEDURE_SCHEM"));
assertEquals(sd + "-> PROCEDURE_NAME", "testBug69298_proc", procColsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + "-> COLUMN_NAME", "param_proc", procColsMD.getString("COLUMN_NAME"));
assertEquals(sd + "-> COLUMN_TYPE", DatabaseMetaData.procedureColumnIn, procColsMD.getShort("COLUMN_TYPE"));
assertEquals(sd + "-> DATA_TYPE", Types.INTEGER, procColsMD.getInt("DATA_TYPE"));
assertEquals(sd + "-> TYPE_NAME", "INT", procColsMD.getString("TYPE_NAME"));
assertEquals(sd + "-> PRECISION", 10, procColsMD.getInt("PRECISION"));
assertEquals(sd + "-> LENGTH", 10, procColsMD.getInt("LENGTH"));
assertEquals(sd + "-> SCALE", 0, procColsMD.getShort("SCALE"));
assertEquals(sd + "-> RADIX", 10, procColsMD.getShort("RADIX"));
assertEquals(sd + "-> NULLABLE", DatabaseMetaData.procedureNullable, procColsMD.getShort("NULLABLE"));
assertEquals(sd + "-> REMARKS", null, procColsMD.getString("REMARKS"));
assertEquals(sd + "-> COLUMN_DEF", null, procColsMD.getString("COLUMN_DEF"));
assertEquals(sd + "-> SQL_DATA_TYPE", 0, procColsMD.getInt("SQL_DATA_TYPE"));
assertEquals(sd + "-> SQL_DATETIME_SUB", 0, procColsMD.getInt("SQL_DATETIME_SUB"));
assertEquals(sd + "-> CHAR_OCTET_LENGTH", 0, procColsMD.getInt("CHAR_OCTET_LENGTH"));
assertEquals(sd + "-> ORDINAL_POSITION", 1, procColsMD.getInt("ORDINAL_POSITION"));
assertEquals(sd + "-> IS_NULLABLE", "YES", procColsMD.getString("IS_NULLABLE"));
assertEquals(sd + "-> SPECIFIC_NAME", "testBug69298_proc", procColsMD.getString("SPECIFIC_NAME"));
assertFalse(sd + "no more rows expected.", procColsMD.next());
}
/**
* Tests fix for BUG#17248345 - GETFUNCTIONCOLUMNS() METHOD RETURNS COLUMNS OF PROCEDURE. (this happens when
* functions and procedures have a common name)
*
* @throws Exception
* if the test fails.
*/
public void testBug17248345() throws Exception {
Connection testConn;
// create one stored procedure and one function with same name
createProcedure("testBug17248345", "(IN proccol INT) SELECT 1");
createFunction("testBug17248345", "(funccol INT) RETURNS INT DETERMINISTIC RETURN 1");
// test with standard connection (getProceduresReturnsFunctions=true & useInformationSchema=false)
assertFalse("Property useInformationSchema should be false",
((ConnectionProperties) conn).getUseInformationSchema());
assertTrue("Property getProceduresReturnsFunctions should be true",
((ConnectionProperties) conn).getGetProceduresReturnsFunctions());
checkMetaDataInfoForBug17248345(conn);
// test with property useInformationSchema=true (getProceduresReturnsFunctions=true)
testConn = getConnectionWithProps("useInformationSchema=true");
assertTrue("Property useInformationSchema should be true",
((ConnectionProperties) testConn).getUseInformationSchema());
assertTrue("Property getProceduresReturnsFunctions should be true",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkMetaDataInfoForBug17248345(testConn);
testConn.close();
// test with property getProceduresReturnsFunctions=false (useInformationSchema=false)
testConn = getConnectionWithProps("getProceduresReturnsFunctions=false");
assertFalse("Property useInformationSchema should be false",
((ConnectionProperties) testConn).getUseInformationSchema());
assertFalse("Property getProceduresReturnsFunctions should be false",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkMetaDataInfoForBug17248345(testConn);
testConn.close();
// test with property useInformationSchema=true & getProceduresReturnsFunctions=false
testConn = getConnectionWithProps("useInformationSchema=true,getProceduresReturnsFunctions=false");
assertTrue("Property useInformationSchema should be true",
((ConnectionProperties) testConn).getUseInformationSchema());
assertFalse("Property getProceduresReturnsFunctions should be false",
((ConnectionProperties) testConn).getGetProceduresReturnsFunctions());
checkMetaDataInfoForBug17248345(testConn);
testConn.close();
}
private void checkMetaDataInfoForBug17248345(Connection testConn) throws Exception {
DatabaseMetaData testDbMetaData = testConn.getMetaData();
ResultSet rsMD;
boolean useInfoSchema = ((ConnectionProperties) testConn).getUseInformationSchema();
boolean getProcRetFunc = ((ConnectionProperties) testConn).getGetProceduresReturnsFunctions();
String stepDescription = "Prop. useInfoSchema(" + (useInfoSchema ? 1 : 0) + ") + getProcRetFunc("
+ (getProcRetFunc ? 1 : 0) + "):";
String sd;
// getFunctions() must return 1 record.
sd = stepDescription + " getFunctions() ";
rsMD = testDbMetaData.getFunctions(null, null, "testBug17248345");
assertTrue(sd + "one row expected.", rsMD.next());
assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
assertFalse(sd + "no more rows expected.", rsMD.next());
// getFunctionColumns() must return 2 records (func return + func param).
sd = stepDescription + " getFunctionColumns() ";
rsMD = testDbMetaData.getFunctionColumns(null, null, "testBug17248345", "%");
assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
assertEquals(sd + " -> FUNCTION_NAME", "testBug17248345", rsMD.getString("FUNCTION_NAME"));
assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
assertFalse(sd + "no more rows expected.", rsMD.next());
// getProcedures() must return 1 or 2 records, depending on if getProceduresReturnsFunctions is false or true
// respectively. When exists a procedure and a function with same name, function is returned first.
sd = stepDescription + " getProcedures() ";
rsMD = testDbMetaData.getProcedures(null, null, "testBug17248345");
if (getProcRetFunc) {
assertTrue(sd + "1st of 2 rows expected.", rsMD.next());
assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
assertTrue(sd + "2nd of 2 rows expected.", rsMD.next());
} else {
assertTrue(sd + "one row expected.", rsMD.next());
}
assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
assertFalse(sd + "no more rows expected.", rsMD.next());
// getProcedureColumns() must return 1 or 3 records, depending on if getProceduresReturnsFunctions is false or
// true respectively. When exists a procedure and a function with same name, function is returned first.
sd = stepDescription + " getProcedureColumns() ";
rsMD = testDbMetaData.getProcedureColumns(null, null, "testBug17248345", "%");
if (getProcRetFunc) {
assertTrue(sd + "1st of 3 rows expected.", rsMD.next());
assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + " -> COLUMN_NAME", "", rsMD.getString("COLUMN_NAME"));
assertTrue(sd + "2nd of 3 rows expected.", rsMD.next());
assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + " -> COLUMN_NAME", "funccol", rsMD.getString("COLUMN_NAME"));
assertTrue(sd + "3rd of 3 rows expected.", rsMD.next());
} else {
assertTrue(sd + "one row expected.", rsMD.next());
}
assertEquals(sd + " -> PROCEDURE_NAME", "testBug17248345", rsMD.getString("PROCEDURE_NAME"));
assertEquals(sd + " -> COLUMN_NAME", "proccol", rsMD.getString("COLUMN_NAME"));
assertFalse(sd + "no more rows expected.", rsMD.next());
}
/*
* Tests DatabaseMetaData.getSQLKeywords().
* (Related to BUG#70701 - DatabaseMetaData.getSQLKeywords() doesn't match MySQL 5.6 reserved words)
*
* The keywords list that this method returns depends on JDBC version.
*
* @throws Exception if the test fails.
*/
public void testReservedWords() throws Exception {
final String mysqlKeywords = "ACCESSIBLE,ADD,ANALYZE,ASC,BEFORE,CASCADE,CHANGE,CONTINUE,DATABASE,DATABASES,DAY_HOUR,DAY_MICROSECOND,DAY_MINUTE,DAY_SECOND,DELAYED,DESC,DISTINCTROW,DIV,DUAL,ELSEIF,ENCLOSED,ESCAPED,EXIT,EXPLAIN,FLOAT4,FLOAT8,FORCE,FULLTEXT,HIGH_PRIORITY,HOUR_MICROSECOND,HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,INDEX,INFILE,INT1,INT2,INT3,INT4,INT8,IO_AFTER_GTIDS,IO_BEFORE_GTIDS,ITERATE,KEY,KEYS,KILL,LEAVE,LIMIT,LINEAR,LINES,LOAD,LOCK,LONG,LONGBLOB,LONGTEXT,LOOP,LOW_PRIORITY,MASTER_BIND,MASTER_SSL_VERIFY_SERVER_CERT,MAXVALUE,MEDIUMBLOB,MEDIUMINT,MEDIUMTEXT,MIDDLEINT,MINUTE_MICROSECOND,MINUTE_SECOND,NONBLOCKING,NO_WRITE_TO_BINLOG,OPTIMIZE,OPTION,OPTIONALLY,OUTFILE,PURGE,READ,READ_WRITE,REGEXP,RENAME,REPEAT,REPLACE,REQUIRE,RESIGNAL,RESTRICT,RLIKE,SCHEMA,SCHEMAS,SECOND_MICROSECOND,SEPARATOR,SHOW,SIGNAL,SPATIAL,SQL_BIG_RESULT,SQL_CALC_FOUND_ROWS,SQL_SMALL_RESULT,SSL,STARTING,STRAIGHT_JOIN,TERMINATED,TINYBLOB,TINYINT,TINYTEXT,UNDO,UNLOCK,UNSIGNED,USAGE,USE,UTC_DATE,UTC_TIME,UTC_TIMESTAMP,VARBINARY,VARCHARACTER,WHILE,WRITE,XOR,YEAR_MONTH,ZEROFILL";
assertEquals("MySQL keywords don't match expected.", mysqlKeywords, this.conn.getMetaData().getSQLKeywords());
}
}