Parameter Array ( oracle) and VB 1

[jabond007]

hello

could sombody tell mehow to insert values into a Parameter array. My codeis as follows. I am entering two values ( varchar2 and number type) from an array. I am getting an error at oraPArray1.putValue
I have refrenced OracleINProcess server

the code is as follows

Public Sub BatchInsertOra_Insert(VRow() As Variant)
Dim OraSession As OraSession
Dim OraDatabase As OracleInProcServer.OraDatabase
Dim strSQL As String
Dim oraPArray1 As Object
Dim oraPArray2 As Object
Dim startTime
Dim EndTime
Dim i As Integer

Set OraSession = CreateObject("OracleInProcServer.XOraSession&quot
Set OraDatabase = OraSession.OpenDatabase("bsdwd", "xpnolan/xpnolan7", 0)

OraDatabase.Parameters.AddTable "Ticker", 1, 1, 1000, 5
OraDatabase.Parameters.AddTable "Price", ORAPARM_INPUT, ORATYPE_NUMBER, 1000

Set oraPArray1 = OraDatabase.Parameters("Ticker&quot
Set oraPArray2 = OraDatabase.Parameters("Price&quot

OraDatabase.ExecuteSQL ("Delete from temp_dde_price&quot

startTime = Timer
For i = 1 To 1000
oraPArray1.put_values VRow(i, 1)
oraPArray2.put_values VRow(i, 2)
Next
OraDatabase.ExecuteSQL ("INSERT into temp_dde_price VALUES ticker, rice)&quot
EndTime = Timer

MsgBox "Insert time = " & EndTime - startTime
End Sub

Could somebody tell me the reason

 

[jabond007]

Somebody please help me

 

[chiph]

oraPArray1.put_values VRow(i, 1)

If I understand your code correctly, VRow is a one-dimensional array of Variants. So why are you indexing on it like it were a two-dimensional array? Try this:
[tt]
oraPArray1.put_values VRow(i)[/tt]

Chip H.

 

[jabond007]

VRow is a two dimensional array.

oraPArray1.put_values VRow(i,1) I get an error here stating method doesnt exist.

Basically i am trying to insert data into a database from a 2 dimesional array. Could u please tell me the fastest way to do it. I tried INSERT,Stored Procedure and both takes 15 seconds. I need it to be 5 secs or lesser....So i was trying batch update using the Parameter arrays. but that fails

 

[chiph]

OK, I missed that.

I think we might need to check the simple things here....

- Make sure that the two oraPArray variables are not nothing.
- Make sure that the call to OraDatabase.ExecuteSQL doesn't return an error.
- Make sure that method put_values exists in object oraPArray1 (i.e. check your spelling)
- Explicitly convert your variant array element to a datatype the put_values function can accept (string, double, etc).

You might need to set another "mode" on the OraDatabase object so that your SQL is automatically committed. I'm not sure about this last one, as I have only done ADO to Oracle, and not used their type library before.

Chip H.

 

[jabond007]

thanks chip


ur post was helpful.....it really made me think.....I found the problem
It should have been put_value and not put_values. Methods are not listed, so i had this problem.

Thanks for ur help

 

[chiph]

Glad to help out, jabond007.

I went back and looked at your code, and your oraPArray1 variable was declared as being of type Object. When you do this, it does two things -- the first is that this variable is late-bound. Which means that the run-time library has to work to determine the datatype and available methods every time you access it. Result is a slower program. OTOH, if you intended to do this (like when you use an optional component, or intentionally want to do dynamic binding), then it's OK.

The other thing declaring variables as Object does is disable the little pop-up window that prompts you for the names of methods and properties. This would have helped you in this case.

Chip H.