9 Replies Latest reply on May 30, 2003 9:27 AM by Jon Barnett

    Connection Pools with SQL Server 2000

    paultaylor Newbie

      Hi can someone help ?
      I have ported a Weblogic 6 application to JBOSS fairly successfully and have changed from using weblogics own SQL driver to Microsofts SQL Driver to connect to SQL Server 2000 (because the weblogic driver is licensed and seems to require the main weblogic.jar).

      In some cases we have a method that gets a connection within a method already using a connection so in effect we want two connections at the same time. This worked fine in Weblogic but with JBOSS and the new driver I get an error 'unable to start a cloned connection whilst in manual mode' . My mssql-ds.xml file is as follows:



      <local-tx-datasource>
      <jndi-name>Drive</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://192.168.1.12:7780;DatabaseName=drivept</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <user-name>pt</user-name>
      drive
      </local-tx-datasource>


      so I thought I had configured pooling but Im not sure if I have.

      in Weblogic I just got a connection as follows:
      Context ctx = new InitialContext(env);
      DataSource ds = (ClDataSource)ctx.lookup(Constants.get("drive.db.JNDIName"));
      con = ds.getConnection();


      but according the to the Microsoft documentation i should be doing
      Context ctx = new InitialContext(env);
      ConnectionPoolDataSource ds = (ConnectionPoolDataSource)ctx.lookup(Constants.get("drive.db.JNDIName"));
      con = ds.getPooledConnection().getConnection();

      however trying this fails because by lookup doesnt return a ConnectionPoolDataSource just a DataSource.

      Can anyone shed some light on this please ?

        • 1. Re: Connection Pools with SQL Server 2000
          Jon Barnett Master

          Ignore the Microsoft documentation.

          In JBoss, you do the following, assuming you have set up the properties for the environment as the JBoss JNDI likes it:
          Context ctx = new InitialContext(env);
          DataSource ds = (DataSource)ctx.lookup("java:/Drive");
          con = ds.getConnection();

          Remember to con.close() when you finish with the connection to release it back to the pool.

          • 2. Re: Connection Pools with SQL Server 2000
            paultaylor Newbie

            Ok, that is what I was originally doing but should I be able to to open multiple connections within each other

            • 3. Re: Connection Pools with SQL Server 2000
              jscalio Newbie

              I recently ported from WebLogic v7.0 to JBoss with great success. However, I was using Microsoft SQL Server 2000 Driver for JDBC with WebLogic and continue to do so with JBoss. I had problems with the BEA WebLogic supplied JDBC driver for SQL Server and I believe that BEA has discontinued support for it as well. Regardless of which application server you are currently using, the Microsoft driver will yield the error 'unable to start a cloned connection whilst in manual mode' under the conditions that you describe.

              Fortunately, the problem is easily resolved by adding an extra property (SelectMethod=cursor) to the connection URL. Make sure to add a semi-colon as necessary. Here is your datasource configuration with the extra property added.


              <local-tx-datasource>
              <jndi-name>Drive</jndi-name>
              <connection-url>jdbc:microsoft:sqlserver://192.168.1.12:7780;DatabaseName=drivept;SelectMethod=cursor</connection-url>
              <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
              <min-pool-size>10</min-pool-size>
              <max-pool-size>100</max-pool-size>
              <user-name>pt</user-name>
              drive
              </local-tx-datasource>


              By the way, unfortunately you cannot ship the Microsoft SQL Server 2000 Driver for JDBC with your product due to the Microsoft license agreement. Here are a few help full links:

              http://www.microsoft.com/downloads/details.aspx?FamilyID=4f8f2f01-1ed7-4c4d-8f7b-3d47969e66ae&DisplayLang=en
              http://msdn.microsoft.com/MSDN-FILES/027/001/779/redistguide.htm

              • 4. Re: Connection Pools with SQL Server 2000
                Jon Barnett Master

                I'm not quite sure what you mean by multiple connections within each other. When you get a connection, it is almost exactly like a normal JDBC connection.

                You can push multiple queries and statements through that connection.

                e.g. Connection con = ds.getConnection();

                When you finish with the connection, you hand it back to the datasource pool (ds) with:
                con.close();

                The important difference is that the physical connection to the database doesn't close.

                The datasource represents a collection of connections.

                Your bean is single-threaded and sequential so opening multiple connections from the one datasource doesn't make sense in the normal running of things, particularly when your other components are also probably vying for a connection from the pool. The most efficient use is if you just sequentially execute your SQL through the one connection in your bean method. No one else can use the connection while your bean instance is using it.

                I guess the only reason you would have multiple conections open at the one time is to work through a resultset in one connection and push data into the database using the other connection.

                Hope that explains things better and I haven't made things more confused.

                • 5. Re: Connection Pools with SQL Server 2000
                  paultaylor Newbie

                  Thanks for the solution btw how did you find this out I did look at the HTML Help that came with the product and saw no mention of SelectMethod=cursor.

                  For the sake of clarity I will explain why we have used multiple connections in some cases. In the example below we have two classes that represent very different entities, Users and Buildings,SQLManager is just a wrapper round the DataSource. If I want to find details of all the users in a building I call the building class to get the ids of the users, however it is up to the user class to get the full details for each user because I dont want to replicate the code in buildings. In fact getUser() is called by many different parts of the system in some cases they may have a pool connection or they may not so getUser() needs to get its own connection from the pool.

                  class Building
                  {
                  public static ArrayList users getUsersInBuilding(buildingId)
                  {
                  SQLManager sqlMan = new SQLManager();
                  ArrayList users = new ArrayList();
                  try
                  {
                  sqlMan.getConnection()
                  ResultSet resultSet = sqlMan.executeQuery("SELECT USERID FROM USERSINBUILDING WHERE BUILDINGID="+buildingId);
                  while(resultSet.next())
                  {
                  int userId = resultSet.get("USERID");
                  users.add(User.getUser(userId);

                  }
                  }
                  catch(Exception e)
                  {
                  }
                  finally
                  {
                  sqlman.closeConnection();
                  }
                  }

                  class User
                  {
                  public static User getUser(userId)
                  {
                  SQLManager sqlMan = new SQLManager();
                  try
                  {
                  sqlMan.getConnection()
                  ResultSet resultSet = sqlMan.executeQuery("SELECT * FROM USERDETAIL WHERE USERID="+userId);
                  while(resultSet.next())
                  {
                  /** Create user Object from ResultSet
                  return this;
                  }
                  }
                  catch(Exception e)
                  {
                  }
                  finally
                  {
                  sqlman.closeConnection();
                  }
                  }
                  }

                  • 6. Re: Connection Pools with SQL Server 2000
                    billb Newbie

                    Hi

                    I am new to Jboss (i am using jboss 3.2.1 with tomcat 4.1.24) , and I would like to use CMP bean with SQL SERVER 2000. Actually I do not really need connection pool. Is there a simple way to achieve this ?

                    I have downloaded JDBC Driver for SQL SERVER. I guess i have to change configuration files (Ejb-generate.xml in my application jar / mssql-ds.xml in Jboss) maybe there are some other files ?

                    by the way i am also using Eclipse 2.1.0 with j2se 1.4.1 and with th lomboz plug-in 2.1.

                    I hope you can help me. thank you by advance.

                    • 7. Re: Connection Pools with SQL Server 2000
                      jscalio Newbie

                      May I make another suggestion at the risk of sounding like a critic, which is not at all my intention?

                      Performance for the code example that you present will suffer from N+1 queries to the database, where N is the number of users in each building. In the current form, this code will not scale very well. However, this code can be very easily optimized to use only a single query.

                      This can be accomplished with basic JDBC code or through the use of EntityBeans and ContainerManagedRelationships (CMR).

                      Moving to EntityBeans and CMR is a big step, which must be carefully considered depending upon many factors, including, but not limited to, the size and skill level of your development team, the complexity and schedule your project, etc.

                      Anyway, time spent doing careful design now will definitely pay off in the long run.

                      Good luck!

                      • 8. Re: Connection Pools with SQL Server 2000
                        paultaylor Newbie

                        Yes, youre right however N+1 is not exponential or anything like that. In the example above we would normally move the call to the second query to occur after the first query so that although two connections would be used, only one one would be used at atime. But the product is actually quite a mature product (on weblogic) with about 150 classes representing database entities and it would be a time consuming process to correct all instances where this is occurring.

                        BTW could you tell me where you found out about that extra Microsoft parameter

                        • 9. Re: Connection Pools with SQL Server 2000
                          Jon Barnett Master

                          Apparently everyone knows about the method. Oracle have a tutorial on transferring data showing the method and so does Apache Torque + Sun:
                          http://jakarta.apache.org/turbine/torque-3.0.0/mssql-howto.html

                          You have a perfectly valid requirement for multiple connections.

                          However, in a "nice" J2EE world, this connection would be requested within another bean related to the User Id. As suggested you could use an entity bean. You could also use a stateless session bean with a method that returns the status info. It depends on your optimal resource usage and reusability. But one of the maintenance issues you solve is that you only need to deploy changes to your middle-tier layer. All applications would access your getUser method in the middle-tier and through a shared connection. It also means that all connections to the DB are through the middle tier - with no possibility to rogue access.

                          The downside is added overhead to call the bean to acces the data. But your middle-tier is the gatekeeper to your enterprise data and only it needs to have the datasource connection information.

                          Having said that, I know your existing deployment uses a variety of access services. The JBoss connection allocation system will cope with your requirements - but even in your instance, you may find that you can get the one connection in your main bean, pass its connection handle to your "User" class and then when it has finished using it, use the same connection to get the "property" info. After all, you are still only doing things in a serial fashion - it will save on your consumption of shared resource.

                          Hope this makes sense.