Problem inserting LONG with Unicode = true (parameterized)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
jp.gouigoux
Posts: 20
Joined: Tue 09 Jun 2009 10:23

Problem inserting LONG with Unicode = true (parameterized)

Post by jp.gouigoux » Fri 08 Jan 2010 09:28

I'm trying to store a long string (more than 4000 chars) into a LONG type field in an oracle table.

I precise you that we store special chars with accents. (é, ç, etc...), so we need set the unicode property value to True on the object connection.

When I insert a string of 2 chars on the LONG field, I retrieve a string of 4 chars.

For example, the insertion of "AA" gives on return "A\0A\0"

If the unicode property is set to False, the insertion works fine.

With special chars, the length of really inserted informations are not necessarely a multiple of 2...

The problem is on Linux and Windows environnements.

Thanks in advance for your help

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 08 Jan 2010 14:41

Is the issue reproduced using some standard utility, like SQL Plus? If yes, the problem should be in the Oracle database or client settings. Please check that you store Unicode characters in the variable-width format in your database.

mg.ddev.mve
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Post by mg.ddev.mve » Tue 12 Jan 2010 16:23

Hi,

I tried to do the same queries using System.Data.OracleClient.OracleConnection instead of Devart.Data.Oracle.OracleConnection. It works fine with System.Data.OracleClient.OracleConnection.

Result with Devart.Data.Oracle.OracleConnection
Image

Result with System.Data.OracleClient.OracleConnection
Image

Source code with Devart.Data.Oracle.OracleConnection :

___________________________________________________________
using System;
using System.Collections.Generic;
using System.Text;

using Devart.Common;
using Devart.Data;
using Devart.Data.Oracle;
using System.Data;

namespace mgdis.administration
{
class Program
{
private static OracleConnection _ConnectionTypee = null;
private static int _myCounter = 0;
private static void CreateConnection()
{
_ConnectionTypee = new Devart.Data.Oracle.OracleConnection();
_ConnectionTypee.Server = "172.16.1.33";
_ConnectionTypee.Sid = "PROGOSF";
_ConnectionTypee.Port = 1521;
_ConnectionTypee.UserId = "TEST";
_ConnectionTypee.Password = "TEST";
_ConnectionTypee.Direct = true;
_ConnectionTypee.ConnectionString += "Pooling=false;";

// We set true to conserve french chars
_ConnectionTypee.Unicode = true;
}

private static string GetUniqueValueString(OracleCommand oCom, CommandBehavior tmpB)
{
IDataReader dr = null;

try
{
dr = oCom.ExecuteReader(tmpB);
string ret = string.Empty;

while (dr.Read())
ret = dr.GetString(0);

dr.Close();

return ret;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed) dr.Close();
throw ex;
}
}

static void Main(string[] args)
{
//OracleTransaction trs = null;
string currentCommand = string.Empty;

// Creation of the connection
CreateConnection();

try
{
_ConnectionTypee.Open();
_myCounter = 0;

// Drop the table before
currentCommand = "DROP TABLE TESTINSERTLNG";
_ConnectionTypee.Unicode = true;
OracleCommand oCom = new OracleCommand(currentCommand, _ConnectionTypee);
oCom.CommandType = CommandType.Text;
oCom.ExecuteNonQuery();

// Creation of the table with a LONG column
currentCommand = "CREATE TABLE TESTINSERTLNG (";
currentCommand += "MYKEY NUMBER(5) DEFAULT 0 NOT NULL";
currentCommand += ",PREFERENCE LONG NOT NULL)";
_ConnectionTypee.Unicode = true;
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
oCom.CommandType = CommandType.Text;
oCom.ExecuteNonQuery();

// Insertion of 2 chars without unicode activated
string insertString = "".PadLeft(2, 'a');
Console.WriteLine("Insertion of {0} chars without special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);

Console.WriteLine("");
// Insertion of 2 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);

// Insertion of 2 chars without unicode activated
insertString = "".PadLeft(2, 'é');
Console.WriteLine("Insertion of {0} chars with only special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);

Console.WriteLine("");
// Insertion of 2 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);

// Insertion of 3 chars without unicode activated
insertString = "aéa";
Console.WriteLine("Insertion of {0} chars with special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);

Console.WriteLine("");
// Insertion of 3 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);

Console.WriteLine("");
Console.WriteLine("Press any key to stop");
Console.ReadLine();
}
finally
{
if (_ConnectionTypee.State == ConnectionState.Open)
_ConnectionTypee.Rollback();
if (_ConnectionTypee != null)
if (_ConnectionTypee.State != ConnectionState.Closed) { _ConnectionTypee.Close(); }
}

}

private static void InsertAndReadValue(ref string currentCommand, ref OracleCommand oCom, string insertString, bool withUnicodeTrue)
{
_myCounter++;

_ConnectionTypee.Unicode = withUnicodeTrue;
_ConnectionTypee.Open();
// Insertion
currentCommand = "INSERT INTO TESTINSERTLNG (MYKEY, PREFERENCE) VALUES (:mykey, :preference)";
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
OracleParameter paramKey = new OracleParameter("mykey", _myCounter);
paramKey.OracleDbType = OracleDbType.Integer;
oCom.Parameters.Add(paramKey);
OracleParameter paramPref = new OracleParameter("preference", insertString);
paramPref.OracleDbType = OracleDbType.Long;
paramPref.Size = insertString.Length;
oCom.Parameters.Add(paramPref);
oCom.ExecuteNonQuery();

// Get de last inserted value
currentCommand = "SELECT preference FROM TESTINSERTLNG WHERE mykey=" + _myCounter.ToString();
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
CommandBehavior comBehav = CommandBehavior.CloseConnection;
string lastValue = GetUniqueValueString(oCom, comBehav);

Console.WriteLine("Unicode : {0}", _ConnectionTypee.Unicode.ToString());
Console.WriteLine("\t insert string length \t\t: \t{0}", insertString.Length.ToString());
Console.WriteLine("\t read value length \t\t: \t{0}", lastValue.Length.ToString());
Console.WriteLine("\t inserted value (between |) \t:\t|{0}|", insertString);
Console.WriteLine("\t readed value (between |) \t:\t|{0}|", lastValue);

Console.WriteLine("");
}
}
}
___________________________________________________________

Thanks for help

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 13 Jan 2010 12:51

We reproduced the problem. We will investigate it and notify you about the results.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 21 Jan 2010 13:29

We fixed the problem. The fix will be available in the nearest build, which we plan to release in the end of the month.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 09 Feb 2010 14:38

The new 5.55.90 Beta build of dotConnect for Oracle is available for download now. It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only). For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=17057.

Post Reply