InsertCommand problem

alexB · Post by **alexB** » Fri 30 Apr 2010 11:36

Hello,

I found an issue while inserting some datas, using the OracleDataAdapter.
I use Devart.Data.Oracle version 5.60.102.

This is the script for creating the table:

CREATE TABLE TESTTABLE
(
ID NUMBER NOT NULL,
NAME VARCHAR2(200 BYTE),
VAL NUMBER
);

I try to insert 3 rows having the following values:

- 1, 'Peter', '3,4'
- 2 , 'Paul', ''
- 3 , 'Marie', ''

Please follow the steps to reproduce the bug:
- the instanciation of the OracleDataAdapter is done by using the default constructor, OracleDataAdapter().
- create the SelectCommand: adapter.SelectCommand = new OracleCommand(selectCommandText, oracleConnection);
- OracleCommandBuilder ocb = new OracleCommandBuilder(adapter);
- then create the Insert/Update/Delete commands using the statement: (OracleCommand)ocb.GetUpdateCommand().Clone().
- call adapter.Update(ds, "TESTTABLE"); the dataSet 'ds' must contain the values specified above.

The result from DataTable should look like this:
- 1, 'Peter', '3,4'
- 2 , 'Paul', '3,4'
- 3 , 'Marie', '3,4'

As you can see, the row 1 is ok, but the other 2 rows have a wrong value for 'Val' column : '3,4' instead of null !

I analyzed the code and found out that if the instanciation of OracleDataAdapter is done by using the following constructor :
OracleDataAdapter(string selectCommandText, OracleConnection selectConnection), all is working as expected !
Seems that the only difference between the use of the 2 constructors is the value of UpdateBatchSize (1 - for default constructor and 0 for the other constructor).

Please investigate further, and if you need i will provided more information.

Thank you.

StanislavK · Post by **StanislavK** » Wed 05 May 2010 17:07

I've sent you a test project performing the claimed scenario. Please check that the letter was not blocked by your mail filter.

I've failed to reproduce the error with this sample. Please tell us what needs to be changed in it or send us a test project reproducing the error.

alexB · Post by **alexB** » Thu 06 May 2010 08:17

I have not received anything, but i want to send you a test project. Should i use the email: [email protected] ?

alexB · Post by **alexB** » Thu 06 May 2010 11:32

I just sent you the test project to [email protected].
Please let me know if you can reproduce it now or if you have more questions.

Thank you.

StanislavK · Post by **StanislavK** » Fri 07 May 2010 12:03

Please add the following lines after ds."TESTUPDATE" is updated:

Code: Select all

foreach (DataRow dr in ds.Tables["TESTUPDATE"].Rows)            
	Console.Write("{0} | {1} | {2} \n", dr[0], dr[1], dr[2]);

and specify what is written in the console. Also, please specify the versions of Oracle client and server you are using.

I couldn't reproduce the error with the test project on both 5.60.102 and the latest 5.60.124 versions in our environment. The proper values (1 | 'Peter' | '3,4' ; 2 | 'Paul' | '' ; 3 | 'Marie' | '') were printed.

alexB · Post by **alexB** » Fri 07 May 2010 12:34

Here are some infos about my Oracle:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Also i am using the latest 5.60.124 version.

About the printed values, you are write i get the same values too, but have a look directly in the Oracle table or add this code:

After this line:

Code: Select all

int recs = adapter.Update(ds, "TESTUPDATE");

Add:

Code: Select all

DataSet newDs = new DataSet();
adapter.Fill(newDs);
foreach (DataRow dr in newDs.Tables[0].Rows)
Console.Write("{0} | {1} | {2} \n", dr[0], dr[1], dr[2]);

Now you get the values that were inserted in the database:

3 | Marie | 3,40
1 | Peter | 3,40
2 | Paul | 3,40

StanislavK · Post by **StanislavK** » Fri 07 May 2010 16:07

Thank you for your assistance. We've reproduced the issue; we will investigate it and notify you about the results as soon as possible.

StanislavK · Post by **StanislavK** » Tue 25 May 2010 07:28

We have released the new 5.70.140 build of dotConnect for Oracle. It can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains fix for the problem with empty strings replaced on OracleDataAdapter.Update. For more information on fixes and improvements available in version 5.70.140, please see
http://www.devart.com/forums/viewtopic.php?t=18028

alexB · Post by **alexB** » Tue 25 May 2010 10:24

I tested with the new version 5.70.140 and the problem is fixed !

Thank you.