3 Replies Latest reply on Nov 5, 2003 8:33 AM by philriley001

    sybase and stored procedure

    gladrags

      Dear Friends

      I have developed a stateless session bean which calls the stored procedure in sybase backend.I am using jconnect driver.

      I have changed the stored procedure backend
      sp_procxmode procname ,'anymode'


      I get the following exception

      com.sybase.jdbc2.jdbc.SybSQLException

      SELECT INTO command not allowed within multi-statement transaction.


      /**
      * ReportMasterSessionBean
      *
      * @ejb:bean name="ae/reliance/dac/ReportMasterSession"
      * display-name="ReportMasterSession"
      * type="Stateless"
      * transaction-type="Container"
      * trans-attribute="NotSupported"
      *
      * jndi-name="ae/reliance/dac/ReportMasterSession"
      */

      apart from other methods i am connecting to database using following method


      /**
      * Gets JDBC connection from connectionpool
      *
      * @return the JDBC connection
      * @ejb:interface-method view-type="remote"
      */
      public java.sql.Connection getConnection() {

      java.sql.Connection con = null;
      try {
      //System.out.println("Inside the getConnection method");

      Context ctx = new InitialContext();
      //ds=(javax.sql.DataSource)ctx.lookup("java:jdbc/SybaseDB");
      javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:jdbc/SybaseDB");
      //System.out.println("After getting the datasource" + ds);
      con = ds.getConnection();
      ctx.close();

      //System.out.println("Getting connection in getConnection " + con);

      } catch (Exception e) {
      System.out.println("Exception occured while getting datasource");
      System.out.println("Exception occured "+e);
      }

      return con;
      }

      the method which calls the procedure

      /**
      * getUserRightsRep
      *
      * @return StringBuffer Description of the Return Value
      * @exception java.rmi.RemoteException Description of the Exception
      * @ejb:interface-method view-type="remote"
      * @ejb:transaction-type="NotSupported"
      * This method selects the appointments from the table based on location and appointment
      * Status.
      */

      public StringBuffer getUserRightsRep(long usercode) throws java.rmi.RemoteException
      {

      //System.out.println("getProgramList,inside method");
      java.sql.Connection con = null;
      java.sql.ResultSet rs=null;
      StringBuffer sbuf=null;
      long reqid=0;

      try {

      con = getConnection();

      String sql = "{ call p_dac_user_rights_xml_rep (?) }";
      java.sql.CallableStatement cs = con.prepareCall(sql);
      cs.setLong(1,usercode);

      rs = cs.executeQuery();
      sbuf=new StringBuffer("<?xml version='1.0'?>");
      while(rs.next())
      {
      sbuf=sbuf.append(rs.getString("xmlstring1"));
      sbuf=sbuf.append(rs.getString("xmlstring2"));
      sbuf=sbuf.append(rs.getString("xmlstring3"));
      }


      //System.out.println("getProgramList after EXECUTE QUERY"+sbuf);
      } catch (java.sql.SQLException e) {
      try {
      //System.out.println("Exception occured ");
      System.out.println("Exception occured "+e);
      } catch (Exception ex) {}
      } finally {
      try {
      if (con != null)
      con.close();
      } catch (java.sql.SQLException e) {
      //System.out.println("Exception occured inside finally ");
      System.out.println("Exception occured "+e);
      }
      }

      return sbuf;
      }



      Can anyone suggest me more about transaction and how it behaves.

      i tried to setAutoCommit(false) but it thorw me another error

      SET CHAINED command not allowed within multi-statement transaction.

        • 1. Re: sybase and stored procedure
          gladrags

          I am using stored procedure in sybase which uses the temp database internally.I am using jconnect driver and configured as shown above


          Most of stored procedures work when i changed using

          sp_procxmode ,'anymode'



          • 2. Re: sybase and stored procedure
            gladrags

            I changed the session bean as follows which was featured in one of the forums in sybase.


            * @ejb:bean name="ae/reliance/dac/ReportMasterSession"
            * display-name="ReportMasterSession"
            * type="Stateless"
            * transaction-type="Bean"
            *
            * jndi-name="ae/reliance/dac/ReportMasterSession"


            in the getConnection method


            if (con.getAutoCommit())
            con.setAutoCommit(false);
            con.commit();

            execute procedure here.......

            while(rs.next())
            {
            get the values from different columns
            }

            con.commit();

            if(!con.getAutoCommit())
            {
            con.commit();
            con.setAutoCommit(true);
            }

            still here too i am facing the same problem.... as

            The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.



            • 3. Re: sybase and stored procedure
              philriley001

              I've had similar problems

              I think it's because a select into creates a temporary table for it's result set [or if you're doing so manually in your procedure] - which you cannot do inside a transaction.

              I think that the JConnect starts a transaction whenever it executes a procedure etc., so you'll have to do one of two things in your stored proc:

              1) Put the data into a permanent table, and just wipe the contents of it each time
              2) Check the @@transstate in your procedure - if there is a transaction open, commit it, create the temp table, then start a new transaction.
              Then, at the end of your code - commit your new transaction and if the old trans state had a transaction open, begin a new one again.

              Course - the problem with this method is that you loose all transactional integrety that JBoss gives you - so it's kind of a no-brainer - go with the first.

              The third option would be to have the stored procedure just do a simple select, and put all your business logic into the EJB.

              But as we all know, Business Logic runs a lot faster in a stored procedure :)