Want to know The Truth About CPM?

22 April 2012

Stupid Programming Tricks #12 -- How to trap for import errors in MaxL


This is truly a Stupid Trick because I said something couldn’t be done and in fact it could.

Oh, the shame.  And then the shame of getting it slightly wrong yet again.  A-g-o-n-y.

Happily, this is not the first time in my life I have been wrong and will certainly not be the last, so my ego can take the blow.  What did Albert Einstein say about stupidity?  “Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”   Al, here I am, proving that really, stupidity is infinite, especially if your name is Cameron.


But the great news is that you, oh Cameron’s Blog For Essbase Hackers readers, actually take (at least some of) what I write under consideration and review, and are kind enough to tell me where I’m wrong.  Yes, Tim Faitsch, I’m looking at your comment in my No Exit (but not in MaxL) post.  

What oh what did I write that was so bad?

Here it is:
Note that MaxL is not going to trap data or dimension load errors.  ‘Twould be nice if it could, but it doesn’t.  So it goes.

Tim quite correctly pointed out that statement simply isn’t true.  And if you (ahem) read the Tech Ref, it’s pretty darn obvious.  What was I thinking?  I think the answer is I wasn’t thinking because here it is in black and white:
Example: Iferror (MaxL Shell)
The following example script tests various errors including MaxL Shell errors, and demonstrates how you can set the exit status variable to a nonzero argument to return an exit status to the MaxL Shell.
###  Begin Script ###
login $1 $2;
echo "Testing syntactic errors...";
spool on to spool.out;
set timestampTypo on;
iferror 'End';
echo "Testing shell escape...";
shell "cat doesnotexist.txt";
iferror 'ShellError';
msh "doesnotexistlerr.mxl";
iferror 'FileDoesNotExistError';
echo "Script completed successfully...";
spool off;
exit 0;

The trick

Did you catch it?  The shell command does a cat (I believe a *nix catalog type statement) looking for the file “doesnotexist.txt”.  If cat (think type in Windows) cannot find the file there’s an error.  If cat does find the file, there’s no error.  Who cares, right?

But consider how you could apply this to an import statement which, when it has bad record or two or two thousand, writes the errors to an error file.  If you could test for the existence of that error file, you’d know that if one existed, that would be there are data load errors, and if you do not, there are no data load errors.  

So here’s the trick and it’s a logical switcheroo:  An error from the shell “dir youerrorfilenamerighthere.err” statement means that the statement worked with no import errors because the no error file exists (this is assuming that you clear out all error files on script start) and the absence of an error really means that an import error occurred because the dir command found the text file.  Yes, it’s opposite land, but only in a good way.

An example with an error that’s not my own

First off, let’s establish a data source that we know isn’t going to work unless The Beverage Company becomes The Beverage and Snack Company.  What is wrong with this data load file for Good Old Sample.Basic?
 Hmm, potato chips.  So good to eat, so bad for you.  Especially when it’s a Scenario.  So we know that record three will fail.

Here’s the code of my script cleverly called LoadDataWithErrorChecking.msh:
Purpose:    Illustrate checking for error in import with good ol' Dir
Written by: Cameron Lackpour
Modified:   Right now

/*    Log in to Essbase    */
login hypadmin password on localhost ;
iferror "BadLogin" ;

/*    Define STDOUT and STDERR outputs    */
spool stdout on to "c:\\Automation\\Sample\\MaxLLog\\mshSample.log" ;
iferror "BadLogFile" ;
spool stderr on to "c:\\Automation\\Sample\\MaxLLog\\mshSample.err" ;
iferror "BadErrorFile" ;

/*    Load data from SQL    */
import database 'Sample'.'Basic' data connect as "hypsql" identified by "hypsql" using server rules_file 'dData'
on error write to "c:\\automation\\sample\\maxllog\\dData.err" ;
iferror "SQLImportError" ;

/*    Test to see if dData.err exists.  If it *doesn't*,MaxL will throw an error.  But we know that's a GOOD thing because the absence of an error file means that there were no data load errors.  If DIR *does* find the file, then there was a data load error.  And so we have to treat it like an error.  It's sort of backwards, but works.  And a big tip of the Essbase Hacker's hat to Tim Faitch for pointing this out when I (embarassingly) completely didn't read it in the docs.  Doh!    */shell "dir dData.err" ;
iferror "ExitWithOutError" ;
/*    If the file was found, in fact there was an error.    */
goto "SQLImportError" ;

/*    Leave the MaxL shell    */
define label "ExitWithoutError" ;
/*    This is where the script exits if there is no error.    */
logout ;
exit 0 ;

/*    Create label for login errors    */
define label "BadLogin" ;
/*    Quit MaxL with a 10 error code    */
exit 10 ;

/*    Create label for log file errors    */
define label "BadLogFile" ;
/*    Quit MaxL with a 20 error code    */
exit 20 ;

/*    Create label for error file errors    */
define label "BadErrorFile" ;
/*    Quit MaxL with a 30 error code    */
exit 30 ;

/*    Create label for SQL import errors    */
define label "SQLImportError" ;
/*    Quit MaxL with a 40 error code    */
exit 40 ;

I am expecting an error level of 40 when I run the above code.  And so it is.

I could have controlling code that tests for the 40 return code and does things – emails, automated Very Light warnings, texts, whatever.

You will note that this line is just the Windows version of the Tech Ref documented line (I just substituted dir for cat and of course used the right file name):
shell "dir dData.err" ;

But what happens when the script and the error file aren’t in the same directory.  This is actually pretty likely, especially in automated environments where locations, launch directories, and everything else are in separate directories.

The documentation lets you down

Well, to be fair, it isn’t wrong, but it sort of leaves out a crucial bit of information.  When you run shell “dir yourerrorfilenamerighthere.err”, there is an assumption on MaxL’s part that the place you launch the MaxL script from and the location of the error file are one and the same.
But when they aren’t in the same directory the dir command can’t find the error file.  No big deal you say (Do you?  Really?  Oh good, it isn’t just craaaaaazy voices in my head.  Or are they?), I’ll simply add in a directory to make everything work, like this:  
shell "dir c:\\\Automation\\\Sample\\\MaxLLog\\\dData.err" ;

Or will it?  Let’s not even change the execution directory and see what happens:

Uh-oh.  We know there’s an error, but MaxL is telling us that there isn’t.

NOTE THE ABOVE ABOUT DOUBLE QUOTES IS NOT TRUE ALL THE TIME.  Thanks to Jason Jones (read the comments) pointing out that "cat" really equals "type" in Windows.  In fact when you run shell "type yourerrorfilenamerighthere.err" things WORK, even when the file is in another directory.  What did I write about stupidity?  Double sigh.

So what’s missing or more accurately, what’s not needed?

The crucial bit of information that is still true, is that at least on Windows 2008 R2, is that YOU DON’T NEED THE DOUBLE QUOTES.  You don’t just don’t need them, you don’t want them at all if using dir.  Get rid of the double quotes and all is sweetness and light.  When you use dir, that is.  Read below to see how type successfully works with " and ".  Why?  Well, they're different commands and the reason is buried in how the two tools use double quotes, I think.  The real reasons behind that different quote handling are buried in the bowels of Windows and beyond our scope.  Suffice to say, dir pukes on double quotes and type works just fine with it.  Continuing along...

So, if you're going to test with dir, this is all you need:
shell dir c:\\\Automation\\\Sample\\\MaxLLog\\\dData.err ;

By the way, shell dir c:\\Automation\\Sample\\MaxLLog\\dData.err ; is also good.

And does it work?  Oh yes.

And you can run it from anywhere now.  Note that I am now running this from c:\Users\Administrator.

In fact, you can simply delete the double quotes altogether and run it from the source directory if you are inclined to follow the Tech Ref:
shell dir dData.err ;

If I actually had a clue about *nix, I would know that this works

Just like in the docs:

You see?  Lovely double quotes around the shell target.


The morals of my story of error

There are five:
  1. Never say never.  Especially when people likely smarter than you  read your work (guys like Tim and Jason).  Whoops.
  2. The Tech Ref is correct when it comes to using this technique in the same directory as the launched script, but incorrect when going outside of that directory if you use dir -- it's just fine if you use type.  Btw, the Tech Ref was also right in pointing out how to do this.  Too bad I wasn't smart enough to read it and understand.  What was I thinking?  The world will never know as I certainly don't.  Again and again and again.
  3. Double quotes are sometimes good, sometimes not.  In the case of dir they are truly unnecessary, at least on Windows.
  4. I can get obsessed by this stuff sometimes – be glad that you didn’t lose an afternoon (and then another evening correcting your errors) trying to figure out why the silly shell statement wouldn’t find the error file.
  5. Maybe I should spend more time reading up on that little known OS, *nix.  I think I got the catalog = directory message from my days, gasp, on an Apple IIe. 

One last thing to consider -- it's a little weird that type handles double quotes and dir does not when passing explicitly named directories.  It would be nice if they both worked the same way.

One last, last thing to consider.  It's really gratifying that people read my posts, correct my errors, and actually care enough to get my mistakes through my thick skull so that you, dear reader, get accurate information in spite of my best efforts to confuse all and sundry.  :)  Both Tim and Jason get my thanks.


Jason said...

Nice post, as always. A slight correction:

The shell command does a cat (I believe a *nix catalog statement) looking for the file “doesnotexist.txt”. If cat (think dir in Windows) cannot find the file there’s an error. If cat does find the file, there’s no error. Who cares, right?

In Unix, cat is "concatenate" files, and when simply used as "cat filename.txt" will print the contents of the file to the console (STDOUT). The somewhat analogous command in Windows is "type", such that running "type filename.txt" will print the contents of the file to the console. I believe the semantics with regard to error reporting are the same.

For example, a quick check on my Mac in the folder I happen to be in (note that $? is the convenience variable for the status of the last command):

jasonmac:Saxbi jasonwjones$ cat file_does_not_exist.txt
cat: file_does_not_exist.txt: No such file or directory

jasonmac:Saxbi jasonwjones$ echo $?

jasonmac:Saxbi jasonwjones$ cat uispec.opts
--workspace Saxbi.xcodeproj/project.xcworkspace --scheme UISpec --sdk 4.3

jasonmac:Saxbi jasonwjones$ echo $?

Ah, the joy of working in the tech industry and having people pedantically point out things to you. I'll look forward to the next time I attend one of your ODTUG sessions so I can heckle you some more. ;-)

Cameron Lackpour said...


I'm glad you read my drivel. :)

So the question remains -- how does cat work with " and " around it within MaxL using shell? Does it go outside of the base directory or not? I guess I need to now go test this with Windows' type to see what happens.

That was really the issue I had with the docs -- " and " make the shell statement in MaxL not see a fully qualified dir which drove me bananas and took me, oh, forever, to figure out.


Cameron Lackpour

Jason said...

Okay, I had to go back and re-read the whole post to come up with an answer. I haven't tested any of this lately but I am relying on knowledge of sorting this out many years ago.

So, first of all, when a string (e.g., the contents of what you pass to the shell command, for example) is enclosed in double quotes (and this is true of many scripting languages), it turns on "variable interpolation" -- meaning that it replaces $foo with its contents. Easy enough. It also means that backslashes will be treated as escape sequences. That's why you use double backslashes -- the first one is an escape that means "okay, the next character is special" and then comes the next character which is a backslash, so that just means "okay, so the character is really a backslash". There are a handful of other special characters (the one that you may be most familiar with is \n or \r\n which are for newlines.

Using single quotes does not interpolate variables or backslashes -- that's why 'the value is $foo' prints out literally "the value is $foo" without taking the $foo variable and sticking it in.

So, anyway, things are a little tricky when you have the MaxL shell interpolating variables and then handing things off to the Windows shell to figure out. I think the examples you have with three backslashes are surprising to me that they work. In order to use these in the past I had to, believe it or not, use four (FOUR!) backslashes in the MaxL code if it was in double-quotes -- meaning that I am escaping two backslashes so that MaxL just sees two literal backslashes, hands that off to the windows shell which sees two, which gets translated into one literal backslash.

So it's really nothing special with cat working with/without the backslashes, it's how the normal string interpolation (which is the same semantics as, say, using an import statement with double quotes or whatever).

That all being said, Windows has supported using forward slashes for quite some time. As long as you don't need to refer to a UNC path, you can use those. So instead of C:\Foo\Bar, you can refer to the same directory as C:/Foo/Bar and windows figures it out. PLUS, forward-slash doesn't have ANY of the issues with escaping and use in double quotes that the backslashes do.

Try it out and if you have any issue at all please hit me up and I'll take a look, hope that helps!

Cameron Lackpour said...


Okay, so I fired up my cloud instance and tried every combination of \ and /. Simply put, if it's in a " and ", e.g.,
shell "dir c:\\\\Automation\\\\Sample\\\\MaxLLog\\\\dData.err" ;

No combination of \ or / works.

If those " and " are removed, neither \ or \\\\ works. Yes, \\ and \\\ work. Oh joy.

But then I thought, "Self, how about getting rid of the dir and replacing it with type? What happens then?" Ah, then the " and " DO work, but only with \\, \\\, and \\\\ (didn't go farther than that).

So the issue here was me thinking that cat was catalog (Where oh where did I get that from? Beats me. Gasp, it might be the Apple II OS.) Tonight I'll amend my post. Thanks for keeping me honest.


Cameron Lackpour

Jason said...

Not sure if this fits but if you are just calling this from a batch file another way to go might be to just do file checking there and call multiple MaxL scripts as needed. E.g., the batch file contains something like

IF EXIST foo.txt GOTO LoadFile

essmsh loadfile.msh

ECHO No file!

ECHO Automation complete.

er77 said...

I am agree thith last comment of Joson:
MAxl only for one operation. BAsh or BAT for WorkFlow. Also U can use Maxl-Perl module for native integration and complex logic WorkFlow.

Cameron Lackpour said...


Let me play Devil's Advocate on this -- if you use the technique outlined in this post, you don't have to go checking for the file or its contents externally. This approach lets you pipe an error to a calling script that "knows" when an error level of 40 exists there was a data load problem.

There are lots of ways (Perl, VBSCript, NT CMDs, PowerShell, all the various *nix scripts, Groovy, and I'm sure I missed a few, and of course MaxL) to handle MaxL errors. I *like* having MaxL generate all of the possible errors with specific values, but that's just me.

Having said that, I completely agree -- there is most definitely more than one way to do this as outlined abvoe. Happily, the world of Essbase scripting is pretty wide open.

If anyone reading this blog is coming to KScope12, I have a beginner's course on scripting called "Script or Die!" where I will go into different ways to handle scripting and of course error handling.


Cameron Lackpour

Bob said...

COuld not see all the images, but great post.


Cameron Lackpour said...


I'm glad you liked it. I hope you enjoyed the tortured back and forth between Jason and me.

Re the images -- Blogspot has dumped a bunch of the graphics in many of my posts. Apparently this happens all the time. Of course there is no one to talk to -- you are on your own. Such is the price of a free service. What is particularly annoying is I think I have already reloaded the graphics and Blogspot lost it *again*. Arrrgh.

If it wouldn't be the most colossal pain I would gladly transition to another provider.

If you're coming to KScope12, I am going to use this example in my Script or Die! presentation.

And I guess that means I need to put the graphics back. Good thing I write these things in Word first so the pictures are readily available.


Cameron Lackpour

Unknown said...


Thanks for a another useful trick.

exit 40;

-Chris Rothermel

Unknown said...


Thanks for another USEFUL tip. This goes into my notes next to your "slay bad data in Essbase" chapter from your book.

exit 40;

-Chris Rothermel