Collection as a parameter in a Callable Statement

[Padma Anand]

Can I pass any kind of parameter to a callable statement? I have a set of values which I want to store in a vector and get them processed through a stored procedure and I want this stored procedure to return a collection. Is this possible? or can I only send string, int, float and other primary datatypes?
Also, can I have this callable statement valid across multiple databases? like oracle, SQLserver, db2 etc? Do I need to take any precautions?
Thanks,
Padma.

[J Craig]

posted May 04, 2004 06:12 AM
--------------------------------------------------------------------------------
Can I pass any kind of parameter to a callable statement? I have a set of values which I want to store in a vector and get them processed through a stored procedure and I want this stored procedure to return a collection. Is this possible? or can I only send string, int, float and other primary datatypes?
Also, can I have this callable statement valid across multiple databases? like oracle, SQLserver, db2 etc? Do I need to take any precautions?
Thanks,
Padma.
The stored procedure wil not accept a vector you can create a method that iterates through the vector calling the statement for the each value
I know you probably wanted your database to do most of the processing but I dont' think you can use any data structures as parameters to stored procedures.
The result set can be stored in a data structure as far as the sql you just need to check syntax I believe at least sql server and oracle are slightly different.

[Sadanand Murthy]

Originally posted by Padma Anand:
Can I pass any kind of parameter to a callable statement? I have a set of values which I want to store in a vector and get them processed through a stored procedure and I want this stored procedure to return a collection. Is this possible? or can I only send string, int, float and other primary datatypes?
Also, can I have this callable statement valid across multiple databases? like oracle, SQLserver, db2 etc? Do I need to take any precautions?
Thanks,
Padma.

What kind of processing do you want to do with the values in the vector?

[Marshall B Thompson]

What database? There are some oracle specific ways of doing this. They don't apply to other databases. (See OraclePreparedStatement/OracleCallableStatement, can pass an array as an input parameter to a stored procedure.)

[Padma Anand]

The kind of processing I need is basically picking up the values. I have a table A with "id" and "name" as columns. I get a list of these ids(from a different query and from a different table B), lets say around 100. I cannot display these ids. So, I have to query on A for each id, get the name and display it. Now, I don't want to query for each id for obviuos reasons. so, I want to store these ids in a collection like vector, send it to a stored procedure, have a loop over the query in the procedure, store the resultant values in a collection and return it back( or a resultset).
I wanted to know if any precautions need to be taken for the SP working uniformly across all the databases. I am working on Oracle, SQL Server, DB2. I want to have a single java solution for calling SP across the databases.
Thanks,
Padma.

[Sadanand Murthy]

Originally posted by Padma Anand:
The kind of processing I need is basically picking up the values. I have a table A with "id" and "name" as columns. I get a list of these ids(from a different query and from a different table B), lets say around 100. I cannot display these ids. So, I have to query on A for each id, get the name and display it. Now, I don't want to query for each id for obviuos reasons. so, I want to store these ids in a collection like vector, send it to a stored procedure, have a loop over the query in the procedure, store the resultant values in a collection and return it back( or a resultset).
I wanted to know if any precautions need to be taken for the SP working uniformly across all the databases. I am working on Oracle, SQL Server, DB2. I want to have a single java solution for calling SP across the databases.
Thanks,
Padma.

Why don't you pass a delimited string of the ids (perhaps delimited by a comma) & have the strored procedure parse it out? If it is a comma delimited string, the sp can perhaps do 'where col in (<delimitedString> '.
Another option: Why not have the 1st query join table B with A & return the name also? This will be far more effecient than having to build a collection/vector/delimitedString/whatever & calling another sp just to get the names. Each call to the db has to go through the network layers (which would most likely include the expensive marshalling & unmarshalling calls).