DOCUMENT:Q177358  26-NOV-1997  [vbwin]
TITLE   :HOWTO: Pass a Date Value to a Oracle Stored Procedure
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:vb5all vb5howto

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SUMMARY
=======

This article gives an example of passing a Date datatype value to an Oracle
stored procedure and getting back a result from the stored procedure. The
example below uses RDO 2.0, and the second version, 2.73.7253, of the
Microsoft Oracle ODBC driver. Note that the first version of the Microsoft
Oracle ODBC driver, Ver 2.00.006325, does not support passing dates to a
stored procedure.

This example also demonstrates how the ODBC will take care of converting
the Visual Basic Date to the acceptable Oracle Date format.

MORE INFORMATION
================

Create a new project in Visual Basic and reference the Microsoft Remote
Data Object 2.0 under Project/References.

1. On the new Form add a CommandButton.

2. Paste the following code in the General Declarations section of the
   Form:

   Option Explicit
   Dim Cn As New rdoConnection
   Dim En As rdoEnvironment
   Dim Qd As New rdoQuery
   Dim Rs As rdoResultset
   Dim Conn As String
   Dim tempcnt As Integer

   Private Sub Command1_Click()

       Dim strDate, strQd$, strQd1$
       strDate = InputBox("Enter the date you wish to retrieve:")
       Qd(0) = CDate(strDate)
       Qd.Execute
       strQd = Mid(Qd(1), 12)
       strQd1 = Mid(Qd(1), 1, 11)
       Debug.Print "For the date you entered: " & strQd1 & vbCrLf & strQd
       MsgBox "For the date you entered: " & strQd1 & vbCrLf & strQd

   End Sub

   Private Sub Form_Load()

       Command1.Caption = "Start"
       'Change each <> field to the appropriate value for your system
       Conn = "UID=<uid>;PWD=<password>;" _
       & "driver={Microsoft ODBC for Oracle};SERVER=<server>;"

       Set En = rdoEnvironments(0)
       En.CursorDriver = rdUseOdbc
       With Cn
           .Connect = Conn
           .CursorDriver = rdUseOdbc
           .EstablishConnection rdDriverNoPrompt, False
       End With

       Set Qd.ActiveConnection = Cn

       Qd.SQL = "{call testdate1(?,?)}"
       Qd(0).Direction = rdParamInput
       Qd(1).Direction = rdParamOutput
       Qd(0).Type = rdTypeDATE

   End Sub

   Private Sub Form_Unload(Cancel As Integer)
       Qd.Close
       Cn.Close
       En.Close

   End Sub

3. Create an Oracle stored procedure using this script:

   CREATE OR REPLACE PROCEDURE TESTDATE1(
   /* This is called by a Visual Basic project that passes in */
   /* a Date parameter in any Visual Basic-acceptable format and */
   /* returns a Count value and the input date in */
   /* a different format */

      INDATE IN DATE,
      OUTSTR OUT VARCHAR2 )

   IS

      m_Count NUMBER;

   BEGIN
      SELECT
      COUNT(*) INTO m_Count
      FROM EMP WHERE HIREDATE < INDATE;

      OUTSTR:=TO_CHAR(INDATE,'DD-Mon-YYYY') ||
      'The Count is: '|| TO_CHAR(m_Count);

   END;
   /

Run the project by pressing the F5 key and, when prompted, enter a date in
any Visual Basic-acceptable date format that is greater than Jan 1, 1983 to
get a count value of all records in the EMP table.

Additional Information:

1. This stored procedure uses the DEMO account EMP table supplied in a
   standard Oracle 7.3 installation.

2. This demonstrates that the Microsoft Oracle ODBC driver will handle Date
   conversion from an acceptable Visual Basic format to an acceptable
   Oracle format.

The return should be a count of 14 and the date that was entered in
standard military format of DD-MON-YYYY.

REFERENCES
==========

For additional information, please see the following article in the
Microsoft Knowledge Base:

   ARTICLE-ID: Q167225
   TITLE     : HOWTO: Access an Oracle Database Using RDO
======================================================================
Keywords          : vb5all vb5howto
Technology        : kbodbc
Version           : WINDOWS:5.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.