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?
forgot to get the actuall error that I get
"[PLEX9999] New transaction is not allowed because there are other threads running in the session."
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.
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
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>
Set Environment<*Returning status> = <*View status.*Error>
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 )
I would also recommend not using the surrogate patterns and using https://en.m.wikipedia.org/wiki/Identity_column
Could you please tell us, how do define an identity column in Plex ?
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)
And I am also doing this for ibmi also now but can't take the credit, read 8A - Unleashing The Power Of Plex.ppt
Many thanks ! Does the source code solution (StartCommitment, EndCommitment) work in your environment ?
Yes - Using the API seems to resolve this issue -
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.
Case 00180025 logged