Some issues when insert record with parameter bindings

[georgewang]

Hello Devart Support,

Our system support a simple function that users can upload excel file(xls) to Oracle DB.

The environment on our system:
1.Oracle 11gr2
2.Devart dotConnect for Oracle
(Devart.Data.Oracle.dll - v7.0.17.0, Devart.Data.dll - v5.0.476.0)
3.Visual studio 2010 vb.net (compile as .net framework 2.0 and any CPU)
4.office excel 2003 or 2010

There're several issues,
1. The parameter of data type DATE is OK binding at runtime but failed on inserting to Oracle DB with exception ora-01483.
So, I ignore issue 1, but only bind values belong to number or varchar2 that got success result.

2. Some situations, for example, some cells' value are null, and I got a shift result on new records just inserted on DB.

Shift means Columns 01 ~ 05 are filled with Values 04 ~ 08, so the Values 01 ~ 03 are disappeared...

3. Based on issue 2, there's wrong-character-values cannot be identified on some DB columns.

[georgewang]

BTW, the dynamic SQL without parameter bindings is successfuly to insert.

INSERT INTO DEMO_UPLOAD( CNUMBER, CVARCHAR2, CVARCHART, CDATE) VALUES(2323, 'BLAHBLAH..', '2323', to_date('2014/10/11 00:00:00','yyyy/mm/dd hh24:mi:ss'))

[Pinturiccio]

1. The parameter of data type DATE is OK binding at runtime but failed on inserting to Oracle DB with exception ora-01483.

BTW, the dynamic SQL without parameter bindings is successfuly to insert.

INSERT INTO DEMO_UPLOAD( CNUMBER, CVARCHAR2, CVARCHART, CDATE) VALUES(2323, 'BLAHBLAH..', '2323', to_date('2014/10/11 00:00:00','yyyy/mm/dd hh24:mi:ss'))

Please create and send us a small test project for this case with the corresponding DDL/DML scripts for reproducing the issue.

2. Some situations, for example, some cells' value are null, and I got a shift result on new records just inserted on DB.

Shift means Columns 01 ~ 05 are filled with Values 04 ~ 08, so the Values 01 ~ 03 are disappeared...

The issue you have described is not related to dotConnect for Oracle. dotConnect for Oracle inserts the data that your application reads from an Excel document. Probably the data are read incorrectly or an incorrect statement for inserting data to Oracle is generated.

3. Based on issue 2, there's wrong-character-values cannot be identified on some DB columns.

Please describe what you mean by 'wrong-character-values' and with which DB columns it can be reproduced.

[georgewang]

Thanks for reply.

'wrong-character-values' means values with wrong characterSet.

Eventually, I found out the root cause that the OracleCommand.Parameters needs to be cleared safely before rebinding next batch of DML command.

But it's happened strangely though the process below in function Attachement of parameters that assigns the new value when parameter has existed in set of ones.

If command.Parameters.Contains(p.ParameterName) Then
CType(command.Parameters.Item(p.ParameterName), IDataParameter).Value = p.Value
Else
command.Parameters.Add(p)
End If

[Pinturiccio]

'wrong-character-values' means values with wrong characterSet.

dotConnect for Oracle uses Oracle Client character set in the OCI mode and operating system character set in the Direct mode.

Eventually, I found out the root cause that the OracleCommand.Parameters needs to be cleared safely before rebinding next batch of DML command.

But it's happened strangely though the process below in function Attachement of parameters that assigns the new value when parameter has existed in set of ones.

If command.Parameters.Contains(p.ParameterName) Then
CType(command.Parameters.Item(p.ParameterName), IDataParameter).Value = p.Value
Else
command.Parameters.Add(p)
End If

Please describe the issue in more details.

Which code causes the issue?

Have you found a solution?