HOWTO: Execute Multiple SQL Action Queries to Oracle Using RDO

ID: Q189677

The information in this article applies to:

Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SUMMARY

Sometimes it is advantageous to commit several SQL statements that do action queries on a single Execute statement. This would normally be the case if working over a slow WAN line and batching these action queries will result in performance gains. This article shows how to Execute a batch of SQL Insert statements on a single RDO Execute command.

MORE INFORMATION

The sample Visual Basic project that follows will create a single SQL statement that contains a number of SQL Insert statements for inserting rows into an Oracle table. This application works against an Oracle table that has a Primary Key (PK) column, so to avoid inserting rows with duplicate PK values, the SQL statement also creates a unique value for the PK. To make this work, each Insert statement must be terminated with a CR/LF and wrapped in a BEGIN/END statement. The statement that is sent to the server will look something like this:


   BEGIN
   INSERT something
   DELETE something
   UPDATE something
   etc.
   END;

In this example, you are only inserting rows.

This application works against an Oracle table that is defined by this script:


     CREATE TABLE Insert_Test (
     ID       NUMBER(3) NOT NULL PRIMARY KEY,
     FldOne   VARCHAR2(20)
     );

NOTE: You need to acquire and install the Microsoft Data Access Components (MDAC) for the sample in this article. Please refer to the article listed in the REFERENCES section for more information on MDAC.

Step-by-Step

To create the application, open a new Standard EXE project and follow these steps:

Under Project - References, make reference to Microsoft Remote Data Objects 2.0.

Add two CommandButtons to the Form.

Paste the following code into the Form's General Declarations Section:


     'This Connect string does a DSN-Less connection
     'Change the settings of the Connect string to match your setup.
     Const gstrConnect As String =  _
             "DRIVER={Microsoft ODBC for Oracle};" & _
             "CONNECTSTRING=MyServer;" & _
             "UID=MyUID;" & _
             "PWD=MyPassword;"
     Dim MyConn As rdoConnection
     Dim MyRS As rdoResultset

     Private Sub Form_Load()
          rdoEnvironments(0).CursorDriver = rdUseClientBatch
          Set MyConn = rdoEnvironments(0).OpenConnection _
          ("", rdDriverNoPrompt, False, gstrConnect)
          Command1.Caption = "Insert"
          Command2.Caption = "Delete"
          Command1.Enabled = False

          Caption = "Opening Connection..."
          Debug.Print MyConn.Connect
          Command1.Enabled = True
     End Sub

     Private Sub Command1_Click()
          Dim strSQL, strSQL1, strSQL2, strSQL3 As String
          Dim i As Integer

          Caption = "Running Query..."

          ' Create the SQL statement

          strSQL1 = "Insert INTO Insert_Test (ID, FldOne) Values ("
          strSQL2 = ", 'HELLO');"
          strSQL3 = "BEGIN " & vbCrLf
          i = 1

          For i = i To 10
              strSQL3 = strSQL3 & strSQL1 & i & strSQL2 & vbCrLf

          Next i
          strSQL3 = strSQL3 & "END;"
          Debug.Print strSQL3

          MyConn.Execute strSQL3

          strSQL = "SELECT * FROM Insert_Test"
          Set MyRS = MyConn.OpenResultset(strSQL, _
                     rdOpenStatic, _
                     rdConcurRowVer)
          Debug.Print "The Rowcount is " & MyRS.RowCount
          Do Until MyRS.EOF
              Debug.Print MyRS(0) & ", " & MyRS(1)
              MyRS.MoveNext
          Loop
          Command2.Enabled = True

          Caption = "Complete..."

     End Sub

     Private Sub Command2_Click()
               ' Delete all rows from the table
               MyConn.Execute "DELETE FROM Insert_Test"
               Command2.Enabled = False
     End Sub

     Private Sub Form_Unload(Cancel As Integer)
         MyConn.Close
         Set MyConn = Nothing
     End Sub

REFERENCES

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

Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Additional query words:

Keywords : kbDatabase kbODBC kbOracle kbRDO kbVBp kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC210SP2
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbhowto