Some issues when insert record with parameter bindings

Some issues when insert record with parameter bindings

Postby georgewang » Thu 30 Oct 2014 02:11

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
 
Posts: 3
Joined: Thu 30 Oct 2014 01:49

Re: Some issues when insert record with parameter bindings

Postby georgewang » Fri 31 Oct 2014 03:25

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'))
georgewang
 
Posts: 3
Joined: Thu 30 Oct 2014 01:49

Re: Some issues when insert record with parameter bindings

Postby Pinturiccio » Tue 04 Nov 2014 14:36

georgewang wrote: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.

georgewang wrote: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.

georgewang wrote: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.
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44

Re: Some issues when insert record with parameter bindings

Postby georgewang » Tue 11 Nov 2014 02:52

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
georgewang
 
Posts: 3
Joined: Thu 30 Oct 2014 01:49

Re: Some issues when insert record with parameter bindings

Postby Pinturiccio » Fri 14 Nov 2014 15:29

georgewang wrote:'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.

georgewang wrote: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?
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle