New post
Question
Dominic Chui · Apr 6, 2022

Dynamic SQL and Setting SQLCODE

#Caché

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?

Product version: Caché 2017.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2.2 (Build 867_4U)
0
0 240
Discussion (7)1
Log in or sign up to continue
Vitaliy Serdtsev · Apr 7, 2022

I confirm (used the following code):

#include %systemInclude
n
try{

  s sql="select * from (select 0 field union select 1) where 0=1"
  
  #dim rs As %SQL.StatementResult=##class(%SQL.Statement).%ExecDirect(,sql)
  w rs.%SQLCODE
  
  s st = ##class(%SQL.Statement).%New()
  $$$ThrowOnError(st.%Prepare(sql))
  s rs = st.%Execute()
  w !,"---",!,rs.%SQLCODE
  
  &sql(select * from (select 0 field union select 1) where 0=1)
  w !,"---",!,SQLCODE

}catch(ex){
  w "Error ", ex.DisplayString(),!
}

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

0 0
Benjamin De Boe · Apr 7, 2022

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

0 0
Dominic Chui · Apr 7, 2022

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?

0 0
Benjamin De Boe · Apr 7, 2022

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 :-)

0 0