• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Collection as a parameter in a Callable Statement

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 382
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 382
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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).
 
reply
    Bookmark Topic Watch Topic
  • New Topic