wood burning stoves 2.0*
The moose likes JDBC and the fly likes Collection as a parameter in a Callable Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Collection as a parameter in a Callable Statement" Watch "Collection as a parameter in a Callable Statement" New topic
Author

Collection as a parameter in a Callable Statement

Padma Anand
Greenhorn

Joined: Apr 14, 2004
Posts: 3
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
Greenhorn

Joined: May 04, 2004
Posts: 15
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
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
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?


Ever Existing, Ever Conscious, Ever-new Bliss
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
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
Greenhorn

Joined: Apr 14, 2004
Posts: 3
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
Ranch Hand

Joined: Nov 26, 2003
Posts: 382
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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Collection as a parameter in a Callable Statement
 
Similar Threads
Passing Arrays to Oracle stored procedures
Problem calling IN parameters Stored Procedure from Java
Issue calling a stored procedure from Weblogic 8.1 workshop project
How to return resultset from stored procdure
%rowtype mapping in java