Solved

Cannot Update or Delete records on tables made from Custom LUs thought API calls using the Modify__ or Remove__ methods.

  • 8 January 2021
  • 10 replies
  • 1179 views
C
Rank
Userlevel 2
Badge +6

When trying to update a record using the API for any Custom LU (_CLT table in IFS), we get this error. ERROR: MODIFIED2: The ‘LU Name’ object has been modified by another user. Please refresh the object and reenter your changes. The error is saying that a record can't be updated because it is already being updated, which is not the case. This happens regardless of which table the triggering action is coming from. The update method that is called using the API is 'PackageName'_CLP.Modify__( ). This modify method is auto generated when the Custom LU is made. We have verified that the New__ API call for inserting a record does work correctly for Custom LU tables, but the Modify__ or Remove__ API calls do not.

To be clear, we are able to call the API methods to Update, Insert, and Delete records from IFS standard tables, (tables ending in _TAB), as well as custom field tables, (tables ending in _CFT) but we are having this issue with the tables made from Custom Logical Units, (tables ending in _CLT).

I am on IFS version 10, Update 10.

Sample error message is below. any help with this would be very much appreciated. Thanks!!

Aurena Error message when attempting to modify a custom LU record via a trigger.

 

icon

Best answer by ChaMylesC 11 January 2021, 14:11

View original
This topic has been closed for comments

10 replies

paul harland
Rank
Userlevel 7
Badge +24

hi @ChaMylesC 

Are you passing a value for Objversion (:p2 shown below)

 

 

C
Rank
Userlevel 2
Badge +6

Hi Paul,

Yes, I am passing a value for the objversion. I’m getting it directly from a select statement.

  action_ := 'DO';

 objkey_ := '0F5D128C1A144BA0B012606AAFABDB49';

  SELECT CHANGE_CONTROL_BOARD_ROLE_CLT.ROWVERSION into objversion_
  FROM CHANGE_CONTROL_BOARD_ROLE_CLT
  WHERE CHANGE_CONTROL_BOARD_ROLE_CLT.ROWKEY = objkey_;
  

--Update Record
 attr_ := 'CF$_NAME' || chr(31) || '350109DCDE1F4A298F4F50608577D5DB' || chr(30);
 CHANGE_CONTROL_BOARD_ROLE_CLP.Modify__(info_, objkey_, objversion_, attr_ , action_);
 attr_ := '';
 

paul harland
Rank
Userlevel 7
Badge +24

You cannot just use rowversion as it is.  You have to convert it Date to text, comme ça:

 

to_char(rowversion,'YYYYMMDDHH24MISS')

C
Rank
Userlevel 2
Badge +6

Hi Paul, 

I actually found out that I should be using the CLV table (the view) . to get the objversion, and objkey, and apparently the objid_. I can use it as is when getting it from the view.

 

Also, I found that the modify__ and remove__ methods, use the objid_ attribute not the objkey_ attribute. I’m not sure why the modify method asks for object key when the object ID needs to be passed. It’s a little confusing.

paul harland
Rank
Userlevel 7
Badge +24

yes, in general it’s better to query the view for security reasons; unless doing so leads to poorer performance.

 

Also in general, these methods use objid and objversion; it’s just that in the CLT case we use the rowkey as the objid.

C
Rank
Userlevel 2
Badge +6

Thanks for the help Paul! So the rowkey is the objid for CLT tables? What is the rowid value for then in the CLT tables.

paul harland
Rank
Userlevel 7
Badge +24

ROWID is an Oracle framework column, not something IFS put there.

For standard tables we use(d) ROWID as OBJID, and used that as the alternative key for updates and deletes.  Rowkey/objkey (on standard tables) is an IFS column that was introduced later, to support various requirements but principally custom fields.

For « insert reason here » when IFS introduced Custom LUs it was decided that the OBJID would be read from the ROWKEY, not the ROWID.  Hence, objkey and objid are the same.

I don’t know the reason, but probably to make it more IFS-y and less Oracle-y.

 

C
Rank
Userlevel 2
Badge +6

Thank you for the explanation Paul, that is going to be great information going forward for me and my team. Thanks!

 

durette
Rank
Userlevel 7
Badge +18

For « insert reason here » when IFS introduced Custom LUs it was decided that the OBJID would be read from the ROWKEY, not the ROWID

I’m only guessing, since I’m not an IFS employee. One good reason is that it reduces vendor lock-in. Even if IFS stays with Oracle Database, moving to a more vendor-neutral architecture would improve their ability to negotiate with Oracle Corporation. Using ISO-compliant SQL increases the talent pool for employee candidates, partners, and customers.

(This is why I think everyone should use COALESCE instead of NVL, for example.)

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
C
Rank
Userlevel 2
Badge +6

You cannot just use rowversion as it is.  You have to convert it Date to text, comme ça:

 

to_char(rowversion,'YYYYMMDDHH24MISS')

Thanks Paul. This definitely works. I just wanted to add that you can also use the view to get the OBJKEY and OBJVERIONS for custom LU tables.

Method 1: use the CLT (table) view to obtain the ROWKEY and ROWVERSION.

SELECT ROWKEY, to_char(ROWVERSION,'YYYYMMDDHH24MISS') into objkey_, objversion_

  FROM TABLE_NAME _ROLE_CLT

  WHERE TABLE_NAME _CLT.ROWKEY = ‘the rowkey for this record’ );

 

Method 2: use the CLV (view) to obtain the OBJID and OBJVERSION.

  SELECT OBJKEY, OBJVERSION, into objkey_, objversion_

  FROM TABLE_NAME _ROLE_CLV

  WHERE TABLE_NAME _CLV.OBJKEY= ‘the rowkey for this record’ );

Terms & ConditionsCookie settings