/// <summary> /// Open 40_qualify_database_and_unlock_.pdc and Parse and run each line. /// </summary> static void RunQualifyDatabaseAndUnlock() { string dropLocation = StartLocation + "\\10_Structure\\" + CurrentEdition + "\\05_UPGRADE\\40_qualify_database_and_unlock_.pdc"; if (File.Exists(dropLocation)) { string readLine; OracleClass oc = new OracleClass(); oc.Connect(); oc.tran = oc.conn.BeginTransaction(); using (StreamReader dpuFile = new StreamReader(dropLocation)) { while ((readLine = dpuFile.ReadLine()) != null) { readLine = readLine.Replace(";", ""); if (readLine.IndexOf("execute", StringComparison.OrdinalIgnoreCase) != -1) { readLine = readLine.Remove(0, 8); ProcedureInputClass pic = new ProcedureInputClass(); pic.numberOfParameters = 0; pic.procedureName = readLine; oc.NonQueryProcedure(pic); } else { oc.NonQueryText(readLine); } } } oc.tran.Commit(); oc.conn.Close(); } }
/// <summary> /// Reads the sql file and then parses and inserts it into the database. /// </summary> /// <param name="filePath"></param> public void ReadFilesToInsert(string scriptType) { Console.WriteLine("Converting " + scriptType + " files to inserts into DB..."); insertV.sectionNumber = 0; // Start the Oracle Stuff // OracleClass oc = new OracleClass(); oc.Connect(); oc.tran = oc.conn.BeginTransaction(); // Get all the schema folders // List<string> lynxFoldersAll; if (scriptType == "Structure") { oc.NonQueryText("update rt_upgrade u set u.upg_error_command_id=null, u.upg_error_ind='N', u.this_db_qualifies_ind='N' where u.upg_id='1'"); oc.NonQueryText("delete from rt_upgrade_command"); lynxFoldersAll = GetStructureLynxFolders(); } else { lynxFoldersAll = GetCodeLynxFolders(); } // Go through each schema folder // foreach (string lynxFolder in lynxFoldersAll) { // Each Folder write connect with section // insertV.sectionNumber++; if (scriptType == "Structure") WriteRunUpgrade(lynxFolder); // Go through each folder contained in the schema folder // var lynxSubFolderList = Directory.GetDirectories(lynxFolder); foreach(string lynxSubFolder in lynxSubFolderList) { // If 900 Folder then write to finalize, does execution order need to change? // insertV.finalIndication = "'N'"; int sectionNum = insertV.sectionNumber; if (lynxSubFolder.IndexOf("900") != -1) { insertV.finalIndication = "'Y'"; sectionNum = insertV.sectionNumber + lynxFoldersAll.Count; if (scriptType == "Structure") WriteRunFinalize(lynxFolder, lynxFoldersAll.Count); } // Go through each file in the sub folders // var filesInLynxSubFolder = Directory.GetFiles(lynxSubFolder); foreach (string filePath in filesInLynxSubFolder) ParseForDbInsert(ref oc, filePath, sectionNum); } } // Commit the transactions // oc.tran.Commit(); oc.conn.Dispose(); Console.WriteLine(scriptType + " files written to rt_upgrade_command."); Console.WriteLine(); }
/// <summary> /// Open 10_drop_prior_upgrade_objects.pdc and Parse and run each line. /// </summary> static void RunDropPriorUpgrade() { string dropLocation = StartLocation + "\\10_Structure\\" + CurrentEdition + "\\05_UPGRADE\\10_drop_prior_upgrade_objects.pdc"; if (File.Exists(dropLocation)) { string readLine; OracleClass oc = new OracleClass(); oc.Connect(); oc.tran = oc.conn.BeginTransaction(); using (StreamReader dpuFile = new StreamReader(dropLocation)) { while((readLine = dpuFile.ReadLine()) != null) { if (readLine != "commit;") { readLine = readLine.Replace(";", ""); oc.NonQueryText(readLine); } } } oc.tran.Commit(); oc.conn.Close(); } }
/// <summary> /// Insert values into Oracle DB. /// </summary> /// <param name="insertTextHold"></param> public void InsertIntoDatabase(ref string insertTextHold, ref OracleClass oc, int sectionNum) { // Local Variables // string preview = ""; // Setup Preview // if (insertTextHold.Length > 40) preview = "'" + ((insertTextHold.Substring(0, 40) + "...").Replace("\r\n", "")).Replace("'"," ") + "'"; else preview = "'" + ((insertTextHold).Replace("\r\n", "")).Replace("'"," ") + "'"; // Final funky format of insert text // insertV.retryType = GetRetryValue(insertTextHold); insertTextHold = insertTextHold.Replace("'", "''"); insertTextHold = insertTextHold + "\r\n\r\n\r\n"; insertTextHold = "'" + insertTextHold + "'"; // Write insert to Oracle // string insertText = string.Format("insert into rt_upgrade_command (upg_id,execution_order,final_ind,retry_type,section,preview,upg_command,success_ind) values (1,{0},{1},{2},{3},{4},{5},'N')", insertV.executionOrder.ToString(), insertV.finalIndication, insertV.retryType, sectionNum.ToString(), preview, insertTextHold); // Check Size Constraint // if (insertText.Length >= 4000) { string declare = ""; string insertDeclare = ""; string testChar = ""; const int MaxInsertSize = 30000; int chopPoint = MaxInsertSize; if (insertText.Length > MaxInsertSize) { // Break into pieces // insertTextHold = insertTextHold.Substring(1, insertTextHold.Length - 2); // remove the "'" from front and back declare = "declare v_clob clob := null; begin "; // Get substring and check for "'" // testChar = insertTextHold.Substring(chopPoint, 1); while((chopPoint > 1) && (testChar == "'")) { chopPoint--; testChar = insertTextHold.Substring(chopPoint, 1); } declare = declare + "v_clob := " + "'" + insertTextHold.Substring(0, chopPoint) + "'" + "; "; insertTextHold = insertTextHold.Substring(chopPoint + 1); while (insertTextHold.Length > chopPoint) { chopPoint = MaxInsertSize; testChar = insertTextHold.Substring(chopPoint,1); while ((chopPoint > 1) && (testChar == "'")) { chopPoint--; testChar = insertTextHold.Substring(chopPoint,1); } declare = declare + "v_clob := v_clob||" + "'" + insertTextHold.Substring(0, chopPoint) + "'" + "; "; insertTextHold = insertTextHold.Substring(chopPoint + 1); } if (insertTextHold != "") declare = declare + "v_clob := v_clob||" + "'" + insertTextHold + "'" + "; "; insertDeclare = string.Format("insert into rt_upgrade_command (upg_id,execution_order,final_ind,retry_type,section,preview,upg_command,success_ind) values (1,{0},{1},{2},{3},{4},{5},'N')", insertV.executionOrder.ToString(), insertV.finalIndication, insertV.retryType, sectionNum.ToString(), preview, "v_clob"); declare = declare + insertDeclare + "; end;"; oc.NonQueryText(declare); } else { declare = "declare v_clob clob := null; begin v_clob := " + insertTextHold + "; "; insertDeclare = string.Format("insert into rt_upgrade_command (upg_id,execution_order,final_ind,retry_type,section,preview,upg_command,success_ind) values (1,{0},{1},{2},{3},{4},{5},'N')", insertV.executionOrder.ToString(), insertV.finalIndication, insertV.retryType, sectionNum.ToString(), preview, "v_clob"); declare = declare + insertDeclare + "; end;"; oc.NonQueryText(declare); } } else { oc.NonQueryText(insertText); } // Clear text hold for next round // insertTextHold = ""; }