Re: Passing date value from VB to Oracle Stored Procedure

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/03/25
Message-ID: <6fallv$j2f$3@news02.btx.dtag.de>#1/1

On Tue, 24 Mar 1998 11:23:21 -0500, "Melinda Schall" <no.one_at_daytonoh.ncr.com> wrote: Hi Melinda,

I know are two ways to pass date-values from VB to oracle sp's:

1. using ODBC and ExecuteSQL or Execute with SQL_PASSTHROUGH specified: Pass the date-value as string like: format$(you_datevalue,"MM/DD/YYYY"). Supposing your sp is declared as my_proc(p_date in VARCHAR2(20)) then you should define: Procedure my_proc(p_date in VARCHAR2(20)) is l_date Date; Begin l_date := to_date(p_date,'MM/DD/YYYY'); <other statements> End my_proc;
2. using oracles objects for ole and Parameters: Suppossing your sp is declared as my_proc(p_date in DATE)

        your VB-call might look like:

Dim oLclOraDatabase As Object
Dim szLclErrorMsg As String
Dim szLclSQL As String
Dim fLclDuringtrans As Integer

On Error GoTo OracleSPError

    fLclDuringtrans = False

    Set oLclOraDatabase = oGblOraSession.OpenDatabase(szGblMaklerDB, szGblUSER & "/" & szGblPWD, 0&)

    oLclOraDatabase.Parameters.Add "MyDate", Date, ORAPARM_INPUT     oLclOraDatabase.Parameters("MyDate").ServerType = ORATYPE_DATE  

    oLclOraDatabase.Parameters("MyDate").Value = DateValue(My_VB_Datevalue)

    szLclSQL = "Begin my_proc(:MyDate ); End;"

    oGblOraSession.DbBeginTrans
    fLclDuringtrans = True

        oLclOraDatabase.DbExecuteSQL (szLclSQL)

    oGblOraSession.DbCommitTrans
    fLclDuringtrans = False

    Set oLclOraDatabase = Nothing

    On Error GoTo 0
    Exit Sub

OracleSPError:
'

    If oLclOraDatabase.LastServerErr <> 0 Or oLclOraDatabase.LastServerErrText <> "" Then

        szLclErrorMsg = oLclOraDatabase.LastServerErr & " " & oLclOraDatabase.LastServerErrText     Else

        szLclErrorMsg = Error
    End If

    MsgBox szLclErrorMsg , MB_FEHLER, Str$(Err)

    If fLclDuringtrans Then

        oGblOraSession.DbRollback
        fLclDuringtrans    = False

    End If

    Set oLclOraDatabase = Nothing

    On Error GoTo 0
    Exit Sub
End Function

>Our developers are trying to pass a date value from VB to an Oracle Stored
>Procedure and it is not working. The stored procedure's input parameter is
>defined as DATE. The VB code wants to pass the date format of MM/DD/YYYY.
>I've tried having them pass the value as a string containing the to_date
>function, i.e. to_date(vb_date, 'MM/DD/YYYY'), to the stored procedure but
>this fails as well. It seems like there must be a simple/obvious thing
>wrong...but it's eluding me and the other database developers here.
>
>Thanks in advance for any information.

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de

Received on Wed Mar 25 1998 - 00:00:00 CST