Passing an Array to a Stored Procedure as a parameter - Please Help

[ToeJamNEarl]

Hello guys,


How would I pass in an array to a stored procedure?

The PL\SQL package is as follows:

package test_varray is 

TYPE t_InputFields IS VARRAY(4) OF VARCHAR2(5); 

PROCEDURE Test_Array(p_InputFields IN t_InputFields,
                     P_Field1      IN OUT VARCHAR2,
                     P_Field2      IN OUT VARCHAR2,
                     P_Field3      IN OUT VARCHAR2,
                     P_Field4      IN OUT VARCHAR2) ;

end test_varray;

The Visual Basic code I am currently using to call this Stored procedure is as follows:

Dim datCmd1 As New ADODB.Command
Dim prmInputFields As New ADODB.Parameter

Dim i As Integer
Dim InputFields(1 To 4) As String

strPF1 = ""
strPF2 = ""
strSF1 = ""
strSF2 = ""
    
rsTMPSF.MoveFirst

Do While rsTMPSF.EOF = False
    
    If (cboPrimary1.Text) = rsTMPSF(0).Value & " - " & rsTMPSF(1).Value Then
        strPF1 = rsTMPSF(0).Value & "." & rsTMPSF(2).Value
    End If
    
    If (cboPrimary2.Text) = rsTMPSF(0).Value & " - " & rsTMPSF(1).Value Then
        strPF2 = rsTMPSF(0).Value & "." & rsTMPSF(2).Value
    End If
    
    If (cboSecondary1.Text) = rsTMPSF(0).Value & " - " & rsTMPSF(1).Value Then
        strSF1 = rsTMPSF(0).Value & "." & rsTMPSF(2).Value
    End If
    
    If (cboSecondary2.Text) = rsTMPSF(0).Value & " - " & rsTMPSF(1).Value Then
        strSF2 = rsTMPSF(0).Value & "." & rsTMPSF(2).Value
    End If
        
    
    rsTMPSF.MoveNext
    
Loop

InputFields(1) = strPF1
InputFields(2) = strPF2
InputFields(3) = strSF1
InputFields(4) = strSF2


datCmd1.ActiveConnection = DBConnection
datCmd1.CommandType = adCmdStoredProc
datCmd1.CommandText = "test_varray.test_array"

    Set prmInputFields = datCmd1.CreateParameter("p_InputFields", adArray, adParamInput)
    datCmd1.Parameters.Append prmInputFields
    prmInputFields(1).Value = InputFields(1)
    prmInputFields(2).Value = InputFields(2)
    prmInputFields(3).Value = InputFields(3)
    prmInputFields(4).Value = InputFields(4)

datCmd1.Execute

It errors out during debug when it gets to the line:

    Set prmInputFields = datCmd1.CreateParameter("p_InputFields", adArray, adParamInput)

Please can someone help me figure out how ot pass in an Array to a stored procedure?

Thanks

-Diran

 

[RaVeNCoder]

Why not use a Collection in stead of an Array?
Just a Question. You can pass a collection to a stored procedure no problems.

 

[SemperFiDownUnda]

Below are the SQL data types with their ADO counter parts.
Stored Procedures have to use one of the SQL data types.
adArray is for other storage systems that might have a native type that is compatible.

If you want to pass array type data to a sp there are a few ways to do it.

One way is to add the data into a table and have the stored proc use that table.

Another way is to pass it in via a TEXT data type and parse it out.

Another way is to design the SP to be called multiple times (once for each element) just passing it one element of the array.


bigint adBigInt
binary adBinary
bit adBoolean
char adChar
datetime adDBTimeStamp
decimal adNumeric
float adDouble
image adVarbinary
int adInteger
money adCurrency
nchar adWChar
ntext adWChar
numeric adNumeric
nvarchar adWChar
real adSingle
smalldatetime adTimeStamp
smallint adSmallInt
smallmoney adCurrency
sql_variant adVariant
sysname adWChar
text adChar
timestamp adBinary
tinyint adVarbinary
uniqueidentifier adGUID
varbinary adVarbinary
varchar adChar


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[ToeJamNEarl]

RaveNCoder:

Can you go more into detail about collections? Would I have to change the SQL procedure to receive it any differently?

Cause I got it to take my array by having the type be adArray + adVariant.

but when the program gets to datCMD1.execute, it goes bonkers, by giving me an error -2147467259 with a garbled text/character description.

Thanks for any help.

-Diran

 

[bmdb]

Probably isn't it but a google search on -2147467259 turns up quite a few pages and several that I quickly looked at referred to setting a timeout on your connection (datCmd1.CommandTimeout = 600 'value in seconds).

 

[TJRTech]

Stored Procedures cannot accept arrays or collections as input. Sorry. Consider some of the suggestions of SemperFi as ways to get around this limitation.

TR

 

[ToeJamNEarl]

TJRTech,

I am not doubting your statement, but may I ask how you can make a definitive statement such as that? What grounds can you show that prove that?

I am only asking because I want to understand.

Thanks,

-Diran

 

[ToeJamNEarl]

RaveNCoder or anyone else,

Can you please explain to me, how I could use a collection and pass it to the stored procedure instead of an array? What all will have to be done to the stored procedure if any?

Thanks,

-Diran

 

[TJRTech]

On the grounds that arrays and collections are UNKNOWN datatypes in Microsoft SQL. Thus, you can't create a stored procedure that accepts them as input.

The work-arounds that most people use are to:

1. Create a real or temporary table and insert all the items in your array/collection as individual records, then call the SP that processes them.

2. If a limited set of information, and especially if single-attribute in nature, pass the list in as a comma-seperated-value string (one big varchar parameter).

3. Re-code your SP to accept one record/entry at a time, and a parameter for indicating the start/end. In this way you can call the SP in a loop, and do something special at the beginning and at the end.

TR