How To Execute Multiple SQL Action Queries to Oracle Using RDO

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