OracleLoader. When?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

OracleLoader. When?

Post by Alladin » Fri 13 Feb 2009 18:34

Hi there

When could we expect OracleLoader working in Unicode mode and supporting Raw column types (Guids)?

Thank you in advance

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 18 Feb 2009 11:41

We will investigate the possibility of adding this functionality and notify you about the results as soon as possible.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 23 Feb 2009 10:16

The Unicode mode and the Raw column types support are implemented. Look forward to the next build of dotConnect for Oracle. I will post here when it is available.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 04 Mar 2009 16:01

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 .

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Mon 16 Mar 2009 18:34

I just tested OracleLoader in 5.20 beta version in Unicode mode.

Sorry, cannot see the difference. Doesn't work as before.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 17 Mar 2009 14:02

Direct Path Loading using OracleLoader when Unicode=true is supported starting from Oracle client 10.2. Please make sure your Oracle client version is 10.2 or higher.

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Thu 19 Mar 2009 16:42

Sorry, when I read my data, saved in Oracle database using OracleLoader over OracleConnection.Unicode=true, I get strings where every second character is \0...

Could you please provide a demo program that works in Unicode mode as you say?

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 23 Mar 2009 10:05

Here is a script.

Code: Select all

CREATE TABLE RAWTABLE(
  ID NUMBER,
  RAWCOLUMN RAW(17),
  CONSTRAINT PK_RAWTABLE PRIMARY KEY (ID))
Here is a C# code.

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());
      }

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Mon 23 Mar 2009 11:53

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:
1) working in Unicode mode 2) and supporting Raw column types
You wrote:
The Unicode mode and the Raw column types support are implemented. Look forward to the next build of dotConnect for Oracle.
So I decided you have fixed both problems in this build:
dotConnect for Oracle 5.20 Beta is available now.
I downloaded it and tested:
Sorry, cannot see the difference. Doesn't work as before.
What I meant is that Unicode mode is not working properly. Here is an example:

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();
      }
    }
  }
}

And here what it displays on my console:

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

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

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 24 Mar 2009 13:01

Thank you for your sample. We will investigate the issue and notify you about the results as soon as possible.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 01 Apr 2009 11:29

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.

Code: Select all

static byte[] testGuid = Guid.NewGuid().ToByteArray();
The build with this fix will be available this week. I will post here when it is available for download on our site.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 02 Apr 2009 16:16

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 .

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Fri 03 Apr 2009 17:44

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.

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);
      }
    }
  }
}

Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Mon 06 Apr 2009 11:35

Hi there,

Could you reproduce this bug?

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 06 Apr 2009 12:06

Thank you. We are investigating the issue. We have reproduced this bug.

Post Reply