OracleLoader. When?
OracleLoader. When?
Hi there
When could we expect OracleLoader working in Unicode mode and supporting Raw column types (Guids)?
Thank you in advance
When could we expect OracleLoader working in Unicode mode and supporting Raw column types (Guids)?
Thank you in advance
dotConnect for Oracle 5.20 Beta is available now.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information about the new version, please refer to http://www.devart.com/forums/viewtopic.php?t=14304 .
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information about the new version, please refer to http://www.devart.com/forums/viewtopic.php?t=14304 .
Here is a script.
Here is a C# code.
Code: Select all
CREATE TABLE RAWTABLE(
ID NUMBER,
RAWCOLUMN RAW(17),
CONSTRAINT PK_RAWTABLE PRIMARY KEY (ID))
Code: Select all
using (OracleConnection conn = new OracleConnection()) {
conn.ConnectionString = "User Id=scott;Password=tiger;Server=ora1020;Home=OraClient10g_home1;Unicode=True;";
// OracleLoader requires open connection
if (connection.State == ConnectionState.Closed)
connection.Open();
OracleLoader loader = new OracleLoader();
// Specify connection that OracleLoader will use for loading
loader.Connection = connection;
// Set table name that will be loaded into
loader.TableName = "rawtable";
// Populate Columns collection from table description
loader.CreateColumns();
// Prepare OracleLoader for loading
loader.Open();
loader.SetValue("id", 1);
loader.SetValue("rawcolumn", Guid.NewGuid().ToByteArray());
loader.NextRow();
// Flush buffer and dispose internal OracleLoader structures
loader.Close();
OracleCommand command = new OracleCommand();
command.Connection = conn;
command.CommandText = "select rawcolumn from rawtable";
conn.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
MessageBox.Show(reader.GetOracleBinary(0).ToString());
}
Looks we do not understand each other. This is probably because english is not our native language Sorry, if my post was misleading.
The problem was two fold:
And here what it displays on my console:
Well done, raw types are now supported as you said.
But, as you may see, besides Guid is still being not a first class citizen in your drivers, unicode string is not stored properly.
I hope with this kind of detalization you'll be able to reproduce and fix the problem. For test, I used latest downloadable Oracle 10g server with AL32UTF8 database encoding (as parameters show).
Thank you in advance,
Sincerely yours,
Alexey "Lex" Lavnikov
The problem was two fold:
You wrote:1) working in Unicode mode 2) and supporting Raw column types
So I decided you have fixed both problems in this build:The Unicode mode and the Raw column types support are implemented. Look forward to the next build of dotConnect for Oracle.
I downloaded it and tested:dotConnect for Oracle 5.20 Beta is available now.
What I meant is that Unicode mode is not working properly. Here is an example:Sorry, cannot see the difference. Doesn't work as before.
Code: Select all
create table RAWTABLE
(
RAWCOLUMN RAW(16),
TEXTCOLUMN VARCHAR2(2000)
)
Code: Select all
using System;
using System.Text;
using Devart.Data.Oracle;
namespace ConsoleApplication1
{
class Program
{
static string testText = "Привет из Unicode // Hällö aus ünicode";
static Guid testGuid = Guid.NewGuid();
static void Main(string[] args)
{
Console.OutputEncoding = Encoding.UTF8;
using (var conn = new OracleConnection("User Id=scott;Password=tiger;Server=ora1020;Home=OraClient10g_home1;Unicode=True;"))
{
// OracleLoader requires open connection
conn.Open();
Console.WriteLine("Database parameters");
using (var command = conn.CreateCommand())
{
command.CommandText = "select parameter, value from nls_database_parameters";
using (var reader = command.ExecuteReader())
while (reader.Read())
Console.WriteLine("{0} = {1}", reader[0], reader[1]);
}
using (var command = conn.CreateCommand())
{
command.CommandText = "delete from rawtable";
command.ExecuteNonQuery();
}
Console.WriteLine("\nUsing Oracle Loader");
using (var loader = new OracleLoader("rawtable", conn))
{
loader.CreateColumns();
loader.Open();
loader.SetValue("textcolumn", testText);
loader.SetValue("rawcolumn", testGuid.ToByteArray());
loader.NextRow();
}
Console.WriteLine("Oracle Loader done");
Console.WriteLine("\nTesting Unicode string roundtrip");
// roundtrip string test
using (var command = conn.CreateCommand())
{
command.CommandText = "select :text from dual";
command.Parameters.AddWithValue("text", testText);
var str = (string)command.ExecuteScalar();
Console.WriteLine("test: {0}, result: {1}", testText, str);
Console.WriteLine("Test passed: {0}", str == testText);
}
Console.WriteLine("\nTesting Guid roundtrip");
// roundtrip guid test
try
{
using (var command = conn.CreateCommand())
{
command.CommandText = "select :guid from dual";
command.Parameters.AddWithValue("guid", testGuid);
var guid = (Guid)command.ExecuteScalar();
Console.WriteLine("test: {0}, result: {1}", testGuid, guid);
Console.WriteLine("Test passed: {0}", guid == testGuid);
}
}
catch (Exception e)
{
Console.WriteLine("Test failed: {0}", e.Message);
}
using (var command = conn.CreateCommand())
{
command.CommandText = "select textcolumn, rawcolumn from rawtable";
using (var reader = command.ExecuteReader())
while (reader.Read())
{
var str = reader.GetString(0);
Console.WriteLine("\nOracle loader: Unicode string");
Console.WriteLine("Test: {0}, result {1}", testText, str);
Console.WriteLine("Test passed: {0}", str == testText);
var guid = reader.GetGuid(1);
Console.WriteLine("\nOracle loader: Guid");
Console.WriteLine("Test: {0}, result {1}", testGuid, guid);
Console.WriteLine("Test passed: {0}", guid == testGuid);
}
}
Console.ReadLine();
}
}
}
}
Code: Select all
Database parameters
NLS_LANGUAGE = ENGLISH
NLS_TERRITORY = UNITED KINGDOM
NLS_CURRENCY = #
NLS_ISO_CURRENCY = UNITED KINGDOM
NLS_NUMERIC_CHARACTERS = .,
NLS_CHARACTERSET = AL32UTF8
NLS_CALENDAR = GREGORIAN
NLS_DATE_FORMAT = DD-MON-RR
NLS_DATE_LANGUAGE = ENGLISH
NLS_SORT = BINARY
NLS_TIME_FORMAT = HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT = DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT = HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT = DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY = ?
NLS_COMP = BINARY
NLS_LENGTH_SEMANTICS = BYTE
NLS_NCHAR_CONV_EXCP = FALSE
NLS_NCHAR_CHARACTERSET = UTF8
NLS_RDBMS_VERSION = 10.2.0.3.0
Using Oracle Loader
Oracle Loader done
Testing Unicode string roundtrip
test: Привет из Unicode // Hällö aus ünicode, result: Привет из Unicode // Hällö aus ünicode
Test passed: True
Testing Guid roundtrip
Test failed: Value with type System.Guid not supported.
Oracle loader: Unicode string
Test: Привет из Unicode // Hällö aus ünicode, result @ 8 2 5 B 8 7 U n i c o d e / / H � l � a u s � n i c o d e
Test passed: False
Oracle loader: Guid
Test: cb0134bc-6745-4c75-8226-6a255b0f5ba6, result cb0134bc-6745-4c75-8226-6a255b0f5ba6
Test passed: True
But, as you may see, besides Guid is still being not a first class citizen in your drivers, unicode string is not stored properly.
I hope with this kind of detalization you'll be able to reproduce and fix the problem. For test, I used latest downloadable Oracle 10g server with AL32UTF8 database encoding (as parameters show).
Thank you in advance,
Sincerely yours,
Alexey "Lex" Lavnikov
1. The problem with OracleLoader is fixed. We have made some tests: it seems like OracleLoader is working properly only with the 11 version of Oracle Client. This is an Oracle issue.
2. Please use byte[] instead of the Guid type.
The build with this fix will be available this week. I will post here when it is available for download on our site.
2. Please use byte[] instead of the Guid type.
Code: Select all
static byte[] testGuid = Guid.NewGuid().ToByteArray();
The new build (5.00.26) of dotConnect for Oracle is available now.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=14522 .
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=14522 .
Just tested. So, what can I say...
Now it simply hangs... Or crashes with Attempt to write to protected memory...
Please find attached demo project to reproduce the bug.
Now it simply hangs... Or crashes with Attempt to write to protected memory...
Please find attached demo project to reproduce the bug.
Code: Select all
using System;
using System.Data.Odbc;
using System.Text;
using Devart.Data.Oracle;
namespace OracleLoaderBugN3
{
class Program
{
static void Main(string[] args)
{
using (var conn = new OracleConnection("User Id=scott;Password=tiger;Server=oracle10g;Unicode=True;"))
{
conn.Open();
Console.WriteLine("Database parameters");
using (var command = conn.CreateCommand())
{
command.CommandText = "select parameter, value from nls_database_parameters";
using (var reader = command.ExecuteReader())
while (reader.Read())
Console.WriteLine("{0} = {1}", reader[0], reader[1]);
}
Console.WriteLine("Create test data");
try
{
using (var command = conn.CreateCommand())
{
command.CommandText = "drop table testdata";
command.ExecuteNonQuery();
}
}
catch
{
}
using (var command = conn.CreateCommand())
{
command.CommandText = @"CREATE TABLE testdata
(MYSECURITYISSUEDATE varchar2(1000 char),
MYSECURITYREDEMPTIONDATE varchar2(1000 char),
MYDETAILEDNATURECODE number,
MYVERSIONID varchar2(1000 char),
SENDEROUTPUTDATE varchar2(1000 char),
SENDEROUTPUTTIME varchar2(1000 char),
PROVIDER varchar2(1000 char),
SECURITYISINCODE varchar2(1000 char),
SECURITYNAME varchar2(1000 char),
SECURITYCURRENCY varchar2(1000 char),
SECURITYISSUEDATE varchar2(1000 char),
SECURITYREDEMPTIONDATE varchar2(1000 char),
SECURITYEFFECTDATE varchar2(1000 char),
REGROUPINGNATURECODE varchar2(1000 char),
DETAILEDNATURECODE varchar2(1000 char),
SECURITYFACEVALUEAMOUNT varchar2(1000 char),
PAYMENTFREQUENCY varchar2(1000 char),
DEFAULTSTOCKEXCHANGECODE varchar2(1000 char),
MARKETTYPE varchar2(1000 char),
ISSUERCODE varchar2(1000 char),
ISSUERNAME varchar2(1000 char),
PEAPOSITIONINDEX varchar2(1000 char),
EMISSIONSECURITIESNUMBER varchar2(1000 char))";
command.ExecuteNonQuery();
}
Console.WriteLine("Create test data done.");
int row = 0;
Console.WriteLine("Using Oracle Loader");
using (var loader = new OracleLoader("testdata", conn))
{
loader.CreateColumns();
loader.Open();
while (row < 1000)
{
row++;
loader.SetValue(0, "1994-11-03");
loader.SetValue(1, "1999-11-04");
loader.SetValue(2, 45);
loader.SetValue(3, "20090313 184826 FR0000299174");
loader.SetValue(4, "20090313");
loader.SetValue(5, "184826");
loader.SetValue(6, "V2");
loader.SetValue(7, "FR0000299174");
loader.SetValue(8, "BNP OBLI 100 SI.");
loader.SetValue(9, "EUR");
loader.SetValue(10, "19941103");
loader.SetValue(11, "19991104");
loader.SetValue(12, "19941103");
loader.SetValue(13, "09");
loader.SetValue(14, "045");
loader.SetValue(15, "0.000000000000000");
loader.SetValue(16, "ANNU");
loader.SetValue(17, "260");
loader.SetValue(18, "S");
loader.SetValue(19, "1120872");
loader.SetValue(20, "BNP OBLI 100");
loader.SetValue(21, "0");
loader.SetValue(22, "00000000000");
loader.NextRow();
Console.WriteLine("Row {0}", row);
}
loader.Close();
}
Console.WriteLine("Oracle Loader done ({0} rows). ", row);
}
}
}
}