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.

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

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.

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.

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!

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.