0 Replies Latest reply on May 25, 2002 6:29 PM by Chris Coy

    SQL Server 2000 Lock problem

    Chris Coy Newbie

      I have a stateless session bean that atomically creates a reference to a pooled MS JDBC driver connection. I close the resultset early, and close and set null all of the database variables before the function ends. However, once the connection starts, the table that it queries remains locked, I can't query it, I can't even look at my processes in SQL Server Enterprise Manager.

      I am using JBoss3.0RC2 and the latest JDBC driver from Microsoft. Why does the pool keep the table locked??

      Here are my files: xxservice.xml --
      <?xml version="1.0" encoding="UTF-8"?>





      MSSQLDbRealm

      <depends optional-attribute-name="ManagedConnectionFactoryName">
      <!--embedded mbean-->


      SQLSRV



      <config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=tribes2stats;SelectMethod=cursor</config-property>
      <config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
      <config-property name="UserName" type="java.lang.String">jboss</config-property>
      <config-property name="Password" type="java.lang.String">gamestats</config-property>



      <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper



      <depends optional-attribute-name="ManagedConnectionPool">
      <!--embedded mbean-->


      0
      2
      500
      1

      ByContainer



      <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

      <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

      java:/TransactionManager
      <!--make the rar deploy! hack till better deployment-->
      jboss.jca:service=RARDeployer







      NEXT is the function that calls the pool:
      public Vector getServers() {
      ResultSet rs=null;

      int i=0;
      int iCount=0;
      String sType="";
      clientInfo cI=null;
      Vector clients = new Vector();

      System.out.println("starting getServers");
      try {
      jndiContext = new InitialContext();
      ds = (DataSource) jndiContext.lookup("java:/SQLSRV");
      connection = ds.getConnection();
      stmt = connection.createStatement();

      System.out.println("getting servers in bean");
      rs = stmt.executeQuery("exec spGetServers");
      boolean hasNext = true;

      while(hasNext) {
      hasNext = rs.next();
      System.out.println("moved cursor, result was " + hasNext);
      if(hasNext == false) {
      break;
      }

      iCount=rs.getInt("countof");

      String address = rs.getString("serveraddress");
      String port = rs.getString("port");
      String password = rs.getString("serverlistenpassword");
      String status = rs.getString("status");
      String serverid = new Integer(rs.getInt("serverid")).toString();
      String servername = rs.getString("servername");

      cI = new clientInfo(address,
      port,
      password,
      status,
      serverid,
      servername);

      System.out.println("adding element");
      clients.addElement(cI);
      }
      rs.close();
      rs = null;
      stmt.close();
      stmt = null;
      connection.close();
      connection = null;
      ds = null;
      System.out.println("released db");
      } catch (Exception e) {
      try {
      e.printStackTrace();
      rs = null;
      stmt.close();
      connection.close();
      } catch(Exception ex) {
      e.printStackTrace();
      }
      } finally {

      return (Vector) clients.clone();
      }
      }