Microsoft KB Archive/189677

= How To Execute Multiple SQL Action Queries to Oracle Using RDO =

Article ID: 189677

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q189677



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



