0 Replies Latest reply on Jan 26, 2013 7:07 PM by 987329

    Oracle date parameter query not working?

    987329
      http://stackoverflow.com/questions/14539489/oracle-date-parameter-query-not-working



      Trying to run the below query, but always fails even though the parameter values matches. I'm thinking there is a precision issue for :xRowVersion_prev parameter. I want too keep as much precision as possible.

      Delete
      from CONCURRENCYTESTITEMS
      where ITEMID = :xItemId
      and ROWVERSION = :xRowVersion_prev

      The Oracle Rowversion is a TimestampLTZ and so is the oracle parameter type.

      The same code & query works in Sql Server, but not Oracle.

      Public Function CreateConnection() As IDbConnection
      Dim sl As New SettingsLoader
      Dim cs As String = sl.ObtainConnectionString
      Dim cn As OracleConnection = New OracleConnection(cs)

      cn.Open()
      Return cn
      End Function

      Public Function CreateCommand(connection As IDbConnection) As IDbCommand
      Dim cmd As OracleCommand = DirectCast(connection.CreateCommand, OracleCommand)
      cmd.BindByName = True
      Return cmd
      End Function

      <TestMethod()>
      <TestCategory("Oracle")> _
      Public Sub Test_POC_Delete()

      Dim connection As IDbConnection = CreateConnection()

      Dim rowver As DateTime = DateTime.Now
      Dim id As Decimal

      Using cmd As IDbCommand = CreateCommand(connection)

      cmd.CommandText = "insert into CONCURRENCYTESTITEMS values(SEQ_CONCURRENCYTESTITEMS.nextval,'bla bla bla',:xRowVersion) returning ITEMID into :myOutputParameter"
      Dim p As OracleParameter = New OracleParameter
      p.Direction = ParameterDirection.ReturnValue
      p.DbType = DbType.Decimal
      p.ParameterName = "myOutputParameter"
      cmd.Parameters.Add(p)

      Dim v As OracleParameter = New OracleParameter
      v.Direction = ParameterDirection.Input
      v.OracleDbType = OracleDbType.TimeStampLTZ
      v.ParameterName = "xRowVersion"
      v.Value = rowver
      cmd.Parameters.Add(v)


      cmd.ExecuteNonQuery()
      id = CType(p.Value, Decimal)


      End Using

      Using cmd As IDbCommand = m_DBTypesFactory.CreateCommand(connection)
      cmd.CommandText = " Delete from CONCURRENCYTESTITEMS where ITEMID = :xItemId and ROWVERSION = :xRowVersion_prev"

      Dim p As OracleParameter = New OracleParameter
      p.Direction = ParameterDirection.Input
      p.DbType = DbType.Decimal
      p.ParameterName = "xItemId"
      p.Value = id
      cmd.Parameters.Add(p)

      Dim v As OracleParameter = New OracleParameter
      v.Direction = ParameterDirection.Input
      v.OracleDbType = OracleDbType.TimeStampLTZ
      v.ParameterName = "xRowVersion_prev"
      v.Value = rowver
      v.Precision = 6 '????
      cmd.Parameters.Add(v)

      Dim cnt As Integer = cmd.ExecuteNonQuery()
      If cnt = 0 Then Assert.Fail() 'should delete
      End Using

      connection.Close()
      End Sub

      Schema:

      -- ****** Object: Table SYSTEM.CONCURRENCYTESTITEMS Script Date: 1/26/2013 11:56:50 AM ******
      CREATE TABLE "CONCURRENCYTESTITEMS" (
      "ITEMID" NUMBER(19,0) NOT NULL,
      "NOTES" NCHAR(200) NOT NULL,
      "ROWVERSION" TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL)
      STORAGE (
      NEXT 1048576 )

      Sequence:

      -- ****** Object: Sequence SYSTEM.SEQ_CONCURRENCYTESTITEMS Script Date: 1/26/2013 12:12:48 PM ******
      CREATE SEQUENCE "SEQ_CONCURRENCYTESTITEMS"
      START WITH 1
      CACHE 20
      MAXVALUE 9999999999999999999999999999