AnsweredAssumed Answered

Handling ExecuteSQL that returns no records

Question asked by AlexanderBorchers on Aug 15, 2015
Latest reply on Aug 16, 2015 by 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!

 

Outcomes