Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Batch insert failed on ORACLE
 

Pages: 1
Bugs & Issues
Batch insert failed on ORACLE
Page:1/1 

  Print all messages in this thread  
Poster Message
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# Posted on: 05-Feb-2020 19:29:11.  
Hi,

for information, I remark today that, on ORACLE, batch insert failed if the primary key (generated with a sequence) isn't the first one when using Set Field Order menu in the designer of LLBLGEN.

I use
* .NET Framework : .NET Core 3.1
* LLBLGEN version : 5.6 (5.6.0) RTM
* Database version : ORACLE 11g
* Database driver : Oracle.ManagedDataAccess.Core 2.19.60

I'm not sure if it is a bug of LLBLGEN (perhaps a bug in the database driver) because the query in the output panel seems good.

Code:

Generated Sql query:
    Query: BEGIN SELECT "NEPTUNE_AC"."MESURE_SEQ".NEXTVAL INTO :p398 FROM DUAL; INSERT INTO "NEPTUNE_AC"."MESURE" ("DATE_MESURE", "ID", "ID_TYPE_MESURE_EQUIPEMENT", "VALEUR") VALUES (:p397, :p398, :p399, :p400) ;END;
    Parameter: :p397 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 11/05/2019 17:42:00.
    Parameter: :p398 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: InputOutput. Value: <undefined value>.
    Parameter: :p399 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 4.
    Parameter: :p400 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 335,0.


but the error is really thrown

Code:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: ORA-06550: line 1, column 16:
PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
ORA-06550: line 1, column 8:


As a workaround,I just change the field order to let the primary column as the first one and all is OK now.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14585 posts
# Posted on: 06-Feb-2020 01:04:07.  
Thanks for the feedback, we'll cross check that.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37878 posts
# Posted on: 06-Feb-2020 15:28:03.  
I think I found it.

For oracle to use named parameters the 'BindByName' property on the command has to be set to true. By default it's false. Your problem stems from the fact named parameters are silently not used by Oracle.

Setting the BindByName property is normally done without a problem in the DQE, however for a batched query, we create a new command and pack all commands in there. We do that by using connection.CreateCommand(). This bypasses the BindByName setter as that's in the DQE.

We have to refactor some code so the command is created using the DQE so we get the properties set and named parameters work properly. This normally goes OK if the sequenced field is indeed the first one which is the case in our tests hence we didn't see it. We hope to have a fix later today or tomorrow.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37878 posts
# Posted on: 07-Feb-2020 10:36:44.  
This has been fixed and will be released later today in the 5.6.2 hotfix

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# Posted on: 07-Feb-2020 12:08:25.  
Great to hear it.

Perhaps, it is already take into account with your correction but I prefer to inform you that the same error arrives (even if I put the column which is the primary key at first position) when I set the BatchSize of the adapter to a value which is important

Code:
                
using (var adapter = new DataAccessAdapter())
{
        adapter.BatchSize = 100;
        unitOfWork.Commit(adapter, true);
}

=> OK

Code:
                
using (var adapter = new DataAccessAdapter())
{
        adapter.BatchSize = 400;
        unitOfWork.Commit(adapter, true);
}

=> KO
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37878 posts
# Posted on: 07-Feb-2020 12:16:48. Goto attachments  
You get the same error with the larger batch? I can't imagine it's something else than this.

In any case, I've attached a release build of the .netstandard ormsupportclasses, for you to test if that fixes your problem.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37878 posts
# Posted on: 07-Feb-2020 15:37:42.  
Hotfix for this problem is now available (see My Account -> 5.5 / 5.6-> Hotfix to download the latest installer, also on Nuget (you have to select prerelease in the nuget explorer to see it).

That might be easier for you to test it. If it's not fixed we'll look at it again and we need more info as your batch doesn't fail in all cases...
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# Posted on: 12-Feb-2020 10:13:14.  
The hotfix fixed the issue.

Thanks


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.