Best practices for developing/debugging PHP macros

    Best practices for developing/debugging PHP macros

    I've been spending the last few weeks trying to write new macros for my web spreadsheet.

    Here's the cycle I've been following when I do my new macro development:
    1. Create/edit a new macro using Tools -> Macros -> Macro Editor.
    2. Create a button in spreadsheet.
    3. Add macro to button.
    4. Press button.
    5. In veritably, macro doesn't work but there is no popup or other indication on spreadsheet that macro failed.
    6. Using System Manager -> Logs and 7-9 clicks, get the most recent failures (or better yet, since log messages are truncated) do tail -f logs/core.logs
    7. Look for error related to [php_macro_engine] (even though this is a spreadsheet failure). Error looks something like this:
    • [2013/02/25 10:06:56] ERROR [php_macro_engine] PHP Fatal error: Call to undefined function retrive_variable() in /core-Linux-i686/tmp/mmdl_f8a19c5a-46db-4571-a4da-5be82c5c5490.php on line 99
    • [2013/02/25 10:06:56] ERROR [php_macro_engine] failed to execute PHP function 'em()' from file '../httpd/app/base/macro/em.php': unable to execute function

    8. Edit the tmp file using vi/emacs and look for line with error in it. In case above error is undefined function, otherwise you'll need to hunt and peck to see why the PHP failed.
    9. Correct the error if obvious. If not obvious insert the following statement to get a popup with various variables populated.
    • return _msgbox("messages $variable1, $variable2.","Debug errors","Info");

    10. If macro continues to fail, move return _msgbox() statement around to isolate problem.

    I'd like to find out the best way to develop macros because this debug loop is getting pretty tiresome.

    I'm also thinking there are at least 3 improvements Jedox could make to make this process better.
    1. Add PHP syntax checking when user selects that "save" icon in the PHP editor page. I think the editor does some rudimentary checking but I think validating it against known functions might be better.
    2. Add a PHP debug error pane/popup would be nice too. That way the developer doesn't need to pop out to shell or log app to investigate error.
    3. Provide context in error dump in logs or error pane. Providing the +-3 line context around the failure would make debugging these issues a lot less painful.
    Something strange in Jedox 5.0.0.

    Rather than getting nice error messages like:
    • [2013/02/25 10:06:56] ERROR [php_macro_engine] PHP Fatal error: Call to undefined function retrive_variable() in /core-Linux-i686/tmp/mmdl_f8a19c5a-46db-4571-a4da-5be82c5c5490.php on line 99
    • [2013/02/25 10:06:56] ERROR [php_macro_engine] failed to execute PHP function 'em()' from file '../httpd/app/base/macro/em.php': unable to execute function

    which I had some chance at debugging, errors in macros in the new 5.0.0 server come up like this:



    • [2013/04/22 14:58:45] ERROR [php_macro_engine] failed to execute PHP function '_()' from file '../httpd/app/base/macro/register.php': unable to execute function
    • [2013/04/22 14:58:57] ERROR [php_macro_engine] failed to execute PHP function 'em()' from file '../httpd/app/base/macro/em.php': unable to execute function


    with out any references to line numbers or anything else that could be useful in debugging the macros.

    Anyone know how to get more useful debugging php macro debugging error messages out of Jedox 5.0.0?
    Hi Dominik,

    Here's an example:


    function test_Click ()
    {
    $app = application();
    $as = activesheet();

    $sel_group = retrieve_variable('sel_group');
    $sel_location = retrieve_variable('sel_location');
    $sel_start_date = retrieve_variable('sel_start_date');
    $sel_month_count = retrieve_variable('sel_month_count');
    $sel_resource = retrieve_variable('sel_resource');

    // update value and comment fields
    $value = $as->range('C16')->value;

    }



    Adding this php macro to my set of macros gives me:
    [2013/04/23 10:05:27] ERROR [php_macro_engine] failed to execute PHP function 'em()' from file '../httpd/app/base/macro/em.php': unable to execute function
    Removing the lines:


    // update value and comment fields
    $value = $as->range('C16')->value;
    Gives me the error:

    [2013/04/23 10:06:12] ERROR [php_macro_engine] failed to execute PHP function '_()' from file '../httpd/app/base/macro/register.php': unable to execute function







    I found one reason for the following error:

    [2013/04/23 16:12:08] ERROR [php_macro_engine] failed to execute PHP function '_()' from file '../httpd/app/base/macro/register.php': unable to execute function

    Caused by not typing a ';' at the end of a statement.

    Too bad the parser couldn't have suggested a parse error at that line instead of simply throwing that exception without any line numbers...
    Another error:

    If you get error like:
    [2013/04/23 16:18:33] INFO [palo] exception caught: 'Couldn't resolve element name "#NULL!" in dimension "<dimension name>".'

    In my case it was in a PALO_ENEXT() call from a php macro.

    Your problem may be the fact you forgot to call PALO_ENEXT() from a reference to the Jedox application.

    e.g.
    PALO_ENEXT(...);
    instead of
    application()->PALO_ENEXT(...);

    Its a shame that the PALO_*() function are callable without a reference to the application (it would have been nice if the error message was a bit more forthcoming with that analysis.)
    Regarding the missing detailed error message for an omitted semicolon, our default PHP error reporting level didn't include this particular type of PHP error (E_PARSE). In the file macro_engine_config.xml you have a line that controls the macro engine log level, by default it's "error":
    <functions loglevel="error">

    This "error" log level was translated to PHP's E_COMPILE_ERROR|E_RECOVERABLE_ERROR|E_ERROR|E_CORE_ERROR.

    I've now fixed it to include E_PARSE and E_USER_ERROR. You don't have to wait for the new version, you can just change the log level to "debug":
    <functions loglevel="debug">

    You'll now get all types of errors.

    Regarding global palo_* functions vs. application-level ones, they aren't "coming" from the same place. Global ones are coming from the Jedox PHP extension (libphp_jedox_palo.so) that extends PHP with a set of Jedox OLAP PHP functions (you can decide not to load this extension by commenting it out in macro_engine_config.xml). The application-level ones are actually spreadsheet functions, i.e. equivalent to functions you would type in a spreadsheet cell, e.g. you can do stuff like application()->sum(1,2,3)...

    Post was edited 1 time, last by “pmalic” ().

    Thanks!

    I found macro_engine.config.xml in core-Linux-i686/etc. I hope that's the right one to change. And thanks for explanation about the difference between the global palo functions and application functions. Its good to know how all of these pieces fit together.

    AHA!

    Also found a line for date.timezone = "Europe/Berlin" in this file. I'll *definitely* be changing that too!
    Yep, that's the one.

    As for the time zone, macro_engine_config.xml controls the PHP inside the Spreadsheet Server (Core). For PHP running inside Apache (e.g. backend for the Log viewer), there's a php.ini file. There's also another php.ini for the OLAP's Supervision Server (SVS).