INSERT OR UPDATE WITH A COUNTER
Hello,
So i want to use the INSERT OR UPDATE command so i can update a COUNTER for a given name:
INSERT OR UPDATE myTable
SET name='Omer', counter = counter + 1;
as you can see with the above code - if the row is non-existent then we get an error because COUNTER is NULL!
I tried the following to fix this but all have failed:
INSERT OR UPDATE myTable
SET name = 'Omer',
counter = CASE
WHEN counter IS NULL THEN 1
ELSE counter + 1
END
INSERT OR UPDATE myTable SET name='Omer',counter = COALESCE(counter + 1, 1)
INSERT OR UPDATE myTable SET name='Omer',counter = IFNULL(counter + 1, 1)
For any of the solutions above i received the error:
|
Side Note: Running the query below does work:
INSERT OR UPDATE myTable SET name='Omer',counter= 1
So this implies that problem is indeed with using the counter when it is NULL...
Would love to get some insight on that, Thx!
Comments
How about setting the column/property to be a calculated field and the system will do it for you with your Getter method?
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…
I don't think this will help, How would i get the counter from the Getter method?
The counter is per row - i don't have a lot of rows that i can sum up, so this will not work...
you have to add you own RowVersion property.
my example only increases the version if there was a any change in the row.
Property RowVer As%Integer [ SqlComputeCode = { if$i({*}) },
SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];works for SQL- and Object-mode
But what happens when i want to decrement it?
the counter in my application can and will be decremented.
if i use this it will only ever increment it.
Calculated doesn't do it.
BUT;
[ SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ]
Have you looked at utilizing
https://docs.intersystems.com/iris20243/csp/docbook/Doc.View.cls?KEY=GS…
Hi @Stephen Canzano,
it's my understanding that none of the counters described in the doc page you linked is incremented using UPDATE, nor can be modified by user code/SQL.
You might also look at https://docs.intersystems.com/hs20241/csp/docbook/DocBook.UI.Page.cls?K…
Just as @Enrico Parisi
said, It seems this works for inserts but not for UPDATES which is what i am looking for here..
The documentation link reports
https://docs.intersystems.com/hs20241/csp/docbook/DocBook.UI.Page.cls?K…
When version checking is implemented, the property specified by VERSIONPROPERTY is automatically incremented each time an instance of the class is updated (either by objects or SQL). Prior to incrementing the property, InterSystems IRIS compares its in-memory value to its stored value. If they are different, then a concurrency conflict is indicated and an error is returned; if they are the same, then the property is incremented and saved.
I've used the VERSIONPROPERTY parameter before and have observed that it is updated each time the object is saved.
The DOC link you sent doesn't show even if i login... So hard to see if this will help.
the posted link is limited to HS licensed users only
HERE is an official public link: VERSIONPROPERTY
It does basically the same as my previous SqlComputeOnChange example
hidden and with less typing
Here is the corresponding (as per @Stephen Canzano post) IRIS documentation (no password required):
But the problem with version checking is that it only increments, what about the part that some updates needs to decrement the counter?
I understand that you want to have full control of your version
Increment and Decrement eventually also more than just +1,-1
so VERSIONPROPERTY is a dead herring.
BUT: You can achieve this in combination with a little SQL method.
Property RowVer As%Integer [
SqlComputeCode = { if$i({*},$g(%IncDec)) },
SqlComputed,
SqlComputeOnChange = (%%INSERT, %%UPDATE) ];ClassMethod IncDec(step As%Integer = 0) As%Boolean [
SqlName = IncDec, SqlProc ]
{
set%IncDec=step quit1
}Now you can set the increment to any %Integer of your choice.
e.g. -1 decrement by 1, 1 increment by 1, 0 leave it
How to use it:
INSERTORUPDATE pck.myTable
SETname='Omer'WHERE pck.IncDec(-2)=1AND .... any other conditions ....the IncDec SQLmethod is used as a static method
it doesn't reference any row dependency
So it is executed once before any row related processing.
if you omit it then row_version is not changed
And if so?
<FONT COLOR="#0000ff">insert or update </FONT><FONT COLOR="#008000">myTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">counter</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">case when </FONT><FONT COLOR="#000000">exists(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">* from </FONT><FONT COLOR="#008000">myTable </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">then </FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">counter </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">myTable </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">) + 1 </FONT><FONT COLOR="#000080">else </FONT><FONT COLOR="#000000">1 </FONT><FONT COLOR="#000080">end</FONT>The "name" field is assumed to be unique.
The query can be yet simplified:
<FONT COLOR="#0000ff">insert or update </FONT><FONT COLOR="#008000">myTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">counter</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#808000">nvl</FONT><FONT COLOR="#000000">((</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">counter </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">dc.myTable </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">) + 1, 1)</FONT>
in your snippet, myTable(name, counter)
name and counter are the fields you want to look out in myTable?
and thank you for your answers!
Yes, these are fields that will either be inserted or updated.
PS: In fact, the following query should work correctly:
<FONT COLOR="#0000ff">INSERT OR UPDATE </FONT><FONT COLOR="#008000">myTable </FONT><FONT COLOR="#000080">SET </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'Omer'</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">counter </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#808000">NVL</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">counter</FONT><FONT COLOR="#000000">,0) + 1 </FONT>
But unfortunately, the SQL engine generates code where an undefined variable is accessed, so the <UNDEFINED> error occurs. Try contacting WRC for a fix.
I think SQL is correct.
The. problem is caused by INSERT or UPDATE
- for UPDATE NVL takes an existing field from a record and handles NULL
- for INSERT there is just no existing record to take a field from
NVL() is not a synonym for $GET() we know from ObjectScript
That's right, the generated code lacks $GET or initial initialization of the variable.
so what would be the correct way to achieve this?
Only InterSystems developers can fix the generated code. To do this, contact the WRC. You and I can only work around the bug by rewriting the query.
💡 This question is considered a Key Question. More details here.
Hi. Maybe this can help (%ROWCOUNT): https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic…
Hi Omer, would your solution consider a trigger? Haven't tried this but you could have a trigger that executes after insert/update that would launch an update on the affected row that basically would "add" the new value in the counter to the old one:
Your queries should change to something like this
INSERT OR UPDATE myTable SET name='Omer', counter = 1
INSERT OR UPDATE myTable SET name='Omer', counter = -1
.....
The logic in the trigger would grab old value (handle NULL as 0) and add the new value only in cases where both are different or whatever your business logic might be.
To prevent infinite loops on updates your after update trigger can be defined to only be executed after any of the other columns in the table are updated with the exception of counter.