Visual Basic Script: Don't Assume Your VB Constants are Available in Your Scripts

From the enough to drive you crazy department…

I was working on a small script for a system we have. The goal was to import from an Access database into Oracle. The catch, one field in the Access database is importing into a CLOB field in Oracle. It looks something like this:

Access:
     TableWithMemo
          Whatever (TexT)
          the_field (Memo)
Oracle:
     Test
          F1( varchar2(255)
          F2(CLOB)

So I wrote the following VB program:
Sub main()
    'The recordset objects for Access and oracle
    Dim access_con As New ADODB.Connection
    Dim access_com As New ADODB.Command
    Dim access_rs As ADODB.Recordset
  
      
    Dim oracle_con As New ADODB.Connection
    Dim oracle_com As New ADODB.Command
    Dim oracle_rs As New ADODB.Recordset
    
        
    'open the Access Database
    access_con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\ db2.mdb;Persist Security Info=False"
    access_con.CursorLocation = adUseClient
    access_con.Open
      
    access_com.ActiveConnection = access_con
    access_com.CommandType = adCmdText
    access_com.CommandText = "select * from TableWithMemo"
    
    Set access_rs = access_com.Execute
    
    'Now open the Oracle database. The key to getting the CLOB/BLOB fields is using the Oracle OleDB data provider
    'instead of the Microsoft one. If you use the Microsoft one, you will get a "Data Type Not Supported" error
    'also, I was getting some goofy errors having to do with the update statement, so I changed the connection type
    'to a Client Side Cursor and opened in Read/Write mode
    oracle_con.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=$$$$;Persist Security Info=True;User ID=$$$$;Data Source=tsqa"
    oracle_con.CursorLocation = adUseClient
    oracle_con.Mode = adModeReadWrite
    oracle_con.Open
    
    oracle_com.ActiveConnection = oracle_con
    oracle_com.CommandType = adCmdTable
    oracle_com.CommandText = "TEST"
    
    'I also set the locktype to optimistic to deal with the fail on update
    oracle_rs.LockType = adLockOptimistic
    oracle_rs.Open oracle_com
    
    'yeah yeah, I know I don't need to check equality for a boolean... clarity and all that
    If (oracle_rs.Supports(adAddNew) = False) Then
        MsgBox ("This recordset cannot update")
        oracle_con.Close
        access_con.Close
        
        Set oracle_rs = Nothing
        Set oracle_com = Nothing
        Set oracle_con = Nothing
        
        Set access_rs = Nothing
        Set access_com = Nothing
        Set access_con = Nothing
        
        Exit Sub
    End If
      
    'Pull in as clob
    While Not (access_rs.EOF)
        oracle_rs.AddNew
        
        oracle_rs("f1") = access_rs("whatever")
        oracle_rs("f2") = access_rs("the_field")
        
        oracle_rs.Update
        
        access_rs.MoveNext
    Wend
    
    oracle_rs.Close
    oracle_con.Close
    
    access_con.Close
    
    Set oracle_rs = Nothing
    Set oracle_com = Nothing
    Set oracle_con = Nothing
    
    Set access_rs = Nothing
    Set access_com = Nothing
    Set access_con = Nothing
End Sub

Great, now with 1 problem. It was requested that I make this a VBScript file so it can easily be edited. “No Problem” I think to myself, so I spit out the following modification:

    'The recordset objects for Access and oracle
    Dim access_con
    Dim access_com
    Dim access_rs
    Set access_con = CreateObject("ADODB.Connection")
    Set access_com = CreateObject("ADODB.Command")
    
    Dim oracle_con
    Dim oracle_com
    Dim oracle_rs
    Set oracle_con = CreateObject("ADODB.Connection")
    Set oracle_com = CreateObject("ADODB.Command")
    Set oracle_rs = CreateObject("ADODB.Recordset")
    
    'open the Access Database
    access_con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\db2.mdb;Persist Security Info=False"
    access_con.CursorLocation = adUseClient
    access_con.Open
      
    access_com.ActiveConnection = access_con
   'access_com.CommandType = adCmdText
    access_com.CommandText = "select * from TableWithMemo"
    
    Set access_rs = access_com.Execute
    
    'Now open the Oracle database. The key to getting the CLOB/BLOB fields is using the Oracle OleDB data provider
    'instead of the Microsoft one. If you use the Microsoft one, you will get a "Data Type Not Supported" error
    'also, I was getting some goofy errors having to do with the update statement, so I changed the connection type
    'to a Client Side Cursor and opened in Read/Write mode
    oracle_con.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=$$$$;Persist Security Info=True;User ID=$$$$;Data Source=tsqa"
    oracle_con.CursorLocation = adUseClient
    oracle_con.Mode = adModeReadWrite
    oracle_con.Open
    
    oracle_com.ActiveConnection = oracle_con
    oracle_com.CommandType = adCmdTable
    oracle_com.CommandText = "test"
    
    'I also set the locktype to optimistic to deal with the fail on update
    oracle_rs.LockType = adLockOptimistic
    oracle_rs.Open oracle_com
    
  
    'Pull in as clob
    While Not (access_rs.EOF)
        oracle_rs.AddNew
        
        oracle_rs("f1") = access_rs("whatever")
        oracle_rs("f2") = access_rs("the_field")
        
        oracle_rs.Update
        
        access_rs.MoveNext
    Wend
    
    oracle_rs.Close
    oracle_con.Close
    
    access_con.Close
    
    Set oracle_rs = Nothing
    Set oracle_com = Nothing
    Set oracle_con = Nothing
    
    Set access_rs = Nothing
    Set access_com = Nothing
    Set access_con = Nothing

Well, this causes the script to bomb constantly with the following errors:

ADODB.Connection: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

So after banging my head against the monitor for about an hour, it finally dawns on me. The constants do not exist in scripting. So I need to change all of my parameter assignments (CursorLocation, Mode, CommandType) to their numeric equivilents, or assign constants for each. In the end, the following code worked like a champ for me:

    'The recordset objects for Access and oracle
    Dim access_con
    Dim access_com
    Dim access_rs
    Set access_con = CreateObject("ADODB.Connection")
    Set access_com = CreateObject("ADODB.Command")
    
    Dim oracle_con
    Dim oracle_com
    Dim oracle_rs
    Set oracle_con = CreateObject("ADODB.Connection")
    Set oracle_com = CreateObject("ADODB.Command")
    Set oracle_rs = CreateObject("ADODB.Recordset")
    
    'open the Access Database
    access_con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\db2.mdb;Persist Security Info=False"
    access_con.CursorLocation = 3
    access_con.Open
      
    access_com.ActiveConnection = access_con
    access_com.CommandType = 1
    access_com.CommandText = "select * from TableWithMemo"
    
    Set access_rs = access_com.Execute
    
    'Now open the Oracle database. The key to getting the CLOB/BLOB fields is using the Oracle OleDB data provider
    'instead of the Microsoft one. If you use the Microsoft one, you will get a "Data Type Not Supported" error
    'also, I was getting some goofy errors having to do with the update statement, so I changed the connection type
    'to a Client Side Cursor and opened in Read/Write mode
    oracle_con.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=$$$$;Persist Security Info=True;User ID=$$$$;Data Source=tsqa"
    oracle_con.CursorLocation = 3
    oracle_con.Mode = 3
    oracle_con.Open
    
    oracle_com.ActiveConnection = oracle_con
    oracle_com.CommandType = 2
    oracle_com.CommandText = "test"
    
    'I also set the locktype to optimistic to deal with the fail on update
    oracle_rs.LockType = 3
    oracle_rs.Open oracle_com
    
  
    'Pull in as clob
    While Not (access_rs.EOF)
        oracle_rs.AddNew
        
        oracle_rs("f1") = access_rs("whatever")
        oracle_rs("f2") = access_rs("the_field")
        
        oracle_rs.Update
        
        access_rs.MoveNext
    Wend
    
    oracle_rs.Close
    oracle_con.Close
    
    access_con.Close
    
    Set oracle_rs = Nothing
    Set oracle_com = Nothing
    Set oracle_con = Nothing
    
    Set access_rs = Nothing
    Set access_com = Nothing
    Set access_con = Nothing

While scripting wasn’t a bad way to go, I assumed that constants had been defined despite the lack of includes. My mind fell victim to the fact that I was working in a VB syntax, so I assumed that the same VB rules applied to scripting as it does to the full fledged environment. After some searching, this appears to be a problem when working with ASP as well. How strange that with all the questions that were asked along these lines, I only found a handful or correct answers. If you are working in ASP, be sure to include the ADOASP.INC file with your projects if your expecting the constants to work.