9 Replies Latest reply on Feb 21, 2012 9:35 PM by ditto9888

    Problem with Datasource for SQL Server Express

    BPM Developer

      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.ad obe.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.jav a:118)

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

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

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

            at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTran sactionBMTAdapterBean.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(StatelessSessionConta iner.java:214)

            at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionI nterceptor.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:1 58)

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

            at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstance Interceptor.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(EjbTransactionProvi der.java:95)

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

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

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

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

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

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

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

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

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

            at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.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(AbstractMessageReceiv er.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.j ava: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.j ava: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.j ava: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.ja va: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(Http11P rotocol.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.executeStatemen t(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

       

        • 1. Re: Problem with Datasource for SQL Server Express
          BPM Developer Level 1

          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.

          • 2. Re: Problem with Datasource for SQL Server Express
            Hodmi Level 4

            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.

            • 3. Re: Problem with Datasource for SQL Server Express
              ditto9888 Level 1

              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

              • 4. Re: Problem with Datasource for SQL Server Express
                $Nith$ Level 4

                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

                • 5. Re: Problem with Datasource for SQL Server Express
                  ditto9888 Level 1

                  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.

                  • 6. Re: Problem with Datasource for SQL Server Express
                    $Nith$ Level 4

                    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

                    • 7. Re: Problem with Datasource for SQL Server Express
                      ditto9888 Level 1

                      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

                      • 8. Re: Problem with Datasource for SQL Server Express
                        $Nith$ Level 4

                        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.