List of usage examples for java.sql DatabaseMetaData procedureColumnIn
int procedureColumnIn
To view the source code for java.sql DatabaseMetaData procedureColumnIn.
Click Source Link
From source file:com.netspective.axiom.sql.StoredProcedure.java
/** * Gets the stored procedure's metadata information from the database. This will search * all available catalogs and schemas. This method will ONLY return the metadata of the * stored procedure only when the <i>procedure-name</i> attribute is set in the XML declaration. *///from w w w.j a v a 2s . com public String getMetaData(ConnectionContext cc) throws NamingException, SQLException { // TODO : Using this metadata, we can determine what variables are in and out so that the developer doesn't even have to set it in XML // but currently the procedure-name attribute isn't required but the 'type' attribute is required. If we go the // metadata route we need to change some handling to accept setting the 'type' and if it's not set, we can use // the metadata to get the param type StringBuffer sb = new StringBuffer(); if (procedureName != null && procedureName.length() > 0) { // Get DatabaseMetaData Connection connection = cc.getConnection(); DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getProcedureColumns(null, null, procedureName, "%"); // Printout table data while (rs.next()) { // Get procedure metadata String dbProcedureCatalog = rs.getString(1); String dbProcedureSchema = rs.getString(2); String dbProcedureName = rs.getString(3); String dbColumnName = rs.getString(4); short dbColumnReturn = rs.getShort(5); String dbColumnReturnTypeName = rs.getString(7); int dbColumnPrecision = rs.getInt(8); int dbColumnByteLength = rs.getInt(9); short dbColumnScale = rs.getShort(10); short dbColumnRadix = rs.getShort(11); String dbColumnRemarks = rs.getString(13); // Interpret the return type (readable for humans) String procReturn; switch (dbColumnReturn) { case DatabaseMetaData.procedureColumnIn: procReturn = "In"; break; case DatabaseMetaData.procedureColumnOut: procReturn = "Out"; break; case DatabaseMetaData.procedureColumnInOut: procReturn = "In/Out"; break; case DatabaseMetaData.procedureColumnReturn: procReturn = "return value"; break; case DatabaseMetaData.procedureColumnResult: procReturn = "return ResultSet"; default: procReturn = "Unknown"; } // Printout sb.append("Procedure: " + dbProcedureCatalog + "." + dbProcedureSchema + "." + dbProcedureName); sb.append(" ColumnName [ColumnType(ColumnPrecision)]: " + dbColumnName + " [" + dbColumnReturnTypeName + "(" + dbColumnPrecision + ")]"); sb.append(" ColumnReturns: " + procReturn + "(" + dbColumnReturnTypeName + ")"); sb.append(" Radix: " + dbColumnRadix + ", Scale: " + dbColumnScale); sb.append(" Remarks: " + dbColumnRemarks); } rs.close(); connection.close(); } return sb.toString(); }
From source file:org.apache.cayenne.access.DbLoader.java
private static int getDirection(short type) { switch (type) { case DatabaseMetaData.procedureColumnIn: return ProcedureParameter.IN_PARAMETER; case DatabaseMetaData.procedureColumnInOut: return ProcedureParameter.IN_OUT_PARAMETER; case DatabaseMetaData.procedureColumnOut: return ProcedureParameter.OUT_PARAMETER; default:/*from www .ja v a 2 s.c o m*/ return -1; } }
From source file:org.apache.cayenne.dbsync.reverse.dbload.ProcedureColumnLoader.java
private int getDirection(short type) { switch (type) { case DatabaseMetaData.procedureColumnIn: return ProcedureParameter.IN_PARAMETER; case DatabaseMetaData.procedureColumnInOut: return ProcedureParameter.IN_OUT_PARAMETER; case DatabaseMetaData.procedureColumnOut: return ProcedureParameter.OUT_PARAMETER; default:/*w w w . j av a 2s . c o m*/ return -1; } }
From source file:org.apache.ddlutils.task.DumpMetadataTask.java
/** * Dumps the contents of the indicated procedure. * //from ww w .j a va 2 s . c o m * @param xmlWriter The xml writer to write to * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param procedureName The procedure name */ private void dumpProcedure(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData, final String catalogName, final String schemaName, final String procedureName) throws SQLException { performResultSetXmlOperation(xmlWriter, null, new ResultSetXmlOperation() { public ResultSet getResultSet() throws SQLException { return metaData.getProcedureColumns(catalogName, schemaName, procedureName, _columnPattern); } public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException { Set columns = getColumnsInResultSet(result); String columnName = result.getString("COLUMN_NAME"); if ((columnName != null) && (columnName.length() > 0)) { xmlWriter.writeElementStart(null, "column"); xmlWriter.writeAttribute(null, "name", columnName); if (columns.contains("COLUMN_TYPE")) { try { switch (result.getShort("COLUMN_TYPE")) { case DatabaseMetaData.procedureColumnIn: xmlWriter.writeAttribute(null, "type", "in parameter"); break; case DatabaseMetaData.procedureColumnInOut: xmlWriter.writeAttribute(null, "type", "in/out parameter"); break; case DatabaseMetaData.procedureColumnOut: xmlWriter.writeAttribute(null, "type", "out parameter"); break; case DatabaseMetaData.procedureColumnReturn: xmlWriter.writeAttribute(null, "type", "return value"); break; case DatabaseMetaData.procedureColumnResult: xmlWriter.writeAttribute(null, "type", "result column in ResultSet"); break; default: xmlWriter.writeAttribute(null, "type", "unknown"); break; } } catch (SQLException ex) { log("Could not read the COLUMN_TYPE value for the column '" + columnName + "' of procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } } addIntAttribute(xmlWriter, "typeCode", result, columns, "DATA_TYPE"); addStringAttribute(xmlWriter, "type", result, columns, "TYPE_NAME"); addIntAttribute(xmlWriter, "length", result, columns, "LENGTH"); addIntAttribute(xmlWriter, "precision", result, columns, "PRECISION"); addShortAttribute(xmlWriter, "short", result, columns, "SCALE"); addShortAttribute(xmlWriter, "radix", result, columns, "RADIX"); if (columns.contains("NULLABLE")) { try { switch (result.getInt("NULLABLE")) { case DatabaseMetaData.procedureNoNulls: xmlWriter.writeAttribute(null, "nullable", "false"); break; case DatabaseMetaData.procedureNullable: xmlWriter.writeAttribute(null, "nullable", "true"); break; default: xmlWriter.writeAttribute(null, "nullable", "unknown"); break; } } catch (SQLException ex) { log("Could not read the NULLABLE value for the column '" + columnName + "' of procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } } addStringAttribute(xmlWriter, "remarks", result, columns, "REMARKS"); } } public void handleError(SQLException ex) { log("Could not read the columns for procedure '" + procedureName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR); } }); }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/*from w w w. ja v a 2 s. c o m*/ */ public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException { if (!prepared()) { return statementResult; } ProcedureParameter[] param = databaseExecutable.getParametersArray(); Arrays.sort(param, new ProcedureParameterSorter()); String procQuery = null; boolean hasOut = false; boolean hasParameters = (param != null && param.length > 0); List<ProcedureParameter> outs = null; List<ProcedureParameter> ins = null; if (hasParameters) { // split the params into ins and outs outs = new ArrayList<ProcedureParameter>(); ins = new ArrayList<ProcedureParameter>(); int type = -1; for (int i = 0; i < param.length; i++) { type = param[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // add to the ins list ins.add(param[i]); } else if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { // add to the outs list outs.add(param[i]); } } char QUESTION_MARK = '?'; String COMMA = ", "; // init the string buffer StringBuilder sb = new StringBuilder("{ "); if (!outs.isEmpty()) { // build the out params place holders for (int i = 0, n = outs.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" = "); } sb.append(" call "); if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) { String namePrefix = null; if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getCatalogName(); } if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getSchemaName(); } if (namePrefix != null) { sb.append(namePrefix).append('.'); } } sb.append(databaseExecutable.getName()).append("( "); // build the ins params place holders for (int i = 0, n = ins.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" ) }"); // determine if we have out params hasOut = !(outs.isEmpty()); procQuery = sb.toString(); } else { StringBuilder sb = new StringBuilder(); sb.append("{ call "); if (databaseExecutable.getSchemaName() != null) { sb.append(databaseExecutable.getSchemaName()).append('.'); } sb.append(databaseExecutable.getName()).append("( ) }"); procQuery = sb.toString(); } //Log.debug(procQuery); // null value literal String NULL = "null"; // clear any warnings conn.clearWarnings(); Log.info("Executing: " + procQuery); CallableStatement cstmnt = null; try { // prepare the statement cstmnt = conn.prepareCall(procQuery); stmnt = cstmnt; } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); return statementResult; } // check if we are passing parameters if (hasParameters) { // the parameter index counter int index = 1; // the java.sql.Type value int dataType = -1; // the parameter input value String value = null; // register the out params for (int i = 0, n = outs.size(); i < n; i++) { //Log.debug("setting out at index: " + index); cstmnt.registerOutParameter(index, outs.get(i).getDataType()); index++; } try { // register the in params for (int i = 0, n = ins.size(); i < n; i++) { ProcedureParameter procedureParameter = ins.get(i); value = procedureParameter.getValue(); dataType = procedureParameter.getDataType(); // try infer a type if OTHER if (dataType == Types.OTHER) { // checking only for bit/bool for now if (isTrueFalse(value)) { dataType = Types.BOOLEAN; } else if (isBit(value)) { dataType = Types.BIT; value = value.substring(2, value.length() - 1); } } if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) { cstmnt.setNull(index, dataType); } else { switch (dataType) { case Types.TINYINT: byte _byte = Byte.valueOf(value).byteValue(); cstmnt.setShort(index, _byte); break; case Types.SMALLINT: short _short = Short.valueOf(value).shortValue(); cstmnt.setShort(index, _short); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: cstmnt.setString(index, value); break; case Types.BIT: case Types.BOOLEAN: boolean _boolean = false; if (NumberUtils.isNumber(value)) { int number = Integer.valueOf(value); if (number > 0) { _boolean = true; } } else { _boolean = Boolean.valueOf(value).booleanValue(); } cstmnt.setBoolean(index, _boolean); break; case Types.BIGINT: long _long = Long.valueOf(value).longValue(); cstmnt.setLong(index, _long); break; case Types.INTEGER: int _int = Integer.valueOf(value).intValue(); cstmnt.setInt(index, _int); break; case Types.REAL: float _float = Float.valueOf(value).floatValue(); cstmnt.setFloat(index, _float); break; case Types.NUMERIC: case Types.DECIMAL: cstmnt.setBigDecimal(index, new BigDecimal(value)); break; /* case Types.DATE: case Types.TIMESTAMP: case Types.TIME: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value)); */ case Types.FLOAT: case Types.DOUBLE: double _double = Double.valueOf(value).doubleValue(); cstmnt.setDouble(index, _double); break; default: cstmnt.setObject(index, value); } } // increment the index index++; } } catch (Exception e) { statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage()); return statementResult; } } /* test creating function for postgres: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; */ try { cstmnt.clearWarnings(); boolean hasResultSet = cstmnt.execute(); Map<String, Object> results = new HashMap<String, Object>(); if (hasOut) { // incrementing index int index = 1; // return value from each registered out String returnValue = null; for (int i = 0; i < param.length; i++) { int type = param[i].getType(); int dataType = param[i].getDataType(); if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { switch (dataType) { case Types.TINYINT: returnValue = Byte.toString(cstmnt.getByte(index)); break; case Types.SMALLINT: returnValue = Short.toString(cstmnt.getShort(index)); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: returnValue = cstmnt.getString(index); break; case Types.BIT: case Types.BOOLEAN: returnValue = Boolean.toString(cstmnt.getBoolean(index)); break; case Types.INTEGER: returnValue = Integer.toString(cstmnt.getInt(index)); break; case Types.BIGINT: returnValue = Long.toString(cstmnt.getLong(index)); break; case Types.REAL: returnValue = Float.toString(cstmnt.getFloat(index)); break; case Types.NUMERIC: case Types.DECIMAL: returnValue = cstmnt.getBigDecimal(index).toString(); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: returnValue = cstmnt.getDate(index).toString(); break; case Types.FLOAT: case Types.DOUBLE: returnValue = Double.toString(cstmnt.getDouble(index)); break; } if (returnValue == null) { returnValue = "NULL"; } results.put(param[i].getName(), returnValue); index++; } } } if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } useCount++; statementResult.setOtherResult(results); } catch (SQLException e) { statementResult.setSqlException(e); } catch (Exception e) { statementResult.setMessage(e.getMessage()); } return statementResult; }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure and returns * a <code>ResultSet</code> object from this query. * <p>If an exception occurs, null is returned and * the relevant error message, if available, assigned * to this object for retrieval./* w ww . j a va2s . c om*/ * * @param the SQL procedure to execute * @return the query result */ private SqlStatementResult executeProcedure(String query) throws SQLException { if (!prepared()) { return statementResult; } //Log.debug("query " + query); String execString = "EXECUTE "; String callString = "CALL "; int nameIndex = -1; int index = query.toUpperCase().indexOf(execString); // check if EXECUTE was entered if (index != -1) { nameIndex = execString.length(); } else { // must be CALL nameIndex = callString.length(); } String procedureName = null; // check for input brackets boolean possibleParams = false; index = query.indexOf("(", nameIndex); if (index != -1) { possibleParams = true; procedureName = query.substring(nameIndex, index); } else { procedureName = query.substring(nameIndex); } String prefix = prefixFromName(procedureName); procedureName = suffixFromName(procedureName); DatabaseHost host = new DatabaseObjectFactoryImpl().createDatabaseHost(databaseConnection); if (prefix == null) { prefix = host.getDefaultNamePrefix(); } DatabaseExecutable procedure = host.getDatabaseSource(prefix).getProcedure(procedureName); if (procedure == null) { // hedge procedure = host.getDatabaseSource(prefix).getFunction(procedureName); } if (procedure != null) { if (possibleParams) { String params = query.substring(index + 1, query.indexOf(")")); if (!MiscUtils.isNull(params)) { // check that the proc accepts params // if (!procedure.hasParameters()) { // // statementResult.setSqlException(new SQLException("Procedure call was invalid")); // return statementResult; // } int paramIndex = 0; ProcedureParameter[] parameters = procedure.getParametersArray(); // extract the parameters StringTokenizer st = new StringTokenizer(params, ","); // no defined params from the meta data but params supplied ?? // attempt to execute as supplied and bubble up db error if an issue if (parameters.length == 0) { parameters = new ProcedureParameter[st.countTokens()]; for (int i = 0, n = st.countTokens(); i < n; i++) { procedure.addParameter("UNKNOWN", DatabaseMetaData.procedureColumnIn, Types.OTHER, "OTHER", -1); } parameters = procedure.getParametersArray(); } while (st.hasMoreTokens()) { String value = st.nextToken().trim(); // check applicable param for (int i = paramIndex; i < parameters.length; i++) { paramIndex++; int type = parameters[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // check the data type and remove quotes if char int dataType = parameters[i].getDataType(); if (dataType == Types.CHAR || dataType == Types.VARCHAR || dataType == Types.LONGVARCHAR) { if (value.indexOf("'") != -1) { // assuming quotes at start and end value = value.substring(1, value.length() - 1); } } parameters[i].setValue(value); break; } } } } } // execute the procedure return execute(procedure); } else { // just run it... CallableStatement cstmnt = null; try { cstmnt = conn.prepareCall(query); boolean hasResultSet = cstmnt.execute(); if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); } return statementResult; /* statementResult.setSqlException( new SQLException("Procedure or Function name specified is invalid")); return statementResult; */ } }
From source file:org.openadaptor.auxil.connector.jdbc.writer.AbstractSQLWriter.java
/** * Get the types of the args of a stored proc. * <br>/*from w w w .ja va 2 s . c o m*/ * From javadoc on DatabaseMetaData.getProcedureColumns() * <pre> * 1. PROCEDURE_CAT String => procedure catalog (may be null) * 2. PROCEDURE_SCHEM String => procedure schema (may be null) * 3. PROCEDURE_NAME String => procedure name * 4. COLUMN_NAME String => column/parameter name * 5. COLUMN_TYPE Short => kind of column/parameter: * * procedureColumnUnknown - nobody knows * * procedureColumnIn - IN parameter * * procedureColumnInOut - INOUT parameter * * procedureColumnOut - OUT parameter * * procedureColumnReturn - procedure return value * * procedureColumnResult - result column in ResultSet * 6. DATA_TYPE int => SQL type from java.sql.Types * 7. TYPE_NAME String => SQL type name, for a UDT type the type name is fully qualified * 8. PRECISION int => precision * 9. LENGTH int => length in bytes of data *10. SCALE short => scale *11. RADIX short => radix *12. NULLABLE short => can it contain NULL. * * procedureNoNulls - does not allow NULL values * * procedureNullable - allows NULL values * * procedureNullableUnknown - nullability unknown *13. REMARKS String => comment describing parameter/column * *</pre> * */ protected int[] getStoredProcArgumentTypes(String storedProcName, Connection connection) throws SQLException { //Fix for #SC36: MapCallableStatementWriter misses first argument for Oracle databases // Now it checks each columnType, and only includes IN or INOUT types. // ToDo: Further validation of this approach. Perhaps OUT should also be included? DatabaseMetaData dmd = connection.getMetaData(); List sqlTypeList = new ArrayList(); String catalog = connection.getCatalog(); String schema = "%"; String proc = storedProcName; String column = "%"; log.debug("Catalog for stored proc " + storedProcName + " is " + catalog); ResultSet rs; //Oracle doesn't bother with catalogs at all :-( //Thus if it's an oracle db, we may need to substitute package name instead //of catalog. if ((catalog == null) && (oraclePackage != null)) { //oraclePackage will only be non-null for oracle db log.debug("Setting catalog to oracle package of: " + oraclePackage); catalog = oraclePackage; schema = null;//Oracle 'ignore' setting. Probably the same as "%" anyway. } //Check if there's a schema reference in the proc name... String[] components = storedProcName.split("\\."); int len = components.length; if (len > 1) { schema = components[len - 2]; proc = components[len - 1]; } log.debug("Resolving proc - catalog=" + catalog + ";schema=" + schema + ";proc=" + proc + ";column=" + column); rs = dmd.getProcedureColumns(catalog, schema, proc, column); //If RS is empty, then we have failed in our mission. if (!rs.next()) { //First rs is return value. rs.close(); String msg = "Failed to lookup stored procedure " + storedProcName; log.warn(msg); throw new SQLException(msg); } do { //Verify that each argument is an IN or INOUT arg type. int type = rs.getInt(5); //Need to check if it is a result, or an input arg. int dataType = rs.getInt(6); // DATA_TYPE is column six! if (log.isDebugEnabled()) { log.debug("Catalog=" + rs.getString(1) + "; Schema=" + rs.getString(2) + "; Proc=" + rs.getString(3) + "; Column=" + rs.getString(4) + "; ParamType=" + spTypeToString(type) + "(" + type + ")" + "; DataType=" + dataType + "; TypeName=" + rs.getString(7)); } if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { log.debug("Argument of type " + type + " is IN or INOUT"); sqlTypeList.add(Integer.valueOf(dataType)); // DATA_TYPE is column six! } else { log.debug("Ignoring column of type " + type + " as it is neither IN nor INOUT"); } } while (rs.next()); log.debug("Number of stored procedure parameters found: " + sqlTypeList.size()); int[] sqlTypes = new int[sqlTypeList.size()]; for (int i = 0; i < sqlTypes.length; i++) { sqlTypes[i] = ((Integer) sqlTypeList.get(i)).intValue(); } rs.close(); return sqlTypes; }
From source file:org.openadaptor.auxil.connector.jdbc.writer.AbstractSQLWriter.java
private static final String spTypeToString(int type) { String result;//from w ww . jav a 2 s.com switch (type) { case DatabaseMetaData.procedureColumnUnknown: // 0 result = "unknown"; break; case DatabaseMetaData.procedureColumnIn: // 1 result = "IN"; break; case DatabaseMetaData.procedureColumnInOut: // 2 result = "INOUT"; break; case DatabaseMetaData.procedureColumnOut: //3 result = "OUT"; break; case DatabaseMetaData.procedureColumnReturn: // 4 result = "RETURN"; break; case DatabaseMetaData.procedureColumnResult: // 5 result = "RESULT"; break; default: //This *should* never arise result = "Illegal value for procedureColumn type"; break; } return result; }
From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfProcedureExtractor.java
protected void setupProcedureColumnMetaInfo(DfProcedureMeta procedureMetaInfo, ResultSet columnRs) throws SQLException { final Set<String> uniqueSet = new HashSet<String>(); while (columnRs.next()) { // /- - - - - - - - - - - - - - - - - - - - - - - - // same policy as table process about JDBC handling // (see DfTableHandler.java) // - - - - - - - - - -/ final String columnName = columnRs.getString("COLUMN_NAME"); // filter duplicated informations // because Oracle package procedure may return them if (uniqueSet.contains(columnName)) { continue; }//from www .ja v a 2s . com uniqueSet.add(columnName); final Integer procedureColumnType; { final String columnType = columnRs.getString("COLUMN_TYPE"); final int unknowType = DatabaseMetaData.procedureColumnUnknown; if (Srl.is_NotNull_and_NotTrimmedEmpty(columnType)) { procedureColumnType = toInt("columnType", columnType); } else { procedureColumnType = unknowType; } } final int jdbcType; { int tmpJdbcType = Types.OTHER; String dataType = null; try { dataType = columnRs.getString("DATA_TYPE"); } catch (RuntimeException ignored) { // pinpoint patch // for example, SQLServer throws an exception // if the procedure is a function that returns table type final String procdureName = procedureMetaInfo.getProcedureFullQualifiedName(); log("*Failed to get data type: " + procdureName + "." + columnName); tmpJdbcType = Types.OTHER; } if (Srl.is_NotNull_and_NotTrimmedEmpty(dataType)) { tmpJdbcType = toInt("dataType", dataType); } jdbcType = tmpJdbcType; } final String dbTypeName = columnRs.getString("TYPE_NAME"); // uses getString() to get null value // (getInt() returns zero when a value is no defined) final Integer columnSize; { final String precision = columnRs.getString("PRECISION"); if (Srl.is_NotNull_and_NotTrimmedEmpty(precision)) { columnSize = toInt("precision", precision); } else { final String length = columnRs.getString("LENGTH"); if (Srl.is_NotNull_and_NotTrimmedEmpty(length)) { columnSize = toInt("length", length); } else { columnSize = null; } } } final Integer decimalDigits; { final String scale = columnRs.getString("SCALE"); if (Srl.is_NotNull_and_NotTrimmedEmpty(scale)) { decimalDigits = toInt("scale", scale); } else { decimalDigits = null; } } final String columnComment = columnRs.getString("REMARKS"); final DfProcedureColumnMeta procedureColumnMetaInfo = new DfProcedureColumnMeta(); procedureColumnMetaInfo.setColumnName(columnName); if (procedureColumnType == DatabaseMetaData.procedureColumnUnknown) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnUnknown); } else if (procedureColumnType == DatabaseMetaData.procedureColumnIn) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnIn); } else if (procedureColumnType == DatabaseMetaData.procedureColumnInOut) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnInOut); } else if (procedureColumnType == DatabaseMetaData.procedureColumnOut) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnOut); } else if (procedureColumnType == DatabaseMetaData.procedureColumnReturn) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnReturn); } else if (procedureColumnType == DatabaseMetaData.procedureColumnResult) { procedureColumnMetaInfo.setProcedureColumnType(DfProcedureColumnType.procedureColumnResult); } else { throw new IllegalStateException("Unknown procedureColumnType: " + procedureColumnType); } procedureColumnMetaInfo.setJdbcDefType(jdbcType); procedureColumnMetaInfo.setDbTypeName(dbTypeName); procedureColumnMetaInfo.setColumnSize(columnSize); procedureColumnMetaInfo.setDecimalDigits(decimalDigits); procedureColumnMetaInfo.setColumnComment(columnComment); procedureMetaInfo.addProcedureColumn(procedureColumnMetaInfo); } adjustProcedureColumnList(procedureMetaInfo); }
From source file:org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.java
/** * Process the procedure column metadata */// www .ja v a2 s . c o m private void processProcedureColumns(DatabaseMetaData databaseMetaData, @Nullable String catalogName, @Nullable String schemaName, @Nullable String procedureName) { String metaDataCatalogName = metaDataCatalogNameToUse(catalogName); String metaDataSchemaName = metaDataSchemaNameToUse(schemaName); String metaDataProcedureName = procedureNameToUse(procedureName); if (logger.isDebugEnabled()) { logger.debug("Retrieving metadata for " + metaDataCatalogName + '/' + metaDataSchemaName + '/' + metaDataProcedureName); } ResultSet procs = null; try { procs = databaseMetaData.getProcedures(metaDataCatalogName, metaDataSchemaName, metaDataProcedureName); List<String> found = new ArrayList<>(); while (procs.next()) { found.add(procs.getString("PROCEDURE_CAT") + '.' + procs.getString("PROCEDURE_SCHEM") + '.' + procs.getString("PROCEDURE_NAME")); } procs.close(); if (found.size() > 1) { throw new InvalidDataAccessApiUsageException( "Unable to determine the correct call signature - multiple " + "procedures/functions/signatures for '" + metaDataProcedureName + "': found " + found); } else if (found.isEmpty()) { if (metaDataProcedureName != null && metaDataProcedureName.contains(".") && !StringUtils.hasText(metaDataCatalogName)) { String packageName = metaDataProcedureName.substring(0, metaDataProcedureName.indexOf(".")); throw new InvalidDataAccessApiUsageException( "Unable to determine the correct call signature for '" + metaDataProcedureName + "' - package name should be specified separately using '.withCatalogName(\"" + packageName + "\")'"); } else if ("Oracle".equals(databaseMetaData.getDatabaseProductName())) { if (logger.isDebugEnabled()) { logger.debug("Oracle JDBC driver did not return procedure/function/signature for '" + metaDataProcedureName + "' - assuming a non-exposed synonym"); } } else { throw new InvalidDataAccessApiUsageException( "Unable to determine the correct call signature - no " + "procedure/function/signature for '" + metaDataProcedureName + "'"); } } procs = databaseMetaData.getProcedureColumns(metaDataCatalogName, metaDataSchemaName, metaDataProcedureName, null); while (procs.next()) { String columnName = procs.getString("COLUMN_NAME"); int columnType = procs.getInt("COLUMN_TYPE"); if (columnName == null && (columnType == DatabaseMetaData.procedureColumnIn || columnType == DatabaseMetaData.procedureColumnInOut || columnType == DatabaseMetaData.procedureColumnOut)) { if (logger.isDebugEnabled()) { logger.debug("Skipping metadata for: " + columnType + " " + procs.getInt("DATA_TYPE") + " " + procs.getString("TYPE_NAME") + " " + procs.getInt("NULLABLE") + " (probably a member of a collection)"); } } else { CallParameterMetaData meta = new CallParameterMetaData(columnName, columnType, procs.getInt("DATA_TYPE"), procs.getString("TYPE_NAME"), procs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable); this.callParameterMetaData.add(meta); if (logger.isDebugEnabled()) { logger.debug("Retrieved metadata: " + meta.getParameterName() + " " + meta.getParameterType() + " " + meta.getSqlType() + " " + meta.getTypeName() + " " + meta.isNullable()); } } } } catch (SQLException ex) { if (logger.isWarnEnabled()) { logger.warn("Error while retrieving metadata for procedure columns: " + ex); } } finally { try { if (procs != null) { procs.close(); } } catch (SQLException ex) { if (logger.isWarnEnabled()) { logger.warn("Problem closing ResultSet for procedure column metadata: " + ex); } } } }