2 Replies Latest reply on Aug 16, 2015 7:43 AM by AlexanderBorchers

    Handling ExecuteSQL that returns no records

    AlexanderBorchers

      Title

      Handling ExecuteSQL that returns no records

      Post

      Hello,

      I am an experienced developer that's relatively new to fm.  This is my first query posted to this form, which has been very useful.  (So as I gain experience I' look forward to being an active contributor...)  I'm using the ExecuteSQL functions that returns a list of table uids, which i then use to do another set of operations.  The way I set this up is that delimited string that is returned rom the ExecuteSQL function is assigned to a variable ($SQL_RESULT) which I then, within a Loop, use the GetValue() function to pull out the individual records.  In a nutshell:

      Set Variable[$REC_NUM; Value:1)

      $SQL_RESULT = ExecuteSQL("SELECT..."";",";"")

      Loop

         Set Variable[$CUR_REC; Value:GetValue( $SQL_RESULT ; $REC_NUM)

           [Do Something with $CUR_REC.....]

         Set Variable[$REC_NUM; Value:$REC_NUM + 1)

      End Loop

      This is all working nicely.  My question is this: I want to add code to handle a situation where the SELECT statement returns no records.  And so in testing this scenario, I find that within the Script Debugger Data Viewer window (where one tracks the current values of variables) , the entry for the $SQL_RESULT variable simply disappears!  I've tried using Get (Last Error) as another approach but that returns zero.  My hunch is that if the ExecuteSQL returns no records, $SQL_RESULT would get set to an empty string or "?", but since the variable disappears from the debugger I have no idea what its value is, if it has been dropped from memory, or what.

      Any advice/assistance would be most appreciated.  Thank you!

       

        • 1. Re: Handling ExecuteSQL that returns no records
          philmodjunk

          IsEmpty ( $SQL_RESULT )

          will be true if your query had a null result returned.

          You might also consider using ValueCount ( $SQL_RESULT )

          since you need to know when to stop looping through values and this function will return a value of 0 when you get a null result.

          But I'd specify the ¶ as my record separator here instead of "".

          • 2. Re: Handling ExecuteSQL that returns no records
            AlexanderBorchers

            Hi PhilModJunk - Thanks!  I tried both your ideas but will use ValueCount ( $SQL_RESULT ) so I can use it (as you suggest) to both evaluate whether the SQL returned records and also to gracefully exit the Loop. 

            Regarding your suggestion as to the record separator, "¶" is the default, and thus by using an empty string in that parameter it produced the same result as what you suggest, but I agree it probably makes the code clearer to explicitly specify the row separator as such.  In any event, thanks again for your response; I really appreciate it.