Query Batching - SQL command not properly ended
Joined: 31-Dec-2014
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. 
It works if the remaining batch with 1 query is also wrapped in a BEGIN / END. 
... 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.
Joined: 31-Dec-2014
Joined: 31-Dec-2014
Got the latest version and it works now - thanks so much!
Joined: 31-Dec-2014
Joined: 31-Dec-2014
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?
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?
Hmm, ok. We will try to reproduce this.
Reproduced. very odd. As if the 'begin/end' wrapping causes a problem here. 
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.
Joined: 31-Dec-2014
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.
Thanks again!
There might be a way, will try some things tomorrow 
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
(edit) We've fixed it and it works fine now We'll upload a new hotfix with the proper batching for updates on oracle shortly.
Joined: 31-Dec-2014
It's now available Hopefully this eases the pain of working with Oracle a bit 
Be sure to use the latest ormsupportclasses and the latest SD.LLBLGen.Pro.DQE.OracleODPNet dll as this fix has changes in both.