The value of parameter is not sent to Oracle

The value of parameter is not sent to Oracle

Postby zhuqijun » Thu 17 Mar 2016 05:52

Hello Support,

Our product is using dotConnect 7.7.276 to connect to the Oracle database.

Our customers' DBA reported that he observed the insert statement only send the SQL to the oracle, without sending the value of the parameter, which makes the oracle waiting for the value of the parameter.

My question is: is the DBA's observation reasonable? Does DotConnect send the SQL and the value of the parameter separately?

Thanks!
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: The value of parameter is not sent to Oracle

Postby Pinturiccio » Thu 17 Mar 2016 15:47

zhuqijun wrote:My question is: is the DBA's observation reasonable? Does DotConnect send the SQL and the value of the parameter separately?

If the Prepare method of the OracleCommand object was not executed before command execution, the command and parameters are sent to the database as one packet. The Prepare method creates a prepared (or compiled) version of the command on the server.

However, parameters of the LOB and Object types are an exception of this rule. Parameters, having such type, are sent in separate packets even if the Prepare method was not called.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: The value of parameter is not sent to Oracle

Postby zhuqijun » Fri 18 Mar 2016 08:45

Thanks for you reply.

Do you know any possibility or bugs that DotConnect only send the SQL to Oracle but not send the value of the parameter?

Our customer insists that the Oracle only received the SQL but not received the value of parameter, so the Oracle keeps waiting.
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: The value of parameter is not sent to Oracle

Postby Pinturiccio » Fri 18 Mar 2016 15:10

zhuqijun wrote:Do you know any possibility or bugs that DotConnect only send the SQL to Oracle but not send the value of the parameter?

We have no knowledge of such bug.

zhuqijun wrote:Our customer insists that the Oracle only received the SQL but not received the value of parameter, so the Oracle keeps waiting.


Try using the last version of dotConnect for Oracle. 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). Is the issue reproduced with this verison?

If you can, please provide us with the flowing information:

1. Please create a small test project which can reproduce the issue and send it to us.
2. DDL/DML scripts of the table, that are required for execution of the application.
3. Oracle server version.
4. If you use OCI connection mode, specify the version of Oracle Client.
5. Also, please specify the way you determine that the command text was sent to the server, and parameters were not.
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: The value of parameter is not sent to Oracle

Postby zhuqijun » Tue 22 Mar 2016 09:45

Our code is like this:

OracleCommand cmd = new OracleCommand();
cmd.Connection= con;
cmd.CommandText = "insert into sourcedatatable(col1,COL_INT) values(:col1,:COL_INT)";

OracleParameter parameter1 = cmd.CreateParameter();
parameter1.ParameterName = "col1";
parameter1.Value = new string[] { "123", "456" };

OracleParameter parameter2 = cmd.CreateParameter();
parameter2.ParameterName = "COL_INT";
parameter2.Value = new int[] { 1,3 };

cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
con.Open();
int result = cmd.ExecuteArray(2);

My question is: if DotConnect may send the request to Oracle in 2 packets, are all the 2 packages are sent after calling the line cmd.ExecuteArray ?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: The value of parameter is not sent to Oracle

Postby Pinturiccio » Wed 23 Mar 2016 14:06

In your example, both command text and parameters should be sent as one packet when calling the ExecuteArray method. Why do you think that in your case parameters are sent as a separate packet?

zhuqijun wrote:My question is: if DotConnect may send the request to Oracle in 2 packets, are all the 2 packages are sent after calling the line cmd.ExecuteArray ?

Yes, packets are sent after executing the following line:
Code: Select all
int result = cmd.ExecuteArray(2);
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44

Re: The value of parameter is not sent to Oracle

Postby zhuqijun » Thu 24 Mar 2016 06:11

Ok, my previous only send one package.
If I add another parameter with the type of LOB or object type, it will send with 2 packages, rights?

And the packets are sent after executing the following line:
int result = cmd.ExecuteArray(2);

Correct?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: The value of parameter is not sent to Oracle

Postby Shalex » Thu 24 Mar 2016 16:21

Correct.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle