OracleParameterCollection exception in insert_version_and_get_version_id() #58

Closed
rdingwall opened this Issue Mar 29, 2012 · 2 comments

Comments

Projects
None yet
2 participants
@rdingwall
Contributor

rdingwall commented Mar 29, 2012

Happening in version 0.8.5.0.

   Attempting to resolve version from C:\tmp\ddl\clean\_BuildInfo.xml using //buildInfo/version.
MSBUILD : warning : Unable to get version from xml file C:\tmp\ddl\clean\_BuildInfo.xml. File doesn't exist. [C:\tmp\ddl\clean\up.proj]
   Migrating (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) from version
   0 to 0.
   Versioning (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) database wi
  th version 0 based on .
MSBUILD : error : RoundhousE encountered an error.\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error : System.ArgumentException: The OracleParameter is already contained by another OracleParameterCollection.\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at System.Data.OracleClient.OracleParameterCollection.Validate(Int32 index, Object value)\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at System.Data.OracleClient.OracleParameterCollection.Add(Object value)\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.setup_database_command(String sql_to_run, ConnectionType connection_type, IEnumerable`1 parameters)
\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters)\r [C:\tmp\d
dl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters)\r [C:\tmp\ddl\clean\
up.proj]
MSBUILD : error :    at roundhouse.databases.oracle.OracleDatabase.insert_version_and_get_version_id(String repository_path, String repository_version)\r [C:\t
mp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.migrators.DefaultDatabaseMigrator.version_the_database(String repository_path, String repository_version)\r [C:\tmp\ddl\clea
n\up.proj]
MSBUILD : error :    at roundhouse.runners.RoundhouseMigrationRunner.run() [C:\tmp\ddl\clean\up.proj]
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018: The "Roundhouse" task failed unexpectedly.\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018: System.ArgumentException: The OracleParameter is already contained by another OracleParameterCollection.\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at System.Data.OracleClient.OracleParameterCollection.Validate(Int32 index, Object value)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at System.Data.OracleClient.OracleParameterCollection.Add(Object value)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.setup_database_command(String sql_to_run, ConnectionType connection_typ
e, IEnumerable`1 parameters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, ILi
st`1 parameters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 para
meters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.oracle.OracleDatabase.insert_version_and_get_version_id(String repository_path, String
 repository_version)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.migrators.DefaultDatabaseMigrator.version_the_database(String repository_path, String repository
_version)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.runners.RoundhouseMigrationRunner.run()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.tasks.Roundhouse.run_the_task()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.tasks.Roundhouse.Microsoft.Build.Framework.ITask.Execute()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggi
ngContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)
Done Building Project "C:\tmp\ddl\clean\up.proj" (default targets) -- FAILED.
@rdingwall

This comment has been minimized.

Show comment
Hide comment
@rdingwall

rdingwall Mar 29, 2012

Contributor

Ok seems this was happening because a command was failing and retrying (and the command was already used or something). First failure was:

System.Data.OracleClient.OracleException was caught
  Message=ORA-01008: not all variables bound

  Source=System.Data.OracleClient
  ErrorCode=-2146232008
  Code=1008
  StackTrace:
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
       at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
       at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\databases\AdoNetDatabase.cs:line 132
       at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\databases\AdoNetDatabase.cs:line 119
  InnerException: 

This is happening because of incorrect null parameter handling in OracleDatabase.create_parameter. It should be:

private IParameter<IDbDataParameter> create_parameter(string name, DbType type, object value, int? size)
{
    IDbCommand command = server_connection.underlying_type().CreateCommand();
    var parameter = command.CreateParameter();
    command.Dispose();

    parameter.Direction = ParameterDirection.Input;
    parameter.ParameterName = name;
    parameter.DbType = type;
    parameter.Value = value ?? DBNull.Value; // <--- fixed
    if (size != null)
    {
        parameter.Size = size.Value;
    }

    return new AdoNetParameter(parameter);
}

This now works. However I found another bug in some SQL:

SELECT id
FROM (SELECT * FROM {0}_{1}
        WHERE 
            NVL(repository_path, '') = NVL(:repository_path, '') -- fixed
        ORDER BY entry_date DESC)
WHERE ROWNUM < 2

This happens because NULL == NULL does not evaluate to true in PL/SQL. If you do not have a repository path specified, this WHERE clause would always have evaluated to false and thus never returned any rows.

As well I found another bug in insert_version_and_get_version_id:

return Convert.ToInt64((decimal)run_sql_scalar(get_version_id_script(), ConnectionType.Default, select_parameters));

The (decimal) cast is not required here. In fact it actually causes errors - in my case the unneccessary cast was causing a NullReferenceException (because there were no rows in the Version table, run_sql_scalar returned null which cannot be casted to a value type).

I'm at work at the moment but will try to get a pull request to address these issues when I get home.

Contributor

rdingwall commented Mar 29, 2012

Ok seems this was happening because a command was failing and retrying (and the command was already used or something). First failure was:

System.Data.OracleClient.OracleException was caught
  Message=ORA-01008: not all variables bound

  Source=System.Data.OracleClient
  ErrorCode=-2146232008
  Code=1008
  StackTrace:
       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
       at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
       at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\databases\AdoNetDatabase.cs:line 132
       at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\databases\AdoNetDatabase.cs:line 119
  InnerException: 

This is happening because of incorrect null parameter handling in OracleDatabase.create_parameter. It should be:

private IParameter<IDbDataParameter> create_parameter(string name, DbType type, object value, int? size)
{
    IDbCommand command = server_connection.underlying_type().CreateCommand();
    var parameter = command.CreateParameter();
    command.Dispose();

    parameter.Direction = ParameterDirection.Input;
    parameter.ParameterName = name;
    parameter.DbType = type;
    parameter.Value = value ?? DBNull.Value; // <--- fixed
    if (size != null)
    {
        parameter.Size = size.Value;
    }

    return new AdoNetParameter(parameter);
}

This now works. However I found another bug in some SQL:

SELECT id
FROM (SELECT * FROM {0}_{1}
        WHERE 
            NVL(repository_path, '') = NVL(:repository_path, '') -- fixed
        ORDER BY entry_date DESC)
WHERE ROWNUM < 2

This happens because NULL == NULL does not evaluate to true in PL/SQL. If you do not have a repository path specified, this WHERE clause would always have evaluated to false and thus never returned any rows.

As well I found another bug in insert_version_and_get_version_id:

return Convert.ToInt64((decimal)run_sql_scalar(get_version_id_script(), ConnectionType.Default, select_parameters));

The (decimal) cast is not required here. In fact it actually causes errors - in my case the unneccessary cast was causing a NullReferenceException (because there were no rows in the Version table, run_sql_scalar returned null which cannot be casted to a value type).

I'm at work at the moment but will try to get a pull request to address these issues when I get home.

rdingwall added a commit to rdingwall/roundhouse that referenced this issue Apr 2, 2012

rdingwall added a commit to rdingwall/roundhouse that referenced this issue Apr 2, 2012

rdingwall added a commit to rdingwall/roundhouse that referenced this issue Apr 2, 2012

Removed unnecessary decimal cast that was causing exceptions with nul…
…l values - Convert.ToInt64 will handle that already. (issue #58)

ferventcoder added a commit to ferventcoder/roundhouse that referenced this issue Sep 7, 2012

ferventcoder added a commit to ferventcoder/roundhouse that referenced this issue Sep 7, 2012

ferventcoder added a commit to ferventcoder/roundhouse that referenced this issue Sep 7, 2012

Removed unnecessary decimal cast that was causing exceptions with nul…
…l values - Convert.ToInt64 will handle that already. (issue #58)
@ferventcoder

This comment has been minimized.

Show comment
Hide comment
@ferventcoder

ferventcoder Sep 7, 2012

Member

This completed. Thanks.

Member

ferventcoder commented Sep 7, 2012

This completed. Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment