2 Replies Latest reply on Oct 5, 2013 1:51 AM by psijmons

    SQL Error Code syntax instead of ?

    taylorsharpe

      OK, I love ExecuteSQL and use it all the time now. But there is nothing so frustrating as getting the ? when it doesn't work and not knowing what is wrong.

       

      I went to the FMPUG (Dallas Chapter) meeting today and Martha Zink of Soliant was teaching about ExecuteSQL and she showed a way to get help that usually tells you what is wrong with your SQL syntax.

       

      Say I am in the Data Viewer and I have the following variable:

       

      Let ( [

      SEL = "Select Account from Rolodex Group By Account" ;

      SQLResult = ExecuteSQL ( SEL ; " " ; ¶ )

      ] ;

      SQLResult

      )

       

      This gives me a correct array of accounts as expected.

       

      But what if I misspelled Account say with one "c".

       

      Let ( [

      SEL = "Select Acount from Rolodex Group By Account" ;

      SQLResult = ExecuteSQL ( SEL ; " " ; ¶ )

      ] ;

      SQLResult

      )

       

      This will result in a ? which is not very useful.

       

      But if I do the following it will result in a blank result in the Data Viewer.

       

      Let ( [

      SEL = "Select Acount from Rolodex Group By Account" ;

      SQLResult = ExecuteSQL ( SEL ; " " ; ¶ )

      ] ;

      If ( SQLResult = "?" ; "" ; SQLResult )

      )

       

      The result will give me a blank. BUT, if I click the "Monitor" button and close this calculation window and reopen this variable, guess what! The result is refreshed to tell me what was wrong with my SQL. In this case, the result is:

       

      "The column named "Acount" does not exist in any table in the column reference's scope."

       

      Now that is a lot more useful than a question mark. Unfortunately, you have to click "Monitor" to close the calc window and then reopen it to get the result with an explanation, but at least it works. Maybe FM13 will show the result without having to close the window and reopen it to refresh it. And it doesn't always give you the answer to the problem with your SQL syntax, but it sure helps in many cases. This can really help out people who are new to ExecuteSQL.

       

      Heather McCue was there from Harmonic Data and said there was some similar way to get these results with the Evaluate function. I'm always interested in learning these new things. They sure help in development!