OracleCommand.ExecuteArray(int iters) Bug

OracleCommand.ExecuteArray(int iters) Bug

Postby isutommyt » Mon 26 Nov 2012 14:00

Greetings,

I'm experiencing some issues with the OracleCommand.ExecuteArray(int iters) method when connecting via direct mode. I'm trying to bulk insert some data that includes some null or empty cells. When in direct mode, the method seems to skip over these parameters and ends up inserting the next parameter into the column, ie shifting the data.

When not in direct mode the method works great. I'm using the 7.2.122 version. Any insight here? If necessary I can work up an example when I get a bit more time.

Thanks,
Jason
Last edited by isutommyt on Mon 26 Nov 2012 19:38, edited 1 time in total.
isutommyt
 
Posts: 5
Joined: Wed 21 Nov 2012 19:53

Re: OracleCommand.ExecuteArray(int iters)

Postby isutommyt » Mon 26 Nov 2012 18:52

Here are some more details with some test code:

Create a table with 3 columns
Create an OracleCommand
Add the CommandText and the OracleParameters

Notice how the first parameter has values that are all null. If just one of the 2 values is changed to a non null value, then both direct and non direct queries work. If both values are null like the code below, then only the non direct connection mode will work.

Code:
Code: Select all
           
OracleConnection conn = new OracleConnection("User Id=TESTUSER;Password=TESTPASSWORD;Server=TESTSERVER");
            //OracleConnection conn = new OracleConnection("User Id=TESTUSER;Password=TESTPASSWORD;Server=server1.test.com;Direct=True;Sid=TESTSERVER;Port=1521;");
            conn.Open();

            OracleCommand cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = "DROP TABLE DEVARTBULKLOAD";
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }

            try
            {
                cmd.CommandText = "create table devartBulkLoad (colA varchar2(100 BYTE), colB number(10,2), colC varchar2(100 BYTE))";
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }

            OracleCommand bulkCommand = conn.CreateCommand();
            bulkCommand.CommandText = "insert into devartbulkload (cola,colb,colc) values (:cola,:colb,:colc)";

            OracleParameter par1 = new OracleParameter("COLA", OracleDbType.VarChar);
            par1.IsNullable = true;
            object[] paramValues = new object[2];
            paramValues[0] = null;
            paramValues[1] = null;
            par1.Value = paramValues;
            bulkCommand.Parameters.Add(par1);

            OracleParameter par2 = new OracleParameter("COLB", OracleDbType.Number);
            par2.IsNullable = true;
            paramValues = new object[2];
            paramValues[0] = 3;
            paramValues[1] = 2;
            par2.Value = paramValues;
            bulkCommand.Parameters.Add(par2);

            OracleParameter par3 = new OracleParameter("COLC", OracleDbType.VarChar);
            par3.IsNullable = true;
            paramValues = new object[2];
            paramValues[0] = "foo";
            paramValues[1] = "bar";
            par3.Value = paramValues;
            bulkCommand.Parameters.Add(par3);

            bulkCommand.ExecuteArray(2);


Non Direct Connection Table Result:
Image

Everything looks good!

Direct Connection Table Result:
Image

Some values are shifted and we get some weird text! Can you please look into this issue?

Thanks,
Jason
isutommyt
 
Posts: 5
Joined: Wed 21 Nov 2012 19:53

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby Pinturiccio » Wed 28 Nov 2012 16:22

We could not reproduce the issue. We execute your code without any changes except the credentials in connection strings and it works fine in our environment in both Direct and OCI modes.

Please specify the following for reproducing the issue:
a) the exact version of your Oracle server (xx.x);
b) the NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
e) the name, version of your operating system and its regional settings: Control Panel > Regional and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby isutommyt » Wed 28 Nov 2012 18:05

Thanks for looking into, here is the information you have requested.

a) the exact version of your Oracle server (xx.x);
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

b) the NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your Oracle server;
NLS_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET UTF8

e) the name, version of your operating system and its regional settings: Control Panel > Regional and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs.
Windows 7 Enterprise x64 SP1
Image
Image
Image
isutommyt
 
Posts: 5
Joined: Wed 21 Nov 2012 19:53

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby Pinturiccio » Fri 30 Nov 2012 14:38

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby isutommyt » Mon 17 Dec 2012 17:13

May I get an update on the progress of the bug fix and potential release? Thanks!
isutommyt
 
Posts: 5
Joined: Wed 21 Nov 2012 19:53

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby Pinturiccio » Fri 21 Dec 2012 14:07

Our investigation is still in progress. We will notify you as soon as we have some results.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby Pinturiccio » Wed 16 Jan 2013 15:36

We have fixed the bug with the ExecuteArray method when connecting to the UTF8 (Unicode) database in the Direct mode. We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby Pinturiccio » Fri 18 Jan 2013 09:17

The new build of dotConnect for Oracle 7.5.164 is available for download now!
It can be downloaded at 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://forums.devart.com/viewtopic.php?t=25664
Pinturiccio
Devart Team
 
Posts: 1860
Joined: Wed 02 Nov 2011 09:44

Re: OracleCommand.ExecuteArray(int iters) Bug

Postby isutommyt » Fri 18 Jan 2013 19:06

My testing no longer produces the bug! Thanks for the fix.
isutommyt
 
Posts: 5
Joined: Wed 21 Nov 2012 19:53


Return to dotConnect for Oracle