CLOB cannot cope with Unicode strings

CLOB cannot cope with Unicode strings

Postby awaitzbe » Sun 18 Dec 2011 12:04

To reproduce the problem:
Code: Select all
DEFINE DATA_SPACE                     = "TEST_DATA_SPACE";
DEFINE INDEX_SPACE                    = "TEST_INDEX_SPACE";

CREATE TABLE clob_test ( clob_id NUMBER NOT NULL, C_DATA CLOB )
TABLESPACE   &DATA_SPACE;

CREATE UNIQUE INDEX pk#150_clob_test ON clob_test ( clob_id )
TABLESPACE         &index_space;

ALTER TABLE clob_test ADD CONSTRAINT pk#150_clob_test PRIMARY KEY ( clob_id ) USING INDEX;

CREATE SEQUENCE clob_tst_seq
START WITH 1
INCREMENT BY 1
nomaxvalue;

CREATE TRIGGER clob_tst_trg           
BEFORE INSERT ON clob_test           
FOR EACH ROW
BEGIN
SELECT clob_tst_seq.NEXTVAL           INTO :NEW.clob_id          FROM dual;
END;
/


Generate the Model using Devart Entity Model and change the C_DATA to Unicode:
Code: Select all
       
         
           
         

         
         
       


*IMPORTANT* Note: Connect using Direct Mode

Then run the following function:
Code: Select all
        private void InsertCLOBToDB(string word)
        {
            if (WordExists(word))
            {
                PrintDebug(String.Format("Word {0} already exists for.", word));
                return;
            }

            PrintDebug(String.Format("Insert word {0}", word));
            CLOB_TEST c = new CLOB_TEST();
            c.CLOB_ID = counter;
            c.C_DATA = word;
            _me.AddToCLOB_TESTs(c);
            _me.AcceptAllChanges();
            counter++;
        }

        private bool WordExists(string word)
        {
            CLOB_TEST d = (from i in _me.CLOB_TESTs
                           where (((i.C_DATA == null) && (word == null)) || (i.C_DATA.ToUpper().StartsWith(word.ToUpper())))
                            select i).FirstOrDefault();
                                 
            return (d != null);
        }

        internal void Run_Main()
        {
            string[] words = { @"Some text with unicode characters to be inserted and checked in the table",
                               @"cds",
                             @"A Federação é uma organização cultura física, intelectual, moral e cívica.",
                             @"O Tribunal de Justiça Desportiva (TJD) é o órgão judicante do futebol do Estado de São Paulo. Presidido pelo Dr. Ronaldo Botelho Piacente é constituído por nove auditores, aos quais cabe a responsabilidade de apreciar os atos decorrentes dos recursos interpostos das decisões das Comissões Disciplinares. O TJD também analisa outros processos previstos no Código Brasileiro Disciplinar de Justiça.",
                             @"Le mouvement de grève des agents de sûreté aéroportuaire, qui provoque depuis vendredi des annulations de vols à Lyon et des retards à Roissy et Toulouse, devrait se poursuivre au deuxième jour des vacances de Noël. ",
                             @"La chanteuse capverdienne est morte à 70 ans dans son île natale de Sao Vicente, près de trois mois après avoir décidé d’abandonner la scène. ",
                             @"Tombées en disgrâce, l’ex-ministre et l’ex-secrétaire d’Etat donnent du fil à retordre à la droite, en affirmant haut et fort leurs ambitions pour les législatives. ",
                             @"Mais les choses sont beaucoup plus compliquées. Imaginons un dessinateur satirique qui s'amuserait à peindre un citoyen se torchant le derrière avec la Déclaration des droits de l'homme; à peindre des étrangers arrivant sur notre sol en crachant sur nous par principe; à peindre un couple homosexuel livré à la débauche et réclamant ouvertement d'adopter des enfants pour les sodomiser; ou encore, à peindre le camp d'Auschwitz sous des aspects burlesques et ridicules auxquels je n'ose pas penser. Croit-on que tout cela pourrait s'exposer tranquillement au nom de la liberté de la presse? Certainement pas. Tout aussitôt, l'ensemble des médias feraient taire l'importun, comme cela est arrivé il y a quelque vingt ans pour Fun Radio, qui, dans une outre-mesure adolescente, avait osé ricaner sur la Shoah. ",
                             @"Na manhã deste domingo (18), o último comboio com tropas dos EUA deixou o Iraque. Ao todo são mais de 110 veículos blindados e cerca de 500 soldados que atravessaram a fronteira com o Kuwait por volta das 7h30 no horário local (2h30 no de Brasília).

As tropas deixam o país quase nove anos após o invadir para depor o ditador Sadam Husein, foi capturado em dezembro de 2003 e executado na forca três anos depois. Permanecem em Bagdá 157 soldados americanos que ficaram com a missão de treinar as forças iraquianas e proteger a Embaixada dos EUA na capital do Iraque.

Durante os últimos dias, o Exército americano entregou os últimos prisioneiros que tinha em suas mãos às autoridades iraquianas, que na sexta-feira passada assumiram o controle da última base militar que permanecia em poder dos EUA.

No último dia 15, se encenou a retirada americana de maneira simbólica com o recolhimento da bandeira em cerimônia em Bagdá, da qual participou o secretário de Defesa deste país, Leon Panetta. Com o pacto de segurança assinado entre Washington e Bagdá há três anos, ambos os países acertaram a retirada para o final deste ano."
                             
                             };
            foreach (string word in words)
            {
                InsertCLOBToDB(word);
            }
           
        }


You get the following exception:

Code: Select all
System.Data.EntityCommandExecutionException was unhandled
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
       at TestCLOB.CLOBTester.WordExists(String word) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 118
       at TestCLOB.CLOBTester.InsertCLOBToDB(String word) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 101
       at TestCLOB.CLOBTester.Run() in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\CLOBTester.cs:line 146
       at TestCLOB.Program.Main(String[] args) in c:\users\awaitzbe\documents\visual studio 2010\Projects\TestCLOB\TestCLOB\Program.cs:line 13
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Devart.Data.Oracle.OracleException
       Message=ORA-01460: unimplemented or unreasonable conversion requested
       Source=Devart.Data.Oracle
       ErrorCode=-2147467259
       Code=1460
       Offset=0
       StackTrace:
            at Devart.Data.Oracle.ap.d()
            at Devart.Data.Oracle.x.m()
            at Devart.Data.Oracle.x.c()
            at Devart.Data.Oracle.u.a(Int32 A_0, bf A_1)
            at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
            at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
            at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at Devart.Data.Oracle.Entity.f.a(CommandBehavior A_0)
            at Devart.Common.Entity.o.b(CommandBehavior A_0)
            at Devart.Data.Oracle.Entity.f.b(CommandBehavior A_0)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException:


Server info:
Code: Select all
Oracle 11.1.0.7.0


Thanks.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby awaitzbe » Tue 27 Dec 2011 09:40

Is there any update on that?
Am I making some mistake here?
Thanks.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Tue 27 Dec 2011 16:43

Sorry for the delay. We are investigating your code.
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 28 Dec 2011 17:20

We cannot reproduce the problem in our environment.
Some tips:
1. Make sure that your Oracle server is configured correctly to insert Unicode strings into CLOB.
2. Try switching to the OCI mode (via Oracle client). Does it help?
3. You do not call SaveChanges() in your code but AcceptAllChanges() (definitely, this is not the reason of the exception).
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Postby awaitzbe » Thu 29 Dec 2011 09:33

1. My server configuration:
Code: Select all
SQL> select * from nls_database_parameters;
NLS_LANGUAGE           = AMERICAN
NLS_TERRITORY          = AMERICA
NLS_ISO_CURRENCY       = AMERICA
NLS_CHARACTERSET       = UTF8
NLS_NCHAR_CHARACTERSET = UTF8
NLS_SORT               = BINARY
NLS_COMP               = BINARY
NLS_LENGTH_SEMANTICS   = BYTE
NLS_NCHAR_CONV_EXCP    = FALSE
NLS_RDBMS_VERSION      = 11.1.0.7.0


So, assuming my server is configured for UTF8, can't it store Unicode strings into CLOB??

From Oracle documentation:
Data in CLOB columns is stored in a format that is compatible with UCS-2 when the database character set is multibyte, such as UTF8 or AL32UTF8.
Data conversion is necessary for insertion


2. I cannot use OCI because my users don't have Oracle client installed in their computer, so my application must use Direct Mode.
3. The exception comes before that line. This code was written to reproduce the problem, I don't use it as is.

Thank you.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby awaitzbe » Thu 29 Dec 2011 14:42

When researching the problem using dbMonitor, the C#
Code: Select all
var d = (from i in _me.CLOB_TESTs where i.C_DATA.ToUpper().StartsWith(word.ToUpper()) select i);
return d.Count() > 0;

gives the following SQL statement:
Code: Select all
SELECT
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
   FROM SEC.CLOB_TEST "Extent1"
   WHERE (INSTR(UPPER("Extent1".C_DATA), UPPER(:p__linq__0))) = 1
)  "GroupBy1"
with parameter p__linq__0 = "çã"
Gives ORA-01460: unimplemented or unreasonable conversion requested
But when I use Oracle SQL Developer and directly type:
Code: Select all
SELECT
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
   FROM SEC.CLOB_TEST "Extent1"
   WHERE (INSTR(UPPER("Extent1".C_DATA), UPPER('çã'))) = 1
)  "GroupBy1"

it does not give any error.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby awaitzbe » Thu 05 Jan 2012 09:04

Please help :?
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby StanislavK » Thu 05 Jan 2012 17:28

Sorry for the delay. Could you please try executing this command via plain ADO.NET? Please tell us if the problem can be reproduced in this case. You can use, e.g., the following code for this:
Code: Select all
OracleConnection conn = new OracleConnection(connectionString);
conn.Open();

OracleCommand cmd = new OracleCommand(
  @"SELECT ""GroupBy1"".A1 AS C1
    FROM ( SELECT Count(1) AS A1
    FROM UTF_CLOB_TEST ""Extent1""
      WHERE (INSTR(UPPER(""Extent1"".C_DATA), UPPER(:p__linq__0))) = 1
    )  ""GroupBy1""",
  conn);
cmd.Parameters.Add("p__linq__0", OracleDbType.VarChar, "çã", ParameterDirection.Input);

var reader = cmd.ExecuteReader();
if (reader.Read())
  Console.WriteLine(reader[0]);


Also, please check via DbMonitor the data type of the p__linq__0 parameter; please inform us if it is VarChar in your environment.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby awaitzbe » Mon 09 Jan 2012 08:47

Hi,
1. Yes, I get the same error "ORA-01460: unimplemented or unreasonable conversion requested" using the ADO .NET (using the code you provided). I get the exception on
Code: Select all
OracleDataReader reader = cmd.ExecuteReader();

2. Yes, the parameter is a VarChar (also verified using DbMonitor).
3. Perhaps my connectionString is faulty?
I am using the following:
Code: Select all
private string ADOConnectionStringBuilder()
{
    OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
    oraCSB.Direct = true;
    oraCSB.Port = 1529;
    oraCSB.Sid = "OPSECDB";
    oraCSB.UserId = username;    // const
    oraCSB.Password = password; // const
    oraCSB.Unicode = true;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Server = "localhost";
    oraCSB.ConnectionTimeout = 0;
    oraCSB.DefaultCommandTimeout = 0;
    return oraCSB.ConnectionString;
}


4. I also tried using OCI mode. I installed Oracle Client on the machine and used the following connection string:
Code: Select all
private string ADOConnectionStringBuilder()
{
    System.Data.SqlClient.SqlConnectionStringBuilder oraCSB = new System.Data.SqlClient.SqlConnectionStringBuilder();
    oraCSB.UserID = username;
    oraCSB.Password = password;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Add("Server", "(DESCRIPTION =(ADDRESS= (PROTOCOL = TCP)(HOST=localhost)(PORT = 1529))(CONNECT_DATA=(SID = OPSECDB)))");
    return oraCSB.ConnectionString;
}


Using OCI the tests passed successfully. But as I stated before, I cannot use OCI, I need Direct Mode.
If you need more tests, please let me know.

Thanks for your help :)
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Thu 12 Jan 2012 12:59

We cannot reproduce the problem with the latest version. Please try the mentioned ADO.NET code with dotConnect for Oracle v 6.60.283 and notify us about the results.
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Postby awaitzbe » Thu 12 Jan 2012 19:32

Thank you for your efforts to try to solve this, but the same problem occurs when using the latest 6.60.283.
Just trying to dig it more, I tried to replace the StartsWith function by Equals, giving
Code: Select all
SELECT
"GroupBy1".A1 AS C1
FROM ( SELECT Count(1) AS A1
   FROM SEC.CLOB_TEST "Extent1"
   WHERE (UPPER("Extent1".C_DATA)) = (UPPER(:p__linq__0))
)  "GroupBy1"

Gave:
ORA-00932: inconsistent datatypes: expected - got CLOB
When I replaced the WHERE clause by (as I think it should be):
Code: Select all
WHERE dbms_lob.compare(UPPER(""Extent1"".C_DATA), UPPER(:p__linq__0)) = 0

Gave:
ORA-01460: unimplemented or unreasonable conversion requested
In both cases the parameter p__linq__0 was of type VarChar

So I started to think the difference between our test environment is the generated code (Entity Model - DataModel -> EntityObject Template) from Devart Entity Developer from the database? Perhaps the generated code does not differentiate between a CLOB and a VARCHAR?

If you want, I can send you the files I use for testing.
Many thanks!
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Mon 16 Jan 2012 15:59

Could you please send us a small complete test project with the corresponding DDL/DML script?
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Postby awaitzbe » Tue 17 Jan 2012 10:24

Done.
The SQL statements used to create the table are in the Test_CLOB.sql file.
awaitzbe
 
Posts: 16
Joined: Thu 05 Nov 2009 14:41

Postby Shalex » Fri 27 Jan 2012 16:57

We have reproduced the ORA-01460 problem. We will investigate it and notify you about the results.
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 08 Feb 2012 14:15

The bug with CLOB data type in Direct mode, when NLS_CHARACTERSET of a server is set to AL24UTF8, is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7539
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for Oracle