· 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.

Product version: Ensemble 2017.1
$ZV: DF453592-2048-11E5-A694-5056AB18ED00
Discussion (11)1
Log in or sign up to continue

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:

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?

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.