OracleLoader. When?

OracleLoader. When?

Postby 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
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby 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
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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.
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby 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.
Shalex
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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?
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby 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());
      }
Shalex
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby 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
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Postby 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

Postby Alladin » Mon 06 Apr 2009 11:35

Hi there,

Could you reproduce this bug?
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Shalex » Mon 06 Apr 2009 12:06

Thank you. We are investigating the issue. We have reproduced this bug.
Shalex
Devart Team
 
Posts: 7205
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for Oracle