Plex 2E

Expand all | Collapse all

Using Commit/rollback with SQL server - .NET and plex patterns -

Jump to Best Answer
  • 1.  Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-27-2015 02:01 PM

    Trying to put a simple Commit/transaction set in the 'Surrogate Entity' process -  .NET C# SQL Server 2008 

     

    using a simple 'Edit' pattern - I want to ensure that the surrogate on the surrogate system table only gets allocated IF the insert was succesfull

     

    so following the plex help I should be able to specify the  'Insert row' (that includes the call to the 'get Next surrogate' function &  update row of that table) as a fnc Commit SYS Parent - and then issue the Commit/rollback after the Insert statement in the

     

     

    Anyone had success at this or what am I missing here?



  • 2.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-27-2015 03:31 PM

    forgot to get the actuall error that I get

     

    "[PLEX9999] New transaction is not allowed because there are other threads running in the session."



  • 3.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 04:51 AM

    Hi Wayne -

     

    please tell us what driver you are using (SQLCLIENT or OLE DB) and if you are using the MARS option on the connection. I think we need MARS for Plex standard AD's , but this will interfere with transactions.



  • 4.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 08:22 AM

    This is using SQLCLIENT (as per the default settings for 7.2 .NET) - I also tried Using 'real' views and using 'Table access' but got the same result -

    I tried 'Transaction Isolation as 'Unspecified/ReadUncommited' same result

     

    I have NOT Specified MARS on the connection String - I assume it is as simple as adding 'MultipleActiveResultSets=True'   - I wil try that



  • 5.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 08:39 AM

    I tried adding MultipleActiveResultSets=True to the connection string for .NET - still I get the same error back

    I have also switch my variant to WinC and using SQLCLIENT Driver - my code behaves as expected - allocates the next Sequence from the Surrogate table (locks the Recordset)  - if the Insert (or check row) fails then the action is rolled back and the surrogate table is NOT updated - as i was hoping to acheive

     

    (to test this I renamed one of the columns on the table to force the insert to fail ) after building and ensuring that the function worked as expected -

     

    this is a Basic Edit Detail

    No other code was added other than below

     

    - the default 'InsertRow' function was FNC Commit Parent

     

    in the 'End Insert Row' edit point -

     

    If  Environment<*Returning status> == <*Returning status.*Successful>

    Commit

    Else

    Rollback

    Set  Environment<*Returning status> = <*View status.*Error>



  • 6.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -
    Best Answer

    Posted 07-28-2015 11:04 AM

    There seems to be a problem with   FNC commit SYS Parent.  I removed  the triple and added source code - works for me (SQL Server 2012, SQLCLIENT )

     

    ICommit SQL Server 2012.jpg



  • 7.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 11:07 AM

    I would also recommend not using the surrogate patterns and using https://en.m.wikipedia.org/wiki/Identity_column



  • 8.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 11:32 AM

    George -

     

    Could you please tell us, how do define an identity column in Plex ?



  • 9.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 11:38 AM

    Lorenz

     

    i have done this by defining an abstract entity

    @GFSSQLEntitywithIdentity Known by @RSTPIdentityID

     

    @RSTPIdentityID  DBMS Script @RSTPIdentityID   (IDENTITY(1,1),)

     

    then i created a new view without this field on and switched the InsertRow to use the view without the Identity field

     

    (this then adds the identity to the SQL table code that gets sent to SQL)

     

    I then added a default *Max value to teh sequence to avoid the Edit panels validating a Zero value)



  • 10.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 12:18 PM

    And I am also doing this for ibmi also now but can't take the credit, read 8A - Unleashing The Power Of Plex.ppt



  • 11.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 07-28-2015 01:29 PM

    Wayne -

     

    Many thanks ! Does the source code solution  (StartCommitment, EndCommitment)  work in your environment ?



  • 12.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 08-17-2015 08:37 AM

    Lorenz

     

    Yes - Using the API seems to resolve this issue -



  • 13.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 08-17-2015 10:08 AM

    Wayne -

    At least there is a workaround. The generator or the runtime still need to be fixed to support  FNC commit SYS Parent. You should open a support case with CA.



  • 14.  Re: Using Commit/rollback with SQL server - .NET and plex patterns -

    Posted 08-17-2015 10:34 AM

    Case 00180025 logged