1 Reply Latest reply on Jan 1, 2002 4:31 PM by shane

    Can't get insert to work properly in SQL SERVER 2000, HELP

    shane Newbie

      Help !!!
      insert,update,delete not working properly for MS SQL server 2000

      I'm using a connection pool with the following Mbean code


      MSSQLPOOL
      org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

      jdbc:microsoft:sqlserver://DR-EVIL:1433;DatabaseName=myDB;SelectMethod=cursor
      1200000
      sa
      0
      sa
      false
      false
      true
      false
      120000
      1800000
      true
      false
      1.0
      0


      which I got from the forum. My views work fine, it's my deletes, updates and inserts that aren't working and it's

      driving me insane. They are being applied in a fashion.

      I'm getting a connection as follows

      Context ctx = new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:/MSSQLPOOL");
      Statement stmt = null;
      conn = ds.getConnection();
      stmt = conn.createStatement( );
      stmt.execute("Exec spInsertMember @userName = 'alpha', @password='snarf'");
      System.out.println("Number of rows affected : " + stmt.getUpdateCount( ));
      stmt.close( );
      OR
      stmt = conn.createStatement( );
      int x = stmt.executeUpdate("Exec spInsertMember @userName = 'beata', @password='snarf'");
      System.out.println("Number of rows affected : " +x);
      stmt.close( );
      //con.close( );

      The weird thing is it is actually printing out 1 row affected, but nothing is getting put into the database !!!
      What I mean is if I performed a view here I would get back what I just entered, which is super, but if I go into

      SQL Server Enterprise Manager, there is nothing there, even though the the view I run through my bean returns the

      two inserts, a view preformed in SQL Server returns empty. Oh By the way I'm using microsofts sql server jdbc

      driver.
      That was with
      <ejb-jar>
      JBoss ResponseProvisioning Application
      <display-name>Response Provisioning EJB</display-name>
      <enterprise-beans>

      <ejb-name>MemberProvisioningEJB</ejb-name>
      com.eContract.ejbs.MemberProvisioningHome
      com.eContract.ejbs.MemberProvisioning
      <ejb-class>com.eContract.ejbs.MemberProvisioningBean</ejb-class>
      <session-type>Stateful</session-type>
      <transaction-type>Container</transaction-type>

      </enterprise-beans>
      </ejb-jar>

      I changed it to

      <transaction-type>Container</transaction-type>
      and I get

      [Default] java.lang.NullPointerException
      [Default] at org.jboss.pool.jdbc.xa.XAPoolDataSource.getConnection(XAPoolDataSource.java:178)
      [Default] at com.eContract.ejbs.MemberProvisioningBean.runSQLQuery(MemberProvisioningBean.java:5
      7)
      [Default] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      [Default] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:42)
      [Default] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java
      :28)
      [Default] at java.lang.reflect.Method.invoke(Method.java:313)
      [Default] at org.jboss.ejb.StatefulSessionContainer$ContainerInterceptor.invoke(StatefulSessio
      nContainer.java:650)
      [Default] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:128)
      [Default] at org.jboss.ejb.plugins.StatefulSessionInstanceInterceptor.invoke(StatefulSessionIn
      stanceInterceptor.java:243)
      [Default] at org.jboss.ejb.plugins.TxInterceptorCMT.invokeNext(TxInterceptorCMT.java:133)
      [Default] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:
      307)
      [Default] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:99)
      [Default] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:195)
      [Default] at org.jboss.ejb.StatefulSessionContainer.invoke(StatefulSessionContainer.java:341)
      [Default] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPContainerInvoke
      r.java:395)
      [Default] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPContainerInvoke
      r.java:476)
      [Default] at org.jboss.ejb.plugins.jrmp.interfaces.GenericProxy.invokeContainer(GenericProxy.j
      ava:335)
      [Default] at org.jboss.ejb.plugins.jrmp.interfaces.StatefulSessionProxy.invoke(StatefulSession
      Proxy.java:136)
      [Default] at $Proxy12.runSQLQuery(Unknown Source)
      [Default] at com.eContract.servlets.MemberProvisioningServlet.doPost(MemberProvisioningServlet.j
      ava:107)
      Any suggestions would be hugely appreciated

      shane