Unable to update or set property value using sql query
I have one block of code that not working as it was supposed to in in SQL. I am using it in a routine
using the above SQL query I am trying to set a property(LocalEnvironment) which is defined as boolean. But the query is not enabling the property. FYI- We have extended businessprocess class to create a new property.
Product version: Ensemble 2017.1
$ZV: DF453592-2048-11E5-A694-5056AB18ED00
What does SQLCODE tell you after your embedded SQL query?
What is your real $ZV ?
I was running the E-SQL in test and stage where the query returns no rows affected. Even when i query the table using select statement in test and stage, it doesnt retrieve any rows. But in PROD Instance E-SQL Fetches multiple rows for same BH.
$ZV -
What I asked for:
In your Method written in Objectscript, after
&sql(update X_X.X set LocalEnvironment=1 where %ConfigName IN('X_X_X', 'Y_Y_Y'))
Variable SQLCODE holds a success / failure code. That's what I asked for.
see this example:
https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GSQL_esql#GSQL_esql_syntax_ampsql
or
https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GSQL_esql#GSQL_esql_code_literals
and
https://docs.intersystems.com/iris20201/csp/docbook/DocBook.UI.Page.cls?KEY=RERR_sql#RERR_C136606
Thanks for the documentation Robert. SQL Code returns error 100.
So, as by docs.
SQLCODE=100 indicates that the SQL operation was successful,
but found no data to act upon
https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=RERR_sql#RERR_sql_noerr
Yes Robert - As per SQL docs, SQL Operation was successfull but found no data to act upon for one of the table. But there are two other tables where I couldnt receive any error code, but then the property was not enabled after executing sql query. FYI- When the property was declared Initial expression was set to "TRUE". Does that impact enabling and disabling any property?
Depending on the environment you use embedded SQL the variable SQLCODE may not reach you. This is related to variable scoping. Therefore it is better to use any variant of Resultset.
For variable scoping see my article here
Given that SQLCODE is 100 (in other comment thread), might you have an index on %ConfigName that has not been built?
What do you see from the following queries?
select * from X_X.X where %ConfigName IN ('X_X_X','Y_Y_Y')
select * from %IGNOREINDEX * X_X.X where %ConfigName IN ('X_X_X','Y_Y_Y')
Also, is X_X.X the table corresponding to the class where the property is defined?
There are multiple ID's for a given %ConfigName, but no indexing was observed. I received SQLCODE 100 only in TEST. In PROD i was able to fetch rows.
FYI- Though there are multiple ID's for a given %ConfigName, i was able to update LocalEnv Property using management portal. But in studio when i use E-SQL in a routine to update the property it doesnt effect any row. As Robert mentioned above, its related to variable scoping. I do not see % sign in front of the property variable when I queried the xx table.
What is the property type? my answer may be redundant but if the property's type is another class (like property As ##class) the Update operation may not have effect and you have to open the object first.
Property Type is Boolean and the property is also declared as a parameter for settings.
As Robert pointed out in one of this documentation, the variable is passed byReference in a Method.
"If you pass a variable ByReference to the called method / procedure you have a small door back to the calling scope."