Hibernate with SQL Azure

Question

Hi,

 I have an on premise Java application and the backend database is on SQL Azure. The Java application is using Hibernate as ORM. Now when I am deploying it in tomcat it is working fine from the log I have checked connection is establishing but when after the Logginn it is throwing error like

"org.hibernate.exception.JDBCConnectionException: Cannot open connection "

Detailed error is as follows

s org.hibernate.exception.JDBCConnectionException: Cannot open connection Message: org.hibernate.exception.JDBCConnectionException: Cannot open connection
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
 at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
 at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
 at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
 at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
 at $Proxy3.beginTransaction(Unknown Source)
 at com.hcl.ghgemf.dao.hibernate.EmployeeDaoImpl.getEmployeeByUserName(EmployeeDaoImpl.java:58)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
 at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:126)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:50)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:50)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
 at $Proxy4.getEmployeeByUserName(Unknown Source)
 at com.hcl.ghgemf.common.authentication.AuthenticationHelper.getEmployeeObject(AuthenticationHelper.java:82)
 at com.hcl.ghgemf.Servlet.AuthenticationHelperServlet.processRequest(AuthenticationHelperServlet.java:72)
 at com.hcl.ghgemf.Servlet.AuthenticationHelperServlet.doPost(AuthenticationHelperServlet.java:120)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
 at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1355)
 at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1548)
 at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:2368)
 at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:2270)
 at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:1877)
 at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4403)
 at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4389)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:1457)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:1462)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:1610)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881)
 at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:61)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
 ... 43 more

 

Any help...

Thanks & Regards,

Ritun

Answer 1

Hello, I don't know much about Hibernate. But can you verify if the connection string is correct? Does your SQL Azure firewall setting allow connection from your local machine?

I haven't tested it, but I found a sample about Hibernate and SQL Azure on http://consultingblogs.emc.com/nileeshabojjawar/archive/2010/03/25/azure-sql-with-spring-hibernate.aspx.

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hi,

All,

I made a small table "book" in SQL Azure while trying to insert values to the table via Hibernate (Implemented in Java application) is throwing me error

could not insert: [com.ritun.hibernate.test.Book]

I have checked connection string it is ok (I'm sure because I used the same connection string and properties in a test java application in which I'm trying to fetch datas from table which is in SQL Azure which is working fine...without using hibernate)

Please anyone help!

Writs 

Answer 3

Hi Writs, To be able to figure this out, we will need to get back the SQL Errorcode that is coming back from SQL Azure. This looks like a wrapped exception from hibernate. Are you getting an error code back from SQL Azure in the inner exception?

-cihan

 

Answer 4

"Cloud not insert" is not very helpful. I'm not sure about Hebernate, but does it allow you to get the detailed error message?

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 5

Hi,

Cihan ,

This is the entire error stack,

could not insert: [com.ritun.hibernate.test.Book]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:40)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2158)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2638)
        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
        at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
        at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
        at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
        at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
        at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
        at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
        at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
        at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
        at com.ritun.hibernate.test.IDIncreamentExample.main(IDIncreamentExample.java:33)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
        at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:73)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
        ... 16 more

This is working fine with SQL Server...

Thanks & Regards,

Writs

Answer 6

Hi,

All,

This issue is fixed I had to create clustered Index on that table to SQL Azure.Now it is working fine. But now trouble with another application which is using spring and hibernate same connection string is not working for it throwing error

 

is org.hibernate.exception.JDBCConnectionException: Cannot open connection Message: org.hibernate.exception.JDBCConnectionException: Cannot open connection
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
 at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
 at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
 at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
 at org.hibernate.loader.Loader.doQuery(Loader.java:673)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
 at org.hibernate.loader.Loader.doList(Loader.java:2220)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
 at org.hibernate.loader.Loader.list(Loader.java:2099)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
 at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
 at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
 at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
 at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
 at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
 at com.hcl.ghgemf.dao.hibernate.EmployeeDaoImpl.getEmployeeByUserName(EmployeeDaoImpl.java:71)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
 at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:126)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:50)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:50)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
 at $Proxy4.getEmployeeByUserName(Unknown Source)
 at com.hcl.ghgemf.common.authentication.AuthenticationHelper.getEmployeeObject(AuthenticationHelper.java:82)
 at com.hcl.ghgemf.Servlet.AuthenticationHelperServlet.processRequest(AuthenticationHelperServlet.java:72)
 at com.hcl.ghgemf.Servlet.AuthenticationHelperServlet.doPost(AuthenticationHelperServlet.java:120)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
 at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1368)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1355)
 at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1548)
 at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:2368)
 at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:2270)
 at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:1877)
 at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4403)
 at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4389)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:1457)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:1462)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:1610)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881)
 at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:61)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
 ... 47 more

Any suggestions..

Thanks &  Regards,

Writs

Answer 7

Hi,

All,

 Can anybody tell me whether there is any limitation for SQL Azure of making concurrent connection to the database???

 Because my application was using a connection pooling mechanism for 100 connections....

I removed that one and now the problem is resolved!

Thanks & Regards,

Ritun

Answer 8

Hi All,

There is no problem with hibernate in SQL Azure the connection related problem occurs due to the firewall in SQL Azure. I'm pasting the possible reason from a discussion over here...

To help protect data by default, all access to SQL Azure server is blocked by the SQL Azure firewall; connection attempts originating from the Internet or Windows Azure will not be able to reach your SQL Azure server. In order to begin using SQL Azure server from web applications, one need to specify firewall one or more firewall settings that enable access to SQL Azure server.

 

Connecting From Internet

When a computer attempts to connect to your SQL Azure server from the Internet, the SQL Azure firewall checks the originating IP address of the request against the full set of firewall settings. If the IP address of the request is not within one of the ranges specified, the connection attempt is blocked and does not reach the SQL Azure server.

Connecting from Windows Azure
When an application from Windows Azure attempts to connect to your SQL Azure server, the SQL Azure firewall looks for a specific firewall setting that indicates whether Windows Azure connections are allowed.
A firewall setting with starting and ending address equal to 0.0.0.0 indicates that Windows Azure connections are allowed. If the connection attempt is not allowed, the request does not reach the SQL Azure server.

 

 

 

 

Thanks & Regards

writs