Solved

Passing Oracle's SYSDATE using ADO's CreateParameter

Posted on 2000-04-24
5
2,553 Views
Last Modified: 2012-06-22
Does anyone know how to pass Oracle's SYSDATE as an input parameter to a stored procedure, using ADO's CreateParameter statement?

Example:

Set testParm = testCmd.CreateParameter("Time_Stamp", adDate, adParamInput, , SYSDATE)

Thanks!
0
Comment
Question by:marcmest
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:epretti
ID: 2745682
There is no way to do that in this way .. because sysdate is a Oracle function, if you want to set the sysdate there what you have to do is, in the SQL put this, for ex.

"INSERT INTO [Table Name] Values (?, ?, SYSDATE())"

add to the command the parameters that you need and put the function in the SQL, not in a parameter. Bye :-)
0
 
LVL 18

Accepted Solution

by:
mdougan earned 50 total points
ID: 2747455
I've created a VB function to get the SysDate from Oracle:

Function SysDate() As Variant
'*************************************************************************************************************************
'   Retrieves the current date/time from the database server
'   Example call:
'       SQL = SQL + "SET LASTUPDATED = " & ToDate(SysDate())
'*************************************************************************************************************************
Dim sErrors As String
Dim sSQL As String
Dim vReturn As Variant
Dim lrs      As Recordset

    On Error GoTo SysDateErr
   
    SysDate = ""
    vReturn = ""
   
    sSQL = "SELECT SYSDATE AS SERVERDATE FROM DUAL"
   
    Set lrs = New ADODB.Recordset
    lrs.CacheSize = 100
    CN.CursorLocation = adUseServer
       
    lrs.Open sSQL, CN, adOpenForwardOnly, adLockReadOnly, adCmdText
   
    If Not lrs.BOF And Not lrs.EOF Then
        If Not IsNull(lrs("SERVERDATE")) Then
            If IsDate(lrs("SERVERDATE")) Then
                vReturn = Format(lrs("SERVERDATE"), "m/d/yyyy hh:nn:ss AM/PM")
            End If
        End If
    End If

    SysDate = vReturn
    lrs.Close
    Set lrs = Nothing
   
SysDateExit:
    Exit Function
SysDateErr:
    Screen.MousePointer = 0
    sErrors = "SysDate " & Err.Description
    frmMessage.MessageBox sErrors, vbCritical, Err & ""
    Resume SysDateExit

End Function

So, you're function call would be:

Set testParm = testCmd.CreateParameter("Time_Stamp", adDate, adParamInput, , SYSDATE())
0
 
LVL 1

Expert Comment

by:epretti
ID: 2748334
Sorry, the Sql is
"INSERT INTO Table_Name Values (?, ?, SYSDATE)"

Why do you say it is wrong? What are your trying to do?
0
 

Author Comment

by:marcmest
ID: 5667446
Many months later...

Another way I thought of to accomplish this is to pass either Null or some bogus date like 1/1/1900 to indicate to the stored procedure that Sysdate should be used.

Then in the stored procedure, check for these values and use SYSDATE as a substitute value using NVL or DECODE when appropriate...

May need to pass dates in as Varchar2 instead of date to accomplish some of this...
0
 
LVL 18

Expert Comment

by:mdougan
ID: 5731646
I guess the question is, will the date parameter sometimes not be the SYSDATE?  If it will always be the SYSDATE then the stored procedure shouldn't need to have this passed in as a parameter, you'd just include it in your select or update statement.

Another thought though, some guys on my latest SQL Server project have been talking about using Optional parameters to the Stored Proc.  I don't know if Oracle supports optional parameters or not, but you might check to see if it does.  If so, then you might be able to define SYSDATE as the default.  Something like:

Create Procedure MyProc(
@UserID    varchar(10),
@HireDate  datetime default SYSDATE)
AS

Select * from Users
Where UserID = @UserID
And HireDate >= @HireDate
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now