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
Problem inserting LONG with Unicode = true (parameterized)
-
- Posts: 20
- Joined: Tue 09 Jun 2009 10:23
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
-
- Posts: 9
- Joined: Mon 11 Jan 2010 09:46
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
Result with System.Data.OracleClient.OracleConnection
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
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
Result with System.Data.OracleClient.OracleConnection
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
http://www.devart.com/forums/viewtopic.php?t=17057.