Query Batching - SQL command not properly ended

Posts   
 
    
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 29-May-2019 21:47:56   

Hello,

I'm trying to implement query batching with my application. It works great for SQL Server, but when I try it with an Oracle (ODP.NET) database, I'm getting the error below. This error occurs if I set the batch size to 2 or higher. Is there any way to tell what's causing this?

I'm using the latest LLBLGen 5.5.3.0 .NET 4.7.2

An exception was caught during the execution of an action query: ORA-00933: SQL command not properly ended. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

QueryExecuted: Query: INSERT INTO "ERINA_FRAMEWORK"."LOGIN" ("ENCRYPTION_VERSION", "EXPIRE_DAYS_MSG_DISP_LONG", "EXPIRE_DAYS_MSG_DISP_MEDIUM", "EXPIRE_DAYS_MSG_DISP_SHORT", "FAIL_PWD_ANSWER_COUNT", "FAIL_PWD_COUNT", "FRAMEWORK_USERS_ID", "IS_APPROVED", "IS_LOCKED_OUT", "PWD_ANSWER", "PWD_FORMAT", "PWD_QUESTION", "PWD_SALT", "PWD", "STATUS") VALUES (: p9, : p10, : p11, : p12, : p13, : p14, : p15, : p16, : p17, : p18, : p19, : p20, : p21, : p22, : p23); Parameter: : p9 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p10 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p11 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p12 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p13 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p14 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p15 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 146537. Parameter: : p16 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: : p17 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p18 : String. Length: 128. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: : p19 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p20 : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: : p21 : String. Length: 192. Precision: 0. Scale: 0. Direction: Input. Value: "x". Parameter: : p22 : String. Length: 192. Precision: 0. Scale: 0. Direction: Input. Value: "test". Parameter: : p23 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.

StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQuery.cs:line 288 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteActionQuery(IActionQuery queryToExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 752 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.<PersistQueue>b__74_0(IActionQuery q) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1271 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecuteElements(List1 elementsToRun) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQueryController.cs:line 174 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecutePending() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQueryController.cs:line 121 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PersistQueue(List1 queueToPersist, Boolean insertActions) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1283 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1235 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass19_0.<SaveEntity>b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 413 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy[T](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 919 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 413 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1150 at Grb.Platform.Framework.Business.DomainManager.SetFrameworkUserEntity(FrameworkuserEntity frameworkUserEntity, IDataAccessAdapter externalAdapter) in C:\Source\Production\Platform\Framework\Business\DomainManager.vb:line 1862

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 30-May-2019 08:01:39   

Hi Erina,

What is your Oracle Server version? We are looking into this.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 09:58:27   

The statement you're quoting is properly ended, but I guess the 'END' is missing around the whole batch? We'll look into if we can reproduce this. For Oracle, queries should be wrapped in BEGIN and END statements.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 10:18:08   

Can't reproduce it.


// code
var toInsert = new EntityCollection<EmpEntity>();
EmpEntity newEmp = new EmpEntity();
newEmp.Empno = 1203;
newEmp.Ename = "Foo Bar";
newEmp.Job = "sleeping";
toInsert.Add(newEmp);

newEmp = new EmpEntity();
newEmp.Ename = "Blah Blah";
newEmp.Job = "sleeping";
newEmp.Empno = 1201;
toInsert.Add(newEmp);

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.BatchSize = 2;
    try
    {
        Assert.AreEqual(2, adapter.SaveEntityCollection(toInsert));
    }
    finally
    {
        adapter.DeleteEntityCollection(toInsert);
    }
}

// sql
BEGIN
    INSERT INTO "SCOTT"."EMP"
                ("EMPNO",
                 "ENAME",
                 "JOB")
    VALUES    (:p1,
                :p2,
                :p3);

    INSERT INTO "SCOTT"."EMP"
                ("EMPNO",
                 "ENAME",
                 "JOB")
    VALUES    (:p4,
                :p5,
                :p6);
END; 

Works fine.

Please use ORM Profiler to see what query is executed, then post the C# code which is executed so we know what's going on and perhaps we use a scenario that's not matching yours. ORM Profiler is free for you, see My Account on our website

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 10:23:22   

Got it to reproduce!

3 elements in the collection, batch is 2, so 1 is left over in the final batch. Looking into it

(edit) It's very odd, queries look fine. It's as if when you have 1 transaction, you can't have both a statement wrapped with BEGIN / END and a statement without BEGIN / END. confused

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 10:41:57   

It works if the remaining batch with 1 query is also wrapped in a BEGIN / END. smile ... oracle, never a dull moment.

Fixing...

(edit) it turns out, it's the ';' at the end of the 3rd insert. This is bizarre. Nevertheless, working on a fix.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 11:31:58   

Fixed.

Please get the latest v5.5.4 hotfix from the website or the assemblies from nuget.

Frans Bouma | Lead developer LLBLGen Pro
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 30-May-2019 16:17:24   

Thanks for the quick reply! I've downloaded the latest version and now I'm getting this:

An exception was caught during the execution of an action query: ORA-06550: line 1, column 276: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

QueryExecuted Query: BEGIN INSERT INTO "ERINA_FRAMEWORK"."FRAMEWORK_USERS" ("DISPLAY_NAME", "EMAIL_ADDRESS", "ENCRYPTION_VERSION", "ID", "INTERNAL", "LICENSE_ID", "USER_NAME") VALUES (: p1, : p2, : p3, : p4, : p5, : p6, : p7) ;SELECT "ERINA_FRAMEWORK"."SEQ_INSERT_USER_ID".CURRVAL INTO : p4 FROM DUAL END Parameter: : p1 : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "110379". Parameter: : p2 : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "testtest1@grbinc.com". Parameter: : p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: InputOutput. Value: <undefined value>. Parameter: : p5 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: : p6 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1001. Parameter: : p7 : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "110379".

StackTrace at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQuery.cs:line 288 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteActionQuery(IActionQuery queryToExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 752 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.<PersistQueue>b__74_0(IActionQuery q) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1271 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecuteElements(List1 elementsToRun) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQueryController.cs:line 175 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.Execute(ActionQueueElement1 actionQueueElement, IActionQuery query, Type typeOfNextElement) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\ActionQueryController.cs:line 104 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PersistQueue(List1 queueToPersist, Boolean insertActions) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1319 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1235 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass19_0.<SaveEntity>b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 413 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy[T](Func1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 919 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 413 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1150 at Grb.Platform.Framework.Business.DomainManager.SetFrameworkUserEntity(FrameworkuserEntity frameworkUserEntity, IDataAccessAdapter externalAdapter) in C:\Source\Production\Platform\Framework\Business\DomainManager.vb:line 1862

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-May-2019 18:59:14   

Hmm... Apparently our tests don't trigger this situation. We'll look into it. Sorry for this... Will be tomorrow tho (friday)

It would help if you shared what code you execute and e.g. how many elements are in the set that you save and what the batch size is, so we can try to mimic that to find the situation where it breaks.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-May-2019 09:42:11   

If the batch contains inserts with a sequence and 1 element is in the final batch, this occurs. We'll work on a fix

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-May-2019 10:22:32   

We fixed the issue. it chopped off the ';' which went unnoticed as the bug we fixed yesterday covered it up, and fixing that revealed this particular scenario. Please download the hotfix again.

Frans Bouma | Lead developer LLBLGen Pro
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 03-Jun-2019 17:29:30   

Got the latest version and it works now - thanks so much! smile

ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 04-Jun-2019 21:43:18   

Well now I'm getting a rather odd issue - I'm pretty sure it's still related to batching though.

After I downloaded the latest hotfix, I got the error below. To make sure the error was related to batching, I set the BatchSize to 0 and ran our program again. Everything ran fine. I set the BatchSize back up to 50, but didn't get the error again. Assuming the error was a fluke, I sent it to one of our testers. He too got the error the first time through, didn't get the error second time through with BatchSize = 0, then didn't get the error third time with BatchSize reverted to its previous value.

Using ORM Profiler, I determined that batching is working correctly (after turning it off and back on again), but I'm at a loss as to what causes it to fail the first time.

Using a third machine we set it up to run our program the first time with BatchSize = 50, and the query that seems to be failing is this

BEGIN
    UPDATE "CRIS_FRAMEWORK_BATCH"."LOGIN"
    SET "FAIL_PWD_COUNT" = :p1,
           "PWD_FORMAT" = :p2,
           "PWD_SALT" = :p3,
           "PWD" = :p4
    WHERE  ("CRIS_FRAMEWORK_BATCH"."LOGIN"."FRAMEWORK_USERS_ID" = :p5);

    UPDATE "CRIS_ASSIST_BATCH"."USERS"
    SET "ADDED_TIMESTAMP" = :p6,
           "SSN_HASH" = :p7,
           "SSN" = :p8
    WHERE  ("CRIS_ASSIST_BATCH"."USERS"."FRAMEWORK_USERS_ID" = :p9);
END; 

This batch statement executes with no problem if run manually against our database.

During a save action an entity's update action failed. The entity which failed is enclosed.

Stack Trace: at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PerformPostEntitySaveActions(Boolean insertActions, ActionQueueElement1 element, EntityBase2 entityToSave, Boolean saveSucceeded) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.<PersistQueue>b__74_2(Boolean a, ActionQueueElement1 b, EntityBase2 c, Boolean d) at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.PerformPostPersistenceQueryExecuted(PackedActionQuery packedQuery, Int32 amountSaved) at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.PerformPostActionQueryWork(Int32 resultActionQuery, PackedActionQuery packedQuery) at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecuteElements(List1 elementsToRun) at SD.LLBLGen.Pro.ORMSupportClasses.ActionQueryController.ExecutePending() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.PersistQueue(List1 queueToPersist, Boolean insertActions) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass19_0.<SaveEntity>b__0() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy[T](Func`1 toExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse)

Any ideas?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Jun-2019 09:47:32   

You get this error when the number of updates executed by the DB doesn't match the number returned by the command. (so the where clause in 1 or more update statements didn't match any rows)

Say you have 2 entities, A and B. Both have changed fields and exist in the db so they're not new. The batch will contain 2 update statements and it will expect 2 as the # of rows affected back from the command.

However if during the time you fetched A and B, changed them and then execute the batch, another thread / user deleted entity B from the database, B's update query won't match a row and the # of rows affected in your batch will be 1, not 2. As the number returned is lower, you'll get this error. The transaction will be rolled back.

So this is a concurrency exception (you didn't include the exception name but I guess that's the one you got, looking at the message).

Could that be the case in your situation? As your test updates entities, when you re-run the tests, be sure the rows they work on are deleted prior to the tests, so you don't work with old data. Then insert the rows to update, then update the rows using your test.

Frans Bouma | Lead developer LLBLGen Pro
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 05-Jun-2019 23:07:32   

Correct me if I'm wrong, but SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity() checks if an entity was successfully updated in the database after the update query command is sent to the database right? And then if there was no change detected an exception is thrown?

Does the detection of a failed SaveEntity() call work any differently when batching is used as opposed to just individual commands?

If it helps, the specific call to SaveEntity() throwing the error passes in true for recurse.

Scenario A) When we have batching turned on (batch size 50), and there is only a single related record that needs to be updated in the database, the query runs successfully to completion. There are 2 different records we want updated, and we set only 1 record to an alternate value (so that it would require updating) during each test.

SQL code observed in the ORMProfiler for Scenario A:

UPDATE "CRIS_ASSIST_BATCH"."USERS"
SET "SSN_HASH" = :p1,
       "SSN" = :p2
WHERE  ("CRIS_ASSIST_BATCH"."USERS"."FRAMEWORK_USERS_ID" = :p3)
UPDATE "CRIS_FRAMEWORK_BATCH"."LOGIN"
SET "PWD" = :p1
WHERE  ("CRIS_FRAMEWORK_BATCH"."LOGIN"."FRAMEWORK_USERS_ID" = :p2) 

Scenario B) When we have batching turned on (batch size 50), and there are 2 related records that need to be updated in the database, the SaveEntity() call throws the exception described above.

Scenario C) Same as scenario B, but we ignore the thrown exception (in debugging) so that there is no rollback, we see that both of the 2 related records were successfully updated in the Oracle database despite the exception being thrown.

SQL code observed in the ORMProfiler for Scenarios B and C:

BEGIN
    UPDATE "CRIS_FRAMEWORK_BATCH"."LOGIN"
    SET "PWD" = :p1
    WHERE  ("CRIS_FRAMEWORK_BATCH"."LOGIN"."FRAMEWORK_USERS_ID" = :p2);

    UPDATE "CRIS_ASSIST_BATCH"."USERS"
    SET "SSN_HASH" = :p3,
           "SSN" = :p4
    WHERE  ("CRIS_ASSIST_BATCH"."USERS"."FRAMEWORK_USERS_ID" = :p5);
END; 

Although the exception thrown was an "SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException", we had multi-threading turned off during all of our tests. We have also tried setting up the test scenario from a new database each time before the tests.

Any further ideas? confused

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 10:28:13   

ErinaG wrote:

Correct me if I'm wrong, but SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SaveEntity() checks if an entity was successfully updated in the database after the update query command is sent to the database right? And then if there was no change detected an exception is thrown?

It checks the number returned by the DbCommand.ExecuteNonQuery() method, which is the number of rows affected, as reported by the database. So if a where clause in an update statement doesn't match with a row, 0 rows are updated, and the method will return 0, so our runtime then concludes that the update failed and it will report a concurrency exception.

Does the detection of a failed SaveEntity() call work any differently when batching is used as opposed to just individual commands?

No it should work the same: it counts the # of update statements batched in the batch, then checks the number returned, and if it isn't the same, an update statement must have failed.

If it helps, the specific call to SaveEntity() throwing the error passes in true for recurse.

Scenario A) When we have batching turned on (batch size 50), and there is only a single related record that needs to be updated in the database, the query runs successfully to completion. There are 2 different records we want updated, and we set only 1 record to an alternate value (so that it would require updating) during each test.

SQL code observed in the ORMProfiler for Scenario A:

UPDATE "CRIS_ASSIST_BATCH"."USERS"
SET "SSN_HASH" = :p1,
       "SSN" = :p2
WHERE  ("CRIS_ASSIST_BATCH"."USERS"."FRAMEWORK_USERS_ID" = :p3)
UPDATE "CRIS_FRAMEWORK_BATCH"."LOGIN"
SET "PWD" = :p1
WHERE  ("CRIS_FRAMEWORK_BATCH"."LOGIN"."FRAMEWORK_USERS_ID" = :p2) 

Scenario B) When we have batching turned on (batch size 50), and there are 2 related records that need to be updated in the database, the SaveEntity() call throws the exception described above.

Scenario C) Same as scenario B, but we ignore the thrown exception (in debugging) so that there is no rollback, we see that both of the 2 related records were successfully updated in the Oracle database despite the exception being thrown.

SQL code observed in the ORMProfiler for Scenarios B and C:

BEGIN
    UPDATE "CRIS_FRAMEWORK_BATCH"."LOGIN"
    SET "PWD" = :p1
    WHERE  ("CRIS_FRAMEWORK_BATCH"."LOGIN"."FRAMEWORK_USERS_ID" = :p2);

    UPDATE "CRIS_ASSIST_BATCH"."USERS"
    SET "SSN_HASH" = :p3,
           "SSN" = :p4
    WHERE  ("CRIS_ASSIST_BATCH"."USERS"."FRAMEWORK_USERS_ID" = :p5);
END; 

Although the exception thrown was an "SD.LLBLGen.Pro.ORMSupportClasses.ORMConcurrencyException", we had multi-threading turned off during all of our tests. We have also tried setting up the test scenario from a new database each time before the tests.

Any further ideas? confused

Hmm, ok. We will try to reproduce this.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 10:32:46   

Reproduced. very odd. As if the 'begin/end' wrapping causes a problem here. disappointed Looking into it.

Hmm. Batching is implemented in a generic way, and as DbCommand.ExecuteNonQuery is a standard .NET method, it should return the proper number of affected rows. Looking at the docs of OracleCommand.ExecuteNonQuery, it states that it returns -1 if the statement isn't an insert/update/delete statement. As we wrap the statements in begin/end, it looks like a different statement and it indeed doesn't work.

Our tests were insufficient in this area as we didn't test for this particular scenario as we assumed it would be ok, as we use standard API calls.

It poses a serious problem, as it doesn't allow for testing whether the updates all succeeded. We have to think about how to solve this problem, as when it doesn't count the number of rows affected it's unreliable.

For now, please switch off batching. We hope to have a solution real soon, and I'll get back to you when we do.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 14:45:08   

Well... it's not possible to return the # of rows affected by multiple update statements inside a begin/end block on oracle if you execute it outside pl/sql (which we do, we execute anonymous queries through ODP.NET). The main problem is that SQL%ROWCOUNT, the cursor variable that contains the # of rows affected after a DML statement exists in PL/SQL and works fine, but using inside a dynamic query like we do, doesn't work, it will throw an error (likely 'invalid number' or 'invalid character' etc.

We tried everything, from adding it to a parameter, to a local variable to checking it inside an IF and raise an error... these blocks work fine in SQL plus, but not in a dynamic query ran through ODP.NET.

E.g. this is the basic layout of what we're after:


DECLARE
    pTmp NUMBER(9, 0);
BEGIN
    pTmp := 0;

    UPDATE "SCOTT"."DEPT"
    SET "DNAME" = 'NewDepx' /* :p1 */
    WHERE  ("SCOTT"."DEPT"."DEPTNO" = 80 /* :p2 */);

    pTmp := pTmp + SQL%ROWCOUNT;

    UPDATE "SCOTT"."DEPT"
    SET "DNAME" = 'NewDep2x' /* :p3 */
    WHERE  ("SCOTT"."DEPT"."DEPTNO" = 81 /* :p4 */);

    pTmp := pTmp + SQL%ROWCOUNT;

    UPDATE "SCOTT"."DEPT"
    SET "DNAME" = 'NewDep3x' /* :p5 */
    WHERE  ("SCOTT"."DEPT"."DEPTNO" = 82 /* :p6 */);

    pTmp := pTmp + SQL%ROWCOUNT;

    SELECT pTmp
    INTO   :pLLBLROWCOUNT
    FROM   DUAL;
END;

Also adding SQL%ROWCOUNT directly to : pLLBLROWCOUNT instead of the tmp variable won't work, same issues. (see: https://stackoverflow.com/questions/7091858/get-the-affected-row-from-pl-sql for the same problem, but with merge).

So this gives a problem: fake the # of returned rows to be always equal to the # of queries executed, or disable batching for updates on oracle. If we fake the number of rows affected, it might cause problems if a row was deleted and the caller will assume the update succeeded. If we disable batching it would hurt performance for the people who don't care for that limitation.

We'll think about this some more and get back to you.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 16:06:55   

We've decided to switch off batching for updates for Oracle. We simply can't silently let a batch succeed while it actually failed.

We'll release a hotfix to reflect this change and also update the documentation with a remark that batch updates for oracle aren't supported. Inserts are still batched and work fine.

We've scheduled a workitem for v5.6 which should give more flexibility over this, i.e. it would allow you to batch the update queries on oracle, and if one or more fail because the rows aren't there anymore it's ok for you. For now we can't assume that's always ok, so we'll add a setting in v5.6.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 17:08:12   

We've uploaded another hotfix for 5.5.4 which disables batching for updates on oracle. We're sorry for this, but we don't see another way to implement this at this point. The update queries will be executed individually, so per query 1 roundtrip, like it has been done before.

We'll continue looking for a solution for this, perhaps there's another way to obtain the # of rows reliably and if we run into such a solution we'll implement it and switch it back on.

Frans Bouma | Lead developer LLBLGen Pro
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 06-Jun-2019 17:53:52   

Thanks for looking into such a frustrating error. Wish we could just drop Oracle support, but I guess we'll just have to deal with it for now. confused

Thanks again!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jun-2019 19:08:09   

There might be a way, will try some things tomorrow simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 07-Jun-2019 10:04:24   

Well, good news, sort of, we can do the updates we want inside a console app so we now have to translate it back to the runtime so it's used there as well.

(edit) we first thought that it was a problem with 32bit/64bit and old odp.net installs being picked up but it turned out we ran into 2 bugs inside odp.net wrt types set on a parameter. I.e. setting the value to 0 (int32) and not any type on the parameter worked fine (and the type on the parameter was set to int32 by odpnet), setting the type to int32 and then the value failed later on as the value 0 was seen as a decimal. The other bug was we used an input/output parameter and sending the value 0 had no effect, the initial value of the parameter was NULL. We had to explicitly set the parameter to 0 in the query.

Using a decimal typed parameter fixed the type issue.

We'll try to get the fix out today so you can enjoy updates in batches as well on Oracle simple_smile

(edit) We've fixed it and it works fine now simple_smile We'll upload a new hotfix with the proper batching for updates on oracle shortly.

Frans Bouma | Lead developer LLBLGen Pro
ErinaG
User
Posts: 22
Joined: 31-Dec-2014
# Posted on: 07-Jun-2019 15:05:21   

You guys are amazing! Thanks so much!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 07-Jun-2019 15:40:13   

It's now available simple_smile Hopefully this eases the pain of working with Oracle a bit wink

Be sure to use the latest ormsupportclasses and the latest SD.LLBLGen.Pro.DQE.OracleODPNet dll as this fix has changes in both.

Frans Bouma | Lead developer LLBLGen Pro