HOWTO: Access an Oracle Database Using RDO

• Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

SUMMARY

With Visual Basic 5.0 Enterprise edition, you have the ability to connect to an Oracle database through a DSN-Less connection, execute a stored procedure using parameters, and get return values from that stored procedure. The example in this article illustrates all of this functionality.

MORE INFORMATION

The example in this article is a simple Visual Basic 5.0 project that creates a DSN-Less connection against an Oracle database and executes a stored procedure. When working with Oracle and the Microsoft ODBC Driver for Oracle, there are some settings that are different than working with Microsoft SQL Server. These differences occur because you are using a different ODBC driver.

The Visual Basic 5.0 Enterprise edition includes RDO 2.0 and the Microsoft ODBC Driver for Oracle. Both of these need to be installed in order to run the example project in this article.

NOTE: The ODBC driver from Oracle does not support the entire RDO 2.0 feature set (including the calling of stored procedures and binding return values).

The following example was created against an Oracle 7.3 database through a SQL*Net 2.3 connection. All of the following code (including the stored procedure) should work fine with Oracle 7.2. But, the Microsoft ODBC Driver for Oracle Help file states that it only supports SQL*Net 2.3

There are two objects that need to be created on the Oracle database; a table (rdooracle) and a stored procedure (rdoinsert). Following are the data definition language (DDL) scripts to create these objects:

• RDOORACLE: This is just a two-column table with the first column set as the primary key:

        CREATE TABLE rdooracle (
                  item_number    NUMBER(3) PRIMARY KEY,
                  depot_number   NUMBER(3));
  
  

• RDOINSERT: This procedure accepts a single numeric input parameter and returns a single numeric output parameter. The input parameter is first used by an input statement then it is divided by 2 and set as the output parameter:

        CREATE OR REPLACE PROCEDURE rdoinsert
           (insnum IN NUMBER, outnum OUT NUMBER)
        IS
        BEGIN
        INSERT INTO rdooracle
           (Item_Number, Depot_Number)
        VALUES
           (insnum, 16);
        outnum := insnum/2;
        END;
        /
  
  

The above scripts can be run from SQL*Plus or from within the Visual Database Tools Add-In in the Visual Basic 5.0 Enterprise edition. Once these objects have been created, you can create the Visual Basic project that will use them.

Step-by-Step Example

This example project uses a simple form to send a bind parameter to the RDOINSERT stored procedure and then return the output parameter from that procedure. Here are the steps to create the project:

1. Open a new project in Visual Basic 5.0 Enterprise edition. Form1 is created by default.

2. Place the following controls on the form:

      Control     Name     Text/Caption
      ---------------------------------
      Button      cmdCheck  Check
      Button      cmdSend   Send
      Text Box    txtInput
      Label       lblInput  Input:
   
   

3. From the Tools menu, select the Options item. Click the "Default Full Module View" option and then click OK. This will allow you to view all of the code for this project.

4. Paste the following code into your code window:

         Option Explicit
         Dim Cn As rdoConnection
         Dim En As rdoEnvironment
         Dim CPw As rdoQuery
         Dim Rs As rdoResultset
         Dim Conn As String
         Dim QSQL As String
         Dim Response As String
         Dim Prompt As String
   

         Private Sub cmdCheck_Click()
   

             QSQL = "Select Item_Number, Depot_Number From rdooracle Where " _
             & "item_number =" & txtInput.Text
             Set Rs = Cn.OpenResultset(QSQL, rdOpenStatic, , rdExecDirect)
   
             Prompt = "Item_Number = " & Rs(0) & ".  Depot_Number = " _
             & Rs(1) & "."
   
             Response = MsgBox(Prompt, , "Query Results")
   
             Rs.Close
   
         End Sub
   
         Private Sub cmdSend_Click()
   
             CPw(0) = Val(txtInput.Text)
             CPw.Execute
   
             Prompt = "Return value from stored procedure is " & CPw(1) & "."
             Response = MsgBox(Prompt, , "Stored Procedure Result")
   
         End Sub
   
         Private Sub Form_Load()
   
             Conn = "UID=;PWD=;driver={Microsoft ODBC Driver for Oracle};" _
                  & "CONNECTSTRING=MyOracle;"
   
             Set En = rdoEnvironments(0)
             Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn)
             QSQL = "{call rdoinsert(?,?)}"
             Set CPw = Cn.CreateQuery("", QSQL)
   
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
   
             En.Close
   
         End Sub
   
   

5. Run the project.

Following is a detailed explanation of the code used in this demonstration project:

The Form_Load event contains the code that creates the DSN-Less connection. It also contains the code that creates the query that calls the stored procedure:

   Private Sub Form_Load()

       Conn = "UID=;PWD=;driver={Microsoft ODBC Driver for Oracle};" _
            & "CONNECTSTRING=MyOracle;"

       Set En = rdoEnvironments(0)
       Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn)
       QSQL = "{call rdoinsert(?,?)}"
       Set CPw = Cn.CreateQuery("", QSQL)

   End Sub

The connect string used to open a connection to an Oracle database (or any database) is very dependent upon the underlying ODBC driver. Even though similar parameters are used by most ODBC drivers (UID, PWD, etc.), the connect string used here will work only with the Microsoft ODBC Driver for Oracle:

   Conn = "UID=;PWD=;driver={Microsoft ODBC Driver for Oracle};" _
            & "CONNECTSTRING=MyOracle;"

Also in the Form_Load event is the code that creates the query object that calls the stored procedure:

   QSQL = "{call rdoinsert(?,?)}"
   Set CPw = Cn.CreateQuery("", QSQL)

The remainder of the project is fairly straight-forward and well-documented in both the online Help file and Books Online, both of which come with Visual Basic 5.0. The RDO issues critical to working with Oracle, the connect string, and the calling of stored procedures have been detailed in this project. For more information on these issues, please consult your Oracle SQL*Net 2.3 documentation, the Help file for the Microsoft ODBC Driver for Oracle, and your Oracle 7 server documentation.

REFERENCES

Visual Basic 5.0 Enterprise Edition Help files

Oracle SQL*Net 2.3 Help file

Hitchhiker’s Guide to Visual Basic & SQL Server - Fourth Edition.