AnsweredAssumed Answered

Handling ExecuteSQL that returns no records

Question asked by AlexanderBorchers on Aug 15, 2015
Latest reply on Aug 16, 2015 by AlexanderBorchers


Handling ExecuteSQL that returns no records



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..."";",";"")


   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!