How to connect from JBoss to SQL server ?

[Meherdad Bomanbehram]

Hi
Could some one tell me (code file in full prefered) how to connect from JBoss server to Microsoft SQL server .I am trying out the examples in the Enterprise Java beans 3.0 by O'Rielly which assumes that the user will be using the hypersonic database which I am not . What exact additions do I make to my persistence.xml file anbd do I require to have a odbc connection ?

Please Help
Many thanks
Meherdad Bomanbehram

[Dinuka Arsakularatne]

Hi,

JBoss comes with hsql so you do not have to install it separately. If you want to use mssql then first you have to update the file jboss-ds.xml file which you can find in the JBOSSHOME/server/default/deploy directory. add the following entries to it;

 
    <datasources>
    <local-tx-datasource>
    <jndi-name>mssql</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/dbname</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>root</user-name>
    <password>rcfi</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>5</idle-timeout-minutes>
  </local-tx-datasource>
</datasources>   

This is for the Mysql database. But as you are using mssql just changed the lines connection-url and the driver-class tags. Then in the persistence.xml file include the line <jta-data-source>java:/<jndi-name></jta-data-source so it will refer to that data source. and make sure to download and copy the jdbc driver for mssql to the JBOSSHOME/server/default/lib directory.

Hope this helps

Dinuka

[Dinuka Arsakularatne]

actually you have to create a file named jboss-ds.xml and put it into JBOSSHOME/server/default/deploy directory...

[Meherdad Bomanbehram]

Thanks a million Dinuka ,Could you pls guide me as to from where I can download the driver from and how to install it ?

Many thanks

[Uchana Jackson]

search google for sqljdbc.jar (or possibly 2005 if you're using sql server 2005)

copy that jar into the %JBOSS_HOME%/server/default/lib folder

[Dinuka Arsakularatne]

The mssql jdbc driver
this driver supports both mssql and sybase.

[bhupesh ravish]

hey buddy
go to the directory jboss-as/docs/examples/jca/ copy the mssql-ds.xml file into the deploy folder and make nessary changes like connection url, user name & password. Now within your application just lookup the datasource name which you have specified in the mssql-ds.xml file and write your code.

[Meherdad Bomanbehram]

Thanks to all of you for helping out .
Special thanks to bhupesh ravish , Thanks buddy It worked now I will try to get my application working again . thanks again
[ April 08, 2008: Message edited by: Meherdad Bomanbehram ]

[Meherdad Bomanbehram]

Hi guys thanks for your help but i still can not connect to the sql server database.
I copied the jtds-1.2.2.jar jar file to the C:\jboss-4.2.2.GA\server\default\lib folder
but still when I start the JBoss server it gives me this error please help

12:16:07,453 INFO [DLQ] Bound to JNDI name: queue/DLQ
12:16:07,781 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=ConnectionFactoryBinding,name=JmsXA' to JNDI name 'jav
a:JmsXA'
12:16:07,875 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=titan_dev' to JNDI name 'java:t
itan_dev'
12:16:07,968 WARN [ServiceController] Problem creating service jboss.j2ee:service=EJB3,module=titan.jar
java.io.IOException: bad magic number: 504b0304
at javassist.bytecode.ClassFile.read(ClassFile.java:689)
at javassist.bytecode.ClassFile.<init>(ClassFile.java:85)
at org.jboss.ejb3.Ejb3Deployment.deployElement(Ejb3Deployment.java:434)
at org.jboss.ejb3.Ejb3Deployment.deployUrl(Ejb3Deployment.java:423)
at org.jboss.ejb3.Ejb3Deployment.deploy(Ejb3Deployment.java:384)
at org.jboss.ejb3.Ejb3Deployment.create(Ejb3Deployment.java:327)
at org.jboss.ejb3.Ejb3Module.createService(Ejb3Module.java:77)
at org.jboss.system.ServiceMBeanSupport.jbossInternalCreate(ServiceMBeanSupport.java:260)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:243)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy0.create(Unknown Source)
at org.jboss.system.ServiceController.create(ServiceController.java:330)
at org.jboss.system.ServiceController.create(ServiceController.java:273)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy33.create(Unknown Source)
at org.jboss.ejb3.EJB3Deployer.create(EJB3Deployer.java:492)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.interceptor.DynamicInterceptor.invoke(DynamicInterceptor.java:97)
at org.jboss.system.InterceptorServiceMBeanSupport.invokeNext(InterceptorServiceMBeanSupport.java:238)
at org.jboss.wsf.container.jboss42.DeployerInterceptor.create(DeployerInterceptor.java:76)
at org.jboss.deployment.SubDeployerInterceptorSupport$XMBeanInterceptor.create(SubDeployerInterceptorSupport.java:180)
at org.jboss.deployment.SubDeployerInterceptor.invoke(SubDeployerInterceptor.java:91)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy34.create(Unknown Source)
at org.jboss.deployment.MainDeployer.create(MainDeployer.java:969)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:818)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
at sun.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy9.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:421)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:634)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:336)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:417)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy5.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
at org.jboss.Main.boot(Main.java:200)
at org.jboss.Main$1.run(Main.java:508)
at java.lang.Thread.run(Thread.java:619)
12:16:07,968 INFO [EJB3Deployer] Deployed: file:/C:/jboss-4.2.2.GA/server/default/deploy/titan.jar
12:16:08,171 INFO [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../deploy/jmx-console.war/
12:16:08,328 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- MBeans waiting for other MBeans ---
ObjectName: jboss.j2ee:service=EJB3,module=titan.jar
State: FAILED
Reason: java.io.IOException: bad magic number: 504b0304

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.j2ee:service=EJB3,module=titan.jar
State: FAILED
Reason: java.io.IOException: bad magic number: 504b0304


12:16:08,734 INFO [Http11Protocol] Starting Coyote HTTP/1.1 on http-127.0.0.1-8080
12:16:08,796 INFO [AjpProtocol] Starting Coyote AJP/1.3 on ajp-127.0.0.1-8009
12:16:08,843 INFO [Server] JBoss (MX MicroKernel) [4.2.2.GA (build: SVNTag=JBoss_4_2_2_GA date=200710221139)] Started in 34s:234ms

I can not understand what is going wrong . please help.