Mutating OracleParameter reference

Mutating OracleParameter reference

Postby klaus linzner » Fri 20 Feb 2015 10:20

Hi,
When a parameter is added to a command and the parameter contains a trailing "$" sign, it happens that the returned instance is removed from the command ParameterCollection once the command is executed. This makes it impossible to create a command and loop over results updating the command parameters.

This happens with Devart 8.4.225 and can be reproduced with the following code:

Code: Select all
#region usings

using System.Data;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;

#endregion

namespace MyCompany.DataProvider.Test.ProviderEvaluation
{
    [TestClass]
    public class DevartParameterReferenceTest
    {
        #region Constants and Fields

        private const string ConnectionString = "Data Source=YPURSERVER;User ID=YOURUSER;Password=YOURPASSWORD;";
        private const string TestProcedure = @"CREATE OR REPLACE PROCEDURE SAMPLE_DEVART_REF (P_FROMSERVICE$ VARCHAR2, P_METHOD$ VARCHAR2, P_DATA$ VARCHAR2)
IS
BEGIN
   null;
END;";

        #endregion

        [TestInitialize]
        public void Initialize()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();

                using (OracleCommand createSpCommand = connection.CreateCommand())
                {
                    createSpCommand.CommandText = TestProcedure;
                    createSpCommand.ExecuteNonQuery();
                }
            }
        }

        [TestMethod]
        public void ParamReferencesStayValid()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();

                using (OracleCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "SAMPLE_DEVART_REF";

                    command.Parameters.Add("P_FROMSERVICE$", OracleDbType.NVarChar, "...some value...", ParameterDirection.Input);
                    OracleParameter paramMethod = command.Parameters.Add("P_METHOD$", OracleDbType.NVarChar);
                    OracleParameter paramData = command.Parameters.Add("P_DATA$", OracleDbType.NVarChar);

                    for (int i = 0; i < 5; i++)
                    {
                        paramMethod.Value = "Method" + i;
                        paramData.Value = "Data" + i;

                        Assert.AreSame(paramMethod, command.Parameters["P_METHOD$"], "P_METHOD$ ref equal before {0}", i);
                        Assert.AreSame(paramData, command.Parameters["P_DATA$"], "P_DATA$ ref equal before {0}", i);

                        command.ExecuteNonQuery();

                        Assert.AreSame(paramMethod, command.Parameters["P_METHOD$"], "P_METHOD$ ref equal after {0}", i);
                        //unreachable - previous assertion fails...
                        //without assertions the same value would be updated each time.
                        Assert.AreSame(paramData, command.Parameters["P_DATA$"], "P_DATA$ ref equal after {0}", i);
                    }
                }
            }
        }
    }
}



However - if the "$" is removed from the parameter names everything works as expected:

Code: Select all
#region usings

using System.Data;
using Devart.Data.Oracle;
using Microsoft.VisualStudio.TestTools.UnitTesting;

#endregion

namespace MyCompany.DataProvider.Test.ProviderEvaluation
{
    [TestClass]
    public class DevartParameterReferenceTest
    {
        #region Constants and Fields

        private const string ConnectionString = "Data Source=YPURSERVER;User ID=YOURUSER;Password=YOURPASSWORD;";
        private const string TestProcedure = @"CREATE OR REPLACE PROCEDURE SAMPLE_DEVART_REF (P_FROMSERVICE VARCHAR2, P_METHOD VARCHAR2, P_DATA VARCHAR2)
IS
BEGIN
   null;
END;";

        #endregion

        [TestInitialize]
        public void Initialize()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();

                using (OracleCommand createSpCommand = connection.CreateCommand())
                {
                    createSpCommand.CommandText = TestProcedure;
                    createSpCommand.ExecuteNonQuery();
                }
            }
        }

        [TestMethod]
        public void ParamReferencesStayValid()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();

                using (OracleCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "SAMPLE_DEVART_REF";

                    command.Parameters.Add("P_FROMSERVICE", OracleDbType.NVarChar, "...some value...", ParameterDirection.Input);
                    OracleParameter paramMethod = command.Parameters.Add("P_METHOD", OracleDbType.NVarChar);
                    OracleParameter paramData = command.Parameters.Add("P_DATA", OracleDbType.NVarChar);

                    for (int i = 0; i < 5; i++)
                    {
                        paramMethod.Value = "Method" + i;
                        paramData.Value = "Data" + i;

                        Assert.AreSame(paramMethod, command.Parameters["P_METHOD"], "P_METHOD$ ref equal before {0}", i);
                        Assert.AreSame(paramData, command.Parameters["P_DATA"], "P_DATA$ ref equal before {0}", i);

                        command.ExecuteNonQuery();

                        Assert.AreSame(paramMethod, command.Parameters["P_METHOD"], "P_METHOD$ ref equal after {0}", i);
                        Assert.AreSame(paramData, command.Parameters["P_DATA"], "P_DATA$ ref equal after {0}", i);

                        //everything works great now - each loops write new values...
                    }
                }
            }
        }
    }
}


BR
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Mutating OracleParameter reference

Postby Shalex » Wed 25 Feb 2015 11:06

Thank you for your test code. We have reproduced the issue and are investigating it. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44

Re: Mutating OracleParameter reference

Postby Shalex » Thu 05 Mar 2015 13:55

This is a designed behaviour, and it will not be changed in the nearest future.

As a workaround, please obtain an instance of the parameter from the command.Parameters collection by name after calling command.ExecuteNonQuery().

If you change Assert.AreSame to Assert.AreEqual in your test, it should pass successfully.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44

Re: Mutating OracleParameter reference

Postby klaus linzner » Mon 09 Mar 2015 11:46

Thanks, but could you please give any further explanation why this is a designed and/or desired behavior? It's certainly possible that there's a reason for it, I just can't think of any. From my point of view it just seems buggy that a reference to an instance is returned, but it's unknown if this instance is still added or orphaned.

(From an API user perspective this is pretty much the same as if I can't rely on the instance returned from connection.BeginTransaction...)
klaus linzner
 
Posts: 28
Joined: Thu 16 May 2013 09:18

Re: Mutating OracleParameter reference

Postby Shalex » Tue 10 Mar 2015 16:02

The bug with redundant describing stored procedure in OracleCommand with CommandType.StoredProcedure and names of parameters ending with '$' symbol is fixed. This will solve the issue you have encountered. Additionally with the new build, include "Describe Stored Procedure=False;" in your connection string to avoid describing in similar cases.

We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44

Re: Mutating OracleParameter reference

Postby Shalex » Thu 26 Mar 2015 16:28

New build of dotConnect for Oracle 8.4.379 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=31498.
Shalex
Devart Team
 
Posts: 7606
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle