SQLCompute does not work when Insert/Update done via the Caché ODBC DLL

Question

Question

Ikara Ikliki · Nov 13, 2019

I have SQLCompute with SQLComputeCode on a couple of Properties in my class.

These work fin when I insert/update via ObjectScript or SQL from the ManagementPortal.

However, if I insert (create new record) via my C# app using the Caché ODBC DLL the SQLCompute is ignored, it does not run.

Is this expected InterSystems Caché behaviour? We are on latest IRIS platform.

Discussion (7)1

Log in or sign up to continue

Eduard Lebedyuk · Nov 13, 2019

You can use both:

[ SqlComputeOnChange = (%%INSERT, %%UPDATE) ]

1 1

Robert Cemper · Nov 13, 2019

SqlComputeOnChange should trigger on INSERT

while it should trigger only if the value is CHANGED by an UPDATE.

If you need recalculation in any case you may use an ordinary Update TRIGGER to adjust your property as you need.

0 0

score 0 · Answer 1 · 2019-11-13T07:42:17-05:00

I think I may have come closer to the problem, I have established that if the SqlComputeOnChange value is %%INSERT then the SqlComputeCode is executed ONLY when a new record is created(inserted), and when it is %%UPDATE it is ONLY executing the SqlComputeCode when a record is updated and NOT for insert as well. This is contradictory to what is stated in the InterSystems documentation, which states that the SqlComputeCode for a value of %%UPDATE is executed for BOTH insert and update, I quote:

"With %%UPDATE, Caché computes the field value when a row is inserted into the table and recomputes it when a row is updated. In both cases, Caché invokes the code specified in the SQLComputeCode keyword to set the value."

Now I am stuck because I need the property to be set on BOTH Insert AND Update and that seems not possible at all.

score 1 · Answer 2 · 2019-11-14T01:38:15-05:00

Thank you Eduard, that is it. I did try to concatenate the %%INSERT and %%UPDATE with a comma separated and it did not like it, but I did not think of adding brackets (), that works perfectly now.

Please someone update InterSystems documentation......

score 0 · Answer 3 · 2019-11-26T04:19:55-05:00

After some tests and trying to figure out what is going on I have to say it is not working. What I noticed now is that the SQLCompute works for both Insert and Update if the update comes from the Management Portal SQL or ObjectScript. The update however does not work for ODBC.

In the FrontEnd the record is selcted via ODBC, the user makes the change to a field and it is saved via ODBC, what happens then is that the SQLCompute does not execute and the value that was in the field is overwritten with what was int he record coming from ODBC. The SQLCompute code thus does not execute on update if it comes from ODBC.

So here is the Property's definition:

/// Keep track of the version of the record each time it is updated, when newly created it starts at 1
Property InstanceVersion As %Integer [ SqlComputeCode = {set {*} = $INCREMENT({*}) }, SqlComputed, SqlComputeOnChange = %%UPDATE ];

I tested it:

Open the record in Front End via ODBC:

Then update from Management Portal. Before update:

After update the version increases:

then back to ODBC and make a change there and save:

The version remained 2, yet the Display value was updated via ODBC:

Update again from Management Portal:

Nice, version increased to 3

Now update again from ODBC:

Now the version has been set back to 2, URGGGH

Clearly this is not going to work, we shall then have to manually set the InstanceVersion from ODBC, which was not what I wanted to do.

score 0 · Answer 4 · 2019-11-26T04:21:50-05:00

Apologies, the real definition of the property incudes %%INSERT, I removed it for the time being to check if that was the problem why it is not working, but the actual definition is:

/// Keep track of the version of the record each time it is updated, when newly created it starts at 1
Property InstanceVersion As %Integer [ SqlComputeCode = {set {*} = $INCREMENT({*}) }, SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

score 0 · Answer 5 · 2019-11-26T04:26:24-05:00

My apologies, after typing all the above I realized what is happening (helps to explain to yourself), the SQLCOMPUTE increases the value in that field that is in the current record being saved and not what is in the last record that was saved. So you have to lock the record rather to not allow updates until you are done saving the record.