7 Replies Latest reply on May 24, 2012 9:12 AM by 783124

    Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait

    783124
      When running the following command in an FDM event script, an error occurs. If I specify the direct path (i.e. "D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient\bin\startMAXL.cmd") the error does not occur. But if I open command prompt and enter the path using the environment variable (as in the script below), the MaxL script is executed fine. It only appears to cause an error when run by API.DataWindow.Utilities.mShellAndWait. Does anyone please have any suggestions as to how I can resolve this?


      Error:
      Error: File not found
      At Line: 77


      Script:
      ' Call MaxL script to run data clear calculation.
      Set objShell = CreateObject("WScript.Shell")
      strCMD = "%EPM_ORACLE_HOME%\products\Essbase\EssbaseClient\bin\startMAXL.cmd " & strFilePath & ".mxl"
      API.DataWindow.Utilities.mShellAndWait strCMD, 0
        • 1. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
          LarryR
          Have to tried to use the UNC path to startMAXL.cmd?
          • 2. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
            783124
            Do you mean the following?

            strCMD = "\\<servername>\%EPM_ORACLE_HOME%\products\Essbase\EssbaseClient\bin\startMAXL.cmd " & strFilePath & ".mxl"

            This is giving the same error...
            • 3. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
              LarryR
              I don't think vbScript will resolve strCMD = "%EPM_ORACLE_HOME%\products\Essbase\EssbaseClient\bin\startMAXL.cmd " & strFilePath & ".mxl" correctly. I think environment variables can only be resolved from within the shell. FDM is probably literally including the string "%EPM_ORACLE_HOME%" in the path.

              If you share (Read and Execute) the folder on the Essbase server that contains startMAXL.cmd (or an ancestor folder of that containing folder), you can use the UNC path: strCMD = "\\<Essbase servername>\<share path to startMAXL.cmd>" & strFilePath & ".mxl"

              However, if startMAXL is installed on the FDM server, you may be able to use the local pathing to startMAXL.cmd.
              • 4. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
                beyerch2
                call a batch file from the shell command have the command line in the batch file ...... That will pickup the environmental variables, no problem. pass the other pieces of the file as a parameter to the batch file.

                i.e.


                ' Call MaxL script to run data clear calculation.
                Set objShell = CreateObject("WScript.Shell")
                strCMD = "myBatch.bat " & strFilePath & ".mxl"
                API.DataWindow.Utilities.mShellAndWait strCMD, 0


                myBatch.bat
                -----------
                %EPM_ORACLE_HOME%\products\Essbase\EssbaseClient\bin\startMAXL.cmd %1

                Edited by: beyerch2 on May 17, 2012 11:56 AM
                1 person found this helpful
                • 5. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
                  783124
                  Many thanks, Larry and beyerch2. Larry's solution will work best for us here, as the other solution will still require the full path of the batch file itself to be hard-coded into the event script, which is what I was hoping to avoid (to aid easy migrations between environments). Apologies if this requirement was not clear from the original question. The batch file method does work though, so many thanks.
                  • 6. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
                    beyerch2
                    Ok,

                    I thought your requirement was to make use of the environment varible.

                    with that said though, don't both solutions presented require some level of hard coding?

                    For Larry's solution, he's using a UNC path which you would have to hard copy and theoretically maintain if your environment changes.

                    In my solution, I'm telling you to call an arbitrary file. As far as path discussion goes, you can call it with a relative path so you are not hard coding the entire thing. In a migration sense, though, it would be one more file you'd have to migrate and add to your process.

                    In reality both of these solutions are not that good because they both require you to do something non-standard in a way. Creating the UNC share and making a additional batch file are not going to work out of the box and if you replicated the environment, it's another step.


                    Here's another option you could try if you want to revisit this in the future......


                    Script:
                    ' Call MaxL script to run data clear calculation.
                    Set objShell = CreateObject("WScript.Shell") 'You declared this before, but didn't use it? I'll give you a use for this line now. :)
                    EPMOracleHome=oShell.ExpandEnvironmentStrings(""%EPM_ORACLE_HOME%")
                    strCMD = EPMOracleHome & "\products\Essbase\EssbaseClient\bin\startMAXL.cmd " & strFilePath & ".mxl"
                    API.DataWindow.Utilities.mShellAndWait strCMD, 0

                    Edited by: beyerch2 on May 21, 2012 10:31 AM
                    1 person found this helpful
                    • 7. Re: Cannot use environment variable with API.DataWindow.Utilities.mShellAndWait
                      783124
                      This is exactly what I was trying to achieve - implemented and all working. Many thanks! :-)