INSERT OR UPDATE WITH A COUNTER

Question

Question

omer · Jan 14

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:

[SQLCODE: <-400>:<Fatal error occurred>]

[%msg: <Unexpected error occurred: <UNDEFINED>%0Ao+3^%sqlcq.74.1 *sqldatad(3)>]

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!

Product version: Caché 2018.1

Discussion (21)4

Log in or sign up to continue

Robert Cemper · Jan 20

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

0 0

Robert Cemper · Jan 22

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 quit 1
}

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:

INSERT OR UPDATE pck.myTable
    SET name='Omer'
    WHERE pck.IncDec(-2)=1
    AND .... 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

0 0

Vitaliy Serdtsev · Jan 23

And if so?

insert or update myTable(name,counter)
  select
    'Omer',
    case
      when exists(select * from myTable where name='Omer')
        then (select counter from myTable where name='Omer') + 1
      else 1
    end

The "name" field is assumed to be unique.

1 0

score 0 · Answer 1 · 2025-01-16T09:06:58-05:00

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

score 0 · Answer 2 · 2025-01-20T00:11:28-05:00

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

score 0 · Answer 3 · 2025-01-22T03:25:20-05:00

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.

score 1 · Answer 4 · 2025-01-21T09:11:36-05:00

Robert Cemper · Jan 21

Calculated doesn't do it.
BUT;
[ SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ]

1 0

score 1 · Answer 5 · 2025-01-17T08:24:40-05:00

Have you looked at utilizing

https://docs.intersystems.com/iris20243/csp/docbook/Doc.View.cls?KEY=GSQ...

score 0 · Answer 6 · 2025-01-17T08:34:09-05:00

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.

score 0 · Answer 7 · 2025-01-21T08:28:16-05:00

Stephen Canzano · Jan 21

You might also look at https://docs.intersystems.com/hs20241/csp/docbook/DocBook.UI.Page.cls?KE...

0 0

score 0 · Answer 8 · 2025-01-20T00:13:12-05:00

Just as @Enrico Parisi
said, It seems this works for inserts but not for UPDATES which is what i am looking for here..

score 0 · Answer 9 · 2025-01-21T09:41:47-05:00

The documentation link reports

https://docs.intersystems.com/hs20241/csp/docbook/DocBook.UI.Page.cls?KE...

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.

score 0 · Answer 10 · 2025-01-22T03:26:46-05:00

omer · Jan 22

The DOC link you sent doesn't show even if i login... So hard to see if this will help.

0 0

score 0 · Answer 11 · 2025-01-22T04:10:45-05:00

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

score 0 · Answer 12 · 2025-01-22T07:48:48-05:00

Here is the corresponding (as per @Stephen Canzano post) IRIS documentation (no password required):

Version Checking (Alternative to Concurrency Argument)

score 0 · Answer 13 · 2025-01-22T08:05:48-05:00

But the problem with version checking is that it only increments, what about the part that some updates needs to decrement the counter?

score 0 · Answer 14 · 2025-01-27T01:36:40-05:00

The query can be yet simplified:

insert or update myTable(name,counter)
  select
    'Omer',
    nvl((select counter from dc.myTable where name='Omer') + 1, 1)

score 0 · Answer 15 · 2025-01-28T00:41:48-05:00

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!

score 0 · Answer 16 · 2025-01-28T01:32:18-05:00

Yes, these are fields that will either be inserted or updated.

PS:
In fact, the following query should work correctly:

INSERT OR UPDATE myTable SET name='Omer',  counter = NVL(counter,0) + 1

But unfortunately, the SQL engine generates code where an undefined variable is accessed, so the <UNDEFINED> error occurs.
Try contacting WRC for a fix.

score 0 · Answer 17 · 2025-01-28T03:05:32-05:00

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

score 0 · Answer 18 · 2025-01-28T03:16:46-05:00

Vitaliy Serdtsev · Jan 28

That's right, the generated code lacks $GET or initial initialization of the variable.

0 0