Q189677: HOWTO: Execute Multiple SQL Action Queries to Oracle Using RDO

Article: Q189677
Product(s): Microsoft Visual Basic for Windows
Version(s): WINDOWS:5.0,6.0
Operating System(s): 
Keyword(s): kbDatabase kbODBC kbOracle kbRDO kbVBp kbVBp500 kbVBp600 kbGrpDSVBDB kbDSupport
Last Modified: 18-APR-2000

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

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

2. Add two CommandButtons to the Form.

3. 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

(c) Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation


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 kbGrpDSVBDB kbDSupport 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Version           : WINDOWS:5.0,6.0
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 1986-2002.