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
0
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
posted
0
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
posted
0
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
posted
0
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
posted
0
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).
0
'.