DOCUMENT:Q189677 19-JUL-1998 [vbwin] TITLE :HOWTO: Execute Multiple SQL Action Queries To Oracle Using RDO PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0 OPER/SYS:WINDOWS KEYWORDS: ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, version 5.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) 1.5 stack for the sample in this article. Please refer to the article listed in the REFERENCES section for more information on MDAC 1.5. 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 REFERENCES ========== For additional information on MDAC 1.5, 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 Additional query words: kbDSupport kbMDAC150 kbVBp500 kbVBp kbRDO kbSQL kbOracle kbdse (c) Microsoft Corporation 1998 All Rights Reserved. Contributions by Ron Nelson, Microsoft Corporation ====================================================================== Version : WINDOWS:5.0 Platform : WINDOWS 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 1998.