Java Code Examples for java.sql.DatabaseMetaData#getProcedureColumns()
The following examples show how to use
java.sql.DatabaseMetaData#getProcedureColumns() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: SQLQuery.java From micro-integrator with Apache License 2.0 | 6 votes |
|
private Object[] getStoredProcFuncProps(String name) throws DataServiceFault, SQLException {
Connection conn = (Connection) this.getConfig().createConnection()[0];
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = null;
boolean error = true;
try {
rs = md.getProcedureColumns(null, null, name, "%");
Object[] resultMap = new Object[2];
if (!rs.next()) {
rs.close();
rs = md.getFunctionColumns(null, null, name, "%");
} else {
rs.close();
rs = md.getProcedureColumns(null, null, name, "%");
}
resultMap[0] = conn;
resultMap[1] = rs;
error = false;
return resultMap;
} finally {
if (error) {
this.releaseResources(rs, null);
}
}
}
Example 2
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 6 votes |
|
/**
* Tests fix for BUG#51912 - Passing NULL as cat. param to
* getProcedureColumns with nullCatalogMeansCurrent = false
*
* @throws Exception
* if the test fails.
*/
public void testBug51912() throws Exception {
if (!versionMeetsMinimum(5, 0)) {
return;
}
Connection overrideConn = null;
try {
Properties props = new Properties();
props.setProperty("nullCatalogMeansCurrent", "false");
overrideConn = getConnectionWithProps(props);
DatabaseMetaData dbmd = overrideConn.getMetaData();
this.rs = dbmd.getProcedureColumns(null, null, "%", null);
this.rs.close();
} finally {
if (overrideConn != null) {
overrideConn.close();
}
}
}
Example 3
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 6 votes |
|
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.");
}
}
}
Example 4
| Source File: MetaDataRegressionTest.java From Komondor with GNU General Public License v3.0 | 6 votes |
|
/**
* Tests fix for BUG#51912 - Passing NULL as cat. param to
* getProcedureColumns with nullCatalogMeansCurrent = false
*
* @throws Exception
* if the test fails.
*/
public void testBug51912() throws Exception {
if (!versionMeetsMinimum(5, 0)) {
return;
}
Connection overrideConn = null;
try {
Properties props = new Properties();
props.setProperty("nullCatalogMeansCurrent", "false");
overrideConn = getConnectionWithProps(props);
DatabaseMetaData dbmd = overrideConn.getMetaData();
this.rs = dbmd.getProcedureColumns(null, null, "%", null);
this.rs.close();
} finally {
if (overrideConn != null) {
overrideConn.close();
}
}
}
Example 5
| Source File: MetaResultSetTest.java From calcite-avatica with Apache License 2.0 | 5 votes |
|
@Test public void testGetProcedureColumns() throws SQLException {
DatabaseMetaData metadata = getDatabaseMetadata();
try (ResultSet rs = metadata.getProcedureColumns(null, null, null, null)) {
ResultSetMetaData rsMeta = rs.getMetaData();
assertEquals(20, rsMeta.getColumnCount());
assertColumn(rsMeta, 1, "PROCEDURE_CAT", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 2, "PROCEDURE_SCHEM", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 3, "PROCEDURE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 4, "COLUMN_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 5, "COLUMN_TYPE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 6, "DATA_TYPE", Types.INTEGER, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 7, "TYPE_NAME", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 8, "PRECISION", Types.INTEGER, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 9, "LENGTH", Types.INTEGER, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 10, "SCALE", Types.SMALLINT, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 11, "RADIX", Types.SMALLINT, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 12, "NULLABLE", Types.SMALLINT, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 13, "REMARKS", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 14, "COLUMN_DEF", Types.VARCHAR, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 15, "SQL_DATA_TYPE", Types.INTEGER,
DatabaseMetaData.columnNullableUnknown);
assertColumn(rsMeta, 16, "SQL_DATETIME_SUB", Types.INTEGER,
DatabaseMetaData.columnNullableUnknown);
assertColumn(rsMeta, 17, "CHAR_OCTET_LENGTH", Types.INTEGER, DatabaseMetaData.columnNullable);
assertColumn(rsMeta, 18, "ORDINAL_POSITION", Types.INTEGER, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 19, "IS_NULLABLE", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
assertColumn(rsMeta, 20, "SPECIFIC_NAME", Types.VARCHAR, DatabaseMetaData.columnNullable);
}
}
Example 6
| Source File: TestDatabaseMetaData.java From evosql with Apache License 2.0 | 5 votes |
|
/**
* Basic test of DatabaseMetaData functions that access system tables
*/
public void testTwo() throws Exception {
Connection conn = newConnection();
int updateCount;
try {
TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");
DatabaseMetaData dbmeta = conn.getMetaData();
dbmeta.allProceduresAreCallable();
dbmeta.getBestRowIdentifier(null, null, "T_1",
DatabaseMetaData.bestRowTransaction,
true);
dbmeta.getCatalogs();
dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
dbmeta.getExportedKeys(null, null, "T_1");
dbmeta.getFunctionColumns(null, "%", "%", "%");
dbmeta.getFunctions(null, "%", "%");
dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getProcedureColumns(null, null, "%", "%");
dbmeta.getProcedures("PUBLIC", "%", "%");
dbmeta.getSchemas(null, "#");
dbmeta.getTablePrivileges(null, "%", "%");
dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT });
} catch (Exception e) {
assertTrue("unable to prepare or execute DDL", false);
} finally {
conn.close();
}
}
Example 7
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 5 votes |
|
/**
* Tests fix for BUG#68307 - getFunctionColumns() returns incorrect "COLUMN_TYPE" information. This JDBC4
* feature required some changes in method getProcedureColumns().
*
* @throws Exception
* if the test fails.
*/
public void testBug68307() throws Exception {
String[] testStepDescription = new String[] { "MySQL MetaData", "I__S MetaData" };
Connection connUseIS = getConnectionWithProps("useInformationSchema=true");
Connection[] testConnections = new Connection[] { this.conn, connUseIS };
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");
for (int i = 0; i < testStepDescription.length; i++) {
DatabaseMetaData testDbMetaData = testConnections[i].getMetaData();
this.rs = testDbMetaData.getProcedureColumns(null, null, "testBug68307_%", "%");
while (this.rs.next()) {
String message = testStepDescription[i] + ", procedure/function <" + this.rs.getString("PROCEDURE_NAME") + "."
+ this.rs.getString("COLUMN_NAME") + ">";
if (this.rs.getString("COLUMN_NAME") == null || this.rs.getString("COLUMN_NAME").length() == 0) {
assertEquals(message, DatabaseMetaData.procedureColumnReturn, this.rs.getShort("COLUMN_TYPE"));
} else if (this.rs.getString("COLUMN_NAME").endsWith("_in")) {
assertEquals(message, DatabaseMetaData.procedureColumnIn, this.rs.getShort("COLUMN_TYPE"));
} else if (this.rs.getString("COLUMN_NAME").endsWith("_inout")) {
assertEquals(message, DatabaseMetaData.procedureColumnInOut, this.rs.getShort("COLUMN_TYPE"));
} else if (this.rs.getString("COLUMN_NAME").endsWith("_out")) {
assertEquals(message, DatabaseMetaData.procedureColumnOut, this.rs.getShort("COLUMN_TYPE"));
} else {
fail(testStepDescription[i] + ", column '" + this.rs.getString("FUNCTION_NAME") + "." + this.rs.getString("COLUMN_NAME")
+ "' not expected within test case.");
}
}
this.rs.close();
}
}
Example 8
| Source File: CallStatement.java From birt with Eclipse Public License 1.0 | 5 votes |
|
/**
* Calls JDBC DatabaseMetaData to find parameter definitions for a stored procedure
* @param schemaPattern Pattern for matching schema name. If null, matches any schema. If empty, matches current user's schema
* @param catalog Pattern for matching catalog. If null, matches any catalog
* @param procedureNamePattern Pattern for matching procedure name
* @param params Definitions of parameters are added to the list, in call order
* @throws SQLException
*/
private void queryProcedureParams( String schemaPattern, String catalog,
String procedureNamePattern, List<ParameterDefn> params )
throws SQLException
{
DatabaseMetaData metaData = conn.getMetaData( );
java.sql.ResultSet rs = null;
try
{
rs = metaData.getProcedureColumns( catalog,
schemaPattern,
procedureNamePattern,
null );
while ( rs.next( ) )
{
ParameterDefn p = new ParameterDefn( );
p.setParamName( rs.getString( "COLUMN_NAME" ) );
p.setParamInOutType( rs.getInt( "COLUMN_TYPE" ) );
p.setParamType( rs.getInt( "DATA_TYPE" ) );
p.setParamTypeName( rs.getString( "TYPE_NAME" ) );
p.setPrecision( rs.getInt( "PRECISION" ) );
p.setScale( rs.getInt( "SCALE" ) );
p.setIsNullable( rs.getInt( "NULLABLE" ) );
if ( p.getParamType( ) == Types.OTHER )
correctParamType( p );
params.add( p );
}
}
finally
{
// Make sure result set is closed in case of error
if ( rs != null )
rs.close( );
}
}
Example 9
| Source File: SqlManager.java From aliyun-maxcompute-data-collectors with Apache License 2.0 | 5 votes |
|
@Override
public Map<String, String>
getColumnTypeNamesForProcedure(String procedureName) {
Map<String, String> ret = new TreeMap<String, String>();
try {
DatabaseMetaData metaData = this.getConnection().getMetaData();
ResultSet results = metaData.getProcedureColumns(null, null,
procedureName, null);
if (null == results) {
return null;
}
try {
while (results.next()) {
if (results.getInt("COLUMN_TYPE")
!= DatabaseMetaData.procedureColumnReturn
&& results.getInt("ORDINAL_POSITION") > 0) {
// we don't care if we get several rows for the
// same ORDINAL_POSITION (e.g. like H2 gives us)
// as we'll just overwrite the entry in the map:
ret.put(
results.getString("COLUMN_NAME"),
results.getString("TYPE_NAME"));
}
}
LOG.debug("Columns returned = " + StringUtils.join(ret.keySet(), ","));
LOG.debug(
"Type names returned = " + StringUtils.join(ret.values(), ","));
return ret.isEmpty() ? null : ret;
} finally {
results.close();
getConnection().commit();
}
} catch (SQLException sqlException) {
LoggingUtils.logAll(LOG, "Error reading primary key metadata: "
+ sqlException.toString(), sqlException);
return null;
}
}
Example 10
| Source File: Show.java From jsqsh with Apache License 2.0 | 5 votes |
|
private ResultSet doProcedure(Session session, Connection con, Options options)
throws SQLException {
if (options.arguments.size() < 2
|| options.arguments.size() > 3
|| !options.arguments.get(1).equalsIgnoreCase("params")) {
session.err.println("Use: \\show procedure params [-e] [-p param-pat] [[[catalog.]schema-pattern.]proc-pattern]");
return null;
}
if (options.essential) {
options.columns = essentialProcedureParamsCols;
}
SQLConnectionContext ctx = (SQLConnectionContext) session.getConnectionContext();
SQLObjectName name =
(options.arguments.size() == 3) ? new SQLObjectName(ctx, options.arguments.get(2))
: new SQLObjectName(ctx, "%");
DatabaseMetaData meta = con.getMetaData();
return meta.getProcedureColumns(
(options.catalog != null ? options.catalog : name.getCatalog()),
(options.schemaPattern != null ? options.schemaPattern : name.getSchema()),
(options.tablePattern != null ? options.tablePattern : name.getName()),
ctx.normalizeIdentifier(options.pattern));
}
Example 11
| Source File: DbStandard.java From syndesis with Apache License 2.0 | 5 votes |
|
@Override
public ResultSet fetchProcedureColumns(final DatabaseMetaData meta, final String catalog, final String schema, final String procedureName) throws SQLException {
return meta.getProcedureColumns(
catalog,
adaptPattern(schema),
adaptPattern(procedureName),
adaptPattern(null));
}
Example 12
| Source File: OracleManager.java From aliyun-maxcompute-data-collectors with Apache License 2.0 | 4 votes |
|
@Override
public Map<String, String>
getColumnTypeNamesForProcedure(String procedureName) {
Map<String, String> ret = new TreeMap<String, String>();
try {
DatabaseMetaData metaData = this.getConnection().getMetaData();
ResultSet results = metaData.getProcedureColumns(null, null,
procedureName, null);
if (null == results) {
return null;
}
try {
while (results.next()) {
if (results.getInt("COLUMN_TYPE")
!= DatabaseMetaData.procedureColumnReturn) {
int index = results.getInt("ORDINAL_POSITION");
if (index < 0) {
continue; // actually the return type
}
// we don't care if we get several rows for the
// same ORDINAL_POSITION (e.g. like H2 gives us)
// as we'll just overwrite the entry in the map:
ret.put(
results.getString("COLUMN_NAME"),
results.getString("TYPE_NAME"));
}
}
LOG.debug("Columns returned = " + StringUtils.join(ret.keySet(), ","));
LOG.debug(
"Type names returned = " + StringUtils.join(ret.values(), ","));
return ret.isEmpty() ? null : ret;
} finally {
results.close();
getConnection().commit();
}
} catch (SQLException sqlException) {
LoggingUtils.logAll(LOG, "Error reading primary key metadata: "
+ sqlException.toString(), sqlException);
return null;
}
}
Example 13
| Source File: MetaDataRegressionTest.java From Komondor with GNU General Public License v3.0 | 4 votes |
|
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());
}
Example 14
| Source File: LangProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
private static void checkMatchingProcedures(Connection conn,
String procedureName, String[] sysAliasDefinition,
String[] DBMetaDefinition, String[] columnDefinition)
throws SQLException {
// Until cs defaults to hold cursor we need to turn autocommit off
// while we do this because one metadata call will close the other's
// cursor
boolean saveAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
+ " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
ps.setString(1, procedureName);
ResultSet rs = ps.executeQuery();
int i = 0;
while (rs.next()) {
assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
+ rs.getString(2) + " AS " + rs.getString(3));
}
rs.close();
DatabaseMetaData dmd = conn.getMetaData();
rs = dmd.getProcedures(null, null, procedureName);
// with jcc 2.1 for now this will fail on the second round,
// because the resultset gets closed when we do getProcedureColumns.
// thus, catch that gracefully...
i = 0;
while (rs.next()) {
String schema = rs.getString(2);
String name = rs.getString(3);
assertEquals(schema + "." + name + " AS " + rs.getString(7)
+ " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
// get the column information.
ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
int j = 0;
while (rsc.next()) {
assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
+ rsc.getString(4) + " " + rsc.getString(7),
columnDefinition[j++]);
}
rsc.close();
}
rs.close();
// restore previous autocommit mode
conn.setAutoCommit(saveAutoCommit);
}
Example 15
| Source File: OracleManager.java From aliyun-maxcompute-data-collectors with Apache License 2.0 | 4 votes |
|
@Override
public String[] getColumnNamesForProcedure(String procedureName) {
List<String> ret = new ArrayList<String>();
try {
DatabaseMetaData metaData = this.getConnection().getMetaData();
ResultSet results = metaData.getProcedureColumns(null, null,
procedureName, null);
if (null == results) {
return null;
}
try {
while (results.next()) {
if (results.getInt("COLUMN_TYPE")
!= DatabaseMetaData.procedureColumnReturn) {
int index = results.getInt("ORDINAL_POSITION");
if (index < 0) {
continue; // actually the return type
}
for (int i = ret.size(); i < index; ++i) {
ret.add(null);
}
String name = results.getString("COLUMN_NAME");
if (index == ret.size()) {
ret.add(name);
} else {
ret.set(index, name);
}
}
}
String[] result = ret.toArray(new String[ret.size()]);
LOG.debug("getColumnsNamesForProcedure returns "
+ StringUtils.join(ret, ","));
return result;
} finally {
results.close();
getConnection().commit();
}
} catch (SQLException e) {
LoggingUtils.logAll(LOG, "Error reading procedure metadata: ", e);
throw new RuntimeException("Can't fetch column names for procedure.", e);
}
}
Example 16
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 4 votes |
|
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());
}
Example 17
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 4 votes |
|
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());
}
Example 18
| Source File: MetaDataRegressionTest.java From r-course with MIT License | 4 votes |
|
/**
* Tests fix for BUG#20727196 - GETPROCEDURECOLUMNS() RETURNS EXCEPTION FOR FUNCTION WHICH RETURNS ENUM/SET TYPE.
*
* Test duplicated in testsuite.regression.MetaDataRegressionTest.
*
* @throws Exception
* if the test fails.
*/
public void testBug20727196() throws Exception {
createFunction("testBug20727196_f1", "(p ENUM ('Yes', 'No')) RETURNS VARCHAR(10) DETERMINISTIC BEGIN RETURN IF(p='Yes', 'Yay!', if(p='No', 'Ney!', 'What?')); END");
createFunction("testBug20727196_f2", "(p CHAR(1)) RETURNS ENUM ('Yes', 'No') DETERMINISTIC BEGIN RETURN IF(p='y', 'Yes', if(p='n', 'No', '?')); END");
createFunction("testBug20727196_f3", "(p ENUM ('Yes', 'No')) RETURNS ENUM ('Yes', 'No') DETERMINISTIC BEGIN RETURN IF(p='Yes', 'Yes', if(p='No', 'No', '?')); END");
createProcedure("testBug20727196_p1", "(p ENUM ('Yes', 'No')) BEGIN SELECT IF(p='Yes', 'Yay!', if(p='No', 'Ney!', 'What?')); END");
for (String connProps : new String[] { "getProceduresReturnsFunctions=false,useInformationSchema=false",
"getProceduresReturnsFunctions=false,useInformationSchema=true" }) {
Connection testConn = null;
try {
testConn = getConnectionWithProps(connProps);
DatabaseMetaData dbmd = testConn.getMetaData();
this.rs = dbmd.getFunctionColumns(null, null, "testBug20727196_%", "%");
// testBug20727196_f1 columns:
assertTrue(this.rs.next());
assertEquals("testBug20727196_f1", this.rs.getString(3));
assertEquals("", this.rs.getString(4));
assertEquals("VARCHAR", this.rs.getString(7));
assertTrue(this.rs.next());
assertEquals("testBug20727196_f1", this.rs.getString(3));
assertEquals("p", this.rs.getString(4));
assertEquals("ENUM", this.rs.getString(7));
// testBug20727196_f2 columns:
assertTrue(this.rs.next());
assertEquals("testBug20727196_f2", this.rs.getString(3));
assertEquals("", this.rs.getString(4));
assertEquals("ENUM", this.rs.getString(7));
assertTrue(this.rs.next());
assertEquals("testBug20727196_f2", this.rs.getString(3));
assertEquals("p", this.rs.getString(4));
assertEquals("CHAR", this.rs.getString(7));
// testBug20727196_f3 columns:
assertTrue(this.rs.next());
assertEquals("testBug20727196_f3", this.rs.getString(3));
assertEquals("", this.rs.getString(4));
assertEquals("ENUM", this.rs.getString(7));
assertTrue(this.rs.next());
assertEquals("testBug20727196_f3", this.rs.getString(3));
assertEquals("p", this.rs.getString(4));
assertEquals("ENUM", this.rs.getString(7));
assertFalse(this.rs.next());
this.rs = dbmd.getProcedureColumns(null, null, "testBug20727196_%", "%");
// testBug20727196_p1 columns:
assertTrue(this.rs.next());
assertEquals("testBug20727196_p1", this.rs.getString(3));
assertEquals("p", this.rs.getString(4));
assertEquals("ENUM", this.rs.getString(7));
assertFalse(this.rs.next());
} finally {
if (testConn != null) {
testConn.close();
}
}
}
}
Example 19
| Source File: LangProcedureTest.java From spliceengine with GNU Affero General Public License v3.0 | 4 votes |
|
private static void checkMatchingProcedures(Connection conn,
String procedureName, String[] sysAliasDefinition,
String[] DBMetaDefinition, String[] columnDefinition)
throws SQLException {
// Until cs defaults to hold cursor we need to turn autocommit off
// while we do this because one metadata call will close the other's
// cursor
boolean saveAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
+ " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
ps.setString(1, procedureName);
ResultSet rs = ps.executeQuery();
int i = 0;
while (rs.next()) {
assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
+ rs.getString(2) + " AS " + rs.getString(3));
}
rs.close();
DatabaseMetaData dmd = conn.getMetaData();
rs = dmd.getProcedures(null, null, procedureName);
// with jcc 2.1 for now this will fail on the second round,
// because the resultset gets closed when we do getProcedureColumns.
// thus, catch that gracefully...
i = 0;
while (rs.next()) {
String schema = rs.getString(2);
String name = rs.getString(3);
assertEquals(schema + "." + name + " AS " + rs.getString(7)
+ " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
// get the column information.
ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
int j = 0;
while (rsc.next()) {
assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
+ rsc.getString(4) + " " + rsc.getString(7),
columnDefinition[j++]);
}
rsc.close();
}
rs.close();
// restore previous autocommit mode
conn.setAutoCommit(saveAutoCommit);
}
Example 20
| Source File: LangProcedureTest.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
|
private static void checkMatchingProcedures(Connection conn,
String procedureName, String[] sysAliasDefinition,
String[] DBMetaDefinition, String[] columnDefinition)
throws SQLException {
// Until cs defaults to hold cursor we need to turn autocommit off
// while we do this because one metadata call will close the other's
// cursor
boolean saveAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("select schemaname, alias, CAST (((javaclassname || '.' ) || CAST (aliasinfo AS VARCHAR(1000))) AS VARCHAR(2000)) AS SIGNATURE "
+ " from sys.sysaliases A, sys.sysschemas S where alias like ? and A.schemaid = S.schemaid ORDER BY 1,2,3");
ps.setString(1, procedureName);
ResultSet rs = ps.executeQuery();
int i = 0;
while (rs.next()) {
assertEquals(sysAliasDefinition[i++], rs.getString(1) + "."
+ rs.getString(2) + " AS " + rs.getString(3));
}
rs.close();
DatabaseMetaData dmd = conn.getMetaData();
rs = dmd.getProcedures(null, null, procedureName);
// with jcc 2.1 for now this will fail on the second round,
// because the resultset gets closed when we do getProcedureColumns.
// thus, catch that gracefully...
i = 0;
while (rs.next()) {
String schema = rs.getString(2);
String name = rs.getString(3);
assertEquals(schema + "." + name + " AS " + rs.getString(7)
+ " type " + TYPE(rs.getShort(8)), DBMetaDefinition[i++]);
// get the column information.
ResultSet rsc = dmd.getProcedureColumns(null, schema, name, null);
int j = 0;
while (rsc.next()) {
assertEquals(PARAMTYPE(rsc.getShort(5)) + " "
+ rsc.getString(4) + " " + rsc.getString(7),
columnDefinition[j++]);
}
rsc.close();
}
rs.close();
// restore previous autocommit mode
conn.setAutoCommit(saveAutoCommit);
}
