This discussion is archived
0 Replies Latest reply: Jan 26, 2013 11:07 AM by 987329 RSS

Oracle date parameter query not working?

987329 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points