HOW-TO: CONNECTING SQL SERVER 2005 JDBC to JBOSS - Port 25: The Open Source Community at Microsoft

HOW-TO: CONNECTING SQL SERVER 2005 JDBC to JBOSS by admin on June 14, 2006 05:46PM

HOW-TO: CONNECTING SQL SERVER 2005 JDBC to JBOSS

We have been working in the labs regarding testing and integration of jBoss talking to a SQL server 2005 backend.

We have seen some questions on how to connect a SQL Server in the Windows environment to jBoss. So I wrote a little how-to that describes just that.

It turns out that it is actually very simple.

Essentially, all that’s involved with installing the driver is:

Download the jdbc driver from here; http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en#filelist (Scroll to the bottom of the screen)
You can either download the Windows or Unix version. It does not really matter which one you use. For this example I use the Windows version.
Run the downloaded exe file; this will create a directory called Microsoft SQL Server 2005 JDBC Driver in the directory you downloaded the file to.
Copy the file Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.0\enu\sqljdbc.jar to your $JBOSS_HOME/server/default/lib
Set your data source of your application to use the driver;

<local-tx-datasource>

<jndi-name>DATASOURCENAME</jndi-name>

<connection-url>jdbc:sqlserver://DATABASE URL;databaseName=jboss;</connection-url>

<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

<user-name>DATABASE USERNAME</user-name>

<password>DATABASE PASSWORD</password>

</local-tx-datasource>

NOTE: Substitute DATASOURCE NAME, DATABASE-URL, DATABASE USERNAME and DATABASE PASSWORD for your local values.

And that is all there is to it. jBoss now should be able to connect to the SQL Server.

Special thanks to Dan Simonton one of our penguins who did a bunch of the testing on this.

Email Digg It! Del.icio.us SlashDot It!

Comments RSS

JonTheNiceGuy said:

Any chance of a similar howto for PHP? I've been trying on-and-off for about 4 years to do it, and never succeeded!

Thanks :)

Jon "The Nice Guy"

posted at 03:29AM 06/15/2006
hjanssen said:

Have you checked http://us2.php.net/mssql_connect ? If that does not have your anwser, let me know and give me the specifics on what it is you where trying to do and I can see if we can figure it out for for you at the labs here.

Hank.

posted at 07:14PM 06/15/2006
johndpalm said:

Does the MS JDBC driver support SQL 2005 mirroring failover?

posted at 09:54PM 09/18/2006
rtrask said:

Hi,

I'm trying to get SQL Server 2005 with the latest Microsoft JDBC driver (released October 2007) configured for XA to work with JBoss 4.2.1 transactions.

I get the feeling that I am plowing new ground, and I was wondering if any one else has had any success with even a remotely similar combination.

I have had quite a bit of success with blue sky scenarios, but I am running into issues with recovery.

This is the datasource configuration that I have been using.

<xa-datasource>

<jndi-name>DefaultDS</jndi-name>

<track-connection-by-tx/>

<isSameRM-override-value>false</isSameRM-override-value>

<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

<xa-datasource-property name="ServerName">localhost</xa-datasource-property>

<xa-datasource-property name="DatabaseName">jboss</xa-datasource-property>

<xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>

<xa-datasource-property name="User">jboss</xa-datasource-property>

<xa-datasource-property name="Password">jboss</xa-datasource-property>

<xa-datasource-property name="URL">jdbc:sqlserver://localhost:1961</xa-datasource-property>



<new-connection-sql>select 1</new-connection-sql>



<check-valid-connection-sql>select 1</check-valid-connection-sql>



<metadata>

<type-mapping>MS SQLSERVER2000</type-mapping>

</metadata>

</xa-datasource>

An example of what I am running into is as follows:

following the directions in http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossTSRecovery

When I run the test, the first step works perfectly. The transactions to the point where a transaction attempts to commit, and then blocks as appropriate. Then in the next step after initiating the recovery via Test -recover, it proceeds to DONE, but never recovers the transaction, and the entry remains in the Object Store.

I'm new to Port 25, and I am not sure if this is the correct place to post this or not. Please forgive me if I am out of bounds here.

Any help is greatly appreciated.

posted at 10:46PM 12/16/2007
rtrask said:

Whoops, I apologize for not doing a better of looking before I lept. :)

It turns out that there is a recent thread that was started on this very topic. For the interested reader, here is the link.

http://www.jboss.com/index.html?module=bb&op=viewtopic&t=125595

posted at 11:08PM 12/16/2007