New post
Question
· Feb 18, 2021

Unable to update or set property value using sql query

#SQL #Ensemble

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
chenna velagala · Feb 18, 2021

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]
0 0
Robert Cemper · Feb 18, 2021

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

1 0
chenna velagala · Feb 23, 2021

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?

0 0
Timothy Leavitt · Feb 24, 2021

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?

1 0
chenna velagala · Feb 25, 2021

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.

0 0