?
Solved

Passing Oracle's SYSDATE using ADO's CreateParameter

Posted on 2000-04-24
5
Medium Priority
?
2,636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 150 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

719 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