Porting my application from oracle to SQL Server

Discussions

Web tier: servlets, JSP, Web frameworks: Porting my application from oracle to SQL Server

  1. Dear Friends,

    I'm facing problems when i ported my application which was running in Oracle to SQL Server. Each and every links(hrefs or menu) in our application is fetching vales from the database. The maximum cursors which is set in SQL is 100. The application hangs after quite a number of clicks on the links.
      I think problem is with handling the database connection.
    I need more input regarding how to handle connections

    plz help me out

    Thanx in advance

    Regards
    Ruban
  2. Ruban, are you properly closing your connections after using them? Be sure that you do, for instance:


    try
    {
       Connection con = DriverManager.getConnection(...);
       Statement stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(...);
    }
    finally
    {
       ... // Close resultset and stmt first, then
       try
       {
          if (con != null) con.close();
       }
       catch(SQLException e)
       {
          ...
       }
    }

    Gordon
  3. Mohd, nice catch. I hadn't thought of that, pooled connections leaving the other resources open.

    Gordon.
  4. This is a tough time developers encounter when handling databse. i had also encountered the same.
    Whenever you fire a select query on the database, it opens a cursor for that query, and the cursor is not closed until you close the statement on which you have executed the query. Even closing the resultset doesn't solve the problem. The cursor remains open after closing the resultset. The basic and very effective technique is to free all the resources you have taken from the database. IF you close the Connection to database then it frees all the resources. But if you are using some advance tech or drivers for Connection Pooling then a connection is never closed even if you close the connection in your porogram. In this case, the problem still remains unsolved.
    The best technique for this is to close the resultset first and then the statement and then free the connection.
    The occurence of Exception should also be taken into account. All resources should be freed in the finally block.
    A typical code which will ensure that all your resources
    will be freed in any case looks like this :

    Connection cn = null;
    Statement st = null;
    ReultSet rs = null;
    try
    {
    cn = DriverManager.getConnection();
    st = cn.createStatement();
    rs = st.executeQuery(SQL);
    }
    catch(Exception e) {}
    finally
    {
      try
      {
        if(rs != null) { rs.close()
      }
      catch(Exception e){}
      try
      {
        if(st != null) { st.close()
      }
      catch(Exception e){}
      try
      {
        if(cn != null) { cn.close()
      }
      catch(Exception e){}
    }

    ----------
    This code assures that you will never encounter the problem of open resources from the database.
    I have successfuly tested and deployed it on an high traffic application. And it's working very fine.
    If you encounter any more problem in handling the datebase ( which, i think the you will ) pls do post your problem.
    I will like to share some more Concepts that you might not still be knowing.

    Farhan