package edu.mit.is.warehouse.financial.reports.sao.beans; public class Utilities { private static final java.util.Date date = new java.util.Date(); private static ConnectionBean globalConnectionBean = null; public static java.util.Properties getPropertiesFromPRB(final String prbName) throws Exception { java.util.Properties properties = null ; java.util.PropertyResourceBundle prb = (java.util.PropertyResourceBundle) java.util.ResourceBundle.getBundle(prbName); return getPropertiesFromPRB(prb); } public static java.util.Properties getPropertiesFromPRB(final java.util.PropertyResourceBundle prb) { java.util.Properties properties = null ; if (prb != null) { properties = new java.util.Properties(); java.util.Enumeration keys = prb.getKeys(); while (keys.hasMoreElements()) { String key = (String) (keys.nextElement()); properties.setProperty(key, (String) prb.handleGetObject(key)); } } return properties; } private static String getTypeName(final int type) { switch(type) { case java.sql.Types.ARRAY: return "java.sql.Types.ARRAY"; //Can't put break here -- javac knows it's an unreachable statement and prevents compilation!break; case java.sql.Types.BIGINT: return "java.sql.Types.BIGINT"; //break; case java.sql.Types.BINARY: return "java.sql.Types.BINARY"; //break; case java.sql.Types.BIT: return "java.sql.Types.BIT"; //break; case java.sql.Types.BLOB: return "java.sql.Types.BLOB"; //break; case java.sql.Types.BOOLEAN: return "java.sql.Types.BOOLEAN"; //break; case java.sql.Types.CHAR: return "java.sql.Types.CHAR"; //break; case java.sql.Types.CLOB: return "java.sql.Types.CLOB"; //break; case java.sql.Types.DATALINK: return "java.sql.Types.DATALINK"; //break; case java.sql.Types.DATE: return "java.sql.Types.DATE"; //break; case java.sql.Types.DECIMAL: return "java.sql.Types.DECIMAL"; //break; case java.sql.Types.DISTINCT: return "java.sql.Types.DISTINCT"; //break; case java.sql.Types.DOUBLE: return "java.sql.Types.DOUBLE"; //break; case java.sql.Types.FLOAT: return "java.sql.Types.FLOAT"; //break; case java.sql.Types.INTEGER: return "java.sql.Types.INTEGER"; //break; case java.sql.Types.JAVA_OBJECT: return "java.sql.Types.JAVA_OBJECT"; //break; case java.sql.Types.LONGVARBINARY: return "java.sql.Types.LONGVARBINARY"; //break; case java.sql.Types.LONGVARCHAR: return "java.sql.Types.LONGVARCHAR"; //break; case java.sql.Types.NULL: return "java.sql.Types.NULL"; //break; case java.sql.Types.NUMERIC: return "java.sql.Types.NUMERIC"; //break; case java.sql.Types.OTHER: return "java.sql.Types.OTHER"; //break; case java.sql.Types.REAL: return "java.sql.Types.REAL"; //break; case java.sql.Types.REF: return "java.sql.Types.REF"; //break; case java.sql.Types.SMALLINT: return "java.sql.Types.SMALLINT"; //break; case java.sql.Types.STRUCT: return "java.sql.Types.STRUCT"; //break; case java.sql.Types.TIME: return "java.sql.Types.TIME"; //break; case java.sql.Types.TIMESTAMP: return "java.sql.Types.TIMESTAMP"; //break; case java.sql.Types.TINYINT: return "java.sql.Types.TINYINT"; //break; case java.sql.Types.VARBINARY: return "java.sql.Types.VARBINARY"; //break; case java.sql.Types.VARCHAR: return "java.sql.Types.VARCHAR"; //break; default: return "UNKNOWN java.sql.Type: " + type; //break; This "break" after default is allowed, somehow, but javac complains about a missing return statement! } } public static java.util.Map getCallResults(final java.sql.Connection c, final String qry, final Object[] inArgs, final int[] inArgIndexes, final int[] outArgIndexes, final int[] outArgTypes) { String method = "getCallResults"; java.util.Map result = null; //new java.util.HashMap(); boolean gotException = false; Exception resultException = null; if ( (c == null) || (qry == null) || (qry.length() <= 0) ) { tempLog("getCallResults(...): WARNING! Connection is null or query is empty/null"); return null; } //if ( (c != null) && (qry != null) && (qry.length() > 0) ) { //java.sql.PreparedStatement ps = null; java.sql.CallableStatement cs = null; /* java.sql.ResultSet rs = null; java.sql.ResultSetMetaData rsmd = null; java.util.Vector rows = null; java.util.Vector cols = null; */ try { result = new java.util.HashMap(); //cs = c.prepareStatement(qry); cs = c.prepareCall(qry); boolean noResults = true; int i, j; // = -1; if( (outArgIndexes != null) && (outArgTypes != null) && ((i = outArgIndexes.length) == (j=outArgTypes.length)) ) { noResults = false; for( i = 0; i < j; ++i) { //XX ASSUME: Arg indexes passed in are one-based!!! tempLog("DEBUG: TRYING to registerOutParameter(outArgIndexes[i] = " + outArgIndexes[i] + ", outArgTypes[i] = " + getTypeName(outArgTypes[i])); cs.registerOutParameter(outArgIndexes[i],outArgTypes[i]); tempLog("DEBUG: registerOutParameter(outArgIndexes[i] = " + outArgIndexes[i] + ", outArgTypes[i] = " + getTypeName(outArgTypes[i])); } } int k = 0; if( (inArgs != null) && ((j = inArgs.length) > 0) ) { if ( (inArgIndexes != null) && ((k = inArgIndexes.length) > 0) ) { for( i = 0; i < k; ++i) { cs.setObject(inArgIndexes[i],inArgs[i]); tempLog("DEBUG: setting IN Parameter(" + inArgIndexes[i] + ") to '" + inArgs[i] + "')"); } } else { for( i = 0; i < j; ++i) { cs.setObject(i+1,inArgs[i]); tempLog("DEBUG: setting IN Parameter('" + (i+1) + ") to " + inArgs[i] + "')"); } } } //result = getResult(cs); result = getResult2(cs,noResults); } catch(final Exception ex) { gotException = true; resultException = ex; // Add logging code here . . . } finally { if ( cs != null ) { try { cs.close(); } catch(final Exception ex) { // Add logging code here . . . tempLog("Exception closing cs: " + ex); } finally { cs = null; } } //Flag possible truncation of result set!! // Log and / or throw Exception!! if ( gotException ) { Object[][] row = new Object[1][]; row[0] = new Object[] { resultException } ; tempLog("EXCEPTION RESULTS: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); result.put("Exception", row); tempLog("getCallResults(): got exception = " + resultException); resultException.printStackTrace(); } } // finally //} // if return result; } static void setAuthenticatedUser(final String authenticatedUser) { } private static java.util.Map getResult(final java.sql.CallableStatement cs) { if ( cs == null ) { tempLog("Cannot process NULL CallableStatement!!"); } int i, j; // = -1; java.sql.ResultSet rs = null; java.sql.ResultSetMetaData rsmd = null; java.util.Vector rows = null; java.util.Vector cols = null; boolean isResultSet = false; java.util.Map result = new java.util.HashMap(); ; boolean gotException = false; Exception resultException = null; try { isResultSet = cs.execute(); //int isResultSet = cs.executeUpdate(); if(!isResultSet) { // This is just an update statement Object[][] row = new Object[1][]; int rowcount = cs.getUpdateCount(); row[0] = new Object[] { new Integer(rowcount) }; tempLog("UPDATE COUNT: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); //row[0][0] = new Integer(cs.getUpdateCount()); result.put("Updates", row); tempLog(result.toString()); //new Object[] { new Integer(cs.getUpdateCount()) }); //new Object[] { new Integer(cs.getUpdateCount()) }); //XXX: COMM return result; } rs = cs.getResultSet(); if ( rs != null ) { rsmd = rs.getMetaData(); int nCols = rsmd.getColumnCount(); rows = new java.util.Vector(); cols = new java.util.Vector(); while( rs.next() ) { // Need to do better than this! Should return map of (String, [][])!! for(i = 0; i < nCols; ++i) { cols.add(rs.getObject(i+1)); } rows.add( (Object[])cols.toArray(new Object[0]) ); cols.clear(); } Object[][] records = (Object[][])rows.toArray(new Object[0][]); tempLog("RESULTS ARE: row[0] = " + records[0] + ", records[0][0] = " + records[0][0]); result.put("Results", records); tempLog(result.toString()); } else { tempLog("rs is null!"); } return result; /* rows.clear(); rows = null; cols.clear(); cols = null; */ } catch(final Exception ex) { gotException = true; resultException = ex; // Add logging code here . . . } finally { if ( rs != null ) { try { rs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { rs = null; } } if ( cs != null ) { try { cs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { } } if (rows != null) { rows.clear(); rows = null; } if (cols != null) { cols.clear(); cols = null; } //Flag possible truncation of result set!! // Log and / or throw Exception!! if ( gotException ) { Object[][] row = new Object[1][]; row[0] = new Object[] { resultException } ; tempLog("EXCEPTION RESULTS: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); result.put("Exception", row); tempLog("getCallResults(): got exception = " + resultException); resultException.printStackTrace(); } return result; } // finally } // getResult private static java.util.Hashtable validate(final java.sql.Connection c, final String qry, final Object[] inArgs, final int[] outArgIndex, final int[] outArgTypes) { return null; } public static final java.sql.Connection getReadOnlyConnection(final ConnectionBean bean) { long start = System.currentTimeMillis(); java.sql.Connection c = bean.getReadOnlyConnection(); while ( c == null ) { try { Thread.currentThread().sleep(15); } catch(final Exception ignored) { //... } c = bean.getReadOnlyConnection(); } tempLog("Connection " + c + " obtained in " + (System.currentTimeMillis() - start) + " ms."); return c; } //Create a global synchronized singleton to access the connection pool? public synchronized static void setGlobalConnectionBean(final ConnectionBean cb) { if ( globalConnectionBean == null ) globalConnectionBean = cb; else tempLog("globalConnectionBean != null => NOT reset!"); } //Create a global synchronized singleton to access the connection pool? public synchronized static ConnectionBean getGlobalConnectionBean() { tempLog("getGlobalConnectionBean(): returning globalConnectionBean of " + globalConnectionBean); return globalConnectionBean; } public static final java.sql.Connection getReadOnlyConnectionWithTimeout(final ConnectionBean bean, final int nTries) { if ( nTries <= 0 ) return getReadOnlyConnection(bean); int counter = nTries; //timeout > 0 will be interpreted, instead, as the number of tries long start = System.currentTimeMillis(); java.sql.Connection c = bean.getReadOnlyConnection(); while ( (c == null) && (counter-- >= 0) ) { try { Thread.currentThread().sleep(15); } catch(final Exception ignored) { //... } c = bean.getReadOnlyConnection(); } if ( c == null ) { tempLog("No Connection (" + c + ") could be obtained in " + (nTries - counter) + " tries. Time elapsed = " + (System.currentTimeMillis() - start) + " ms." ); } else { tempLog("Connection " + c + " obtained in " + (nTries - counter) + " tries. Time elapsed = " + (System.currentTimeMillis() - start) + " ms."); } return c; } //synchronized !!!! static synchronized void tempLog(final String msg) { String m; date.setTime(System.currentTimeMillis()); m = date + ": Thread = " + Thread.currentThread() + " => " + msg; System.err.println( m ); System.err.flush(); } public static void main(final String[] args) { final ConnectionBean bean = new ConnectionBean(); final java.sql.Connection c = getReadOnlyConnectionWithTimeout(bean,3); /* final String qry = "{? = call SCOTT.TEST_PKG.badFunction(?,?,?,?)}"; final Object[] inArgs = new Object[] { new Character('A'),"arg2", "20-MAY-03", new Integer(Integer.MAX_VALUE), new Double(Double.MAX_VALUE) }; //final int[] outArgIndexes = new int[] { 2, 3, 4, 5 }; final int[] outArgIndexes = new int[] { 1, 3, 4, 5 }; final int[] outArgTypes = new int[] { java.sql.Types.VARCHAR, java.sql.Types.DATE, java.sql.Types.INTEGER, java.sql.Types.NUMERIC }; // new Character('A'),"arg2", "20-MAY-03", new Integer(Integer.MAX_VALUE), new Double(Double.MAX_VALUE) ]; //displayResults(c, qry, inArgs, new int[] { 2 }, outArgIndexes, outArgTypes); */ System.err.println("########################################################################################"); String q2 = "{call WAREUSER.client_credential_cache.set_authenticated_user(?)}"; displayResults(c, q2, new Object[] { "giris" }, new int[] { 1 }, null, null); System.err.println("########################################################################################"); System.err.println(); displayResults(c, "{? = call WAREUSER.client_credential_cache.get_authenticated_user() }", null, null, new int[] {1}, new int[] { java.sql.Types.CHAR //VARCHAR } ); //System.err.println("########################################################################################"); //System.err.println(); // displayResults(c, "{? = call WAREUSER.testpkg.getemps() }", null, null, new int[] {1}, new int[] { oracle.jdbc.OracleTypes.CURSOR //VARCHAR //}); System.err.println("########################################################################################"); System.err.println(); displayResults(c, "{ ? = call WAREUSER.SAO_REPORTS.GET_COST_COLLECTOR_LIST(?) }", new Object[] { "giris" }, new int[] {2}, new int[] {1}, new int[] { oracle.jdbc.OracleTypes.CURSOR } ); } private static final void displayResults(final java.sql.Connection c, final String qry, final Object[] inArgs, final int[] inArgIndexes, final int[] outArgIndexes, final int[] outArgTypes) { tempLog("Processing query: '" + qry + "'."); java.util.Map map = getCallResults(c, qry, inArgs, inArgIndexes, outArgIndexes, outArgTypes); tempLog("Map received = " + map); if ( map == null ) { tempLog("NULL Map received! Returning . . ."); return; } java.util.Set set = map.entrySet(); java.util.Iterator it = set.iterator(); while ( it.hasNext() ) { java.util.Map.Entry entry = (java.util.Map.Entry)it.next(); tempLog( "map[" + entry.getKey() + "] ==>" ); Object[][] rc = (Object[][])entry.getValue(); int arrayLen = rc.length; //tempLog("arrayLen = " + arrayLen); int cols = rc[0].length; //tempLog("cols = " + cols); for(int i = 0; i < arrayLen; ++i ) { //tempLog("Row #" + i); for(int j = 0; j < cols; ++j ) { //tempLog("Col #" + j); tempLog("row[" + i + "][" + j + "] = " + rc[i][j]); } } } } private static java.util.Map getResult2(final java.sql.CallableStatement cs, final boolean noResults) { if ( cs == null ) { tempLog("Cannot process NULL CallableStatement!!"); } tempLog("IN GETRESULT2(" + cs + ", " + noResults + ") !!!"); int i, j; // = -1; java.sql.ResultSet rs = null; java.sql.ResultSetMetaData rsmd = null; java.util.Vector rows = null; java.util.Vector cols = null; boolean isResultSet = false; int rowcount = 0; java.util.Map result = new java.util.HashMap(); ; boolean gotException = false; Exception resultException = null; try { //isResultSet = cs.execute(); rowcount = cs.executeUpdate(); tempLog("ROWCOUNT = " + rowcount); if( rowcount > 0) { // This is just an update statement Object[][] row = new Object[1][]; int rowcount2 = cs.getUpdateCount(); row[0] = new Object[] { new Integer(rowcount2) }; tempLog("UPDATE COUNT2: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); //row[0][0] = new Integer(cs.getUpdateCount()); result.put("Updates", row); tempLog(result.toString()); Object o = null; if (!noResults) o = cs.getObject(1); if ( o instanceof java.sql.ResultSet ) rs = (java.sql.ResultSet)o; if ( rs != null ) { rsmd = rs.getMetaData(); int nCols = rsmd.getColumnCount(); rows = new java.util.Vector(); cols = new java.util.Vector(); while( rs.next() ) { // Need to do better than this! Should return map of (String, [][])!! for(i = 0; i < nCols; ++i) { cols.add(rs.getObject(i+1)); } Object[] ro = (Object[])cols.toArray(new Object[0]); tempLog("Added row of " + ( (ro != null) ? (ro.length) : -1 * Integer.MAX_VALUE) + " cols." ); rows.add( ro ); cols.clear(); } Object[][] records = (Object[][])rows.toArray(new Object[0][]); int nRows = records != null ? records.length : -1 * Integer.MAX_VALUE; tempLog("RESULTS ARE:" + nRows + " records, row[0] = " + records[0] ); tempLog(" records[0][0] = " + (records[0] != null ? records[0][0] : " INVALID!") ); result.put("Results", records); tempLog(result.toString()); return result; } else { tempLog("rowcount > 0: rs is null!"); } } // rowcount > 0 rs = cs.getResultSet(); if ( rs != null ) { rsmd = rs.getMetaData(); int nCols = rsmd.getColumnCount(); rows = new java.util.Vector(); cols = new java.util.Vector(); while( rs.next() ) { // Need to do better than this! Should return map of (String, [][])!! for(i = 0; i < nCols; ++i) { cols.add(rs.getObject(i+1)); } rows.add( (Object[])cols.toArray(new Object[0]) ); cols.clear(); } Object[][] records = (Object[][])rows.toArray(new Object[0][]); tempLog("RESULTS ARE: row[0] = " + records[0] + ", records[0][0] = " + records[0][0]); result.put("Results", records); tempLog(result.toString()); } else { tempLog("getResultSet: rs is null!"); } return result; /* rows.clear(); rows = null; cols.clear(); cols = null; */ } catch(final Exception ex) { gotException = true; resultException = ex; // Add logging code here . . . } finally { if ( rs != null ) { try { rs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { rs = null; } } if ( cs != null ) { try { cs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { } } if (rows != null) { rows.clear(); rows = null; } if (cols != null) { cols.clear(); cols = null; } //Flag possible truncation of result set!! // Log and / or throw Exception!! if ( gotException ) { Object[][] row = new Object[1][]; row[0] = new Object[] { resultException } ; tempLog("EXCEPTION RESULTS: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); result.put("Exception", row); tempLog("getCallResults(): got exception = " + resultException); resultException.printStackTrace(); } return result; } // finally } // getResult2 }; /* if( (outArgIndexes != null) && (outArgTypes != null) && ((i = outArgIndexes.length) == (j=outArgTypes.length)) ) { */ //public Object[] getCallResults(final java.sql.Connection c, final String qry, final Object[] inArgs, // final int[] outArgTypes) //throws java.sql.Exception // final int[] outArgTypes) { // if( (outArgTypes != null) && ((j = outArgTypes.length) > 0) ) { /* if(!isResultSet) { // This is just an update statement Object[][] row = new Object[1][]; int rowcount = cs.getUpdateCount(); row[0] = new Object[] { new Integer(rowcount) }; tempLog("UPDATE COUNT: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); //row[0][0] = new Integer(cs.getUpdateCount()); result.put("Updates", row); tempLog(result.toString()); //new Object[] { new Integer(cs.getUpdateCount()) }); //new Object[] { new Integer(cs.getUpdateCount()) }); return result; } rs = cs.getResultSet(); rsmd = rs.getMetaData(); int nCols = rsmd.getColumnCount(); rows = new java.util.Vector(); cols = new java.util.Vector(); while( rs.next() ) { // Need to do better than this! Should return map of (String, [][])!! for(i = 0; i < nCols; ++i) { cols.add(rs.getObject(i+1)); } rows.add( (Object[])cols.toArray(new Object[0]) ); cols.clear(); } Object[][] records = (Object[][])rows.toArray(new Object[0][]); tempLog("RESULTS ARE: row[0] = " + records[0] + ", records[0][0] = " + records[0][0]); result.put("Results", records); tempLog(result.toString()); return result; * rows.clear(); rows = null; cols.clear(); cols = null; ** } catch(final Exception ex) { gotException = true; resultException = ex; // Add logging code here . . . } finally { if ( rs != null ) { try { rs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { rs = null; } } if ( cs != null ) { try { cs.close(); } catch(final Exception ex) { // Add logging code here . . . } finally { cs = null; } } if (rows != null) { rows.clear(); rows = null; } if (cols != null) { cols.clear(); cols = null; } //Flag possible truncation of result set!! // Log and / or throw Exception!! if ( gotException ) { Object[][] row = new Object[1][]; row[0] = new Object[] { resultException } ; tempLog("EXCEPTION RESULTS: row[0] = " + row[0] + ", row[0][0] = " + row[0][0]); result.put("Exception", row); tempLog("getCallResults(): got exception = " + resultException); resultException.printStackTrace(); } //return result; } // finally */ /* public static final java.sql.Connection getReadOnlyConnectionWithTimeout(final ConnectionBean bean, final int nTries) { if ( nTries <= 0 ) return getReadOnlyConnection(bean); //timeout > 0 will be interpreted, instead, as the long start = System.currentTimeMillis(); java.sql.Connection c = bean.getReadOnlyConnection(); while ( (c == null) ) { try { Thread.currentThread().sleep(15); } catch(final Exception ignored) { //... } c = bean.getReadOnlyConnection(); } tempLog("Connection " + c + " obtained in " + (System.currentTimeMillis() - start) + " ms."); return c; } */