Dynamic SQL and Setting SQLCODE
Is there a reason why calling %ExecDirect() (or %Execute) won't set the %SQLCODE of the result set to 100 even when the query returns no data?
Here is the code in question with a bit of contrivance to force the issue:
set statement = ##class(%SQL.Statement).%New()
set result = ##class(%SQL.Statement).%ExecDirect(statement,"select top 0 0 from Example.Testing")
w result.%SQLCODE
The %SQLCODE is set to 0 in this case. The same happens if I use %Prepare and %Execute, e.g.
set statement = ##class(%SQL.Statement).%New()
set status = statement.%Prepare("select top 0 0 from Example.Testing")
set result = statement.%Execute()
w result.%SQLCODE
The documentation suggests that %Execute should in fact set %SQLCODE:
method %Execute(%parm...) as %SQL.StatementResult
Execute the current statement and return the result. The result of the execute is always the return value. Success/Failure information is reported in the result object as %SQLCODE, %Message, %ROWCOUNT, and/or %ROWID.
So does anyone know what's happening here?