10 Replies Latest reply on Nov 2, 2001 9:36 AM by Ken Chen

    Merant and Microsoft - Unable to Create Prepared Statement

    Geoffrey Wiseman Newbie

      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

        • 1. Re: Merant and Microsoft - Unable to Create Prepared Stateme
          Erik Sliman Apprentice

          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.");
          }
          }

          • 2. Re: Merant and Microsoft - Unable to Create Prepared Stateme
            Erik Sliman Apprentice

            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

            • 3. Re: Merant and Microsoft - Unable to Create Prepared Stateme
              Geoffrey Wiseman Newbie

              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.

              • 4. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                Erik Sliman Apprentice

                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).

                • 5. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                  Geoffrey Wiseman Newbie

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

                  • 6. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                    Erik Sliman Apprentice

                    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.

                    • 7. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                      Rainer Schmitz Newbie

                      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

                      • 8. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                        chenchen hsiao Newbie

                        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)

                        • 9. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                          Geoffrey Wiseman Newbie

                          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.

                          • 10. Re: Merant and Microsoft - Unable to Create Prepared Stateme
                            Ken Chen Newbie

                            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.