Retrieving records using a query from oracle into a 2 D array using vb

KNS

Hi,

I've been thrown into the deepend a bit. I'm trying to a) pull records from oracle using a query and throw the reults into a 2 D array. Then b) plan to move the records from the 2d array into a SQL Server table which will be cleared before the insert.

I've been trying to write a function to achieve part a) but i'm having no success.

I am getting a Type mismatch error on line:

astrCostCodeDescriptions = objRS.GetRows

What is the best way to get the results into an array??

Any tips would be hugely welcomed. Thanks in advance. I've put a copy of the fuction below.

Public Function getCostCodeDescriptions(ByRef astrCostCodeDescriptions() As String) As Boolean
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String
Dim intI As Integer

' Set the error handling
On Error GoTo errHandler

' Create data access objects
Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

' Open the connection
objConn.Open gstrOracleDSN

'Build the SQL query
strSQL = ""
strSQL = strSQL & "select invoiceid, comment, createdbyuserid from tbinvoicecomments"

' Fix table names if is a development machine
If blnIsPMC = True Then strSQL = correctSQLString(strSQL)

With objRS
Set .ActiveConnection = objConn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Source = strSQL
'***** Open the recordset
.Open
'***** Disconnect the recordset
.ActiveConnection = Nothing
End With

astrCostCodeDescriptions = objRS.GetRows

' Close the connection
objConn.Close

' Reached here without error
getCostCodeDescriptions = True

procExit:
Exit Function
errHandler:
getCostCodeDescriptions = False
prepErrorMsg "getCostCodeDescriptions", Err.Description
Resume procExit
End Function

Evil Phil

What error message are you getting back? Using your approach why don't you pass in a recordset instead of an array - it would be easier to work with.

bonkey

Stupid question.....

Is the aim here to mobe data from ORacle to MSSQL, or something more detailed? The reason I'm asking is because if its just data-moving you want to do, there seems little reason to use VB at all.

If its something mroe detailed, and there's a reason for using VB for this....fair enuff. In this case, GetRows is the correct method to be using.

I don't understand what you're asking though...GetRows returns your results into an array. There is nothing more to be done, surely?

jc

Mutant_Fruit

EDIT: Ignore this post. VB .GetRows() works differently to what i thought. Therefore making this post stupid.

bonkey wrote:

GetRows returns your results into an array. There is nothing more to be done, surely?

No, theres a lot to be done to make that happen . I don't know VB, so this is psuedo code but it should illustrate the point nicely.

DIM my_array as NEW array[10,10]; // This is my array in which to save the data

Now, you want to do: my_array = objRS.GetRows();

This will never work. You are trying to save some kind of collection of rows into a variable which is a 2D array. Collection of rows != 2D array! Therefore type mismatch.

What you need to do is create a variable which is suitable for storing a collection of datarows (do you have DataSet in VB?) and save the info into that first.

i.e. DIM my_DataSet as NEW DataSet;
my_DataSet = objRS.GetRows();

Then you loop through each row of the dataset, and copy each position in the current row over to its corresponding place in the 2d array.

i.e.
for(ALL THE ROWS)
{
currentrow = my_DataSet.NextRow();
currentrow[0] = my_array[0,1];
currentrow[1] = my_array[0,2];
..
...
..

}

kenaman

KNS wrote:

Public Function getCostCodeDescriptions(ByRef astrCostCodeDescriptions() As String) As Boolean

should be

Public Function getCostCodeDescriptions(ByRef astrCostCodeDescriptions As Variant) As Boolean

Ensure you pass astrCostCodeDescriptions as a Variant when you call the function. This should take care of it.

KNS

kenaman wrote:

should be

Public Function getCostCodeDescriptions(ByRef astrCostCodeDescriptions As Variant) As Boolean

Ensure you pass astrCostCodeDescriptions as a Variant when you call the function. This should take care of it.

Thanks for the replys. Kenaman you were spot on.

Cheers

bonkey

Mutant_Fruit wrote:

No, theres a lot to be done to make that happen .

Nope. There isn't.

I don't know VB, so this is psuedo code but it should illustrate the point nicely.

I do know VB, and I have used this technique.

So you'll forgive me if I take my production-environment-based experience over your pseudo-code

DIM my_array as NEW array[10,10]; // This is my array in which to save the data

Now, you want to do: my_array = objRS.GetRows();

This will never work.

This is correct. This will never work, because you're using GetRows incorrectly.

You are trying to save some kind of collection of rows into a variable which is a 2D array. Collection of rows != 2D array! Therefore type mismatch.

From a quick google on "ADO recordset to array"
http://www.devguru.com/Technologies/ado/quickref/recordset_getrows.html

First paragraph...

The GetRows method is used to copy records from a Recordset object into a variant that is a two-dimensional array. The variant array is automatically dimensioned (sized) to fit the requested number of columns and rows. To allow backwards compatibility with earlier versions of ADO, the columns are placed in the first dimension of the array and the rows are placed in the second dimension.

See

You use a dynamic array, albeit one originally defined as a variant. Then it works just fine. This is why changing the function parameter type solved the problem.

What you need to do is create a variable which is suitable for storing a collection of datarows (do you have DataSet in VB?) and save the info into that first.

No, you don't. Well, you can, but there's no need to when you can use getRows and assign directly to a variant.

jc

amen

yeah change ByRef astrCostCodeDescriptions() As String to
ByRef astrCostCodeDescriptions as variant
but can you not import the data directly from Oracle into SQL Server and then have SP to do your processing or you could use the DTS wizard and use it to create a VB class that would do the maniuplation for you or just save the DTZ on sql server and call from VB

bonkey

DTS is what I was thinking....which is why I asked in my first post whether the objevtive was to move the data or something else...

If its just a data-move, DTS should also be faster. By lots. But YMMV, as always

amen

what is YYMV ?

Mutant_Fruit

amen wrote:

what is YMMV ?

"Your milage my vary"

@Bonkey. Good thing i prefaced my post with "I don't know VB". I never knew you could do that. I suppose it makes sense when you think about it. Ignore my previous post