Java User Group Berlin Brandenburg

	/**
	 * load procedure and functions
	 * 
	 * @param schema
	 *            object that represent a schema
	 * @return true if everything went well
	 */
	public boolean loadProcedures(SQLSchema schema) {
		if (logger.isDebugEnabled()) {
			logger.debug("loadProcedures schema=" + schema);
		}
		// makes this thread save
		inRefreshCycle = true;
		boolean ok = false;
		// don' worry about that, we use our own database pool
		final DatabaseSession session = DatabaseSessionPool.getDatabaseSession(cd.getUniqueId());
		if (session == null) {
			// return if no connection to database avialable
			return false;
		}
		try {
			Connection conn = session.getConnection();
			DatabaseMetaData dbmd = conn.getMetaData();
			schema.clearProcedures(); // remove any previously loaded procedures
			ResultSet rs = dbmd.getProcedures(null, schema.getName(), null);
			if (rs != null) {
				// loading all procedures and functions
				while (rs.next()) {
					// PROCEDURE_CAT contains the package name
					String catalogName = rs.getString("PROCEDURE_CAT");
					String name = rs.getString("PROCEDURE_NAME");
					if (catalogName != null && catalogName.length() > 0) {
						name = catalogName + "." + name;
					}
					// create an new one
					SQLProcedure procedure = new SQLProcedure(this, schema,	name);
					// decide if it is a function
					short pType = rs.getShort("PROCEDURE_TYPE");
					procedure.setFunction(pType == DatabaseMetaData.procedureReturnsResult);
					schema.addProcedure(procedure);
					objectMap.put(name, procedure);
				}
				rs.close();
			}
			// loading procedure/function parameters
			rs = dbmd.getProcedureColumns(null, schema.getName(), null, null);
			if (rs != null) {
				// an index for overloaded procedures
				int procedureIndex = 0;
				String prevProcedureName = "noprocedurehere_xx";
				while (rs.next()) {
					// PROCEDURE_CAT contains the package name
					String catalogName = rs.getString("PROCEDURE_CAT");
					String name = rs.getString("PROCEDURE_NAME");
					if (catalogName != null && catalogName.length() > 0) {
						// add the catalog name as package name
						name = catalogName + "." + name;
					}
					int pos = 0;
					try {
						// position of this parameter in the procedure parameter
						// list
						pos = rs.getInt("ORDINAL_POSITION");
					} catch (Exception e) {
						// perhaps this column is not defined in every database
						// type
						pos = -1;
					}
					if (prevProcedureName.equals(name)) {
						if (pos == 0) {
							// if we get the same procedure name and a parameter
							// with position zero increase the procedure index
							// that means point to the next procedure with the
							// same name
							procedureIndex++;
						}
					} else {
						procedureIndex = 0;
					}
					String columnName = rs.getString("COLUMN_NAME");
					List list = schema.getProcedures(name);
					SQLProcedure procedure = list.get(procedureIndex);
					if (procedure != null) {
						procedure.addParameter(columnName, // parameter name
								rs.getShort("DATA_TYPE"),  // parameter type as
														   // integer defined
														   // in class
														   // java.sql.Type
								rs.getString("TYPE_NAME"), // parameter type as
														   // string
								rs.getInt("LENGTH"),    // length of the parameter
														// if parameter is
														// string or number type
								rs.getInt("PRECISION"), // precision of the
														// parameter if the
														// parameter is a number
														// type
								rs.getShort("COLUMN_TYPE")); // input or output
														     // parameter as
														     // integer:
															 // refer
															 // DatabaseMetaData.procedureColumnReturn
					}
					// keep the name of the last procedure to detect overloaded
					// procedures
					prevProcedureName = name;
				}
				rs.close();
			}
			schema.setProcedureLoaded();
			ok = true;
		} catch (SQLException sqle) {
			// creates a error message to show it in GUI
			errorMessage = "loadProcedures schema=" + schema + " failed: "
					+ sqle.getMessage();
		} finally {
			// put the session back in the pool
			DatabaseSessionPool.release(session);
		}
		inRefreshCycle = false;
		return ok;
	}