Subscribe Bookmark RSS Feed

Open Database() Errors

klockk

Occasional Contributor

Joined:

Oct 7, 2016

I've got a weird one today.....

I've been working with several JSL scripts... each of them has several Open Database() Calls.. and if the Open Database fails for any reason.. I have always seen entries in the log file regarding the issue...  I then borrowed code from log_execute_sql.jsl I found somewhere... and basically read the last 10 lines of the log fiel and display dialogs for users if an error occurred....

Each of these scripts has a UI associated with them....

today I started working with a new script, and it seems to not be writing the Open Database() Errors to the log, but rather throwing JMP ALERT Dialog Boxes up .. which is sort of nice, but not quite friendly enough... and I would like to use my function to check for errors....

Is there some method of controlling if these go to the log or not??? The Only difference I can see in this new script I inherited is that it doesn't make any DB calls until AFTER the UI comes up ... If I make an Open Database() Call BEFORE the New Window is rendered and defined/populates.. the error is in the log... Interesting!! how can I make it write them ALL to the log..??

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

If you turn on batch interactive, it will force the dialogs to the log (in most cases).  Just make sure you don't leave it on as it can mess up a whole lot.  I'd just do something like

Scriptscriptscriptscript;

Batch Interactive(1);

DatabaseCall;

Batch Interactive(0);

MoreScriptScriptScript

4 REPLIES
erichill

Staff

Joined:

Oct 1, 2013

Hey, klockk,

This might be worth opening a tech support issue on (e-mail to support@sas.com). 

In general, errors that are generated by scripts go to the JMP Log, and errors that are generated by interaction with the JMP UI are displayed as alerts, but gray areas exists where both a script and a UI are involved.  So it is not shocking that you are seeing a difference depending on whether a UI is displayed or not.

If you could add information about the version of JMP you are using and a screen shot of the alert dialog box that is appearing, that would be helpful.  And if you have a script that reproduces the problem that you could attach (or send to tech support), that would be great as well.

Thanks,

Eric

klockk

Occasional Contributor

Joined:

Oct 7, 2016

I’m seeing this in both JMP9 and JMP12…

It’s a bit odd, because I don’t see these things in several other Add-in Scripts we have been working with as of late…

With regard to this specific script… I can’t find anything different about the DB Calls (Open Database()), but if the UI hasn’t presented  itself yet, the error goes to the log file…

If the UI is presented, and the user is interacting with it.. all of the sudden these errors are not going to the log, but instead are displayed interactively..

(See Image 001)

Which of ‘course  results in further errors, because we couldn’t trap the error from the log like we have for all our other scripts…

(See Image 002)

As I test, I added a DB call prior to the UI appearing (via new Window())

And sure, enough, the error goes to  the log, and I can  trap it….

(See Image 003)

// Test the Database Connect and Report if there is an Error....

// Oddly, this script doesn’t write ALL DB Errors to the Log file, so let's at least make sure we can connect ok..

dbTestQuery = "select SYSDATE from dual";

if (Version < 11,

      dtTest = Open Database(

              "DSN="||DSN_NAME||"",

              dbTestQuery

              );

      , /// Else we can use the Invisible directive here

      dtTest = Open Database(

              "DSN="||DSN_NAME||"",

              dbTestQuery,

              Invisible // This DataTable won’t be visible, but will show in the Window List

              );

);

_DBError=checkLogForODBCErrors("dbConnectTest");

print("dbTest DBError=" || char(DBError)) ;

// close the data source as we have already retrieved data from table

close(dtTest, "nosave");

print("Closed DB connection : dbTest");

Then I can present the user with a more meaningful error message, AND use this after the DB Call to verify we had an error or not…

(See Image 004)

    /*

        Check JMP Logfile for Errors after DB Calls

    */

    checkLogForODBCErrors = Function(,

    // OK we're good to go now.

    // clearlog();

    // Check the log window for ODBC errors

        logcontents = get log(-10); // Get the last 20 lines from the Log

        founderror  = 0;

    // Look for [ODBC Driver Manager] errors

    // or

    // Look for [VerticaDSII] errors

    // or

    // Look for "LDAP authentication"

        for (i = 1, i <= nitems(_log_contents), i++,

            if (contains(_log_contents, "[Microsoft][ODBC Driver Manager]"),    // Looking for these errors

                founderror = 1;

                extraErrorMessageText="Please Check your ODBC Datasource Configuration and ensure you have named your DSN=" || DSN_NAME || "." ||

                                      "Also Verify your username and password are correct. and the connection tests with success";

                break();

                , // else

                if (contains(_log_contents, "[Vertica][VerticaDSII]"),  // Looking for these errors

                    founderror = 1;

                    extraErrorMessageText="There appears to be an issue connecting to the Database using Data Source Name=" || DSN_NAME || ".";

                    break();

                    , // ELSE

                    if (contains(_log_contents, "LDAP authentication"),  // Looking for these errors

                        founderror = 1;

                        extraErrorMessageText="Please Check your ODBC Datasource Configuration for DSN: " || DSN_NAME || "." ||

                                              "And Verify your username and password are correct.";

                        break();

                        ,

                        if (contains(_log_contents, "FATAL "),  // Looking for these errors

                            founderror = 1;

                            extraErrorMessageText="Please Check your ODBC Datasource Configuration for DSN: " || DSN_NAME || "." ||

                                                  "And Verify your username and password are correct.";

                            break();

                            );

                        );

                    );

              );

            );

        if (_found_error,

        // then

            // Print some useful information to the Log

            print("Addin: " || addinName);

            print("DSN: " || DSN_NAME);

          print("_Caller: " || _Caller);

            _nw = new window("MFG360: DB Error", << modal,

                textbox("The following error occurred in " || _Caller || ": "),

                textbox(""),

                textbox(_log_contents),

                textbox(""),

                textbox(extraErrorMessageText),

                textbox(""),

                textbox("If requested, please email the contents of the JMP Log window to technical support." ||

                        "  To see the Log window click View > Log and then Window > Log.")

            );

          );

      founderror; // Return 1 (True) or 0 (False) ....

    );

I just performed a test on one of our other scripts based on what I am figuring out… and I DO see the same behavior….

Any DB Calls AFTER the UI is up and running, seem to present interactive JMP Alerts, but nothing is going to the log file..??

This sort of breaks my error handling….. and I’ll have to add more validation tests to check the DataTable returned I guess….??

Unless there is a way to force these errors to go to the log so I can provide more user friendly error dialogs…

(See Image 005)

Solution

If you turn on batch interactive, it will force the dialogs to the log (in most cases).  Just make sure you don't leave it on as it can mess up a whole lot.  I'd just do something like

Scriptscriptscriptscript;

Batch Interactive(1);

DatabaseCall;

Batch Interactive(0);

MoreScriptScriptScript

klockk

Occasional Contributor

Joined:

Oct 7, 2016

Perfect! Thanx! That works wonderfully... exactly what I was looking for.....