Plex 2E

 View Only
  • 1.  Update tables when Identity column doesnt allow Updates

    Posted Jul 20, 2021 07:20 AM

    using the standard plex 'update' action diagram option - on a table that has an Identity column - we get an error "Explicit value specified for identity field in table 'xxx' when 'SET IDENTITY_UPDATE is Off'

    there is a 'Delete Where' but not an 'Update Where'

    this is obviously set by the table/database level but as the update view includes the Identity column   - (I remove from the Insert view and this works)

    I can obviously add a Exec SQL to perform an Update Table 'Where identitycolumn = parameter  - but then I have to add source for each table

    I have tried dropping the identity column - but then the update doesnt happen - 

    can anyone figured a 'cleaner' way to do this when Updating rows with an Identity column ?




    ------------------------------
    Wayne Morgan
    Application Developer
    SS&C Technologies
    ------------------------------


  • 2.  RE: Update tables when Identity column doesnt allow Updates

    Posted Jul 20, 2021 03:20 PM

    To Add.. -  I have attempted to use The 'Positioned Updates' Option in the INI  - but his still generates the Identity Column in the Update Statement to be sent to the Database 

    I am able to create a separate view without the Identity column that works well for the updates, but I cannot use on the Update function  - the only close way of doing this is to try and find a 'unique key' from the remaining columns and use that  - however in most cases, the reason for using the 'Identity' column is because there isnt a unique key to be used with the remaining columns.

    just looking for a better way than using hand created SQL statements 



    ------------------------------
    Wayne Morgan
    Application Developer
    SS&C Technologies
    ------------------------------



  • 3.  RE: Update tables when Identity column doesnt allow Updates

    Posted Aug 10, 2021 02:10 PM
    Wayne,
    You could try developing your own UpdateRow over the view that does not contain the identity column.  Your new function may need to use use the "Select Where" statement (with For Update).   You could use meta code to create a where clause that fetched based on the identity column, so it could be developed as a pattern.  Once the Select had been processed, then the update may work.  I have not tried this myself, so do not know how it will work.


  • 4.  RE: Update tables when Identity column doesnt allow Updates

    Posted Sep 10, 2021 11:01 AM
    Is this Identity column a part of the key for your entity, or is it just there for database synchronization?  If it is not in use in the action diagram, you could probably add "TBL Column FLD" triples to the Physical table for the entity for ALL columns on the table, and just not include that column on the entity itself.  This allows you to model a physical table that has columns that you will not be using.
    If this identity column is part of your key structure, you will need it to be present in the Update view, and you will probably want to add code to your Insert that determines what the identity used was, and returns it for any future update values.  That makes things more difficult.