string length for VARCHAR2(4000)

string length for VARCHAR2(4000)

Postby romanFleischer » Wed 23 May 2012 10:49

Hello,

now after we installed the verson 6.80.335 of dotConnect, we can not insert strings with correct length in our database.

When we try to insert an string with 2888 chars into a column defined as VARCHAR2(4000) - we get an error. LONG value..

It is only possible to insert strings of length smaler than 2001 characters. We changed realy nothing in our environment. We updated only the dotConnect version from 5.70 to 6.80. With the old version of dotConnect we do not have any problem. We use the direct mode to connect.

Code: Select all
<add name="ApplDataModel" connectionString="User Id=xxxx;Password=yyyy;Server=test.anywhere.com;Port=1521;Direct=True;Sid=Appl;Persist Security Info=True" providerName="Devart.Data.Oracle" />

<add name="dotConnect for Oracle" invariant="Devart.Data.Oracle" description="Devart dotConnect for Oracle" type="Devart.Data.Oracle.OracleProviderFactory, Devart.Data.Oracle, Version=6.80.325.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx"/>


What do we have to change to make this version of dotConnect working?

Thanks Roman
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby Pinturiccio » Fri 25 May 2012 11:20

romanFleischer wrote:now after we installed the verson 6.80.335 of dotConnect

There is no public version 6.80.335 of dotConnect for Oracle. There are versions 6.80.325 and 6.80.332. Please specify with which one the issue occurs.

We could not reproduce the issue. We can insert a string with any value greater than 2000 and less than 4000 in a column with type equal to varchar2(4000 BYTE) or varchar(4000 CHAR).
Could you please send us a small test project with DDL/DML scripts for reproducing the issue.
Pinturiccio
Devart Team
 
Posts: 1893
Joined: Wed 02 Nov 2011 09:44

Re: string length for VARCHAR2(4000)

Postby romanFleischer » Wed 30 May 2012 11:38

Sorry i typed a mismatch - installed is of course 6.80.325.0 as displayed in our configuration file.

Of course we can give you a small example how we test this stange effect. We use .NET Entity Framework 4.0 and Oracle 11g.

When inserting more than 2000 characters into a VARCHAR2 (4000 CHAR) db column, we get an Oracle Error.
Devart.Data.Oracle.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column


If we try to insert 2000 characters into a VARCHAR2 (2000 CHAR), it works! (We use the same table and the same enitity for our test)

Here is the definition of our table:
Code: Select all
CREATE TABLE MODUL (
  MODUL_ID NUMBER(5),
  ZERTIFIKATSTEXT VARCHAR2(4000 CHAR),
  TEXT VARCHAR2(2000 CHAR)
)


the entity class:
Code: Select all
public partial class Module
{
   //Id-Fields
   public int Id { get; set;}
   // Attributes
   public String CertificateText { get; set;}
   public String Text { get; set;}
}


the entity mapping:
Code: Select all
private void MapModule(ModelBuilder modelBuilder)
{
   // table-assignment
   modelBuilder.Entity<Domain.Module>().MapSingleType(entity => new
   {
      MODUL_ID = entity.Id
      ,TEXT = entity.Text
      ,ZERTIFIKATSTEXT = entity.CertificateText
   }).ToTable("MODUL");
   // Id
   modelBuilder.Entity<Domain.Module>().Property(entity => entity.Id).StoreGeneratedPattern = StoreGeneratedPattern.Identity;
   modelBuilder.Entity<Domain.Module>().HasKey(entity => entity.Id);
   // Properties
    modelBuilder.Entity<ebis.Domain.Sibis.Module>().Property(entity => entity.CertificateText).IsNotUnicode();
}


And then we only call the SaveChanges() method of the Entity.Context
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby romanFleischer » Wed 30 May 2012 11:48

We found a workaround for our problem. If we insert folowing line into our entity mapping:
Code: Select all
...
modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

we can insert 4000 charcters into the VARCHAR2(4000 CHAR) column.

But i hope this is not the only solution, because we have more than 2000 string columns in our solution and it will take some time to insert this line for every column. Also the mapping is generated automaticaly from an UML model and can not be modified every time.

This is the complete exception code of our solution and devart together. Mabe this will help analyze the problem.
Code: Select all
---> Devart.Data.Oracle.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column     bei Devart.Data.Oracle.t.d()     bei Devart.Data.Oracle.aq.h()     bei Devart.Data.Oracle.aq.c()     bei Devart.Data.Oracle.aa.a(Int32 A_0, bt A_1)     bei Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)     bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)     bei Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()     bei Devart.Data.Oracle.Entity.e.j()     bei System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)     bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)     --- Ende der internen Ausnahmestapelüberwachung ---     bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)     bei System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)     bei System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)     bei System.Data.Entity.DbContext.SaveChanges()     bei Sirius.EntityFramework.DbContextManager.SiriusPocoBaseDao`2.SaveAllObjectChanges()     bei ebis.Domain.Sibis.ModuleDao.Change(Module entity) in d:\Projekte-Zusatz\ebis2\Deployment\tfsCheckout\ebis.Domain.Sibis\target\ModuleDao.g.cs:Zeile 104.     bei ebis.PlanningAdministration.Core.TrainingCourseService.HandleSaveModule(ModuleVO
...
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby Shalex » Thu 31 May 2012 11:33

romanFleischer wrote:When we try to insert an string with 2888 chars into a column defined as VARCHAR2(4000) - we get an error. LONG value..
It is only possible to insert strings of length smaler than 2001 characters.

It seems like the character set for VARCHAR2 at your server is multibyte (two bytes per character in your case). Character set is determined by server's NLS_CHARACTERSET setting, the current value can be found out with the following query:
Code: Select all
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby romanFleischer » Thu 31 May 2012 12:10

I get the result AL32UTF8
I wonder why it ist possible to insert 1999 character into a VARCHAR2(2000 CHAR) column but not possible to insert 2888 character into a VARCHAR2(4000 CHAR) colum?

Do the columns use different character sets dependent on the specified length, or does oracle handle VARCHARS in different ways?
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby Shalex » Fri 01 Jun 2012 16:00

Please refer to https://forums.oracle.com/forums/thread.jspa?messageID=10272011: "The maximum size limit for a VARCHAR2 column is 4000 bytes regardless of CHAR semantics".
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby romanFleischer » Mon 04 Jun 2012 08:33

Sounds good, but the reason for our problem can not be the Oracle database.
Why not? If we enter the following code into our entity mapping - everything works well without any change on the database.
Code: Select all
modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

It is also posssible to enter 4000 characters by using a simple insert into, oder update SQL-Statement.

Newly we have also to check the "use unicode" checkbox inside our VisualStudio Database Explorer. This was also not necessary with our former used version 5.70. The databse has not been changed for the last 4 month.
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby Shalex » Tue 05 Jun 2012 15:49

romanFleischer wrote:Why not? If we enter the following code into our entity mapping - everything works well without any change on the database.
Code: Select all
modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

The reason is that Entity Framework considers the string property as a unicode column by default now.
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby romanFleischer » Tue 05 Jun 2012 16:02

This is a helpfull information, but we still use the .NET 4.0 Framework (and the same version of Entity Framework). Nothing has changed since our update of the dotConnect. Is the new dotConnect version no longer compatible with older Entity Framework?
romanFleischer
 
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Postby Shalex » Fri 08 Jun 2012 16:52

romanFleischer wrote:Nothing has changed since our update of the dotConnect. Is the new dotConnect version no longer compatible with older Entity Framework?

There was the bug in the old versions of dotConnect for Oracle when provider treated unicode columns as non-unicode.
We are investigating the possibility to implement a workaround for changing string mappings in the Code-First models which are not configured completely (your case).
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby skoub » Wed 13 Jun 2012 18:30

I also have this problem. Everything was fine with v6.50.237 and today we have upgraded to v7.0.17 and now i get the error "ORA-12704: character set mismatch".

We are working with CodeFirst! What have changed since the v6.50.237??
skoub
 
Posts: 32
Joined: Fri 21 May 2010 15:47

Re: string length for VARCHAR2(4000)

Postby Shalex » Mon 18 Jun 2012 11:59

skoub wrote:What have changed since the v6.50.237??

As we answered in the previous post, we are investigating the possibility to implement a workaround for changing string mappings in the Code-First models which are not configured completely to treat unicode columns as non-unicode (the incorrect behaviour of the previous versions). We will post here about the results.
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby Shalex » Fri 22 Jun 2012 15:57

romanFleischer and skoub, we have sent the internal build of dotConnect for Oracle to you with the implemented config.CodeFirstOptions.UseNonUnicodeStrings option. Please test it in your environment and notify us about the results.
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Postby Shalex » Mon 02 Jul 2012 07:03

The config.CodeFirstOptions.UseNonUnicodeStrings configuration option (default value is False) is added in order to treat string properties without an explicitly specified server data type, as non-unicode.

We will post here when the corresponding public build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7475
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for Oracle