Question
chenna velagala · Feb 18, 2021

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

&sql(update X_X.X  set LocalEnvironment=1 where %ConfigName IN('X_X_X', 'Y_Y_Y'))

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.
  

$ZV: DF453592-2048-11E5-A694-5056AB18ED00
Product version:
Ensemble 2017.1
00
2 0 10 89

Replies

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 -

Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.1.3 (Build 317_0_18252U) Thu Jun 28 2018 13:12:49 EDT [HealthShare Modules:Core:15.03.1007 + Linkage Engine:15.03.1007]

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. 

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.