Merant and Microsoft - Unable to Create Prepared Statement

[diathesis]

I've tried JBoss with a variety of SQL Server drivers, including Merant and the new Microsoft driver based on Merant.

Free TDS and the Bridge both tend to give me issues, which I accept. Inet Opta seems to be working well enough, but I get errors with both Merant and the Microsoft driver, as follows:

[User] TRANSACTION ROLLBACK EXCEPTION:Load failed; nested exception is:
 java.sql.SQLException: Unable to create PreparedStatement!; nested excep
tion is:
 java.rmi.ServerException: Load failed; nested exception is:
 java.sql.SQLException: Unable to create PreparedStatement!
[User] java.rmi.ServerException: Load failed; nested exception is:
[User] java.sql.SQLException: Unable to create PreparedStatement!
[User] java.sql.SQLException: Unable to create PreparedStatement!
[User] at org.jboss.pool.jdbc.xa.wrapper.XAClientConnection.prepareStatement(XA
ClientConnection.java:169)
[User] at org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand.jdbcExecute(JDBCCommand.j
ava:158)
[User] at org.jboss.ejb.plugins.jaws.jdbc.JDBCLoadEntityCommand.execute(JDBCLoa
dEntityCommand.java:147)
[User] at org.jboss.ejb.plugins.jaws.JAWSPersistenceManager.loadEntity(JAWSPers
istenceManager.java:156)
[User] at org.jboss.ejb.plugins.CMPPersistenceManager.loadEntity(CMPPersistence
Manager.java:362)
[User] at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntityS
ynchronizationInterceptor.java:286)
[User] at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstance
Interceptor.java:208)
[User] at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockIntercep
tor.java:136)
[User] at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.ja
va:133)
[User] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxIntercep
torCMT.java:307)
[User] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:9
9)
[User] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.
java:128)
[User] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:195)
[User] at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:427)
[User] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPCon
tainerInvoker.java:483)
[User] at org.jboss.ejb.plugins.jrmp.interfaces.GenericProxy.invokeContainer(Ge
nericProxy.java:335)
[User] at org.jboss.ejb.plugins.jrmp.interfaces.EntityProxy.invoke(EntityProxy.
java:133)
[User] at $Proxy6.getPassword(Unknown Source)
[User] at com.mediumone.thesquare.servlet.Login.authenticateUser(Login.java:101
)
[User] at com.mediumone.thesquare.servlet.Login.doPost(Login.java:57)
[User] at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
[User] at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[User] at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:4
05)
[User] at org.apache.tomcat.core.Handler.service(Handler.java:287)
[User] at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372
)
[User] at org.apache.tomcat.core.ContextManager.internalService(ContextManager.
java:812)
[User] at org.apache.tomcat.core.ContextManager.service(ContextManager.java:758
)
[User] at org.apache.tomcat.service.http.HttpConnectionHandler.processConnectio
n(HttpConnectionHandler.java:213)
[User] at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
416)
[User] at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java
:501)
[User] at java.lang.Thread.run(Unknown Source)

Anyone have any suggestions on why that would be?

- Geoffrey

[erik777]

Funny, cuz I was just searching for an answer to this same problem. I, too, am using the new Microsoft driver.

I keep suspecting it has something to do with connection pooling, but don't really know. I am using BMP with container managed transactions.

I am trying to deploy the SavingsAccount example in Sun's J2EE tutorial in JBoss. There were times I would occassionally get part way through. I found that under certain circumstances, if I restart JBoss, then it gets through part of it before giving this error. Once this problem starts, it is endless.

One note, it appears to be specific to each bean instance. In other words, whereas instance #1 will start doing this, it does not mean that bean #2 will not have a chance to perform a successful statement before it, too, starts to do it. I presume each instance is getting its own connection.

It does create the records in the database before creating this problem.

I created my own JSP client to test the EJB. Here is my current web page output:

Duke
Successfully created duke! Caught an exception: null
Caught an exception: ejbLoad: Unable to create PreparedStatement!; nested exception is: javax.ejb.EJBException: ejbLoad: Unable to create PreparedStatement!

Joe
Successfully created joe!Caught an exception: null

----------------
Here is the ejbLoad:

public void ejbLoad() {

try {
loadRow();
} catch (Exception ex) {
throw new EJBException("ejbLoad: " +
ex.getMessage());
}
}

and here is the loadRow():

private void loadRow() throws SQLException {

String selectStatement =
"select firstname, lastname, balance " +
"from savingsaccount where id = ? ";
PreparedStatement prepStmt =
con.prepareStatement(selectStatement);

prepStmt.setString(1, this.id);

ResultSet rs = prepStmt.executeQuery();

if (rs.next()) {
this.firstName = rs.getString(1);
this.lastName = rs.getString(2);
this.balance = rs.getDouble(3);
prepStmt.close();
}
else {
prepStmt.close();
throw new NoSuchEntityException("Row for id " + id +
" not found in database.");
}
}

[erik777]

I got ejbLoad() to work. What I cannot get to work no matter what I try is ejbSave(). I believe that no matter what I do, it seems to have the same PrepareStatement cached from the ejbLoad(). The only time it goes further is if I substitute a SELECT with the exact same signature as the ebjLoad, indicating this is true.

I tried everything to get a different connection in ejbSave to no avail. Nullifying it at any place between the ejbLoad's prepare and ejbSave's is futile. Here is some output I generate with System.out showing what is happening:

[SavingsAccountEJB] Activated bean SavingsAccountEJB with id = 836
[Default] -- Running ejbLoad()
[Default] -- Beginning loadRow()
[Default] -- Running makeConnection()
[Default] -- Running closePreviousConnection()
[Default] -- closePreviousConnection(): con is null
[Default] -- End of closePreviousConnection()
[SQLDB] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (com.microsoft.jdbc.sqlserver.SQLServerConnection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[SQLDB] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@58ca48' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@59780d'.
[SQLDB] Pool SQLDB [4/4/50] gave out new object: org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@59780d
[Default] -- makeConnection(): con is open.
[Default] -- makeConnection() got a con
[Default] -- Finishing loadRow()
[Default] -- Nullifying con in loadRow()
[Default] -- Nullifying con in loadRow() #2
[Default] -- Running ejbStore()
[Default] -- Beginning storeRow()
[Default] -- storeRow(): con is already null
[Default] -- Making a connection
[Default] -- Running makeConnection()
[Default] -- Running closePreviousConnection()
[Default] -- closePreviousConnection(): con is null
[Default] -- End of closePreviousConnection()
[Default] -- makeConnection(): con is open.
[Default] -- makeConnection() got a con
[Default] -- storeRow(): we have an open connection!
[Default] -- storeRow(): Preparing a statement
[Default] -- storeRow(): Error, nullifying con...
[SavingsAccountEJB] javax.ejb.EJBException: ejbStore: -- storeRow(): error at -TRYING-: -- storeRow(): error at -TRYING-: Unable to create PreparedStatement!

Is there a way to get ejbSave() to work? I've seen other posts indicating there is, but can't say I understand exactly what I need to do in my case.

Erik

[diathesis]

I'm curious--have either of you tried the Merant driver? I had the same problem with the Merant driver, so I'm curious if other people have as well. Since the Merant driver is listed in the JBoss documentation, it seems as if /someone/ had it working, but I get the identical problems with the current Merant driver that I do on the Microsoft one, surprise, surprise.

[erik777]

I switched to MySQL using InnoDB tables for transactions, and now the problem is gone. I haven't tested transactions from JBoss, yet. But, hey, now that I'm using an open source database, I now have a complete open source solution (Apache/Tomcat/JBoss/MySQL/InnoDB).

[diathesis]

Unfortunately, we have several clients tied to MS SQL Server, and I was considering using JBoss for one of them.

[erik777]

The problem isn't with SQL Server or JBoss, but just the Merant driver and Microsoft's dirivitive of it. There are pleny of other Microsoft drivers out there if you are willing to pay. For me, it wasn't worth it because SQL Server was just going to be a temporary solution until I selected a good open source database. This merely expedited my decision, and MySQL is the choice.

[swiftrs]

Solution:

With the Microsoft driver the problem seems to be solved by setting the driver parameter SelectMethod=cursor.

E.g. in jboss.jcml you have to specify

jdbc:microsoft:sqlserver://:1433;DatabaseName=;SelectMethod=cursor

in the MBean section.


I hope this will work for you (and me :-)

Rainer

[chsiao]

I try the "SelectMethod" and it works for me. So, I don't have "Create Prepared Statement" problem anymore. However, I still couldn't run the CD example. I got the following error message when running ant cmp-cd-upload. Any help or suggestion is appreciated. Thanks.

- chsiao

[Default] Caught FinderException in CDCollection::deleteAll()
[Default] Added CD with id=1, title=Great Organ Works/Bach JS, artist=Koopman, type=classical, organ , notes=Incl. Toccata & Fugue in D minor
[CDCollectionBean] TRANSACTION ROLLBACK EXCEPTION:null Embedded Exception
Could not create entity:java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]Invalid object name 'CDBean'.; nested exception is:
javax.ejb.EJBException: null Embedded Exception
Could not create entity:java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]Invalid object name 'CDBean'.
[CDCollectionBean] javax.ejb.CreateException: Could not create entity:java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]Invalid object name 'CDBean'.
[CDCollectionBean] at org.jboss.ejb.plugins.jaws.jdbc.JDBCCreateEntityCommand.execute(JDBCCreateEntityCommand.java:139)
[CDCollectionBean] at org.jboss.ejb.plugins.jaws.JAWSPersistenceManager.createEntity(JAWSPersistenceManager.java:128)
[CDCollectionBean] at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:231)
[CDCollectionBean] at org.jboss.ejb.EntityContainer.createHome(EntityContainer.java:616)
[CDCollectionBean] at java.lang.reflect.Method.invoke(Native Method)

[diathesis]

Can't comment on the CD example, because I've never tried it. My homegrown application works just fine with SelectMethod=cursor, though, so I'm happy. Thanks for the suggestion, it seems to be working for me at last, and three times faster than the Opta trial we were evaluating.

[gavotte]

That worked, but I have another problem when select-for-update is set to true for an entity bean. Here is the message.

java.sql.SQLException: [Microsoft][SQLServer JDBC Driver][SQLServer]FOR UPDATE cannot be specified on a READ ONLY cursor.

Any idea? Thanks.