Published on InterSystems Developer Community (https://community.intersystems.com)

Home > Dynamic SQL and Setting SQLCODE

Question
Dominic Chui · Apr 6, 2022

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?

#Caché
Product version: Caché 2017.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2.2 (Build 867_4U)

Source URL:https://community.intersystems.com/post/dynamic-sql-and-setting-sqlcode