Quirky Oracle parameter queries

VBAHole22

VBAHole22

Well-known member
Joined
Oct 21, 2003
Messages
432
Location
VA
I know how to send Oracle queries in with parameters but I am having trouble with a few particularly sticky ones involving Sequences and Sysdate.
What I want to do is send a record in with an insert statement and then turn around and get the sequence number that was applied to it. I can get this done with SQL text strings like so:

Record goes in
PHP: 
 strRequestInsert = "INSERT INTO aTable (REQUESTID, REQUESTSTARTDATE, REQUESTPP, REQUESTUSER," & _
        " REQUESTCOMPLETED) VALUES (VIZ_REQUEST_SEQ.NEXTVAL, SYSDATE," & PPMS & ", " & strUser & ", N)"

Sequence comes out:
PHP: 
strRequestID = "SELECT VIZ_REQUEST_SEQ.CURRVAL from aTable"

But parameterizing this hasnt been trivial. I keep getting errors about bad data values. I realize that I could substitute .NETs Now for Oracles SYSDATE but I shouldnt have to and one is client time and the other is server time.
Any suggestions?
 
I think I am narrowing in on what the problem is. I was using placeholders with numbers like :1
I replaces these with the names of the parameters themselves and got some better results but I am still hung up on the dates. Here is what I am trying to send to Oracle:

PHP: 
INSERT INTO VIZ_TRAN (TRAN_ID, TRAN_STARTTIME, DGN_PATH) VALUES (VIZ_TRAN_SEQ.NEXTVAL, TO_DATE(14-Dec-2004 10:56:47,DD-MON-YYYY HH24:MI:SS), //501RICADD/PROJECTS$/11314/D11314/11314CON3.DGN)

And here is how I am trying to get it done:

Code: 
Private Function LogTranStartStatement() As OracleCommand
            Dim command As New OracleCommand
            command.CommandType = CommandType.Text
            command.Connection = Database.GetConnection()
             command.CommandText = "INSERT INTO " & TransactionTable & " (TRAN_ID, TRAN_STARTTIME, DGN_PATH)" & _
             " VALUES (:TRAN_ID, :TRAN_STARTTIME, :DGN_PATH)"

            command.Parameters.Add("TRAN_ID", "VIZ_TRAN_SEQ.NEXTVAL")
            command.Parameters.Add("TRAN_STARTTIME", "TO_DATE(" & Me.trStartTime.ToString("dd-MMM-yyyy HH:mm:ss") & ",DD-MON-YYYY HH24:MI:SS)")
            command.Parameters.Add("DGN_PATH", Me.trPath)
            Return command
        End Function LogTranStartStatement

It might be getting hung up on the sequence value. But I know its getting hung on the date. I think the problem is that .NET is treating it as a string parmeter and so is enclosing it with a delimiter like or "
I dont need that but I dont know how else to get it done.

I supposed I could go to stored procedures, but there should be a way to get this done .NET stylee.
 
Back
Top