9 Replies Latest reply on Oct 27, 2003 6:13 PM by Adrian Brock

    SQL Server 2000 Exception

    lenart17 Newbie

      Hi, I need help with the following problem. First I will describe the layout - JBoss 3.2.2 RC4, SQL Server 2000, Sql Server JDBC Driver Service Pack 1. Now for the problem - I am executing a basic stored procedure through the execute method on a callable statement the callable statement is created from a connection which is retrieved from a configured datasource in JBoss. The Problem is that I can successfully execute the stored procedure from a servlet (web container) and when I run the same exact code from a stateless session bean I get the following exception (which is truncated for briefness):

      *** SQLException caught ***
      13:11:36,281 INFO [STDOUT] SQLState: 37000
      13:11:36,281 INFO [STDOUT] Message: Unexpected token: GETALLSPORTS in statement [ call GetAllSports() ]
      13:11:36,281 INFO [STDOUT] Vendor: -11
      13:11:36,281 INFO [STDOUT] NULL ResultSet Returned
      13:11:36,281 INFO [STDOUT] Closing ResultSet!
      13:11:36,291 INFO [CachedConnectionManager] Closing a connection for you. Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@1d281f1
      java.lang.Exception: STACKTRACE
      at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:281)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:498)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:800)
      at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:102)
      at com.trendsrus.mw.ds.sql.SQLDataSource.getConnection(SQLDataSource.java:108)
      at com.trendsrus.mw.rdbms.sqlserver.SQLServerDataAccessComponent.initialize(SQLServerDataAccessComponent.java:58)
      at com.trendsrus.bs.dao.rdbms.RDBMSDataAccessObject.connect(RDBMSDataAccessObject.java:55)
      at com.trendsrus.bs.dao.sportmgr.SQLServerSportManagerDAO.findAllSports(SQLServerSportManagerDAO.java:57)
      at com.trendsrus.bs.sportmanager.ejb.SportManagerBean.getAllSports(SportManagerBean.java:61)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)


      HERE IS My datasource configuration file:
      <local-tx-datasource>
      <jndi-name>SportStatisticsDS</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      <user-name>TrendsRUsUser</user-name>
      letmein2
      <!-- sql to call when connection is created
      <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->

      <!-- sql to call on an existing pooled connection when it is obtained from pool
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->
      <!-- this will be run before a managed connection is removed from the pool for use by a client-->
      <!--<check-valid-connection-sql>select * from something</check-valid-connection-sql> -->

      <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
      <!--<min-pool-size>5</min-pool-size> -->

      <!-- The maximum connections in a pool/sub-pool -->

      <!--<max-pool-size>20</max-pool-size> -->

      <!-- The time before an unused connection is destroyed -->
      <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
      <idle-timeout-minutes>5</idle-timeout-minutes>

      <!-- Use the security domain defined in conf/login-config.xml
      <security-domain>HsqlDbRealm</security-domain>-->
      </local-tx-datasource>

      ANY Suggestions????????????

        • 1. Re: SQL Server 2000 Exception
          Adrian Brock Master

          You are not closing the connection to return it to the pool.
          Something like:

          Connection c = datasource.getConnection();
          try
          {
          // Do work
          }
          finally
          {
          c.close();
          }

          And the same for statements.

          Regards,
          Adrian

          • 2. Re: SQL Server 2000 Exception
            lenart17 Newbie

            I would love to have the opportunity to close the statement and the connection, but the exception isthrown during the execution of :

            objCallStatement.executeQuery();

            Thnaks for the effort but any other ideas?

            • 3. Re: SQL Server 2000 Exception
              Adrian Brock Master

              Show the code snippet.
              Most likely you have an exception leak that bypasses your close

              enable TRACE logging so you can see what is happening
              for categories
              org.jboss.resource
              org.jboss.tm

              in conf/log4j.xml (there are examples).

              Regards,
              Adrian

              • 4. Re: SQL Server 2000 Exception
                lenart17 Newbie

                Thnak You! It is nice to have help avaialble. I have been working at this issue for 4 days now. Here is the latest log info. The top log info is the execution from the servlet and the second is from execution from the EJB. I did notice that there are some log statements in the servlet case where there is not in the EJB case. I highlighted these statements.


                TEST SQL SERVER SPORTMANAGERDAO from SERVLET

                21:43:19,683 INFO [STDOUT] Inside SQLServerDataAccessComponent.initialize(IRequestBuffer requestBuffer)
                21:43:19,683 INFO [STDOUT] Calling DataAccessComponent.initialize()
                21:43:19,693 INFO [STDOUT] class=class org.jboss.resource.adapter.jdbc.WrapperDataSource
                21:43:19,693 DEBUG [IdleRemover] internalRegisterPool: registering pool with interval 300000 old interval:
                9223372036854775807
                21:43:19,693 DEBUG [IdleRemover] internalRegisterPool: about to notify thread: old next: 1067222749693, new next:
                1067222749693
                21:43:19,703 DEBUG [LocalManagedConnectionFactory] Using properties: {user=TrendsRUsUser, password=letmein2}
                21:43:19,703 DEBUG [LocalManagedConnectionFactory] Checking driver for URL:
                jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor
                21:43:19,703 DEBUG [LocalManagedConnectionFactory] Driver not yet registered for url:
                jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor
                21:43:19,753 DEBUG [LocalManagedConnectionFactory] Driver already registered for url:
                jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor
                21:43:22,217 INFO [STDOUT] Returned from DataAccessComponent.initialize()
                21:43:22,217 INFO [STDOUT] Leaving SQLServerDataAccessComponent.initialize(IRequestBuffer requestBuffer)
                21:43:59,841 INFO [STDOUT] Connection not null
                21:44:03,296 INFO [STDOUT] Preparing Callable Statement
                21:44:03,296 INFO [STDOUT] SQLString = {call GetAllSports()}
                21:44:04,227 INFO [STDOUT] Statement prepared
                21:44:04,227 INFO [STDOUT] java.sql.SQLWarning: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed database
                context to 'SportStatistics'.
                21:44:04,227 INFO [STDOUT] java.sql.SQLWarning: [Microsoft][SQLServer 2000 Driver for JDBC]Language changed to us_english
                21:44:04,237 INFO [STDOUT] java.sql.SQLWarning: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed language
                setting to us_english.
                21:44:04,237 INFO [STDOUT] Executing QUERY on CallableStatement
                21:44:48,651 INFO [STDOUT] Closing ResultSet!
                21:44:48,651 INFO [STDOUT] CLosing Connection!
                21:44:48,651 INFO [STDOUT] Returned from findAllSports()!
                21:44:48,651 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@151b1b7
                21:44:48,651 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@6a9e79
                21:44:48,661 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@1ca4955
                21:44:48,661 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@1949a87
                21:44:48,661 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@12d34a6
                21:44:48,661 INFO [STDOUT] com.trendsrus.bs.dto.sport.SportDTO@dbb335



                TEST SQL SERVER SPORTMANAGERDAO from EJB

                21:55:20,990 INFO [STDOUT] Getting initial context!
                21:55:20,990 INFO [STDOUT] Retrieved initial context!
                21:55:21,030 INFO [STDOUT] Located SportManager Home interface!
                21:55:21,060 DEBUG [JUnitVersionHelper]
                <getTestCaseName([testSportManagerGetAllTeams(com.trendsrus.as.cactus.CactusTestSportManagerEJB)])
                21:55:21,060 DEBUG [JUnitVersionHelper] >getTestCaseName = [testSportManagerGetAllTeams]
                21:55:21,080 INFO [STDOUT] Inside Sport Manager EJB
                21:55:21,180 INFO [STDOUT] Inside SQLServerDataAccessComponent.initialize(IRequestBuffer requestBuffer)
                21:55:21,180 INFO [STDOUT] Calling DataAccessComponent.initialize()
                21:55:21,180 INFO [STDOUT] class=class org.jboss.resource.adapter.jdbc.WrapperDataSource
                21:55:21,180 INFO [STDOUT] Returned from DataAccessComponent.initialize()
                21:55:21,180 INFO [STDOUT] Leaving SQLServerDataAccessComponent.initialize(IRequestBuffer requestBuffer)
                21:55:23,894 INFO [STDOUT] Connection not null
                21:55:24,605 INFO [STDOUT] Preparing Callable Statement
                21:55:24,605 INFO [STDOUT] SQLString = {call GetAllSports()}
                21:55:24,605 INFO [STDOUT] Statement prepared
                21:55:24,615 INFO [STDOUT] Executing QUERY on CallableStatement
                21:55:25,567 INFO [STDOUT]
                *** SQLException caught ***
                21:55:25,567 INFO [STDOUT] SQLState: 37000
                21:55:25,567 INFO [STDOUT] Message: Unexpected token: GETALLSPORTS in statement [ call GetAllSports() ]
                21:55:25,567 INFO [STDOUT] Vendor: -11
                21:55:25,567 INFO [STDOUT] java.lang.NullPointerException
                21:55:25,567 INFO [STDOUT] NULL ResultSet Returned
                21:55:25,567 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
                21:55:25,567 INFO [STDOUT] CLosing Connection!
                21:55:25,567 INFO [STDOUT] Returned from findAllSports()!
                21:55:25,567 INFO [STDOUT] Message returned: []


                Can't wait for your repely!!!!!!!!!!

                • 5. Re: SQL Server 2000 Exception
                  Adrian Brock Master

                  You have a NullPointerException in there,
                  it is also telling you the SQL is wrong.
                  Now it is showing you are not closing the statement.

                  Regards,
                  Adrian

                  • 6. Re: SQL Server 2000 Exception
                    lenart17 Newbie

                    The SQL statement is identical, for it is the same class and method executing. What are the following statements telling me and why aren't they there during the execution of the EJB?

                    21:43:19,693 DEBUG [IdleRemover] internalRegisterPool: registering pool with interval 300000 old interval:
                    9223372036854775807
                    21:43:19,693 DEBUG [IdleRemover] internalRegisterPool: about to notify thread: old next: 1067222749693, new next:
                    1067222749693
                    21:43:19,703 DEBUG [LocalManagedConnectionFactory] Using properties: {user=TrendsRUsUser, password=letmein2}
                    21:43:19,703 DEBUG [LocalManagedConnectionFactory] Checking driver for URL:
                    jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor
                    21:43:19,703 DEBUG [LocalManagedConnectionFactory] Driver not yet registered for url:
                    jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor
                    21:43:19,753 DEBUG [LocalManagedConnectionFactory] Driver already registered for url:
                    jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor

                    Here is my -ds.xml

                    <local-tx-datasource>
                    <jndi-name>SportStatisticsDS</jndi-name>
                    <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SportStatistics;SelectMethod=cursor</connection-url>
                    <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
                    <user-name>TrendsRUsUser</user-name>
                    letmein2
                    <!-- sql to call when connection is created
                    <new-connection-sql>some arbitrary sql</new-connection-sql>
                    -->

                    <!-- sql to call on an existing pooled connection when it is obtained from pool
                    <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
                    -->
                    <!-- this will be run before a managed connection is removed from the pool for use by a client-->
                    <!--<check-valid-connection-sql>select * from something</check-valid-connection-sql> -->

                    <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
                    <!--<min-pool-size>5</min-pool-size> -->

                    <!-- The maximum connections in a pool/sub-pool -->

                    <!--<max-pool-size>20</max-pool-size> -->

                    <!-- The time before an unused connection is destroyed -->
                    <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
                    <idle-timeout-minutes>5</idle-timeout-minutes>

                    <!-- Use the security domain defined in conf/login-config.xml
                    <security-domain>HsqlDbRealm</security-domain>-->
                    </local-tx-datasource>


                    Here is my my ejb-jar.xml:
                    <ejb-name>SportManager</ejb-name>
                    com.trendsrus.bs.sportmanager.ejb.SportManagerHome
                    com.trendsrus.bs.sportmanager.ejb.SportManager
                    <local-home>com.trendsrus.bs.sportmanager.ejb.SportManagerLocalHome</local-home>
                    com.trendsrus.bs.sportmanager.ejb.SportManagerLocal
                    <ejb-class>com.trendsrus.bs.sportmanager.ejb.SportManagerBean</ejb-class>
                    <session-type>Stateless</session-type>
                    <transaction-type>Container</transaction-type>
                    <resource-ref>

                    <res-ref-name>jdbc/SportStatisticsDSRef</res-ref-name>
                    <res-type>javax.sql.DataSource</res-type>
                    <res-auth>Container</res-auth>
                    <res-sharing-scope>Shareable</res-sharing-scope>
                    </resource-ref>


                    Here is my jboss.xml:

                    <resource-ref>
                    <res-ref-name>jdbc/SportStatisticsDSRef</res-ref-name>
                    <res-type>javax.sql.DataSource</res-type>
                    <jndi-name>java:/SportStatisticsDS</jndi-name>
                    </resource-ref>

                    If I need to send you the code I will do that. But there a few components involved in the execution. Thanks again for helping!

                    • 7. Re: SQL Server 2000 Exception
                      Raja Master

                      There is probably a Syntax error at
                      21:55:25,567 INFO [STDOUT] SQLState: 37000
                      21:55:25,567 INFO [STDOUT] Message: Unexpected token: GETALLSPORTS in statement [ call GetAllSports()

                      this call. Can you run it from a SQL Editor and check if "call GetAllSports()" returns a value.

                      -Raj

                      • 8. Re: SQL Server 2000 Exception
                        lenart17 Newbie

                        The SQL statement is contained within a component and this component executes correctly in the web container and not in the EJB container. Same exact calls from an EJB as there is in the servlet. I copied/pasted the code from one component to another. I have a feeling there is a configuration error. Why else would there be extra debug statements printed when I retireve the connection from the servlet component verses the EJB component. I am at a lost though, I have tried everything that I could think of. I have been through the documentation aq zillion times and nothing is standing out. I am starting to second guess whether chooising JBoss for a production platform was the right idea! Just a little frustrated. Thanks for your help!

                        • 9. Re: SQL Server 2000 Exception
                          Adrian Brock Master

                          Go back to first principles.
                          You have been looking at the problem too long :-)

                          You are receiving an error message about
                          GETALLSPORTS being an invalid token.
                          Check your sql, delete the method and rewrite if necessary.

                          Previously you showed a NullPointerException? What was
                          the cause?

                          The [STDOUT] is your logging, I haven't seen the jboss logging?

                          Basically what I'm saying is, you haven't shown a jboss error.
                          You are writing some sql yourself and it is wrong.

                          By all means try a different a container, it might give
                          a better error message in this circumstance, but I
                          doubt it.
                          The code is you -> jdbc driver (no jboss)

                          Regards,
                          Adrian