SQL Server 2000 Exception

[lenart17]

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????????????

[adrian.brock]

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

[lenart17]

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?

[adrian.brock]

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

[lenart17]

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!!!!!!!!!!

[adrian.brock]

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

[lenart17]

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!

[raja05]

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

[lenart17]

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!

[adrian.brock]

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