/// <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 = "";
}
