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?
Comments
I confirm (used the following code):
<FONT COLOR="#0000ff">#include </FONT><FONT COLOR="#000000">%systemInclude
</FONT><FONT COLOR="#0000ff">n
try</FONT><FONT COLOR="#800080">{
</FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"select from (select 0 field union select 1) where 0=1"
</FONT><FONT COLOR="#0000ff">#dim </FONT><FONT COLOR="#800000">rs </FONT><FONT COLOR="#0000ff">As </FONT><FONT COLOR="#008080">%SQL.StatementResult</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%SQLCODE
s </FONT><FONT COLOR="#800000">st </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
</FONT><FONT COLOR="#0000ff">$$$ThrowOnError</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Prepare</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">))
</FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">rs </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Execute</FONT><FONT COLOR="#000000">()
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#008000">"---"</FONT><FONT COLOR="#000000">,!,</FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%SQLCODE
</FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080"> from </FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">0 </FONT><FONT COLOR="#008000">field </FONT><FONT COLOR="#000080">union </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1) </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#000000">0=1</FONT><FONT COLOR="#800080">)
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#008000">"---"</FONT><FONT COLOR="#000000">,!,</FONT><FONT COLOR="#800000">SQLCODE
</FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">catch</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ex</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">{
</FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#008000">"Error "</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">ex</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DisplayString</FONT><FONT COLOR="#000000">(),!
</FONT><FONT COLOR="#800080">}</FONT>
Output:
0
0
100
Quote from the documentation:When retrieving results, first check for successful execution by examining %SQLCODE. For many statement types, success/failure is all that is needed. The %SQLCODE property contains the SQL error code value. Successful completion values are 0 (successful completion) and 100 (the operation has either found no data or reached the end of the data).proof
Calling @Benjamin De Boe
I can't write you a direct message.
This is expected behaviour. for a SELECT statement, %Execute() (or %ExecDirect()) will set %SQLCODE to 0 if the statement was successfully executed and ready to return rows, or an error code if the execution went wrong. It's through the use of %Next() that it will figure out whether there are any rows left. The first %Next() call on such a resultset will return 0 and at the same time set %SQLCODE to 100. This behaviour is similar and perhaps even more visible in the case of %ROWCOUNT for SELECT statements, which increases with every call to %Next(). See also the docs for %Next() (second bullet)
The simple reason for this is that we're not building the whole result set upfront, but rather "get ready" to iterate through it with %Next() calls, as users may only be interested in the first few rows. Some call this a lazy execution model, but I prefer the term efficient :-). Of course, for DML statements such as UPDATE and DELETE, the %ROWCOUNT is known immediately upon execution, as there is no result set to scan through
Thanks, that makes sense (although it does seem like parts of the documentation are somewhat misleading and could be improved.)
As a follow up question, is there an idiomatic way of checking whether a dynamic SQL SELECT query returns any values then?
That would be through calling %Next() right away. The way how our query code works is to walk through results and tell you when it runs out of rows to pass back.
Our online documentation has a feedback button that makes your comments immediately find their way to the doc writers. They really appreciate suggestions and usually turn them around quite quickly :-)
One of the options:
<FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%ScrollableResultSetdoc</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Prepare</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Execute</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">rs</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Count</FONT><FONT COLOR="#000000">()</FONT>
how can i pass dynamicarray which has args item to this %ExecDirect() method?