Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Problem with Datasource for SQL Server Express

Avatar

Avatar
Validate 1
Level 1
BPM_Developer
Level 1

Likes

0 likes

Total Posts

28 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
BPM_Developer
Level 1

Likes

0 likes

Total Posts

28 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
BPM_Developer
Level 1

24-09-2009

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

Accepted Solutions (0)

Answers (9)

Answers (9)

Avatar

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile
ditto9888
Level 2

21-02-2012

thanks.

Avatar

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile
_Nith_
Level 7

21-02-2012

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.

Avatar

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile
ditto9888
Level 2

20-02-2012

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

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile
_Nith_
Level 7

20-02-2012

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

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile
ditto9888
Level 2

13-02-2012

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

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile

Avatar
Validate 25
Level 7
_Nith_
Level 7

Likes

121 likes

Total Posts

1,333 posts

Correct Reply

91 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Boost 50
Boost 5
View profile
_Nith_
Level 7

11-02-2012

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

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile

Avatar
Validate 10
Level 2
ditto9888
Level 2

Like

1 like

Total Posts

110 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 1
View profile
ditto9888
Level 2

10-02-2012

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

Avatar
Boost 50
Level 5
Hodmi
Level 5

Likes

68 likes

Total Posts

454 posts

Correct Reply

53 solutions
Top badges earned
Boost 50
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Boost 50
Level 5
Hodmi
Level 5

Likes

68 likes

Total Posts

454 posts

Correct Reply

53 solutions
Top badges earned
Boost 50
Boost 5
Boost 3
Boost 25
Boost 10
View profile
Hodmi
Level 5

25-09-2009

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

Avatar
Validate 1
Level 1
BPM_Developer
Level 1

Likes

0 likes

Total Posts

28 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile

Avatar
Validate 1
Level 1
BPM_Developer
Level 1

Likes

0 likes

Total Posts

28 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
View profile
BPM_Developer
Level 1

24-09-2009

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.