INFO: Visual Basic 5.0 Accessing an Oracle Database Using ADO

• Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

SUMMARY

With Visual Basic 5.0 and MDAC 1.5, 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

To run the sample code in this article, you will need to download and install the Microsoft Data Access Components v1.5. For instructions on where to get MDAC 1.5 and how to install it, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC
               Driver v2.0

There are two objects that need to be created on the Oracle database; a table (adooracle) and a stored procedure (adoinsert).

NOTE: If you have worked through Microsoft Knowledge Base article Q167225, "HOWTO: Access an Oracle Database Using RDO," then you can use the Oracle objects created in that article (rdooracle and rdoinsert). Just change the following Visual Basic 5.0 code accordingly.

Here are the data definition language (DDL) scripts to create these objects:

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

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

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

The above scripts can be run from SQL*Plus. Once these objects have been created, you can create the Visual Basic project that will use them.

This example project uses a simple form to send a bind parameter to the ADOINSERT 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.

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. Select the Options item from the Tools menu. 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 ADODB.Connection
       Dim CPw1 As ADODB.Command
       Dim CPw2 As ADODB.Command
       Dim Rs As ADODB.Recordset
       Dim Conn As String
       Dim QSQL As String
   

       Private Sub cmdCheck_Click()
   

         CPw1(0) = Val(txtInput.Text)
   

         Set Rs = New ADODB.Recordset
         Rs.CursorType = adOpenStatic
         Rs.LockType = adLockReadOnly
   

         Set Rs = CPw1.Execute
   

         MsgBox "Item_Number = " & Rs(0) & ".  Depot_Number = " & Rs(1) & "."
   

         Rs.Close
   

       End Sub
   

       Private Sub cmdSend_Click()
   

          CPw2(0) = Val(txtInput.Text)
   

          CPw2.Execute
   

          MsgBox "Return value from stored procedure is " & CPw2(1) & "."
   

       End Sub
   

       Private Sub Form_Load()
   

          'You will need to replace the "*" with the appropriate values.
          Conn = "UID=*****;PWD=****;DRIVER={Microsoft ODBC for Oracle};" _
               & "SERVER=*****;"
   
          Set Cn = New ADODB.Connection
   
          With Cn
            .ConnectionString = Conn
            .CursorLocation = adUseClient
            .Open
          End With
   
          QSQL = "Select Item_Number, Depot_Number From adooracle Where " _
          & "item_number = ?"
   
          Set CPw1 = New ADODB.Command
   
          With CPw1
            .ActiveConnection = Cn
            .CommandText = QSQL
            .CommandType = adCmdText
            .Parameters.Append .CreateParameter(, adInteger, adParamInput)
          End With
   
          QSQL = "adoinsert"
   
          Set CPw2 = New ADODB.Command
   
          With CPw2
            .ActiveConnection = Cn
            .CommandText = QSQL
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter(, adInteger, adParamInput)
            .Parameters.Append .CreateParameter(, adDouble, adParamOutput)
          End With
   
       End Sub
   
       Private Sub Form_Unload(Cancel As Integer)
   
          Cn.Close
          Set Cn = Nothing
          Set CPw1 = Nothing
          Set CPw2 = Nothing
   
       End Sub
   
   

5. Run the project.

What follows 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:

   Conn = "UID=<uid>;PWD=<pwd>;DRIVER={Microsoft ODBC for Oracle};" _
           & "SERVER=<MyServer>;"

   Set Cn = New ADODB.Connection

   With Cn
       .ConnectionString = Conn
       .CursorLocation = adUseClient
       .Open
   End With

The connect string that is used to open a connection to an Oracle database (or any database for that matter) is very dependant on the underlying ODBC driver. You can see in the connect string below that the Microsoft Oracle driver you are using is named specifically by DRIVER=:

   Conn = "UID=<uid>;PWD=<pwd>;DRIVER={Microsoft ODBC for Oracle};" _
           & "SERVER==<MyServer>;"

Also in the Form_Load event is the code that creates the two ADO Command objects used in the project:

   QSQL = "Select Item_Number, Depot_Number From adooracle Where " _
   & "item_number = ?"

   Set CPw1 = New ADODB.Command

   With CPw1
       .ActiveConnection = Cn
       .CommandText = QSQL
       .CommandType = adCmdText
       .Parameters.Append .CreateParameter(, adInteger, adParamInput)
   End With

   QSQL = "adoinsert"

   Set CPw2 = New ADODB.Command

   With CPw2
       .ActiveConnection = Cn
       .CommandText = QSQL
       .CommandType = adCmdStoredProc
       .Parameters.Append .CreateParameter(, adInteger, adParamInput)
       .Parameters.Append .CreateParameter(, adDouble, adParamOutput)
   End With

"Use the CommandType property to optimize evaluation of the CommandText property. If the CommandType property value equals adCmdUnknown (the default value), you may experience diminished performance because ADO must make calls to the provider to determine if the CommandText property is an SQL statement, a stored procedure, or a table name. If you know what type of command you're using, setting the CommandType property instructs ADO to go directly to the relevant code. If the CommandType property does not match the type of command in the CommandText property, an error occurs when you call the Execute method."

Using the WITH command, you can create and append parameters to the command object easily. The first parameter of the CreateParameter function is for the name of the parameter. This has been left blank because the sample program uses the index of the parameters collection to identify the individual parameters (such as CPw1(0) to identify the first parameter). The sample program uses adInteger and adDouble datatypes. If it had used a variable length datatype, then the size parameter of the CreateParameter function would need to be set. Again, from the ADO Help HTML:

"If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs."

The remainder of the project is fairly straightforward and well-documented in both the Online Help file and Books Online which come with Visual Basic 5.0. The ADO issues that are critical to working with Oracle (the connect string and the calling of stored procedures) have been detailed in this project.

REFERENCES

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, the ADO HTML that comes with MDAC 1.5, and your Oracle7 server documentation.

Microsoft ODBC Driver for Oracle Help File

ADO Help HTML

"Oracle PL/SQL Programming" by Steven Feuerstein

"Hitchhiker's Guide to Visual Basic & SQL Server" by William Vaughn, Fifth Edition

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

   ARTICLE-ID: Q174679
   TITLE     : HOWTO: Retrieve Resultsets from Oracle Stored Procedures
   
   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC
               Driver v2.0
   
   ARTICLE-ID: Q174981
   TITLE     : HOWTO: Retrieve Typical Resultsets from Oracle Stored
               Procedures
   
   ARTICLE-ID: Q167225
   TITLE     : HOWTO: Access an Oracle Database Using RDO
   
   ARTICLE-ID: Q176086
   TITLE     : HOWTO: Retrieve Recordsets from Oracle Stored Procs
               Using ADO