Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

Problem with Datasource for SQL Server Express

Avatar

Former Community Member

I have set up a development environment to develop some processes for the HCSO client and have a question.  I have a turnkey install on my laptop with MYSQL.  The liveCycle databases are in MYSQL.  I have also installed SQL Server Express on this machine and created a table to query that will control workflow.  I added a datasource configuration in the adobe-ds.xml configuration file.  That configuration is:

<local-tx-datasource>

            <jndi-name>HCSO</jndi-name>

            <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=DBName</connection-url>

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

            <user-name>username</user-name>

            <password>password</password>

            <min-pool-size>1</min-pool-size>

            <max-pool-size>30</max-pool-size>

            <blocking-timeout-millis>60000</blocking-timeout-millis>

            <autoReconnect>true</autoReconnect>

            <idle-timeout-minutes>15</idle-timeout-minutes>

            <prepared-statement-cache-size>100</prepared-statement-cache-size>

            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

            <new-connection-sql>Select id from HCSOUser.eric</new-connection-sql>

            <check-valid-connection-sql>Select id from HCSOUser.eric</check-valid-connection-sql>

            <metadata>

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

            </metadata>

      </local-tx-datasource>

In SQL Server, I created a user and a schema with the same name in a database.  I created a simple table called "eric" with one column called "id".  The user was given the appropriate default schema and given full permissions on the database and table.

In workbench, I added a JDBC query single row activity.  I have configured the datasource as java:/HCSO and also tried java:HCSO.  I then entered the query as "Select id from HCSOUser.eric" and hit test.  Nothing appears in the results area.  I see the following in the server.log:

2009-09-24 14:44:26,437 ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException in method: public abstract java.lang.Object com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterLocal.doBMT(com.adobe.idp.dsc.transaction.TransactionCallback) throws com.adobe.idp.dsc.DSCException:

java.lang.RuntimeException: A result set was generated for update.

      at com.adobe.idp.dsc.jdbc.JDBCService.testExecute(JDBCService.java:616)

      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:592)

      at com.adobe.idp.dsc.component.impl.DefaultPOJOInvokerImpl.invoke(DefaultPOJOInvokerImpl.java:118)

      at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor.java:140)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)

      at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTransactionBMTAdapterBean.java:197)

      at sun.reflect.GeneratedMethodAccessor573.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

      at java.lang.reflect.Method.invoke(Method.java:592)

      at org.jboss.invocation.Invocation.performCall(Invocation.java:345)

      at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)

      at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)

      at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:54)

      at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)

      at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)

      at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:158)

      at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)

      at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:154)

      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)

      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)

      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)

      at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)

      at org.jboss.ejb.Container.invoke(Container.java:873)

      at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:415)

      at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)

      at $Proxy270.doBMT(Unknown Source)

      at com.adobe.idp.dsc.transaction.impl.ejb.EjbTransactionProvider.execute(EjbTransactionProvider.java:95)

      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInterceptor.java:72)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStrategyInterceptor.java:55)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateInterceptor.java:37)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:132)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)

      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)

      at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineImpl.java:115)

      at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:118)

      at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.invoke(AbstractMessageReceiver.java:315)

      at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkEndpoint.invokeCall(SoapSdkEndpoint.java:138)

      at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkEndpoint.invoke(SoapSdkEndpoint.java:81)

      at sun.reflect.GeneratedMethodAccessor710.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

      at java.lang.reflect.Method.invoke(Method.java:592)

      at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)

      at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)

      at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)

      at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)

      at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)

      at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)

      at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)

      at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)

      at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

      at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

      at com.adobe.idp.dsc.provider.impl.soap.axis.InvocationFilter.doFilter(InvocationFilter.java:43)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)

      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)

      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)

      at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)

      at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)

      at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)

      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)

      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)

      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)

      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)

      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)

      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)

      at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)

      at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)

      at java.lang.Thread.run(Thread.java:595)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.

      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)

      at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)

      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)

      at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(Unknown Source)

      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:161)

      at com.adobe.idp.dsc.jdbc.helper.SqlHelper.executeTestUpdate(SqlHelper.java:117)

      at com.adobe.idp.dsc.jdbc.JDBCService.testExecute(JDBCService.java:610)

      ... 82 more

9 Replies

Avatar

Former Community Member

I put the datasource in a separate datasource file and I now see the following error: Exception: HCSO not bound. Cause: HCSO not bound.  So, that did not help.

Avatar

Level 8

Is your schema named HCSOUser or DBName?  The connection string URL you give in the connection string indicates that the database is called DBName while your SQL looks like it is called HCSOUser.

You may try something like:

jdbc:sqlserver://localhost:1433;databaseName=HCSOUser

I believe that your JDBC source should look something like:  java:/HCSO

I've never put the data sources in seperate files, but from what I understand about JBoss as long as the data source file name ends in  '-ds.xml' (a dash NOT an underscore) JBoss should pick the file up.

Another note, changes in the ds file are not hot deployable in JBoss.  You must bounce the server.

Avatar

Former Community Member

Hi, I have the same configuration/installation, but got a different error.

I have the Turnkey Install, Express Mode.  MySQL, JBoss on Windows-7 Ultimate.

Trying to connect to a SQL Server Express 2008 database.

And got the following error in JBoss server log when trying to use the "Query Single Row" JDBC service:

java.lang.RuntimeException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.sqlserver.jdbc.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.sqlserver.jdbc.SQLServerDriver)); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.sqlserver.jdbc.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: No ClassLoaders found for: com.microsoft.sqlserver.jdbc.SQLServerDriver)))

I tried using the latest version of JDBC driver from Microsoft website, as well as the sqljdbc.jar file in LCES2.5's install directory; neither worked.

Where should I put the jar file in?

is there any Windows environment variables I need to set up?

anything I should check?

do you have sample sql-server-datasource.xml file that you can share?

thanks

Avatar

Level 10

You are almost there. Do not copy the JDBC.jar in LCES2 installation folder.

Copy the file into lib directory of JBoss installation directory such as

C:\Jboss\Server\all\lib

After placing the JAR files, please restart JBoss service. There you go..

Nith

Avatar

Former Community Member

Thanks fot the tip, but now I got a different message in the "Test" results of "Query Single Row":

Exception: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host  has failed. java.net.ConnectException: Connection refused: connect); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host  has failed. java.net.ConnectException: Connection refused: connect))

My datasource xml is like this:

<?xml version="1.0" encoding="UTF-8"?>

        <datasources>

        <local-tx-datasource>

        <jndi-name>MSSQL</jndi-name>

        <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=ADOBE</connection-url>

          <use-java-context>false</use-java-context>

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

        <user-name>sa</user-name>

        <password>adobe</password>

          <min-pool-size>10</min-pool-size>

          <max-pool-size>50</max-pool-size>

          <blocking-timeout-millis>60000</blocking-timeout-millis>

          <idle-timeout-minutes>15</idle-timeout-minutes>

          <prepared-statement-cache-size>100</prepared-statement-cache-size>

          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

        <check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>

        <metadata>

          <type-mapping>MS SQLSERVER2005</type-mapping>

        </metadata>

        </local-tx-datasource>

        </datasources>        

Where is the problem and how to fix it?

Thank you.

Avatar

Level 10

1. Are you sure that your JBoss and SQL Server are running on the same machine?

2. The Database Server configured with the default port number (1433)

3. If yes, open a command prompt and type telnet localhost:1433

If the connection successful, try to write a simple java application and run a simple SQL statement.

Keep troubleshooting.. I'm with you.

Nith

Avatar

Former Community Member

telnet says: Could not open connection to the host, on port 23 : Connection failed

why is it port 23?

I have installed SQL Server Express.  During the install it didn't ask me for no port number, everything was default value.

I have SQL Server Management Studio installed on the same machine.

thanks

Avatar

Level 10

There could be some port conflict issue.

Take to your DB administrator and identify the correct port number.

It seems there are no other issues related to DS configuration.