• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Try/Catch/Return

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to return a result set from a public method following a jdbc insert.


I am getting compile errors when dealing with the return statement. My original intentions was to declare the ResultSet rs inside the Try block and also do the return(rs) in the Try block. During compliation, i get "Method must return a value of type ResultSet". So when I move the declaration and return out of the Try Block like I have above, I get an error saying "local variable rs may have not been initialized" Where I am supposed to put the declaration and return statements?
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just do it this way
public ResultSet insert(AldermanBean bean)
{
Connection con;
Statement stmt;
ResultSet rs;
String url = "jdbc racle:thin:@ServerNameRemoved";
String sql = "INSERT INTO ALDERMAN VALUES(AldermanID.NextVal," + bean.getFirstName() + ", " + bean.getMiddleName() + ", " + bean.getLastName() + ", " + bean.getSuffixID() + ", " + bean.getWardID() + ", " + bean.getActive() + ")";

String query = "Select AldermanID, FName, MName, LName, SuffixID," +" WardID, Active From Alderman;";



try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); con = DriverManager.getConnection(url,"boauser", "boauser");
stmt = con.createStatement();
stmt.executeUpdate(sql);
rs = stmt.executeQuery(query);
}
catch (SQLException sqle)
{
System.err.print("SQL Exception: " + sqle.getMessage());
}
finally
{
try
{
stmt.close();
con.close();
}
catch(Exception e)
{}
}
return rs ;
}

let me know if it is still failing to compile
 
Makarand Parab
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Brian
This is a wrong way of coding where u are closing connection and statement object but passing around resultset. i would suggest you to get the data out of the resultset into some collection object and close the resultset also just above statement object and return back the collection object. Passing resultset can be harmful.
 
Brian Smith
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your help. I am still getting compilations errors.

I am getting "the local variable rs may not have been initialized". I am getting this message for variables rs, stmt, and con. I would assume this is because they are initialized in a different try block?
 
Makarand Parab
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Brian
Just replace
Connection con;
Statement stmt;
ResultSet rs;

with

Connection con = null;
Statement stmt = null;
ResultSet rs = null;


Let me know if u still face problem.
 
Brian Smith
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Makarand, this is my first attempt at JDBC and I appreciate any advice. So you are saying that I should iterate the ResultSet into a List or some other Data Structure instead of passing the ResultSet back to the calling class? Can you tell me what specifically is dangerous about passing ResultSets? I though that a ResultSet was basically just a collection similar to an array specifically used to hold jdbc data? Again, I appreciate any advice you can provide. I may revise this post and place it in the JDBC forum once I have my compile errors worked out.
 
Makarand Parab
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi
Resultset is a database related collection objects. All database objects are heavy. Good design is to destroy them where they get created. I would iterate the resultset to java collection object and release the resultset object. While creation the flow is connetion -> statement -> resultset in the same way when u release db objects the flow should be resultset -> statement -> connection.
Remember one thing all db objects are heavy objects. If u keep the resultset passing not only ur exception handling becomes touch but if u forget to close the resultset object u can lead to some other exceptions like max cursor reached after n number of user have used your application. Just do one thing when ur compile errors are gone, try to run the same application and get the rs in the calling method and try to access it. I think u will face problem accessing the resultset coz u have closed the statement object from which the resultset was created. U will get runtime exception. That's the reason i am telling u to move resultset data to java collection object and free up resultset.
 
Brian Smith
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, the code you provided above worked great. Also thanks for the explanation about ResultSets. I will take your advice and move the data to a more light weight data structure such as a List. Thanks again!

-Brian
 
reply
    Bookmark Topic Watch Topic
  • New Topic