/// <summary>
/// Run Procedure lynx.store_clr_dlls.
/// </summary>
static void ExecuteClrDll()
{
Console.WriteLine("Running lynx.store_clr_dlls ...");
OracleClass oc = new OracleClass();
oc.Connect();
oc.tran = oc.conn.BeginTransaction();
ProcedureInputClass pic = new ProcedureInputClass();
pic.numberOfParameters = 0;
pic.procedureName = "lynx.store_clr_dlls";
oc.NonQueryProcedure(pic);
oc.tran.Commit();
oc.conn.Close();
Console.WriteLine("Finished lynx.store_clr_dlls.");
}
/// <summary>
/// Open 50_run_upgrade.pdc and Parse and run each line.
/// </summary>
static void RunUpgrade()
{
//string dropLocation = StartLocation + "\\10_Structure\\" + CurrentEdition + "\\05_UPGRADE\\50_run_upgrade.pdc";
string dropLocation = StartLocation + "\\10_Structure\\" + CurrentEdition + "\\05_UPGRADE\\50_run_upgrade_original.pdc";
if (File.Exists(dropLocation))
{
string readLine;
OracleClass oc = new OracleClass();
using (StreamReader dpuFile = new StreamReader(dropLocation))
{
while ((readLine = dpuFile.ReadLine()) != null)
{
readLine = readLine.Replace(";", "");
if (readLine.IndexOf("connect", StringComparison.OrdinalIgnoreCase) != -1)
{
// Connect with edition or normal //
readLine = readLine.Remove(0, 8);
string UserId = readLine.Remove(readLine.IndexOf("/"));
string Password = readLine.Remove(0,readLine.IndexOf("/") + 1);
string Edition = "";
if (Password.IndexOf("edition") != -1)
{
// Parse out edition //
Edition = Password.Remove(0,Password.IndexOf("edition"));
Password = Password.Remove(Password.IndexOf("edition") - 1);
}
// Now try to connect //
//oc.Connect(UserId, Password, Edition);
}
else if (readLine.IndexOf("execute", StringComparison.OrdinalIgnoreCase) != -1)
{
//oc.tran = oc.conn.BeginTransaction();
// execute with a parameter (x) //
readLine = readLine.Remove(0, 8);
string proName = readLine.Remove(readLine.IndexOf("("));
string paramNum = readLine.Remove(0,readLine.IndexOf("(") + 1);
paramNum = paramNum.Remove(1);
ProcedureInputClass pic = new ProcedureInputClass();
pic.parameterName = new List<string>();
pic.parameterValue = new List<int>();
pic.numberOfParameters = 1;
pic.parameterName.Add("i_section_number");
pic.parameterValue.Add(Convert.ToInt32(paramNum));
pic.procedureName = proName;
oc.NonQueryProcedure(pic);
//oc.tran.Commit();
//oc.conn.Close();
}
}
}
}
}
/// <summary>
/// Test if the xml file is setup correctly for a database connection.
/// </summary>
static void TestDbConnection(string UserId, string Password, string Privilege)
{
Console.WriteLine("Testing Database Connection...");
try
{
OracleClass oc = new OracleClass();
oc.Connect(UserId, Password, Privilege);
oc.conn.Close();
}
catch
{
}
}
/// <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>
/// 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>
/// 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>
/// Parse out the file and insert the commands into the rt_upgrade_command table.
/// </summary>
/// <param name="oc"></param>
/// <param name="filePath"></param>
/// <param name="sectionNum"></param>
public void ParseForDbInsert(ref OracleClass oc, string filePath, int sectionNum)
{
bool inBlock = false;
string readLine = "";
string insertTextHold = "";
StreamReader file = new StreamReader(filePath);
while ((readLine = file.ReadLine()) != null)
{
insertV.executionOrder = insertV.executionOrder + 1000;
readLine = readLine.Replace("\r\n", "");
if (readLine == "")
{
// Ignore nothing //
}
else
{
if (!inBlock)
inBlock = CheckForBlock(readLine);
if (!inBlock)
{
insertTextHold = insertTextHold + readLine + "\r\n";
if (insertTextHold.IndexOf(";") != -1)
{
insertTextHold = insertTextHold.Replace(";", "");
InsertIntoDatabase(ref insertTextHold, ref oc, sectionNum);
}
}
else
{
if (readLine == "/")
{
InsertIntoDatabase(ref insertTextHold, ref oc, sectionNum);
inBlock = false;
}
else
{
insertTextHold = insertTextHold + readLine + "\r\n";
}
}
}
}
}
/// <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 = "";
}