/******************************************************************************* ! Program: decodePC.java !******************************************************************************* ! ! Programmer: David L. Price (Ideatec, Inc.) ! www.ideatec.us ! Email: david@ideatec.us ! ! Re-written to Java and expanded: Jeff Chochon (Little Beirut) ! !******************************************************************************* ! ! This program was designed as a learning experiment to help me understand ! better how PeopleSoft stored PeopleCode. ! The only table that it INSERTs into or DELETEs from is DLP_PCODE (which it ! will create if not found)(Obsolete -Jeff). ! ! TO DO / KNOWN ISSUES: ! * There are still only a very little bit of indenting logic. Mostly just ! playing around trying to find out what works. Most likely it needs to be ! removed and a new approach used. ! * There are still several instances where line feeds aren't correct. Such ! as where semi-colons are options (single statement 'if' blocks) ! * Spacing is fairly good. Semi-colons sometimes get an extra space before them. ! !******************************************************************************* ! Modification Log ! ! DD Mon CCYY Description ! == === ==== ================================================================== ! 4 Dec 2004 Created for PeopleSoft 8.4x. -David L. Price ! 14 Mar 2005 Added Interactive selection mode. Enter 'I' at prompt. -David L. ! Price ! Summer 2007 +Rewrite to Java. ! +No need to use Application Designer to view PeopleCode. ! +A JDBC database connection is used. ! +Should work on any database (Oracle, DB2, MYSQL). ! +SUN Microsystems J2SE 1.4.2 or higher required. ! +Based on changes done to original logic, it appears Oracle changes ! how it stores PeopleCode. ! +Do not use table DLP_PCODE. Only reads from PeopleSoft. ! +Did not figure out intent of Interactive mode and removed it. ! +Added Application Engine expansion. ! +Big numbers or numbers with high precision display in ! scientific notation. ! +SQL formatting differs from App Designer. ! +SQL ReUse Statement not yet implemented. ! +Reading of individual XSLT (same as SQL) doesn't work yet. Does ! work when XSLT within an App Engine. ! +App Engine Actions might not always display in their correct ! order. Haven't yet figured out how Action order is maintained. ! +Triple double quotes in PeopleCode loose one of the double quotes. ! +Will add Application Packages soon. ! +Code is for database MySQL. Oracle code has been commented out. ! +Driven by desire to read code in whatever format and editor I ! wish as programming is tough enough and App Designer was driving ! me crazy. ! -Jeff Chochon ! Sep 2007 +Add code in method PCdecode for 0x72. ! Log: 200709-0x72 ! +Add Application Packages. ! Log: 200709-AP ! +Fix a couple of the many PeopleCode/class formatting problems. ! Log: 200709-format ! +Fix reading of individual XSLT. ! Log: 200709-xslt ! -Jeff Chochon ! Nov 2007 +Improve non expected user input handling. ! Log: 200711-handle ! +Change SQLID matching to case insensitive and allow SQL wildcard. ! Method stageSQL is re-written without code change log comments. ! Log: 200711-sqlwild ! +Add AE %Sql (SQL fragments in Application Engines). ! Log: 200711-ae%sql ! +MySQL tables do not have owners. ! Log: 200711-mysql ! +Add code in method PCdecode for 0x60. ! Log: 200711-0x60 ! -Jeff Chochon ! !******************************************************************************* */ import java.io.*; import java.sql.*; import java.util.*; import java.text.*; import java.util.regex.*; public class decodePC { /* Java class variables are global within the class, i.e., this program. */ public static String[] class_WhereClause = null; public static boolean class_Debug = false; public static boolean class_Output_To_File = false; public static boolean class_Output_To_Screen = true; public static boolean class_Output_AESQL = true; /* Log: 200711-ae%sql Start */ public static boolean class_Output_AE_SQL = true; /* Log: 200711-ae%sql end */ public static boolean class_Output_AEPC = true; /* Log: 200709-AP Start */ public static boolean class_Output_APPC = true; /* Log: 200709-AP end */ public static boolean class_EndOfCode = false; public static String class_SBefore = "N"; public static String class_SAfter = "N"; public static String class_Indent = ""; /* +: Add one level, -: Remove one level, 0: Reset to zero */ public static int class_Indent_size = 0; public static String class_CodeLine = ""; public static String[][][][] class_ae_data; public static byte[] class_outData; /* class_oD_pos is used to hold the position within byte array class_outData */ public static int class_oD_pos = 0; public static FileOutputStream class_outFile; public static boolean _TRUE = true; public static int INDENT_SIZE = 3; //public static int MAX_AE_SECTION = 1400; /* App Engine AR_POSTING is a monster */ public static int MAX_AE_SECTION = 200; /* In case of Java memory error */ public static int MAX_AE_STEP = 200; public static int MAX_AE_ACTION = 6; public static int MAX_AE_DESCR = 5; /* Log: 200711-ae%sql Start */ public static int MAX_AE_SQL = 100; /* Log: 200711-ae%sql end */ public static String SQL_LITERAL = "SQL"; public static String XSLT_LITERAL = "XSLT"; public static String CALL_LITERAL = "Call Section"; public static String PC_LITERAL = "PeopleCode"; public static String LOGMSG_LITERAL = "Log Message"; public static String EXIT_LITERAL = "EXIT"; public static String WRITE_LITERAL = "WRITE"; public static String RETURN_LITERAL = "RETURN"; public static String class_UserID = "UserID"; public static String class_Password = "Password"; public static String class_Database = "Database"; /* Log: 200711-mysql Start */ public static String class_Db = "Oracle"; //public static String class_Db = "MySQL"; public static String class_Dbowner = ""; public static String class_PsDbowner = ""; /* Log: 200711-mysql end */ /* PeopleCode decoding flags */ public static boolean class_Declare = false; public static boolean class_Class = false; public static boolean class_inClass = false; public static boolean class_ClassExtends = false; /* Log: 200709-format Start */ public static boolean class_Evaluate = false; /* Log: 200709-format end */ /* Log: 200709-0x72 Start */ public static boolean class_implements = false; public static int MAX_AP_PIECES = 100; /* Log: 200709-0x72 end */ /* Oracle begin */ public static String class_DBUrl = "jdbc:oracle:oci8:@"; /* Oracle end */ /* MySQL begin */ //public static String class_DBUrl = "jdbc:mysql://localhost/"; /* MySQL end */ public static void main(String[] args) { String currentMethod = "main:"; class_UserID = readEntry("User ID: "); int slash_index = class_UserID.indexOf('/'); if (slash_index != -1) { class_Password = class_UserID.substring(slash_index + 1); class_UserID = class_UserID.substring(0, slash_index); } else { class_Password = readEntry("Password: "); } class_Database = readEntry("Database (a TNSNAME entry, name-value pairs): "); String WhereClause = ""; int output_cnt = 1; String junk = ""; /* Log: 200711-mysql Start */ if (class_Db.equalsIgnoreCase("ORACLE")) { class_Dbowner = "sysadm"; class_PsDbowner = "ps"; } else { class_Dbowner = ""; class_PsDbowner = ""; } /* Log: 200711-mysql end */ Connection dbconn = null; dbconn = openDbConnection(class_DBUrl, class_Database, class_UserID, class_Password); initReport(dbconn); closeDbConnection(dbconn); while (_TRUE) { /* Open the database connection, do some work, and close it to avoid running out of cursors. */ dbconn = openDbConnection(class_DBUrl, class_Database, class_UserID, class_Password); WhereClause = promptUser(); if (WhereClause.equalsIgnoreCase(EXIT_LITERAL)) { if (class_Debug) System.out.println(currentMethod + " fell through to exit"); closeDbConnection(dbconn); break; } else if (WhereClause.substring(0,2).equalsIgnoreCase("AE")) { if (class_Debug) System.out.println(currentMethod + " processing Application Engine program"); output_cnt = openOutputFile(output_cnt); processAE(dbconn, WhereClause); closeOutputFile(); /* Log: 200709-AP Start */ } else if (WhereClause.substring(0,2).equalsIgnoreCase("AP")) { if (class_Debug) System.out.println(currentMethod + " processing Application Package"); output_cnt = openOutputFile(output_cnt); processAP(dbconn, WhereClause); closeOutputFile(); /* Log: 200709-AP end */ } else if (WhereClause.substring(0,5).equalsIgnoreCase("SQLID")) { if (class_Debug) System.out.println(currentMethod + " processing SQLID"); output_cnt = openOutputFile(output_cnt); junk = processSQL(dbconn, WhereClause, WRITE_LITERAL); closeOutputFile(); } else if (!(WhereClause.equalsIgnoreCase("IGNORE"))) { if (class_Debug) System.out.println(currentMethod + " fell through to dot delimited processing"); output_cnt = openOutputFile(output_cnt); junk = processWhereClausePC(dbconn, WhereClause, WRITE_LITERAL); closeOutputFile(); } closeDbConnection(dbconn); } } public static String processWhereClausePC(Connection pWCP_dbconn, String pWCP_WhereClause, String pWCP_disposition) { String currentMethod = "processWhereClausePC:"; String $Code = ""; int oD_int = 0; String pWCP_return = ""; class_EndOfCode = false; class_CodeLine = ""; class_Indent = ""; class_Indent_size = 0; /* Skip first 37 bytes. PeopleCode header information. Did not discover its purpose. */ class_oD_pos = 37; class_outData = stagePeopleCode(pWCP_dbconn, pWCP_WhereClause); /* Move through the data byte by byte. */ while ((class_oD_pos < class_outData.length) && (!class_EndOfCode)) { class_SBefore = "N"; /* class_SBefore and class_SAfter: 'Y' - Space required 'N' - Space not required 'P' - Space required, except next to punctuation: ().,; */ /* Go from byte to Integer. AND byte with 0xff so casting does conversion against bits. */ oD_int = (int)(class_outData[class_oD_pos] & 0xff); /* Decode the byte into meaningful PeopleCode. */ pWCP_return = pWCP_return + PCdecoder(pWCP_dbconn, oD_int, pWCP_WhereClause, pWCP_disposition); class_oD_pos++; } return pWCP_return; } static void processAE(Connection pAE_dbconn, String pAE_AE) { String currentMethod = "processAE:"; int section = 0; int step = 0; int action = 0; String noparm = ""; String junk = ""; class_ae_data = stageAE(pAE_dbconn, pAE_AE); /* Display the Application Engine program name. */ class_CodeLine = pAE_AE.trim(); junk = lineFeed(noparm); if (class_Debug) { System.out.println(currentMethod + " Following should be all the sections in alphabetical order. "); section = 0; while (!(class_ae_data[section][0][0][0] == null) && (section < class_ae_data.length)) { class_CodeLine = class_ae_data[section][0][0][0] + " " + class_ae_data[section][0][0][1]; junk = lineFeed(noparm); section++; } System.out.println(currentMethod + " Following should be all the sections in alphabetical order and their steps. "); section = 0; while (!(class_ae_data[section][0][0][0] == null) && (section < class_ae_data.length)) { step = 0; while (!(class_ae_data[section][step][0][0] == null) && (step < class_ae_data[section].length)) { if (step == 0) class_Indent = "B"; class_CodeLine = class_ae_data[section][step][0][0] + " " + class_ae_data[section][step][0][1]; junk = lineFeed(noparm); step++; } section++; } System.out.println(" "); } /* Display the Application Engine program. */ /* First display section MAIN, then the others. */ for (int main_display = 1; main_display < 3; main_display++) { /* Spin through the AE sections */ section = 0; while (!(class_ae_data[section][0][0][0] == null) && (section < class_ae_data.length)) { if (((main_display == 1) && (class_ae_data[section][0][0][0].equalsIgnoreCase("MAIN"))) || ((main_display == 2) && (!(class_ae_data[section][0][0][0].equalsIgnoreCase("MAIN"))))) { /* Spin through the AE steps */ step = 0; while (!(class_ae_data[section][step][0][0] == null) && (step < class_ae_data[section].length)) { if (step == 0) class_Indent = "B"; class_CodeLine = class_ae_data[section][step][0][0] + " " + class_ae_data[section][step][0][1] + " " + class_ae_data[section][step][0][2]; junk = lineFeed(noparm); /* Spin through the AE actions. Action is handled differently as they start at position 1 instead of 0. */ action = 1; while (action < class_ae_data[section][step].length) { if (!(class_ae_data[section][step][action][0] == null)) { class_Indent = "R"; /* If the action is SQL, get the No Row info from step level data */ if (class_ae_data[section][step][action][0].equals(SQL_LITERAL)) class_CodeLine = class_ae_data[section][step][action][0] + " " + class_ae_data[section][step][action][1] + " " + class_ae_data[section][step][0][4]; else class_CodeLine = class_ae_data[section][step][action][0] + " " + class_ae_data[section][step][action][1]; junk = lineFeed(noparm); if (!(class_ae_data[section][step][action][2] == null)) { /* Output the Message | SQL | PeopleCode seperately to get the indentation. */ class_Indent = "B"; /* If the action is Log Message, get the message info from step level data */ if (class_ae_data[section][step][action][0].equals(LOGMSG_LITERAL)) class_CodeLine = "\t" + class_ae_data[section][step][0][3] + " " + class_ae_data[section][step][action][2]; else class_CodeLine = class_ae_data[section][step][action][2]; junk = lineFeed(noparm); } } action++; } step++; } /* If MAIN has been processed, skip the rest of the sections. */ if ((main_display == 1) && (class_ae_data[section][0][0][0].equalsIgnoreCase("MAIN"))) section = class_ae_data.length - 2; } section++; } } } /* Log: 200709-AP Start */ static void processAP(Connection pAP_dbconn, String pAP_AP) { String currentMethod = "processAP:"; String junk = ""; String noparm = ""; String pkg_cls_tmp = ""; int oD_int = 0; String pWCP_return = ""; String pAP_whereClause = ""; String blobdata = ""; StringBuffer sb = new StringBuffer(); sb.append("Select pc.OBJECTVALUE1, pc.OBJECTVALUE2, pc.OBJECTVALUE3, pc.OBJECTVALUE4, "); sb.append(" pc.OBJECTVALUE5, pc.OBJECTVALUE6, pc.OBJECTVALUE7, pc.PROGSEQ, pc.PROGTXT, "); sb.append(" case when objectvalue3 = 'OnExecute' then 1 else 2 end as ORDERBY "); /* Log: 200711-mysql Start */ //sb.append(" from sysadm.pspcmprog pc "); //sb.append(" ,sysadm.pspackagedefn pk "); sb.append(" from " + class_Dbowner + ".pspcmprog pc "); sb.append(" ," + class_Dbowner + ".pspackagedefn pk "); /* Log: 200711-mysql end */ sb.append(" where pk.PACKAGEROOT = ? "); sb.append(" and pk.PACKAGEID = pc.OBJECTVALUE1 "); sb.append(" and pc.OBJECTVALUE1 = pk.PACKAGEROOT "); sb.append(" order by ORDERBY, pc.OBJECTVALUE2, pc.OBJECTVALUE3, pc.PROGSEQ desc "); String s = sb.toString(); /* Oracle begin */ String[] OOB = new String[4000]; /* Oracle end */ /* MySQL begin */ //byte[] OOB = new byte[4000]; //int bytecnt = 0; //blobdata = ""; /* MySQL end */ String[] ov1 = new String[MAX_AP_PIECES]; String[] ov5 = new String[MAX_AP_PIECES]; String[] ov6 = new String[MAX_AP_PIECES]; String[] ov7 = new String[MAX_AP_PIECES]; String[] pkg_cls = new String[MAX_AP_PIECES]; String[] cls_evt = new String[MAX_AP_PIECES]; String[] event = new String[MAX_AP_PIECES]; int[] progseq = new int[MAX_AP_PIECES]; int row_cnt = 0; int OOB_cnt = 0; /* Parse out the "AP = ". The regular expression matches "=" and space zero or more times */ String regex = "= *"; String[] $AP = pAP_AP.split(regex); $AP[1] = $AP[1].trim(); $AP[1] = $AP[1].toUpperCase(); if (class_Debug) System.out.println(currentMethod + " SQL: " + s); if (class_Debug) System.out.println(currentMethod + " PackageRoot: " + $AP[1]); /* Output the primary Application Package name */ /* Indent twice */ class_Indent = "0"; /* Indent set to zero */ class_CodeLine = $AP[1] + " primary package"; class_Indent = "+"; /* First Indent */ junk = lineFeed(noparm); class_Indent = "+"; /* Second Indent */ junk = lineFeed(noparm); try { PreparedStatement ps = pAP_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, $AP[1]); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { /* Oracle begin */ OOB[row_cnt] = resultset.getString("PROGTXT"); /* Oracle end */ /* MySQL begin */ //Blob datablob = resultset.getBlob("PROGTXT"); //InputStream in = datablob.getBinaryStream(); //try { // bytecnt = in.read(OOB); // System.out.println(currentMethod + " bytecnt= " + bytecnt); //} catch (IOException e) { // System.out.println(currentMethod + " IOException error" + e.getMessage()); //} //for (row_cnt = 0; row_cnt < bytecnt; row_cnt++) { // System.out.println(currentMethod + " Select from pspcmprog: " + OOB[row_cnt] + " pos " + row_cnt); //} //for (int k = 0; k < bytecnt; k++) { // blobdata= blobdata + (char) OOB[k]; //} /* MySQL end */ ov1[row_cnt] = resultset.getString("OBJECTVALUE1"); pkg_cls[row_cnt] = resultset.getString("OBJECTVALUE2"); cls_evt[row_cnt] = resultset.getString("OBJECTVALUE3"); event[row_cnt] = resultset.getString("OBJECTVALUE4"); ov5[row_cnt] = resultset.getString("OBJECTVALUE5"); ov6[row_cnt] = resultset.getString("OBJECTVALUE6"); ov7[row_cnt] = resultset.getString("OBJECTVALUE7"); progseq[row_cnt] = resultset.getInt("PROGSEQ"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } /* Output the Packages and Classes */ for (int zz = 1; zz < row_cnt; zz++) { if (cls_evt[zz].equals("OnExecute")) { /* Classes under the primary package. */ class_CodeLine = pkg_cls[zz] + " primary package class"; junk = lineFeed(noparm); } else { /* Check if package has changed. */ if (!pkg_cls_tmp.equalsIgnoreCase(pkg_cls[zz])) { /* If first sub package, back off two indents. */ pkg_cls_tmp = pkg_cls[zz]; class_Indent = "B"; class_CodeLine = pkg_cls[zz] + " subpackage"; junk = lineFeed(noparm); } class_CodeLine = cls_evt[zz] + " " + event[zz] + " subpackage class"; junk = lineFeed(noparm); } /* Output the PeopleCode */ if (class_Output_APPC) { /* Avoid using all of the cursors by closing and opening the database connection */ closeDbConnection(pAP_dbconn); pAP_dbconn = openDbConnection(class_DBUrl, class_Database, class_UserID, class_Password); if (class_Debug) System.out.println(currentMethod + " Convert from hex to decimal."); if (class_Debug) { System.out.println(currentMethod + " Select from pspcmprog: " + OOB[1]); } /* Oracle begin */ /* Convert from hex to decimal. First put data into String. */ OOB_cnt = progseq[zz]; blobdata = ""; /* Combine array entries for PeopleCode spanning rows. */ for (int jj = 0; jj <= OOB_cnt; jj++) { if (jj > 0) zz++; blobdata = OOB[zz] + blobdata; } /* Oracle end */ char[] blobchar = blobdata.toCharArray(); /* String into char array */ if (class_Debug) System.out.println(currentMethod + " Length of progtxt from pspcmprog: " + blobdata.length()); int length = blobdata.length(); byte[] outData = new byte[length/2]; /* Create byte array for decodeHex output. */ outData = decodeHex(blobchar); class_outData = outData; class_EndOfCode = false; /* Skip first 37 bytes. PeopleCode header information. Did not discover its purpose. */ class_oD_pos = 37; pWCP_return = ""; /* Move through the data byte by byte. */ while ((class_oD_pos < class_outData.length) && (!class_EndOfCode)) { class_SBefore = "N"; /* class_SBefore and class_SAfter: 'Y' - Space required 'N' - Space not required 'P' - Space required, except next to punctuation: ().,; */ /* Go from byte to Integer. AND byte with 0xff so casting does conversion against bits. */ oD_int = (int)(class_outData[class_oD_pos] & 0xff); /* Decode the byte into meaningful PeopleCode. */ pAP_whereClause = "' OBJECTVALUE1 = '" + ov1[zz] + "' OBJECTVALUE2 = '" + pkg_cls[zz] + "' OBJECTVALUE3 = '" + cls_evt[zz] + "' OBJECTVALUE4 = '" + event[zz] + "' OBJECTVALUE5 = '" + ov5[zz] + "' OBJECTVALUE6 = '" + ov6[zz] + "' OBJECTVALUE7 = '" + ov7[zz] + "'"; if (class_Debug) System.out.println(currentMethod + " Build where clause: " + pAP_whereClause); pWCP_return = pWCP_return + PCdecoder(pAP_dbconn, oD_int, pAP_whereClause, RETURN_LITERAL); class_oD_pos++; } class_CodeLine = "\n" + pWCP_return; junk = lineFeed(noparm); } } } /* Log: 200709-AP end */ static String processSQL(Connection pS_dbconn, String pS_WhereClause, String pS_disposition) { String currentMethod = "processSQL:"; class_CodeLine = stageSQL(pS_dbconn, pS_WhereClause); /* Log: 200711-ae%sql Start */ if (class_Output_AE_SQL) { class_CodeLine = class_CodeLine + stageAE_SQL(pS_dbconn, class_CodeLine); } /* Log: 200711-ae%sql end */ return lineFeed(pS_disposition); } static String processMsg(Connection pM_dbconn, String pM_ae, String pM_section, String pM_step, String pM_db, String pM_disposition) { String currentMethod = "processMsg:"; class_CodeLine = stageMsg(pM_dbconn, pM_ae, pM_section, pM_step, pM_db); return lineFeed(pM_disposition); } static String PCdecoder(Connection dPC_dbconn, int dPC, String dPC_WhereClause, String dPC_disposition) { String currentMethod = "PCdecoder:"; /* ! This list is not conclusive. There are still many gaps. I can only assume ! that every number has a meaning...but some of these could be for obsolete ! commands and so no longer used. Currently it will stop when it encounters an ! unrecognized code. */ String $tString = ""; String dPC_return = ""; String[] dPC_RecRef; int NameNum1 = 0; int NameNum2 = 0; switch (dPC) { case 1: /* 0x01 */ class_SBefore = "P"; $tString = get_String(); writeCode($tString); class_SAfter = "P"; break; case 3: /* 0x03 */ class_SBefore = "N"; writeCode(","); class_SAfter = "Y"; break; case 4: /* 0x04 */ class_SBefore = "Y"; writeCode("/"); class_SAfter = "Y"; break; case 5: /* 0x05 */ class_SBefore = "N"; writeCode("."); class_SAfter = "N"; break; case 6: /* 0x06 */ class_SBefore = "Y"; writeCode("="); class_SAfter = "Y"; break; case 7: /* 0x07 */ /* End of code */ class_EndOfCode = true; break; case 8: /* 0x08 */ class_SBefore = "Y"; writeCode(">="); class_SAfter = "Y"; break; case 9: /* 0x09 */ class_SBefore = "Y"; writeCode(">"); class_SAfter = "Y"; break; case 10: /* 0x0a */ /* Function | Method | External Datatype | Class name */ class_SBefore = "P"; $tString = get_String(); writeCode($tString); class_SAfter = "P"; /* If in a class definition, look ahead to the next byte to check for "extends" or "implements" */ if (class_Class) { /* Log: 200709-format Start */ /* if ((int)(class_outData[class_oD_pos + 1] & 0xff) != 92) { */ if (((int)(class_outData[class_oD_pos + 1] & 0xff) != 92) && ((int)(class_outData[class_oD_pos + 1] & 0xff) != 114)) { /* Log: 200709-format end */ /* If no class extends do a line feed */ dPC_return = lineFeed(dPC_disposition); } else { class_ClassExtends = true; } class_Class = false; } break; case 11: /* 0x0b */ class_SBefore = "N"; writeCode("("); class_SAfter = "N"; break; case 12: /* 0x0c */ class_SBefore = "Y"; writeCode("<="); class_SAfter = "Y"; break; case 13: /* 0x0d */ class_SBefore = "Y"; writeCode("<"); class_SAfter = "Y"; break; case 14: /* 0x0e */ class_SBefore = "Y"; writeCode("-"); class_SAfter = "Y"; break; case 15: /* 0x0f */ class_SBefore = "Y"; writeCode("*"); class_SAfter = "Y"; break; case 16: /* 0x10 */ class_SBefore = "Y"; writeCode("<>"); class_SAfter = "Y"; break; case 17: /* 0x11 */ /* Indicator of a number coming up */ $tString = getNumber(14); writeCode($tString); class_SAfter = "P"; break; case 18: /* 0x12 */ /* System variable name */ class_SBefore = "P"; $tString = get_String(); writeCode($tString); class_SAfter = "P"; break; case 19: /* 0x13 */ class_SBefore = "Y"; writeCode("+"); class_SAfter = "Y"; break; case 20: /* 0x14 */ class_SBefore = "N"; writeCode(")"); class_SAfter = "N"; break; case 21: /* 0x15 */ class_SBefore = "N"; writeCode(";"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "N"; break; case 22: /* 0x16 */ /* Quoted literal */ class_SBefore = "P"; $tString = get_String(); $tString = "\"" + $tString + "\""; writeCode($tString); class_SAfter = "P"; break; case 24: /* 0x18 */ class_SBefore = "Y"; writeCode("And"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 25: /* 0x19 */ class_SBefore = "Y"; class_Indent = "B"; /* Log: 200709-format Start */ /* writeCode("Else"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("Else;"); class_oD_pos++; } else { writeCode("Else"); } /* Log: 200709-format end */ dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 26: /* 0x1a */ class_SBefore = "Y"; class_Indent = "-"; writeCode("End-If"); /* Log: 200709-format Start */ /* class_SAfter = "Y"; */ class_SAfter = "N"; /* Log: 200709-format end */ break; case 27: /* 0x1b */ class_SBefore = "Y"; writeCode("Error"); class_SAfter = "Y"; break; case 28: /* 0x1c */ class_SBefore = "Y"; class_Indent = "+"; writeCode("If"); class_SAfter = "Y"; break; case 29: /* 0x1d */ class_SBefore = "Y"; writeCode("Not"); class_SAfter = "Y"; break; case 30: /* 0x1e */ class_SBefore = "Y"; writeCode("Or"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 31: /* 0x1f */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* writeCode("Then"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("Then;"); class_oD_pos++; } else { writeCode("Then"); } /* Log: 200709-format end */ dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 32: /* 0x20 */ class_SBefore = "Y"; writeCode("Warning"); class_SAfter = "Y"; break; case 33: /* 0x21 */ /* PeopleSoft object indicator (w/ Object name) */ class_SBefore = "P"; /* Move to next byte */ class_oD_pos++; /* Cast the byte array member to integer */ NameNum1 = (int) class_outData[class_oD_pos]; /* Move to the next byte. */ /* This one is skipped. Could be the high order byte of NameNum. */ class_oD_pos++; dPC_RecRef = getObjectReference(dPC_dbconn, NameNum1, dPC_WhereClause); writeCode(dPC_RecRef[1] + "." + dPC_RecRef[2]); class_SAfter = "P"; break; case 35: /* 0x23 */ class_SBefore = "Y"; writeCode("|"); class_SAfter = "Y"; break; case 36: /* 0x24 */ $tString = getComment(); writeCode($tString); dPC_return = lineFeed(dPC_disposition); break; case 37: /* 0x25 */ class_SBefore = "Y"; class_Indent = "+"; writeCode("While"); class_SAfter = "Y"; break; case 38: /* 0x26 */ class_SBefore = "Y"; class_Indent = "-"; /* Log: 200709-format Start */ /* writeCode("End-While"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("End-While;"); class_oD_pos++; } else { writeCode("End-While"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 39: /* 0x27 */ class_SBefore = "Y"; class_Indent = "+"; writeCode("Repeat"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 40: /* 0x28 */ class_SBefore = "Y"; class_Indent = "-"; writeCode("Until"); class_SAfter = "Y"; break; case 41: /* 0x29 */ class_SBefore = "Y"; class_Indent = "+"; writeCode("For"); class_SAfter = "Y"; break; case 42: /* 0x2a */ class_SBefore = "Y"; writeCode("To"); class_SAfter = "Y"; break; case 43: /* 0x2b */ class_SBefore = "Y"; writeCode("Step"); class_SAfter = "Y"; break; case 44: /* 0x2c */ class_SBefore = "Y"; class_Indent = "-"; /* Log: 200709-format Start */ /* writeCode("End-For"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("End-For;"); class_oD_pos++; } else { writeCode("End-For"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 45: /* 0x2d */ /* Code Section Seperator */ //writeCode(""); /* writeCode("==================================================================="); */ dPC_return = lineFeed(dPC_disposition); break; case 46: /* 0x2e */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* writeCode("Break"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("Break;"); class_oD_pos++; } else { writeCode("Break"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 47: /* 0x2f */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* writeCode("True"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("True;"); class_oD_pos++; } else { writeCode("True"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 48: /* 0x30 */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* writeCode("False"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("False;"); class_oD_pos++; } else { writeCode("False"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 49: /* 0x31 */ class_SBefore = "Y"; writeCode("Declare"); class_SAfter = "Y"; /* Do not indent for "Declare Function" */ class_Declare = true; break; case 50: /* 0x32 */ class_SBefore = "Y"; /* Do not indent for "Declare Function" */ if (!class_Declare) class_Indent = "+"; writeCode("Function"); class_SAfter = "Y"; class_Declare = false; break; case 51: /* 0x33 */ class_SBefore = "Y"; writeCode("Library"); class_SAfter = "Y"; break; case 53: /* 0x35 */ class_SBefore = "Y"; writeCode("As"); class_SAfter = "Y"; break; case 54: /* 0x36 */ class_SBefore = "Y"; writeCode("Value"); class_SAfter = "Y"; break; case 55: /* 0x37 */ class_SBefore = "Y"; class_Indent = "-"; writeCode("End-Function"); class_SAfter = "Y"; break; case 56: /* 0x38 */ class_SBefore = "Y"; writeCode("Return"); class_SAfter = "Y"; break; case 57: /* 0x39 */ class_SBefore = "Y"; writeCode("Returns"); class_SAfter = "Y"; break; case 58: /* 0x3a */ class_SBefore = "Y"; writeCode("PeopleCode"); class_SAfter = "Y"; break; case 59: /* 0x3b */ class_SBefore = "Y"; writeCode("Ref"); class_SAfter = "Y"; break; case 60: /* 0x3c */ class_SBefore = "Y"; class_Indent = "+"; writeCode("Evaluate"); /* Log: 200709-format Start */ class_Evaluate = true; /* Log: 200709-format end */ class_SAfter = "Y"; break; case 61: /* 0x3d */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* Check if currently on Evaluate */ if (class_Evaluate) { dPC_return = lineFeed(dPC_disposition); class_Evaluate = false; } class_Indent = "B"; /* Log: 200709-format end */ writeCode("When"); class_SAfter = "Y"; break; case 62: /* 0x3e */ class_SBefore = "Y"; /* Log: 200709-format Start */ class_Indent = "B"; /* Log: 200709-format end */ writeCode("When-Other"); class_SAfter = "Y"; break; case 63: /* 0x3f */ class_SBefore = "Y"; class_Indent = "-"; /* Log: 200709-format Start */ /* writeCode("End-Evaluate"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("End-Evaluate;"); class_oD_pos++; } else { writeCode("End-Evaluate"); } dPC_return = lineFeed(dPC_disposition); /* Log: 200709-format end */ class_SAfter = "Y"; break; case 64: /* 0x40 */ /* PeopleCode Variable Type Name */ class_SBefore = "P"; $tString = get_String(); writeCode($tString); class_SAfter = "P"; break; case 65: /* 0x41 */ /* Parmlist */ break; case 66: /* 0x42 */ /* End Parmlist | End of Declare Statement */ break; case 67: /* 0x43 */ class_SBefore = "Y"; writeCode("Exit"); class_SAfter = "Y"; break; case 68: /* 0x44 */ class_SBefore = "Y"; writeCode("Local"); class_SAfter = "Y"; break; case 69: /* 0x45 */ class_SBefore = "Y"; writeCode("Global"); class_SAfter = "Y"; break; case 70: /* 0x46 */ class_SBefore = "Y"; writeCode("**"); class_SAfter = "Y"; break; case 71: /* 0x47 */ class_SBefore = "N"; writeCode("@"); class_SAfter = "N"; break; case 73: /* 0x49 */ class_SBefore = "Y"; writeCode("set"); class_SAfter = "Y"; break; case 74: /* 0x4a */ /* PeopleSoft object indicator (w/ Object name) */ class_SBefore = "P"; /* Move to the next byte */ class_oD_pos++; /* Cast the byte array member to integer. */ NameNum1 = (int)class_outData[class_oD_pos]; /* Move to the next byte */ /* This one is skipped. Could be the high order byte of NameNum. */ class_oD_pos++; dPC_RecRef = getObjectReference(dPC_dbconn, NameNum1, dPC_WhereClause); writeCode(dPC_RecRef[1] + "." + dPC_RecRef[2]); class_SAfter = "P"; break; case 75: /* 0x4b */ class_SBefore = "Y"; writeCode("Null"); class_SAfter = "Y"; break; case 76: /* 0x4c */ class_SBefore = "Y"; writeCode("["); class_SAfter = "N"; break; case 77: /* 0x4d */ class_SBefore = "N"; writeCode("]"); class_SAfter = "Y"; break; case 78: /* 0x4e */ /* Comments with a line of code. */ $tString = getComment(); writeCode($tString); dPC_return = lineFeed(dPC_disposition); break; case 79: /* 0x4f */ /* Blank line */ dPC_return = lineFeed(dPC_disposition); break; case 80: /* 0x50 */ /* Indicator of a number coming up. */ /* Arguement is number of bytes used to represent number. */ $tString = getNumber(18); writeCode($tString); class_SAfter = "P"; break; case 81: /* 0x51 */ class_SBefore = "Y"; writeCode("PanelGroup"); class_SAfter = "Y"; break; case 84: /* 0x54 */ class_SBefore = "Y"; writeCode("Component"); class_SAfter = "Y"; break; case 85: /* 0x55 */ $tString = getComment(); writeCode($tString); dPC_return = lineFeed(dPC_disposition); break; case 86: /* 0x56 */ class_SBefore = "Y"; writeCode("Constant"); class_SAfter = "Y"; break; case 87: /* 0x57 */ class_SBefore = "N"; writeCode(":"); class_SAfter = "N"; break; case 88: /* 0x58 */ class_SBefore = "Y"; writeCode("import"); class_SAfter = "Y"; break; case 89: /* 0x59 */ class_SBefore = "N"; writeCode("*"); class_SAfter = "N"; break; case 90: /* 0x5a */ class_SBefore = "Y"; class_Indent = "+"; writeCode("class"); /* New line after the class name */ class_Class = true; /* Flag indicating within class */ class_inClass = true; class_SAfter = "Y"; break; case 91: /* 0x5b */ class_SBefore = "Y"; class_Indent = "-"; /* Log: 200709-format Start */ /* Look back to the previous byte to check for semi colon */ if ((int)(class_outData[class_oD_pos - 1] & 0xff) != 21 ) { /* If there wasn't a semi colon, do a linefeed. */ dPC_return = lineFeed(dPC_disposition); } /* writeCode("end-class"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("end-class;"); class_oD_pos++; } else { writeCode("end-class"); } /* Log: 200709-format end */ class_SAfter = "Y"; class_inClass = false; break; case 92: /* 0x5c */ class_SBefore = "Y"; writeCode("extends"); class_SAfter = "P"; break; case 93: /* 0x5d */ class_SBefore = "Y"; writeCode("out"); class_SAfter = "P"; break; case 94: /* 0x5e */ class_SBefore = "Y"; writeCode("property"); class_SAfter = "Y"; break; case 95: /* 0x5f */ class_SBefore = "Y"; writeCode("get"); class_SAfter = "Y"; break; /* Log: 200711-0x60 Start */ case 96: /* 0x60 */ class_SBefore = "Y"; writeCode("readonly"); break; /* Log: 200711-0x60 end */ case 97: /* 0x61 */ class_SBefore = "Y"; /* If within a class definition, backoff indent */ //if (class_inClass) class_Indent = "-"; /* Log: 200709-format Start */ if (class_inClass) class_Indent = "B"; /* writeCode("private"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("private;"); class_oD_pos++; } else { writeCode("private"); } /* Log: 200709-format end */ //if (class_inClass) class_Indent = "+"; dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 98: /* 0x62 */ class_SBefore = "Y"; writeCode("instance"); class_SAfter = "Y"; break; case 99: /* 0x63 */ /* Check if previous was a class extends or implements definition */ /* Log: 200709-format Start */ /* if (class_ClassExtends) { */ if (class_ClassExtends || class_implements) { /* Log: 200709-format end */ dPC_return = lineFeed(dPC_disposition); class_Class = false; /* Log: 200709-format Start */ class_ClassExtends = false; class_implements = false; /* Log: 200709-format end */ } class_SBefore = "Y"; /* Do not indent method if in class declaration */ if (!class_inClass) class_Indent = "+"; writeCode("method"); class_SAfter = "Y"; break; case 100: /* 0x64 */ class_SBefore = "Y"; class_Indent = "-"; /* Log: 200709-format Start */ /* writeCode("end-method"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("end-method;"); class_oD_pos++; } else { writeCode("end-method"); } /* Log: 200709-format end */ class_SAfter = "Y"; break; case 101: /* 0x65 */ class_SBefore = "Y"; writeCode("try"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "Y"; break; case 102: /* 0x66 */ class_SBefore = "Y"; writeCode("catch"); class_SAfter = "Y"; break; case 103: /* 0x67 */ class_SBefore = "Y"; writeCode("end-try"); class_SAfter = "Y"; break; case 105: /* 0x69 */ class_SBefore = "Y"; writeCode("create"); class_SAfter = "Y"; break; case 106: /* 0x6a */ class_SBefore = "Y"; /* Log: 200709-format Start */ /* class_SAfter = "Y"; */ /* break; */ /* writeCode("end-get"); */ /* Look ahead to the next byte to check for semi colon. */ if ((int)(class_outData[class_oD_pos + 1] & 0xff) == 21) { writeCode("end-get;"); class_oD_pos++; } else { writeCode("end-get"); } /* Log: 200709-format end */ class_SAfter = "Y"; break; case 107: /* 0x6b */ class_SBefore = "Y"; writeCode("end-set"); class_SAfter = "Y"; break; case 109: /* 0x6d */ class_SBefore = "Y"; writeCode("/+ "); $tString = get_String(); writeCode($tString); writeCode(" +/"); dPC_return = lineFeed(dPC_disposition); class_SAfter = "N"; break; case 110: /* 0x6e */ class_SBefore = "Y"; writeCode("Continue"); class_SAfter = "Y"; break; /* Log: 200709-format Start */ case 114: /* 0x72 */ /* Check if currently in a class definition */ if (class_Class) { class_implements = true; class_Class = false; } class_SBefore = "Y"; writeCode("implements"); class_SAfter = "Y"; break; /* Log: 200709-format end */ default: System.out.println(""); System.out.println("****************"); System.out.println("CodeLine: " + class_CodeLine); System.out.println("Unknown:" + dPC); break; } return dPC_return; } static String get_String() { String currentMethod = "get_String:"; /* PeopleCode string characters are stored as two byte characters. With the standard ASCII, only the first byte is used. I have not bothered trying to support this yet since we are using just ASCII. I am reading the first byte, and then just junking the second byte (reading, but doing nothing). */ /* Spins through bytes until the ASCII position is zero, i.e., end of string. */ String $out_String = ""; char tmp_c; /* Move to the next byte */ class_oD_pos++; while (class_outData[class_oD_pos] != 0) { tmp_c = (char)class_outData[class_oD_pos]; /* Cast byte array member to a char. */ $out_String = $out_String + tmp_c; class_oD_pos++; /* Junk 00 byte */ class_oD_pos++; /* Possible ASCII char */ } class_oD_pos++; /* Position to next byte */ return $out_String; } static String getComment() { String currentMethod = "getComment:"; int comment_length = 0; class_oD_pos++; /* Next byte, the comment length has 2 bytes low order first */ /* The length byte is bit wide ANDed and cast to integer. */ comment_length = (int)class_outData[class_oD_pos] & 0xff; class_oD_pos++; /* Next byte */ comment_length = comment_length + ((int)class_outData[class_oD_pos] & 0xff) * 256; if (class_Debug) System.out.println(currentMethod + "comment_length= " + comment_length); String $out_String = ""; char tmp_c; for (int i = 1; i <= comment_length; i++) { if (class_outData[class_oD_pos] != 0) { tmp_c = (char)class_outData[class_oD_pos]; $out_String = $out_String + tmp_c; } class_oD_pos++; /* Next byte */ } if (class_Debug) System.out.println(currentMethod + "$out_String=" + $out_String); return $out_String; } static String getNumber(int gN_bytes) { String currentMethod = "getNumber:"; /* Handles whatever is stored, but displays large and pricise numbers using scientific notation. Example bugabo: 312345678901234567890123456789012 */ double nValue = 0.0; int dValue = 0; /* decimal position from far right going left */ String out_number = ""; boolean display_integer = true; int num_bytes = gN_bytes - 3; class_oD_pos++; /* Skip the first byte */ class_oD_pos++; /* Decimal position is in the second byte. */ dValue = (int)class_outData[class_oD_pos]; class_oD_pos++; /* Move to the 3rd byte */ /* Base 256 number without decimal starts at 3rd byte and goes for 16 bytes. */ for (int i = 0; i < num_bytes; i++) { /* AND the byte with xFF so casting to integer does an unsigned conversion. */ nValue = nValue + ((int)(class_outData[class_oD_pos] & 0xff) * Math.pow(256, i)); if ((i > 2) && (!((int)(class_outData[class_oD_pos] & 0xff) == 0))) display_integer = false; class_oD_pos++; } if ((dValue == 0) && (display_integer)) /* No decimal and it's relatively small, cast to integer. */ out_number = String.valueOf((int)nValue); else { /* If there is a deciaml value, use it to set the decimal position. */ out_number = String.valueOf((double)nValue * Math.pow(10, (-1 * dValue))); } return out_number; } static void writeCode(String $inChar) { String currentMethod = "writeCode:"; /* Handle spacing of each character, command, variable, etc. within PeopleCode. */ String LastChar = class_CodeLine.substring(class_CodeLine.length()); /* Special characters that a "P" will not add a space before or after */ String skipper = " ().;,[]|:"; if ($inChar.length() > 0) { if (!class_CodeLine.equalsIgnoreCase("")) { if ((class_SAfter.equalsIgnoreCase("Y") && (!LastChar.equalsIgnoreCase(" "))) || (class_SAfter.equalsIgnoreCase("P") && (skipper.indexOf(LastChar) == 0) && (skipper.indexOf($inChar.substring(0,1)) == 0))) { class_CodeLine = class_CodeLine + " "; LastChar = " "; } if ((class_SBefore.equalsIgnoreCase("Y") && (!LastChar.equalsIgnoreCase(" "))) || (class_SBefore.equalsIgnoreCase("P") && (skipper.indexOf(LastChar) == 0))) { class_CodeLine = class_CodeLine + " "; } } class_CodeLine = class_CodeLine + $inChar; class_SAfter = "N"; class_SBefore = "N"; } } static String[] getObjectReference(Connection gOR_dbconn, int in_NameNum, String gOR_WhereClause) { String currentMethod = "getObjectReference:"; /* For some reason the number referenced in the PeopleCode is one less than the NAMENUM. Hence the '+1' in the WHERE clause. Output is String array with the first being the Recname and the 2nd being Refname. */ String $out_Recname = "Unknown"; String $out_Refname = "Unknown"; StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ //sb.append("Select RECNAME, REFNAME from sysadm.pspcmname"); sb.append("Select RECNAME, REFNAME from " + class_Dbowner + ".pspcmname"); /* Log: 200711-mysql end */ sb.append(" where upper(OBJECTVALUE1) = ? and upper(OBJECTVALUE2) = ? "); sb.append(" and upper(OBJECTVALUE3) = ? and upper(OBJECTVALUE4) = ? "); sb.append(" and upper(OBJECTVALUE5) = ? and upper(OBJECTVALUE6) = ? "); sb.append(" and upper(OBJECTVALUE7) = ? "); sb.append(" and NAMENUM = ? "); String s = sb.toString(); String[] Rec = new String[4]; String[] Ref = new String[4]; int row_cnt = 0; String regex = "OBJECTVALUE[1-7] = '"; String[] $ObjectValue = gOR_WhereClause.split(regex); for (int w1 = 1; w1 <= 7; w1++) { $ObjectValue[w1] = $ObjectValue[w1].substring(0,$ObjectValue[w1].indexOf("'")); } try { PreparedStatement ps = gOR_dbconn.prepareStatement(s); ps.clearParameters(); for (int w2 = 1; w2 <= 7; w2++) { ps.setString(w2, $ObjectValue[w2].toUpperCase()); } ps.setInt(8, in_NameNum + 1); /* Note that NameNum has 1 added. */ ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { Rec[row_cnt] = resultset.getString("RECNAME"); Ref[row_cnt] = resultset.getString("REFNAME"); } } catch(SQLException sqlexception) { sqlexception.printStackTrace(); System.out.println(currentMethod + sqlexception.toString()); } Rec[2] = Ref[1]; /* Expect 1 row returned. */ if (class_Debug) System.out.println(currentMethod + " Select from pspcmname: " + Rec[1] + " " + Rec[2]); return Rec; } static String lineFeed(String lF_Disposition) { String currentMethod = "lineFeed:"; String indenter = ""; String lF_Output = ""; byte[] out_b; /* Indentation preprocessing */ if (class_Indent.equalsIgnoreCase("-") && (class_Indent_size >= INDENT_SIZE)) class_Indent_size = class_Indent_size - INDENT_SIZE; if (class_Indent.equalsIgnoreCase("B") && (class_Indent_size >= INDENT_SIZE)) class_Indent_size = class_Indent_size - INDENT_SIZE; if (class_Indent.equalsIgnoreCase("R") && (class_Indent_size >= INDENT_SIZE)) class_Indent_size = class_Indent_size + INDENT_SIZE; if (class_Indent.equalsIgnoreCase("0")) class_Indent_size = 0; /* Do the indentation */ for (int i = 1; i <= class_Indent_size; i++) { indenter = indenter + " "; } class_CodeLine = indenter + class_CodeLine; if (lF_Disposition.equals(RETURN_LITERAL)) { /* Output returned by method. */ /* PeopleCode and SQL are being formatted for output as part of an App Engine. */ lF_Output = "\t" + class_CodeLine + "\n"; } else { /* Output written to screen or file */ if (class_Output_To_Screen) System.out.println(class_CodeLine); if (class_Output_To_File) { class_CodeLine = class_CodeLine + "\n"; out_b = class_CodeLine.getBytes(); try { class_outFile.write(out_b); } catch (Exception e) { System.out.println(currentMethod + " Error is " + e.toString()); } } } /* Indentation post processing */ if (class_Indent.equalsIgnoreCase("+")) class_Indent_size = class_Indent_size + INDENT_SIZE; if (class_Indent.equalsIgnoreCase("B")) class_Indent_size = class_Indent_size + INDENT_SIZE; if (class_Indent.equalsIgnoreCase("R")) class_Indent_size = class_Indent_size - INDENT_SIZE; class_CodeLine = ""; class_Indent = ""; return lF_Output; } static byte[] stagePeopleCode(Connection sPC_dbconn, String sPC_WhereClause) { String currentMethod = "stagePeopleCode:"; /* PeopleSoft store PeopleCode in a LONG RAW field. Each field can only hold 24000 bytes of code. If the PC is longer than that, it will be split up in multiple rows, ordered by PROGSEQ. When reading LONG RAW, SQR translates the binary data into text representing the hex values. However SQR starts choking when the data gets too big. It seemed that the only hhope I had was to deal with it in managable size chunks, however I was unable to find any Oracle / SQR tricks to return only a substring of the LONG RAW field. BLOBs were easier to work with, but I could still not find a way to translate the LONG RAW into a BLOB on the fly. My solution was to create a temp table (if it didn't already exist), and copy the LONG RAW into a BLOB field there, converting it using the to_lob Oracle function. Now I can use the dbms_lob functions to return 2000 byte chunks. */ /* Java works with byte streams so the whole thing simplified. Don't need method Build-Temp-Table, NextByte, Get-Next-Segment, HexToDec. sPC_WhereClause is: OBJECTVALUE1 = 'something', OBJECTVALUE2 = 'something', etc. */ StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ //sb.append("Select PROGSEQ, PROGTXT from sysadm.pspcmprog"); sb.append("Select PROGSEQ, PROGTXT from " + class_Dbowner + ".pspcmprog"); /* Log: 200711-mysql end */ sb.append(" where upper(OBJECTVALUE1) = ? and upper(OBJECTVALUE2) = ? "); sb.append(" and upper(OBJECTVALUE3) = ? and upper(OBJECTVALUE4) = ? "); sb.append(" and upper(OBJECTVALUE5) = ? and upper(OBJECTVALUE6) = ? "); sb.append(" and upper(OBJECTVALUE7) = ? "); sb.append(" order by PROGSEQ "); String s = sb.toString(); /* Oracle begin */ String[] OOB = new String[4000]; /* Oracle end */ /* MySQL begin */ //byte[] OOB = new byte[4000]; //int bytecnt = 0; //String blobdata = ""; /* MySQL end */ int[] progseq = new int[100]; int row_cnt = 0; if (class_Debug) System.out.println(currentMethod + " SQL: " + s); if (class_Debug) System.out.println(currentMethod + " sPC_WhereClause: " + sPC_WhereClause); String regex = "OBJECTVALUE[1-7] = '"; String[] $ObjectValue = sPC_WhereClause.split(regex); for (int w1 = 1; w1 <= 7; w1++) { $ObjectValue[w1] = $ObjectValue[w1].substring(0,$ObjectValue[w1].indexOf("'")); } try { PreparedStatement ps = sPC_dbconn.prepareStatement(s); ps.clearParameters(); for (int w2 = 1; w2 <= 7; w2++) { ps.setString(w2, $ObjectValue[w2].toUpperCase()); } ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { /* Oracle begin */ OOB[row_cnt] = resultset.getString("progtxt"); /* Oracle end */ /* MySQL begin */ //Blob datablob = resultset.getBlob("progtxt"); //InputStream in = datablob.getBinaryStream(); //try { // bytecnt = in.read(OOB); //} catch (IOException e) { // System.out.println("IOException error" + e.getMessage()); //} //for (int k = 0; k < bytecnt; k++) { // blobdata = blobdata + (char) OOB[k]; //} /* MySQL end */ progseq[row_cnt] = resultset.getInt("progseq"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if (class_Debug) { /* Display the hexadecimal data as stored in the database. */ for (int zz = 1; zz <= row_cnt; zz++) { System.out.println(currentMethod + " Select from pspcmprog: " + OOB[zz]); } } if (class_Debug) System.out.println(currentMethod + " Convert from hex to decimal. "); /* Oracle begin */ /* Convert from hex to decimal. First put data into String. */ String blobdata = ""; for (int k = 1; k < row_cnt; k++) { blobdata = blobdata + OOB[k]; } /* Oracle end */ char[] blobchar = blobdata.toCharArray(); /* String into char array. */ if (class_Debug) System.out.println(currentMethod + " Length of progtxt from pspcmprog: " + blobdata.length()); int length = blobdata.length(); byte[] outData = new byte[length/2]; /* Create byte array for decodeHex output. */ outData = decodeHex(blobchar); return outData; } static String[][][][] stageAE(Connection sAE_dbconn, String sAE_WhereClause) { String currentMethod = "stageAE:"; /* Return a 4 dimension array of Application Engine Sections, Steps, Actions, and their descriptions. String [0][0][0][0] is 1st section String [0][0][0][1] is 1st section description String [0][1l[0][0] is 1st section, 1st step String [0][1l[0][1] is 1st section, 1st step description String [0][1l[0][2] is 1st section, 1st step active/inactive String [0][1l[0][3] is 1st section, 1st step Msg Set Nbr and Msg Nbr String [0][1l[0][4] is 1st section, 1st step No Row processing String [0][1l[1][0] is 1st section, 1st step, 1st action String [0][1l[1][1] is 1st section, 1st step, 1st action description String [0][1l[1][2] is 1st section, 1st step, 1st action PeopleCode | SQL | Call String [0][1l[2][0] is 1st section, 1st step, 2nd action String [0][1l[2][1] is 1st section, 1st step, 2nd action description String [0][1l[2][2] is 1st section, 1st step, 2nd action PeopleCode | SQL | Call String [0][1l[3][0] is 1st section, 1st step, 3rd action String [0][1l[3][1] is 1st section, 1st step, 3rd action description String [0][1l[3][2] is 1st section, 1st step, 3rd action PeopleCode | SQL | Call String [0][2l[0][0] is 1st section, 2nd step String [0][2l[0][1] is 1st section, 2nd step description String [0][2l[0][2] is 1st section, 2nd step active/inactive String [0][2l[0][3] is 1st section, 2nd step Msg Set Nbr and Msg Nbr String [0][2l[0][4] is 1st section, 2nd step No Row processing String [0][2l[1][0] is 1st section, 2nd step, 1st action String [0][2l[1][1] is 1st section, 2nd step, 1st action description String [0][2l[1][2] is 1st section, 2nd step, 1st action PeopleCode | SQL | Call String [1][0][0][0] is 2nd section String [1][0][0][1] is 2nd section description String [1][1][0][0] is 2nd section, 1st step String [1][1][0][1] is 2nd section, 1st step description String [1][1][0][2] is 2nd section, 1st step active/inactive String [1][1][0][3] is 2nd section, 1st step Msg Set Nbr and Msg Nbr String [1][1][0][4] is 2nd section, 1st step No Row processing */ String[][][][] ae_data = new String[MAX_AE_SECTION][MAX_AE_STEP][MAX_AE_ACTION][MAX_AE_DESCR]; /* Array of actions with: Action, Action description, PeopleCode | SQL | Call */ String[][] ae_action = new String[5][3]; String s = ""; byte[] ae_section_b = null; byte[] ae_step_b = null; byte[] ae_descr_b = null; byte[] ae_do_appl_id_b = null; byte[] ae_do_section_b = null; byte[] ae_step_act_b = null; byte[] ae_step_row_b = null; int ae_step_msgs_i = 0; int ae_step_msgn_i = 0; int Section = 0; int Step = 0; int Action = 1; /* Zero value used for section and step. */ String tmp1_S = ""; String tmp2_S = ""; String tmp3_S = ""; String tmp4_S = ""; String tmp5_S = ""; String tmp6_S = ""; String tmp7_S = ""; StringBuffer sb = new StringBuffer(); sb.append("Select sd1.AE_SECTION, sd1.AE_STEP, sd1.DESCR, sd1.AE_DO_APPL_ID, sd1.AE_DO_SECTION, "); sb.append(" sd1.AE_ACTIVE_STATUS, sd1.AE_ON_NOROWS, sd1.MESSAGE_SET_NBR, sd1.MESSAGE_NBR "); /* Log: 200711-mysql Start */ //sb.append(" from sysadm.psaestepdefn sd1 "); sb.append(" from " + class_Dbowner + ".psaestepdefn sd1 "); /* Log: 200711-mysql end */ sb.append(" where sd1.AE_APPLID = ? "); sb.append(" and sd1.EFFDT = "); /* Log: 200711-mysql Start */ //sb.append(" (Select max(sd2.EFFDT) from sysadm.psaestepdefn sd2 "); sb.append(" (Select max(sd2.EFFDT) from " + class_Dbowner + ".psaestepdefn sd2 "); /* Log: 200711-mysql end */ sb.append(" where sd1.AE_APPLID = sd2.AE_APPLID "); sb.append(" and sd1.AE_SECTION = sd2.AE_SECTION "); sb.append(" and sd1.MARKET = sd2.MARKET "); sb.append(" and sd1.DBTYPE = sd2.DBTYPE "); sb.append(" and sd1.AE_STEP = sd2.AE_STEP "); sb.append(" and sd1.AE_SEQ_NUM = sd2.AE_SEQ_NUM) "); sb.append(" order by sd1.AE_SECTION, sd1.AE_SEQ_NUM "); s = sb.toString(); /* Parse out the "AE = ". The regular expression matches "=" and space zero or more times */ String regex = "= *"; String[] $ObjectValue = sAE_WhereClause.split(regex); if (class_Debug) System.out.println(currentMethod + " AE: " + s); if (class_Debug) System.out.println(currentMethod + " sAE_WhereClause: " + sAE_WhereClause + "Value only: " + $ObjectValue[1]); try { PreparedStatement ps = sAE_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, $ObjectValue[1]); ResultSet resultset = ps.executeQuery(); for (int row_cnt = 0; resultset.next(); row_cnt++) { /* Get the SQL results into byte arrays. */ ae_section_b = resultset.getBytes("AE_SECTION"); ae_step_b = resultset.getBytes("AE_STEP"); ae_descr_b = resultset.getBytes("DESCR"); ae_do_appl_id_b = resultset.getBytes("AE_DO_APPL_ID"); ae_do_section_b = resultset.getBytes("AE_DO_SECTION"); ae_step_act_b = resultset.getBytes("AE_ACTIVE_STATUS"); ae_step_row_b = resultset.getBytes("AE_ON_NOROWS"); ae_step_msgs_i = resultset.getInt("MESSAGE_SET_NBR"); ae_step_msgn_i = resultset.getInt("MESSAGE_NBR"); try { tmp1_S = new String(ae_section_b, "utf-8"); tmp2_S = new String(ae_step_b, "utf-8"); tmp3_S = new String(ae_descr_b, "utf-8"); tmp4_S = new String(ae_do_appl_id_b, "utf-8"); tmp5_S = new String(ae_do_section_b, "utf-8"); tmp6_S = new String(ae_step_act_b, "utf-8"); if (tmp6_S.equalsIgnoreCase("A")) tmp6_S = "ACTIVE"; if (tmp6_S.equalsIgnoreCase("I")) tmp6_S = "INACTIVE"; tmp7_S = new String(ae_step_row_b, "utf-8"); if (tmp7_S.equalsIgnoreCase("A")) tmp7_S = "No Rows: Abort"; if (tmp7_S.equalsIgnoreCase("B")) tmp7_S = "No Rows: Section Break"; if (tmp7_S.equalsIgnoreCase("C")) tmp7_S = "No Rows: Continue"; if (tmp7_S.equalsIgnoreCase("S")) tmp7_S = "No Rows: Skip Step"; if (Step == 0) { /* First time through */ ae_data[Section][0][0][0] = tmp1_S; /* Section name */ /* Section description */ ae_data[Section][0][0][1] = stageAESectionDescr(sAE_dbconn, $ObjectValue[1], tmp1_S); /* Sections do not have an ACTIVE/INACTIVE */ ae_data[Section][0][0][2] = " "; Step++; ae_data[Section][Step][0][0] = tmp2_S; /* Step name */ /* Step description */ ae_data[Section][Step][0][1] = tmp3_S; /* Step active/inactive */ ae_data[Section][Step][0][2] = tmp6_S; /* Step message */ ae_data[Section][Step][0][3] = Integer.toString(ae_step_msgs_i) + " " + Integer.toString(ae_step_msgn_i); /* Step No Row processing */ ae_data[Section][Step][0][4] = tmp7_S; } else { if (ae_data[Section][0][0][0].equals(tmp1_S)) { /* Next step in same section */ Step++; Action = 1; ae_data[Section][Step][0][0] = tmp2_S; ae_data[Section][Step][0][1] = tmp3_S; ae_data[Section][Step][0][2] = tmp6_S; ae_data[Section][Step][0][3] = Integer.toString(ae_step_msgs_i) + " " + Integer.toString(ae_step_msgn_i); ae_data[Section][Step][0][4] = tmp7_S; } else { /* Section change */ Section++; Step = 1; Action = 1; ae_data[Section][0][0][0] = tmp1_S; /* Get the section description */ ae_data[Section][0][0][1] = stageAESectionDescr(sAE_dbconn, $ObjectValue[1], tmp1_S); /* Sections do not have an ACTIVE/INACTIVE */ ae_data[Section][0][0][2] = " "; /* Step */ ae_data[Section][Step][0][0] = tmp2_S; /* Step description */ ae_data[Section][Step][0][1] = tmp3_S; ae_data[Section][Step][0][2] = tmp6_S; ae_data[Section][Step][0][3] = Integer.toString(ae_step_msgs_i) + " " + Integer.toString(ae_step_msgn_i); ae_data[Section][Step][0][4] = tmp7_S; } } /* Each iteration through psaestepdefn is a section + step. Now get the actions */ /* Pass along the ae_do_appl_id and ae_do_section for calls. Parms 5 & 6. */ /* Open new database connection and then close it to keep from using max cursors */ Connection sAEA_dbconn = null; sAEA_dbconn = openDbConnection(class_DBUrl, class_Database, class_UserID, class_Password); ae_action = stageAEAction(sAEA_dbconn, $ObjectValue[1], tmp1_S, tmp2_S, tmp4_S, tmp5_S); closeDbConnection(sAEA_dbconn); /* Output from stageAEAction uses zero position. */ while (!(ae_action[Action - 1][0] == null)) { ae_data[Section][Step][Action][0] = ae_action[Action - 1][0]; ae_data[Section][Step][Action][1] = ae_action[Action - 1][1]; ae_data[Section][Step][Action][2] = ae_action[Action - 1][2]; Action++; } } catch(UnsupportedEncodingException e) { System.out.println(currentMethod + e.toString()); } catch(NullPointerException e1) { System.out.println(currentMethod + e1.toString()); } if (class_Debug) { System.out.println(currentMethod + " section step dim1 dim2=" + tmp1_S + " " + tmp2_S + " " + Section + " " + Step); } } }catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if (class_Debug) { System.out.println(currentMethod + " ae_data[0][0]=" + ae_data[0][0]); System.out.println(currentMethod + " ae_data[0][1]=" + ae_data[0][1]); } return ae_data; } static String[][] stageAEAction(Connection sAE_dbconn, String ae, String section, String step, String ae_do_appl_id, String ae_do_section) { String currentMethod = "stageAEAction:"; /* Return String array of actions with: Action, Action description, SQL | PeopleCode | Call */ if (class_Debug) System.out.println(currentMethod + " called with dbconn " + ae + " " + section + " " + step); String[][] ae_data = new String[10][6]; int Action = 0; int row_cnt = 0; String WhereClause = ""; String peopleCode = ""; /* SQL with case statement used to put ordering on Actions - results are imperfect. */ StringBuffer sb = new StringBuffer(); sb.append("Select AE_STMT_TYPE, DESCR, SQLID, MARKET, DBTYPE, EFFDT "); sb.append(" ,case "); sb.append(" when ae_stmt_type = 'C' then 18 "); //Call sb.append(" when ae_stmt_type = 'D' then 5 "); //Do Select sb.append(" when ae_stmt_type = 'H' then 3 "); //So When sb.append(" when ae_stmt_type = 'M' then 17 "); //Log Message sb.append(" when ae_stmt_type = 'N' then 19 "); //Do Until sb.append(" when ae_stmt_type = 'P' then 12 "); //PeopleCode sb.append(" when ae_stmt_type = 'S' then 14 "); //SQL sb.append(" when ae_stmt_type = 'W' then 9 "); //Do While sb.append(" when ae_stmt_type = 'X' then 0 "); //XSLT sb.append(" end "); /* Log: 200711-mysql Start */ //sb.append(" from sysadm.psaestmtdefn "); sb.append(" from " + class_Dbowner + ".psaestmtdefn "); /* Log: 200711-mysql end */ sb.append(" where AE_APPLID = ? "); sb.append(" and AE_SECTION = ? "); sb.append(" and AE_STEP = ? "); sb.append(" order by 5, 7 "); String s = sb.toString(); try { PreparedStatement ps = sAE_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, ae); ps.setString(2, section); ps.setString(3, step); ResultSet resultset = ps.executeQuery(); for (row_cnt = 0; resultset.next(); row_cnt++) { /* Get the SQL results into byte arrays */ ae_data[Action][0] = resultset.getString("AE_STMT_TYPE"); ae_data[Action][1] = resultset.getString("DESCR"); ae_data[Action][2] = resultset.getString("SQLID"); ae_data[Action][3] = resultset.getString("MARKET"); ae_data[Action][4] = resultset.getString("DBTYPE"); ae_data[Action][5] = resultset.getString("EFFDT"); Action++; } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } catch(NullPointerException e2) { System.out.println(currentMethod + e2.toString()); } /* Translate to an action statement */ for (Action = 0; Action < row_cnt; Action++) { /* Translate database codes into their human form */ if (ae_data[Action][4].equalsIgnoreCase("")) ae_data[Action][4] = "default"; if (ae_data[Action][4].equalsIgnoreCase(" ")) ae_data[Action][4] = "default"; if (ae_data[Action][4].equalsIgnoreCase("1")) ae_data[Action][4] = "DB2"; /* Use only CCYY-MM-DD of the EFFDT */ ae_data[Action][5] = ae_data[Action][5].substring(0,10); /* Decipher and process the Action type */ if (ae_data[Action][0].equalsIgnoreCase("C")) { ae_data[Action][0] = CALL_LITERAL; class_CodeLine = "Program ID: " + ae_do_appl_id + " Section Name: " + ae_do_section; ae_data[Action][2] = lineFeed(RETURN_LITERAL); } if (ae_data[Action][0].equalsIgnoreCase("D")) { ae_data[Action][0] = "Do Select"; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("H")) { ae_data[Action][0] = "Do When"; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("M")) { ae_data[Action][0] = LOGMSG_LITERAL; /* Get the Log Message parm, if it exists, by appl ID, Section, Step, Database Type */ ae_data[Action][2] = processMsg(sAE_dbconn, ae, section, step, ae_data[Action][4], RETURN_LITERAL); } if (ae_data[Action][0].equalsIgnoreCase("N")) { ae_data[Action][0] = "Do Until"; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("P")) { ae_data[Action][0] = PC_LITERAL; /* Check if user has PeopleCode output turned on or off */ if (class_Output_AEPC) { /* Build up the where clause for extracting PeopleCode */ WhereClause = "OBJECTVALUE1 = '" + ae + "'and OBJECTVALUE2 = '" + section + "' and OBJECTVALUE3 = '" + ae_data[Action][3] + "'and OBJECTVALUE4 = '" + ae_data[Action][4] + "' and OBJECTVALUE5 = '" + ae_data[Action][5] + "'and OBJECTVALUE6 = '" + step + "' and OBJECTVALUE7 = 'OnExecute'"; if (class_Debug) System.out.println(currentMethod + " PeopleCode where clause=" + WhereClause); /* Get the PeopleCode as a byte array. */ peopleCode = processWhereClausePC(sAE_dbconn, WhereClause, RETURN_LITERAL); /* Add an initial line feed to the PeopleCode output. */ ae_data[Action][2] = "\n" + peopleCode; if (class_Debug) System.out.println(currentMethod + " PeopleCode=" + ae_data[Action][2]); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("S")) { ae_data[Action][0] = SQL_LITERAL; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("W")) { ae_data[Action][0] = "Do While"; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } if (ae_data[Action][0].equalsIgnoreCase("X")) { ae_data[Action][0] = "XSLT"; if (class_Output_AESQL) { ae_data[Action][2] = processSQL(sAE_dbconn, " = '" + ae_data[Action][2] + "'", RETURN_LITERAL); } else { ae_data[Action][2] = " "; } } } if (class_Debug) { for (Action = 0; Action < row_cnt; Action++) { System.out.println(currentMethod + " ae_data[" + Action + "][0]=" + ae_data[Action][0] + " ae_data[" + Action + "][1]=" + ae_data[Action][1]); } } return ae_data; } static String stageAESectionDescr(Connection sAE_dbconn, String ae, String section) { String currentMethod = "stageAESectionDescr:"; String ae_data = ""; StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ //sb.append("Select DESCR from sysadm.psaesectdtldefn"); sb.append("Select DESCR from " + class_Dbowner + ".psaesectdtldefn"); /* Log: 200711-mysql end */ sb.append(" where AE_APPLID = ? "); sb.append(" and AE_SECTION = ? "); String s = sb.toString(); try { PreparedStatement ps = sAE_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, ae); ps.setString(2, section); ResultSet resultset = ps.executeQuery(); for (int row_cnt = 0; resultset.next(); row_cnt++) { ae_data = resultset.getString("DESCR"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if (class_Debug) System.out.println(currentMethod + " ae_data=" + ae_data); return ae_data; } /* Log: 200711-ae%sql Start */ static String stageAE_SQL(Connection sAESQL_dbconn, String sAESQL_in) { /* Extract App Engine stand alone SQL. */ String currentMethod = "stageAE_SQL:"; String[] sAESQL_sql = new String[MAX_AE_SQL]; String parseme = ""; String parseme2 = ""; String regex = ""; int cnt = 0; int j = 0; String sql_S = ""; if (class_Debug) System.out.println(currentMethod + " sAESQL_in: " + sAESQL_in); /* Following pattern works to parse out %Sql in its many variations with testers: http://ww.fileformat.info/tool/regex.htm http://regex.powertoy.org/ %Sql\(.+\) but cannot get it to work here. -Jeff */ /* Parse out "%Bind(" + anything not a right banana one or more times + right banana ")". */ regex = "%(?i)Bind\\([^)]+\\)"; Pattern patternB = Pattern.compile(regex); Matcher matcherB = patternB.matcher(sAESQL_in); while (matcherB.find()) { parseme = sAESQL_in.substring(matcherB.start(), matcherB.end()); if (class_Debug) System.out.println(currentMethod + " First regex out of %Sql = " + parseme); } /* Replace all %Bind variables with SQL wildcard. */ parseme = matcherB.replaceAll("%"); if (class_Debug) System.out.println(currentMethod + " After %Bind replaced with % = " + parseme); /* Parse out "%Sql(" + anything not a comma nor a right banana one or more times. */ regex = "%(?i)Sql\\([^,^)]+"; Pattern pattern = Pattern.compile(regex); Matcher matcher = pattern.matcher(parseme); while (matcher.find()) { parseme2 = parseme.substring(matcher.start(), matcher.end()); /* Get rid of "%Sql(". */ parseme2 = parseme2.substring(5,parseme2.length()).trim(); if (class_Debug) System.out.println(currentMethod + " Second regex out of %Sql = " + parseme2); /* Put unique AE %Sql's into the String array in the order of their appearance in the SQL. */ /* Using MAX_AE_SQL works because arrays start at zero. */ j = 0; while (!(sAESQL_sql[j] == null) && (j < MAX_AE_SQL)) { if (sAESQL_sql[j].equalsIgnoreCase(parseme2)) { j = MAX_AE_SQL; break; } j++; } if (j != MAX_AE_SQL) { sAESQL_sql[cnt] = parseme2; cnt++; } } /* Build up output String of AE stand alone SQLs separated by 2 lines and tabbed (same format as SQL). */ j = 0; while (!(sAESQL_sql[j] == null) && (j < MAX_AE_SQL)) { sql_S = sql_S + "\n\n\t" + stageSQL(sAESQL_dbconn, " = '" + sAESQL_sql[j] + "'"); j++; } if (class_Debug) System.out.println(currentMethod + " output=" + sql_S); return sql_S; } /* Log: 200711-ae%sql end */ static String stageSQL(Connection sSQL_dbconn, String sSQL_WhereClause) { String currentMethod = "stageSQL:"; /* Log: 200711-sqlwild Method stageSQL is re-written without log comments. */ byte[] sqlid_b = null; byte[] sqltext_b = null; byte[] dbtype_b = null; byte[] sqltype_b = null; int row_cnt = 0; String regex = ""; String[] $ObjectValue = new String[5]; String sql_S = ""; String sqlid_S = ""; String sqltype_S = ""; String dbtype_S = ""; String tmp_S = ""; String sqlid_tmp = ""; String sqltype_tmp = ""; String dbtype_tmp = ""; /* SQL and XSLT are stored in PSSQLTEXTDEFN.SQLTEXT. */ StringBuffer sb = new StringBuffer(); sb.append("Select s1.SQLID, s1.SQLTYPE, s1.DBTYPE, s1.SQLTEXT from " + class_Dbowner + ".pssqltextdefn s1"); sb.append(" where upper(s1.SQLID) like ? "); sb.append(" and s1.EFFDT = "); sb.append(" (select max(s2.EFFDT) from " + class_Dbowner + ".pssqltextdefn s2 "); sb.append(" where s2.SQLID = s1.SQLID "); sb.append(" and s2.SEQNUM = s1.SEQNUM "); sb.append(" and s2.SQLTYPE = s1.SQLTYPE "); sb.append(" and s2.DBTYPE = s1.DBTYPE) "); sb.append(" order by s1.SQLID, s1.DBTYPE, s1.SQLTYPE, s1.SEQNUM "); String s = sb.toString(); /* Parse out the "SQL = '" and the ending "'". The regular expression matches "=", space zero or more times, and "'". */ regex = "= *'"; $ObjectValue = sSQL_WhereClause.split(regex); $ObjectValue[1] = $ObjectValue[1].substring(0,$ObjectValue[1].indexOf("'")); $ObjectValue[1] = $ObjectValue[1].toUpperCase(); if (class_Debug) System.out.println(currentMethod + " SQL: " + s); if (class_Debug) System.out.println(currentMethod + " sSQL_WhereClause: " + sSQL_WhereClause + " Value only: " + $ObjectValue[1]); try { PreparedStatement ps = sSQL_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, $ObjectValue[1]); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { sqlid_b = null; sqltype_b = null; dbtype_b = null; sqltext_b = null; sqlid_b = resultset.getBytes("SQLID"); sqltype_b = resultset.getBytes("SQLTYPE"); dbtype_b = resultset.getBytes("DBTYPE"); sqltext_b = resultset.getBytes("SQLTEXT"); if (class_Debug) System.out.println(currentMethod + " Put byte array SQL results into Strings. "); sqlid_S = new String(sqlid_b, "utf-8"); sqlid_S = sqlid_S.trim(); sqltype_S = new String(sqltype_b, "utf-8"); sqltype_S = sqltype_S.trim(); if (sqltype_S.equalsIgnoreCase("0")) sqltype_S = SQL_LITERAL; if (sqltype_S.equalsIgnoreCase("1")) sqltype_S = SQL_LITERAL; if (sqltype_S.equalsIgnoreCase("2")) sqltype_S = "SQL View"; if (sqltype_S.equalsIgnoreCase("6")) sqltype_S = XSLT_LITERAL; dbtype_S = new String(dbtype_b, "utf-8"); dbtype_S = dbtype_S.trim(); if (dbtype_S.equalsIgnoreCase("")) dbtype_S = "default"; if (dbtype_S.equalsIgnoreCase("1")) dbtype_S = "DB2"; if (dbtype_S.equalsIgnoreCase("2")) dbtype_S = "Oracle"; if (dbtype_S.equalsIgnoreCase("3")) dbtype_S = "Informix"; if (dbtype_S.equalsIgnoreCase("4")) dbtype_S = "DB2/Unix"; if (dbtype_S.equalsIgnoreCase("5")) dbtype_S = "Sybase"; if (dbtype_S.equalsIgnoreCase("6")) dbtype_S = "Microsoft"; if (dbtype_S.equalsIgnoreCase("7")) dbtype_S = "MySQL"; tmp_S = new String(sqltext_b, "utf-8"); if ((!sqlid_tmp.equalsIgnoreCase(sqlid_S) ) || (!sqltype_tmp.equalsIgnoreCase(sqltype_S)) || (!dbtype_tmp.equalsIgnoreCase(dbtype_S) )) { if (row_cnt != 1) sql_S = sql_S + "\n\n\t"; sql_S = sql_S + "SQLID: " + sqlid_S + " "; if (sqltype_S.equalsIgnoreCase(XSLT_LITERAL)) sql_S = sql_S + "\n\t"; else sql_S = sql_S + "Database type: " + dbtype_S + "\n\t"; } if (!sqltype_S.equalsIgnoreCase(XSLT_LITERAL)) tmp_S = parseSQL(tmp_S); sql_S = sql_S + tmp_S; sqlid_tmp = sqlid_S; sqltype_tmp = sqltype_S; dbtype_tmp = dbtype_S; } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } catch(UnsupportedEncodingException e) { e.printStackTrace(); System.out.println(currentMethod + e.toString()); } catch(NullPointerException e1) { e1.printStackTrace(); System.out.println(currentMethod + e1.toString()); } if (class_Debug) System.out.println(currentMethod + " sqltext=" + sql_S); return sql_S; } static String parseSQL(String pS_SQL) { String currentMethod = "parseSQL:"; /* Formatting of SQL differs from Application Designer */ String regex = ""; String replace = ""; String b_a = ""; String[] out1; String out = ""; /* Two dimentional array of: a) regular expression to parse SQL b) if the return line goes Before or After c) what to replace the lost delimiter with */ String[][] sql_return = { //{", +", "B", " ,"}, {" +(?i)select *", "B", "SELECT "}, {"(?i)from +", "B", " FROM "}, {"(?i)and *", "B", " AND "}, {" +(?i)set *", "B", " SET "}, {"(?i)where +", "B", " WHERE "}, {"(?i)group +(?i)by +", "B", " GROUP BY "}, {"(?i)having +", "B", "HAVING "}, {"(?i)order +(?i)by +", "B", " ORDER BY "}}; for (int loop = 0; loop < sql_return.length; loop++) { regex = sql_return[loop][0]; b_a = sql_return[loop][1]; replace = sql_return[loop][2]; if (loop == 0) { out1 = pS_SQL.split(regex); } else { out1 = out.split(regex); } out = ""; for (int i = 0; i < out1.length; i++) { if (!(i == 0)) if (b_a.equalsIgnoreCase("A")) out = out + "\n\t"; out = out + out1[i]; if (i < out1.length - 1) { if (b_a.equalsIgnoreCase("B")) { out = out + "\n\t" + replace; } else { out = out + replace; } } } } return out; } static String stageMsg(Connection sM_dbconn, String sM_ae, String sM_section, String sM_step, String sM_db) { String currentMethod = "stageMsg:"; if (class_Debug) System.out.println(currentMethod + " sM_ae=" + sM_ae + " sM_section=" + sM_section + " sM_step=" + sM_step + " sM_db=" + sM_db); StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ //sb.append("Select MD1.AE_MESSAGE_PARMS from sysadm.psaestepmsgdefn MD1 "); sb.append("Select MD1.AE_MESSAGE_PARMS from " + class_Dbowner + ".psaestepmsgdefn MD1 "); /* Log: 200711-mysql end */ sb.append(" where MD1.AE_APPLID = ? "); sb.append(" and MD1.AE_SECTION = ? "); sb.append(" and MD1.AE_STEP = ? "); sb.append(" and MD1.DBTYPE = ? "); sb.append(" and MD1.EFFDT = "); /* Log: 200711-mysql Start */ //sb.append(" (Select max(MD2.EFFDT) from sysadm.psaestepmsgdefn MD2 "); sb.append(" (Select max(MD2.EFFDT) from " + class_Dbowner + ".psaestepmsgdefn MD2 "); /* Log: 200711-mysql end */ sb.append(" where MD2.AE_APPLID = MD1.AE_APPLID "); sb.append(" and MD2.AE_SECTION = MD1.AE_SECTION "); sb.append(" and MD2.AE_STEP = MD1.AE_STEP "); sb.append(" and MD2.DBTYPE = MD1.DBTYPE ) "); String s = sb.toString(); byte[] parm_b = null; int row_cnt = 0; String dbtype_S = ""; if (sM_db.equalsIgnoreCase("default")) dbtype_S = " "; if (sM_db.equalsIgnoreCase("DB2")) dbtype_S = "1"; if (sM_db.equalsIgnoreCase("Oracle")) dbtype_S = "2"; if (sM_db.equalsIgnoreCase("Informix")) dbtype_S = "3"; if (sM_db.equalsIgnoreCase("DB2/Unix")) dbtype_S = "4"; if (sM_db.equalsIgnoreCase("Sybase")) dbtype_S = "5"; if (sM_db.equalsIgnoreCase("Microsoft")) dbtype_S = "6"; if (sM_db.equalsIgnoreCase("MySQL")) dbtype_S = "7"; try { PreparedStatement ps = sM_dbconn.prepareStatement(s); ps.clearParameters(); ps.setString(1, sM_ae); ps.setString(2, sM_section); ps.setString(3, sM_step); ps.setString(4, dbtype_S); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { parm_b = resultset.getBytes("AE_MESSAGE_PARMS"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if (class_Debug) System.out.println(currentMethod + " Put byte array into String."); String parm_S = ""; try { parm_S = new String(parm_b, "utf-8"); /* Convert from byte array to String */ } catch(UnsupportedEncodingException e) { System.out.println(currentMethod + e.toString()); } catch(NullPointerException e1) { System.out.println(currentMethod + e1.toString()); } if (class_Debug) System.out.println(currentMethod + " Msg Parm=" + parm_S); return parm_S; } static String promptUser() { String currentMethod = "promptUser:"; String $InputMask = ""; String TestChar = ""; String regex = ""; String pU_WhereClause = ""; String $ObjectName = ""; /* Log: 200711-handle Start */ String tmp = ""; /* Log: 200711-handle end */ while (_TRUE) { pU_WhereClause = ""; $ObjectName = ""; System.out.println(" "); $InputMask = readEntry("Enter Search ('?' for instructions) "); if ($InputMask.equalsIgnoreCase("")) { pU_WhereClause = EXIT_LITERAL; break; } TestChar = $InputMask.substring(0,1); if (TestChar.equals("?")) { showInstructions(); //break; } else if (TestChar.equals("/")) { /* Configuration Options */ /* Remove leading "/" from $InputMask */ regex = "(/+)"; $InputMask = $InputMask.replaceFirst(regex,""); if ($InputMask.equals("*")) { System.out.println(" "); System.out.println(" == Current Settings =="); System.out.print(" Output to File : "); showTrueOrFalse(class_Output_To_File); System.out.print(" Output to Screen : "); showTrueOrFalse(class_Output_To_Screen); System.out.print(" Output App Engine SQL : "); showTrueOrFalse(class_Output_AESQL); /* Log: 200711-ae%sql Start */ System.out.print(" Output App Eng %Sql : "); showTrueOrFalse(class_Output_AE_SQL); /* Log: 200711-ae%sql end */ System.out.print(" Output App Engine PC : "); showTrueOrFalse(class_Output_AEPC); /* Log: 200709-AP Start */ System.out.print(" Output App Package PC : "); showTrueOrFalse(class_Output_APPC); /* Log: 200709-AP end */ System.out.print(" Debug messages : "); showTrueOrFalse(class_Debug); System.out.println(" "); } else if ($InputMask.equalsIgnoreCase("O")) { if ((class_Output_To_File) && (class_Output_To_Screen)) { /* If both on, turn file off */ class_Output_To_File = !class_Output_To_File; } else if (class_Output_To_File) { class_Output_To_File = !class_Output_To_File; class_Output_To_Screen = true; } else if (class_Output_To_Screen) { class_Output_To_Screen = !class_Output_To_Screen; class_Output_To_File = true; } System.out.println(" "); if (class_Output_To_File) System.out.println("=== Output To File ==="); if (class_Output_To_Screen) System.out.println("=== Output To Screen ==="); System.out.println(" "); } else if ($InputMask.equalsIgnoreCase("B")) { class_Output_To_File = true; class_Output_To_Screen = true; System.out.println(" "); System.out.println("=== Output To File & Screen ==="); } else if ($InputMask.equalsIgnoreCase("D")) { class_Debug = !class_Debug; System.out.println(" "); System.out.println("=== Debug is now " + class_Debug + " ==="); /* Log: 200711-handle Start */ } else if ($InputMask.length() < 3) { System.out.println(" "); System.out.println(" *** Invalid user input. ***"); } else if (($InputMask.substring(0,2).equalsIgnoreCase("AE")) && ($InputMask.indexOf("=",2) > 0 )) { /* Check that next non space character is an equal. */ if (class_Debug) System.out.println(currentMethod + " /AE something $InputMask=" + $InputMask); if ($InputMask.length() > 2) { tmp = $InputMask.substring(2,$InputMask.length()); tmp = tmp.trim(); if (class_Debug) System.out.println(currentMethod + " /AE something tmp=" + tmp); if (tmp.substring(0,1).equalsIgnoreCase("=")) { pU_WhereClause = $InputMask.toUpperCase(); if (class_Debug) System.out.println(currentMethod + " /AE pU_WhereClause=" + pU_WhereClause); break; } } /* Log: 200709-AP Start */ } else if (($InputMask.substring(0,2).equalsIgnoreCase("AP")) && ($InputMask.indexOf("=",2) > 0 )) { /* Check that next non space character is an equal. */ if (class_Debug) System.out.println(currentMethod + " /AP something $InputMask=" + $InputMask); if ($InputMask.length() > 2) { tmp = $InputMask.substring(2,$InputMask.length()); tmp = tmp.trim(); if (class_Debug) System.out.println(currentMethod + " /AP something tmp=" + tmp); if (tmp.substring(0,1).equalsIgnoreCase("=")) { pU_WhereClause = $InputMask.toUpperCase(); if (class_Debug) System.out.println(currentMethod + " /AP pU_WhereClause=" + pU_WhereClause); break; } } //} else if ($InputMask.substring(0,2).equalsIgnoreCase("AP")) { // pU_WhereClause = $InputMask.toUpperCase(); // if (class_Debug) System.out.println(currentMethod + " /AP pU_WhereClause=" + pU_WhereClause); // break; /* Log: 200709-AP end */ /* } else if ($InputMask.substring(0,3).equalsIgnoreCase("SQL")) { */ /* pU_WhereClause = $InputMask; */ /* if (class_Debug) System.out.println(currentMethod + " /SQL pU_WhereClause=" + pU_WhereClause); */ /* break; */ } else if ($InputMask.length() < 4) { System.out.println(" "); System.out.println(" *** Invalid user input. ***"); /* Log: 200711-handle end */ } else if ($InputMask.substring(0,4).equalsIgnoreCase("AEPC")) { class_Output_AEPC = !class_Output_AEPC; System.out.println(" "); System.out.println("=== Application Engine PeopleCode output is now " + class_Output_AEPC + " ==="); /* Log: 200709-AP Start */ } else if ($InputMask.substring(0,4).equalsIgnoreCase("APPC")) { class_Output_APPC = !class_Output_APPC; System.out.println(" "); System.out.println("=== Application Package PeopleCode output is now " + class_Output_APPC + " ==="); /* Log: 200709-AP end */ /* Log: 200711-handle Start */ } else if ($InputMask.length() < 5) { System.out.println(" "); System.out.println(" *** Invalid user input. ***"); } else if ($InputMask.substring(0,5).equalsIgnoreCase("SQLID")) { /* Log: 200709-AP Start */ /* pU_WhereClause = $InputMask.toUpperCase(); */ pU_WhereClause = $InputMask; /* Log: 200709-AP end */ if (class_Debug) System.out.println(currentMethod + " /SQLID pU_WhereClause=" + pU_WhereClause); break; /* Log: 200711-handle end */ } else if ($InputMask.substring(0,5).equalsIgnoreCase("AESQL")) { class_Output_AESQL = !class_Output_AESQL; System.out.println(" "); System.out.println("=== Application Engine SQL output is now " + class_Output_AESQL + " ==="); /* Log: 200711-ae%sql Start */ /* Log: 200711-handle Start */ } else if ($InputMask.length() < 6) { System.out.println(" "); System.out.println(" *** Invalid user input. ***"); /* Log: 200711-handle end */ } else if ($InputMask.substring(0,6).equalsIgnoreCase("AE%SQL")) { class_Output_AE_SQL = !class_Output_AE_SQL; System.out.println(" "); System.out.println("=== Application Engine %Sql output is now " + class_Output_AE_SQL + " ==="); /* Log: 200711-ae%sql end */ } else { System.out.println(" "); System.out.println(" *** Unknown Option ***"); System.out.println(" "); break; } } else { /* dot is a regex metacharacter matching anything, therefore force it to be */ /* treated as an ordinary character by using backslash which itself needs to be */ /* escaped with backslash. */ regex = "\\."; String[] $ObjectValue = $InputMask.split(regex); if ($ObjectValue.length > 7) System.out.println(currentMethod + " First 7 values used, others ignored. "); pU_WhereClause = ""; $ObjectName = ""; int x = 0; for (x = 0; (x < $ObjectValue.length) && (x < 7); x++) { $ObjectValue[x] = $ObjectValue[x].trim(); if (x != 0) $ObjectName = $ObjectName + "."; $ObjectName = $ObjectName + $ObjectValue[x]; pU_WhereClause = pU_WhereClause + "OBJECTVALUE" + (x + 1) + " = '" + $ObjectValue[x] + "'"; if (x < 6) pU_WhereClause = pU_WhereClause + " AND "; } for (; x < 7; x++) { pU_WhereClause = pU_WhereClause + "OBJECTVALUE" + (x + 1) + " = ' " + "'"; if (x < 6) pU_WhereClause = pU_WhereClause + " AND "; } if (class_Debug) System.out.println(currentMethod + " dot delimited pU_WhereClause=" + pU_WhereClause); break; } } System.out.println(" "); if (pU_WhereClause.equalsIgnoreCase(EXIT_LITERAL)) { System.out.println("Ending program."); } else if (pU_WhereClause.equalsIgnoreCase("")) { System.out.println("Input ignored."); pU_WhereClause = "IGNORE"; } else { System.out.println("Processing...."); } System.out.println(" "); return pU_WhereClause; } static void initReport(Connection dbconn) { String currentMethod = "initReport:"; String $ToolsRel = getToolsVersion(dbconn); String $DBName = getDatabaseName(dbconn); String $sqr_database = getDatabase(dbconn); System.out.println("-------------------------------------------------------------------------------"); System.out.println("PeopleSoft Extractor/Decoder"); System.out.println("Summer 2007"); System.out.println("by David L. Price (david@ideatec.us)"); System.out.println(" rewritten to Java and expanded (Sun's version 1.4 or later)"); System.out.println(" by Jeff Chochon"); System.out.println("Tools: " + $ToolsRel); System.out.println("Database: " + $sqr_database); System.out.println("Instance: " + $DBName); System.out.println("-------------------------------------------------------------------------------"); System.out.println(" "); String DATE_FORMAT = "yyyy-MM-dd-HH.mm.ss"; SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT); Calendar cal = Calendar.getInstance(TimeZone.getDefault()); sdf.setTimeZone(TimeZone.getDefault()); String $ReportDate = sdf.format(cal.getTime()); DATE_FORMAT = "HH:mm"; sdf = new SimpleDateFormat(DATE_FORMAT); cal = Calendar.getInstance(TimeZone.getDefault()); sdf.setTimeZone(TimeZone.getDefault()); String $ReportTime = sdf.format(cal.getTime()); /* Log: 200711-mysql Start */ if (class_Db.equalsIgnoreCase("MySQL")) return; /* Log: 200711-mysql end */ if ($sqr_database.equalsIgnoreCase("ORACLE")) { StringBuffer sb = new StringBuffer(); sb.append("Alter session set NLS_DATE_FORMAT='DD-MON-YYYY'"); String s = sb.toString(); try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet rs = ps.executeQuery(); } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } StringBuffer sb2 = new StringBuffer(); sb2.append("Alter session set NLS_DATE_LANGUAGE='AMERICAN'"); s = sb2.toString(); try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet rs = ps.executeQuery(); } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } } else { System.out.println(" "); System.out.println("= = = = = = = = = E R R O R = = = = = = = = ="); System.out.println(" "); System.out.println("Sorry. PeopleSoft Page Information currently"); System.out.println("only works on Oracle. "); System.out.println(" "); System.out.println("= = = = = = = = = = = = = = = = = = = = = = ="); } String ToolCheck = $ToolsRel.substring(0,3); if (!ToolCheck.equals("8.1") && !ToolCheck.equals("8.4")) { System.out.println(" "); System.out.println("= = = = = = = = = E R R O R = = = = = = = = ="); System.out.println(" "); System.out.println("Sorry. PeopleSoft Page Information currently"); System.out.println("only works on PeopleTools version 8.1x or 8.4x. "); System.out.println(" "); System.out.println("= = = = = = = = = = = = = = = = = = = = = = ="); } } static String getToolsVersion(Connection dbconn) { String currentMethod = "getToolsVersion:"; String $ToolsVersionTable = " "; String $ToolsRel = " "; StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ /* sb.append("Select RECNAME from psrecfield"); */ sb.append("Select RECNAME from " + class_Dbowner + ".psrecfield"); /* Log: 200711-mysql end */ sb.append(" where FIELDNAME = 'TOOLSREL' and RECNAME in ('PSLOCK','PSSTATUS') "); String s = sb.toString(); int row_cnt = 0; try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { $ToolsVersionTable = resultset.getString("RECNAME"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if ($ToolsVersionTable.length() > 0) { StringBuffer sb2 = new StringBuffer(); if ($ToolsVersionTable.equalsIgnoreCase("PSSTATUS")) /* Log: 200711-mysql Start */ // sb2.append("Select TOOLSREL from sysadm.psstatus "); //else // sb2.append("Select TOOLSREL from sysadm.pslock "); sb2.append("Select TOOLSREL from " + class_Dbowner + ".psstatus "); else sb2.append("Select TOOLSREL from " + class_Dbowner + ".pslock "); /* Log: 200711-mysql end */ s = sb2.toString(); try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { $ToolsRel = resultset.getString("TOOLSREL"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } } else { System.out.println(currentMethod + " Error selecting from psrecfield."); } return $ToolsRel; } static String getDatabaseName(Connection dbconn) { String currentMethod = "getDatabaseName:"; String $DBName = "Unknown"; String $ToolsVersionTable = ""; StringBuffer sb = new StringBuffer(); /* Log: 200711-mysql Start */ //sb.append("Select RECNAME from sysadm.psrecfield "); sb.append("Select RECNAME from " + class_Dbowner + ".psrecfield "); /* Log: 200711-mysql end */ sb.append(" where FIELDNAME = 'TOOLSREL' and RECNAME in ('PSLOCK','PSSTATUS') "); String s = sb.toString(); int row_cnt = 0; try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { $ToolsVersionTable = resultset.getString("RECNAME"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if ($ToolsVersionTable.length() > 0) { StringBuffer sb2 = new StringBuffer(); /* Log: 200711-mysql Start */ //sb2.append("Select DBNAME from ps.psdbowner psdb "); sb2.append("Select DBNAME from " + class_PsDbowner + ".psdbowner psdb "); /* Log: 200711-mysql end */ if ($ToolsVersionTable.equalsIgnoreCase("PSSTATUS")) /* Log: 200711-mysql Start */ // sb2.append(",sysadm.PSSTATUS t2 "); //else // sb2.append(",sysadm.PSLOCK t2 "); sb2.append("," + class_Dbowner + ".psstatus t2 "); else sb2.append("," + class_Dbowner + ".pslock t2 "); /* Log: 200711-mysql end */ sb2.append("where upper(psdb.OWNERID) = upper(t2.OWNERID) "); s = sb2.toString(); try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { $DBName = resultset.getString("DBNAME"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } } else { System.out.println(currentMethod + " Error selecting from psrecfield."); } return $DBName; } static String getDatabase(Connection dbconn) { String currentMethod = "getDatabase:"; String $DB = "Unknown"; /* Log: 200711-mysql Start */ if (class_Db.equalsIgnoreCase("MySQL")) return "MySQL"; /* Log: 200711-mysql end */ StringBuffer sb = new StringBuffer(); sb.append("Select DUMMY from dual "); String s = sb.toString(); int row_cnt = 0; try { PreparedStatement ps = dbconn.prepareStatement(s); ps.clearParameters(); ResultSet resultset = ps.executeQuery(); for (row_cnt = 1; resultset.next(); row_cnt++) { $DB = resultset.getString("DUMMY"); } } catch(SQLException se) { se.printStackTrace(); System.out.println(currentMethod + se.toString()); } if (($DB.length() > 0) && ($DB != "Unknown")) { $DB = "ORACLE"; } else { $DB = "Unknown"; } return $DB; } static Connection openDbConnection(String DBUrl, String Database, String UserID, String Password) { String currentMethod = "openDbConnection:"; Connection dbconn = null; /* Log: 200711-mysql Start */ /* String dbowner = "sysadm"; */ /* Log: 200711-mysql end */ try { /* Load the Oracle JDBC driver. */ /* Oracle begin */ DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); /* Oracle end */ /* MySQL begin */ //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); /* MySQL end */ dbconn = DriverManager.getConnection(DBUrl + Database, UserID, Password); } catch(Throwable throwable) { String s2 = "Unexpected error when retrieving database connection: " + throwable.getMessage(); System.out.println(currentMethod + " " + s2); } if (dbconn == null) { System.out.println(currentMethod + " DB connection is null."); } else { try { if (class_Debug) System.out.println(currentMethod + " Testing database connection. "); /* Log: 200711-mysql Start */ /* String testSql = "Select count(*) from " + dbowner + ".psrecfield"; */ String testSql = "Select count(*) from " + class_Dbowner + ".psrecfield"; /* Log: 200711-mysql end */ Statement stmt = dbconn.createStatement(); ResultSet resultset = stmt.executeQuery(testSql); ResultSetMetaData resultsetmetadata = resultset.getMetaData(); int i = 0; i = resultsetmetadata.getColumnCount(); if (class_Debug) System.out.println(currentMethod + " " + i + " columns are found."); if (i > 0) { if (class_Debug) System.out.println(currentMethod + " Connection is OK."); } else { System.out.println(currentMethod + " Connection failed."); } } catch(SQLException e) { e.printStackTrace(); System.out.println(currentMethod + e.toString()); } } return dbconn; } static void closeDbConnection(Connection in_dbconn) { String currentMethod = "closeDbConnection:"; if (in_dbconn != null) { if (class_Debug) System.out.println(currentMethod + " Closing database connection."); try { in_dbconn.close(); } catch(SQLException e) { e.printStackTrace(); System.out.println(currentMethod + e.toString()); } } } /* Utility function to read a line from standard input. */ static String readEntry (String prompt) { String currentMethod = "readEntry:"; try { StringBuffer buffer = new StringBuffer (); System.out.print (prompt); System.out.flush (); int c = System.in.read (); while (c != '\n' && c != -1) { buffer.append ((char)c); c = System.in.read (); } return buffer.toString ().trim (); } catch (IOException e) { return ""; } } static void showTrueOrFalse(boolean in_evaluate) { String currentMethod = "showTrueOrFalse:"; if (in_evaluate) System.out.println("YES"); else System.out.println("NO"); } static void showInstructions() { String currentMethod = "showInstructions:"; System.out.println(" "); System.out.println(" PeopleCode (App Engine, App Package, Message):"); System.out.println(" Enter up to 7 dot delimited values (case insensitive) for"); System.out.println(" selecting from PSPCMPROG where OBJECTVALUE# = value."); System.out.println(" Example: ORIGIN_TBL.DESCRSHORT.SaveEdit"); System.out.println(" Example: po_pnls_wrk2.project_id.fieldchange"); System.out.println(" Example: PO_HDR.BUYER_ID.FieldChange"); System.out.println(" Example: AP_APY2015.MAIN.GBL.default.1900-01-01.step05.onexecute"); System.out.println(" Example: funclib_pc.ae_appllibrary.fieldformula"); System.out.println(" "); /* Log: 200709-AP Start */ System.out.println(" Application Package:"); System.out.println(" Enter back slash, AP, equals, and the application package."); System.out.println(" Example: /AP = EOEP_PRICING_FOUNDATION"); System.out.println(" "); /* Log: 200709-AP end */ System.out.println(" Application Engine Program:"); System.out.println(" Enter back slash, AE, equals, and the application engine program."); System.out.println(" Example: /AE = PC_POADJUST"); System.out.println(" "); /* Log: 200709-AP Start */ /* System.out.println(" SQL:"); */ System.out.println(" SQL or XSLT:"); /* Log: 200709-AP end */ /* Log: 200711-sqlwild Start */ /* System.out.println(" Enter back slash, SQLID, and single quote delimited where clause for exact database matching."); */ System.out.println(" Enter back slash, SQLID, and single quote delimited where clause."); /* Log: 200711-sqlwild end */ System.out.println(" Example: /SQLID = 'PC_POADJUST MAIN INIT S'"); /* Log: 200709-AP Start */ /* Log: 200711-sqlwild Start */ /* System.out.println(" Example: /sqlid = 'pc_poadjust check_kksel_kk S'"); */ System.out.println(" Example: /sqlid = 'pc_POADJUST%Check_kksel%S'"); /* Log: 200711-sqlwild end */ System.out.println(" Example: /sqlid = 'AR_CRM_RQ_V1MAIN Step01 X'"); /* Log: 200709-AP end */ System.out.println(" "); System.out.println(" Settings (enter one at a time):"); System.out.println(" /* show current settings"); System.out.println(" /O (oh) toggles output to file and screen"); System.out.println(" File(s), 'output#.txt', created in 'output' subdirectory of current directory."); System.out.println(" /B output to both file and screen"); System.out.println(" /D toggles debug messages to screen"); System.out.println(" /AEPC toggles Application Engine PeopleCode output"); /* Log: 200709-AP Start */ System.out.println(" /APPC toggles Application Package PeopleCode output"); /* Log: 200709-AP end */ System.out.println(" /AESQL toggles Application Engine SQL & XSLT output"); /* Log: 200711-ae%sql Start */ System.out.println(" /AE%SQL toggles Application Engine %Sql (AE SQL fragments) output"); /* Log: 200711-ae%sql end */ System.out.println(" "); System.out.println(" Enter with no input ends program."); System.out.println(" "); } static int openOutputFile(int oOF_output_cnt) { String currentMethod = "openOutputFile:"; if (class_Output_To_File) { /* Open the output file */ String blobfile = "/" + "output" + oOF_output_cnt++ + ".txt"; File outdir = new File("."); String StorageDir = "StorageDir"; try { StorageDir = outdir.getCanonicalPath() + "/output"; } catch(Exception e) { e.printStackTrace(); } System.out.println(currentMethod + " Output directory: " + StorageDir); File AttFile = new File(StorageDir, blobfile); try { class_outFile = new FileOutputStream(AttFile.toString(), true); } catch(Exception e) { e.printStackTrace(); System.out.println(currentMethod + " Error is " + e.toString()); } } return oOF_output_cnt; } static void closeOutputFile() { String currentMethod = "closeOutputFile:"; if (class_Output_To_File) { try { class_outFile.close(); } catch(Exception e) { e.printStackTrace(); System.out.println(currentMethod + " Error is " + e.toString()); } } } /** Apache Jakarta component codec. * Converts an array of characters representing hexidecimal values into an * array of bytes of those same values. The returned array will be half the * length of the passed array, as it takes two characters to represent any * given byte. An exception is thrown if the passed char array has an odd * number of elements. * * @param data An array of characters containing hexidecimal digits * @return A byte array containing binary data decoded from * the supplied char array. * @throws DecoderException Thrown if an odd number of illegal of characters * is supplied. */ /* public static byte[] decodeHex(char[] data) throws DecoderException { */ public static byte[] decodeHex(char[] data) { int len = data.length; String currentMethod = "decodeHex"; if ((len & 0x01) != 0) { /* throw new DecoderException("Odd number of characters."); */ System.out.println(currentMethod + ". Error: Odd number of characters."); } byte[] out = new byte[len >> 1]; // two characters form the hex value for (int i=0, j=0; j