Executing a SQL through Scripting

Here is the function which takes SQL File Name as imput which is to be executed.

sConnectString = sUserName + "/" + sPswd + "@" + sDBname;
sSQLCommand = "sqlplus" + " " + sConnectString + " @" + vSQLFile;
Clib.system(sSQLCommand);

It looks straight forward but we need to get few important values like SQLFilePath, Username, Password and DBName.


function ExecuteSQL(sqlFileName)
{
    try
    {
        TheApplication().WriteLog("Inside Function ExecuteSQL:Start");
        TheApplication().WriteLog("SQL File Name:" + sqlFileName);

        var boSysPref = TheApplication().GetBusObject("System Preferences");
        var bcSysPref = boSysPref.GetBusComp("System Preferences");
        var sqlPath;
        var vSQLFile;
        var vPropFileName = "/siebel.properties";
       
        //we have stored SQL file path in system preferences
        with(bcSysPref)
        {
            ClearToQuery();
            SetViewMode(AllView);
            ActivateField("Name");
            ActivateField("Value");
            SetSearchSpec("Name", "SQLPath");
            ExecuteQuery();
            if (FirstRecord())
            {
                sqlPath = GetFieldValue("Value");
            }
        }
        vSQLFile = sqlPath + sqlFileName;

        //Read the siebe;.properties file to get Username, Password and DBName
        var propertyFileName = sqlPath + vPropFileName;
        TheApplication().WriteLog("propertyFileName :" + propertyFileName);
       
        var fp = Clib.fopen(propertyFileName, "r");
        var sUserName = "";
        var sPswd = "";
        var sDBname = "";
        var sConnectString = "";
        var sSQLCommand = "";
        var line;
        var Pattern = /\s*$/;

        for (line = Clib.fgets(fp); line != null; line = Clib.fgets(fp))
        {
            var cells = line.split("=");
            var sName = cells[0];
            var sValue = cells[1];

            sName = sName.replace(Pattern, "");
            sValue = sValue.replace(Pattern, "");

            if (sName == "oracle.siebel.userName")
            {
                sUserName = sValue;
            }
            else if (sName == "oracle.siebel.password")
            {
                sPswd = sValue;
            }
            else if (sName == "oracle.siebel.db.name")
            {
                sDBname = sValue;
            }
        }

        //Call to execute SQL
        if ((sUserName.length == 0) || (sPswd.length == 0) || (sDBname.length == 0))
        {
            TheApplication().WriteLog("Insufficient DB connection info");
        }
        else
        {
            sConnectString = sUserName + "/" + sPswd + "@" + sDBname;
            sSQLCommand = "sqlplus" + " " + sConnectString + " @" + vSQLFile;
            Clib.system(sSQLCommand);
        }
    }

    catch (e)
    {
        TheApplication().WriteLog("Error in function ExecuteSQL:" + e.toString());
    }

    finally
    {
        TheApplication().WriteLog("ExecuteSQL:End");
    }
}

Amazing! Isn't it? Hope it helps :)