Announcement Announcement Module
Collapse
No announcement yet.
Stored procedure may be run only in unchained transaction mode Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure may be run only in unchained transaction mode

    Transaction management runs perfectly until it hits this a nested stored procedure, and I wonder if there is something using Spring to solve it?

    Code:
    Cause: com.sybase.jdbc3.jdbc.SybSQLException: Stored procedure 'dbo.get_sec_usr_details' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
    I see some sybase changes you can make if you can get a DBA to give you the time of day, I tried wrapping the SP in
    Code:
    SET CHAINED OFF
    EXEC some_procedure ?, ?, ?
     SET CHAINED ON
    .. still no joy

    and tried adding autoCommit false/true in my ProxyDataSource class per some suggestions from Google
    Code:
                proxyConn = ProxyConnection.newInstance(conn, originalUserName);
                proxyConn.setAutoCommit(false);
                ...execute
                proxyConn.setAutoCommit(true);
    even this didn't help
    Code:
     <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
            <property name="nestedTransactionAllowed" value="true"/>
            <property name="rollbackOnCommitFailure" value="true"/>
        </bean>
    if anyone has come upon this with transaction management and nested stored procedure issue (and solved it) I'd really appreciate some insight

  • #2
    This is rather SyBase then Spring problem. Probably, following links would help you, especially part that explains how to change mode for procedure.
    http://manuals.sybase.com/onlinebook...iew/55096;hf=0
    http://manuals.sybase.com/onlinebook...w/53740;hf=0#X

    Comment


    • #3
      Looks like Spring transaction management turns chained mode on

      I drilled through this problem and it looks like Spring transactions is setting the chained mode on:

      my dao method
      Code:
       public List<AgentOrPrincipleDO> getAgentOrPrincList(String agentPrincInd) throws DataAccessException {
              return (List<AgentOrPrincipleDO>) getSqlMapClientTemplate().queryForList("agentOrPrincAlias.getAgentOrPrinc", agentPrincInd);
          }
      modified for troubleshooting
      Code:
       public List<AgentOrPrincipleDO> getAgentOrPrincList(String agentPrincInd) throws UncategorizedSQLException {
              try {
      
                  String getChainedMode = "select @@tranchained";
                  Statement statement = getDataSource().getConnection().createStatement();
                 ResultSet resultSet = statement.executeQuery(getChainedMode);
                  if (resultSet.next()) {
                      String chainedMode = resultSet.getString(1);
                      System.out.println("chainedMode = " + chainedMode);
                  }
      
                  //getDataSource().getConnection().commit();  
                  String setChained = "SET CHAINED OFF";
                  Statement stmt = getDataSource().getConnection().createStatement();
                  //stmt.execute(setChained);
                  //getDataSource().getConnection().commit();
      
      
                  return (List<AgentOrPrincipleDO>) getSqlMapClientTemplate().queryForList("agentOrPrincAlias.getAgentOrPrinc", agentPrincInd);
              } catch (SQLException e) {
                  e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
              }
      
              return null;
      
          }
      and the result of this local test is when i turn Spring transaction management off (uncomment my aop tx advice) the chained mode if off, when I enable Spring tx, chained mode is on.

      has anyone some advice on how to have spring transactions not turn chained on? We're trying to find out what is calling and setting this in the spring code

      thank you

      Comment


      • #4
        Originally posted by holly View Post
        has anyone some advice on how to have spring transactions not turn chained on? We're trying to find out what is calling and setting this in the spring code

        thank you
        There are several ways - you may write data source proxy that explicitly sets connection mode or, if you use c3p0 you may write ConnectionCustomizer (see c3p0 documentation).

        Or, in the worst case, you may read documentation for SyBase JDBC driver
        http://www.inetsoftware.de/products/...ted_Properties

        There is written in the chapter "Implemented Driver Properties"
        chained If set to "true" the driver will change in the CHAINED MODE on autocommit = false.
        And Spring transaction management sets autocommit to false (no big surprise).

        Regards,
        Oleksandr

        Comment


        • #5
          sadly, I've done all of those: ProxyDataSource, sybase jconnect driver (doesn't support creation of JDBC 3 savepoints yet (!!!), setting autocommit false...). well, thank you so much for your feedback.

          kind regards,
          Holly

          Comment


          • #6
            May you post more info about your configuration (OS, Sybase version, Spring version, Java version, JDBC driver vendor and version, part of your context related to datasource and transaction management etc.).

            BTW, one additional remark and one question:

            1. Spring is almost completely agnostic of SyBase, so its does not anything SyBase-related, like intentional switching of transaction mode.

            2. Why you can simply change mode of the stored procedure in question - once and forever? SyBase provides system call intended for this purpose.

            Regards,
            Oleksandr

            Originally posted by holly View Post
            sadly, I've done all of those: ProxyDataSource, sybase jconnect driver (doesn't support creation of JDBC 3 savepoints yet (!!!), setting autocommit false...). well, thank you so much for your feedback.

            kind regards,
            Holly

            Comment


            • #7
              Figured it out

              This is for anyone that runs into this problem and wants to hit their head against the wall:

              In your ProxyDataSource, ... the usual stuff, then after execute()
              Code:
              proxyConn.setAutoCommit(false);
              String chainedSP = "SET CHAINED OFF";
              stmt.execute(chainedSP);
              i was not doing both set chained off AND set autocommit false. Even though the DataSourceTransactionManager sets autocommit to false, you still need to set it on the proxy.

              Then, in your ProxyConnection you have to explicitly add connection.commit();
              Code:
               public void closeProxy() throws SQLException {
              
                       connection.commit();
              
                      String query = "set proxy " + originalUserName;
                      Statement stmt = connection.createStatement();
                      stmt.execute(query);
                  }
              Last edited by Helena; Feb 7th, 2008, 03:21 PM.

              Comment


              • #8
                I'm no Sybase expert, but as far as I can infer from the driver documentation, the "chained" property can be set at the JDBC URL level. So provided that your DataSource is driver-based, you should be able to append "?chained=false" to the specified driver URL there.

                Spring's DataSourceTransactionManager should work fine with its default behavior then, i.e. setting autoCommit=false at the beginning of each transaction. There should be no need to change connection settings or the like in your data access code.

                Note that if you try to access the transactional Connection directly anywhere in your data access code, use DataSourceUtils.getConnection(dataSource) rather than a raw DataSource.getConnection() call. This will make sure that you're always obtaining the current transactional Connection - even when talking to the raw target DataSource there, which would always obtain fresh Connections when calling DataSource.getConnection().

                Juergen

                Comment


                • #9
                  sp_procxmode allows you to set execution mode of stored procedures to "chained", "unchained" or "any".

                  Comment


                  • #10
                    thank you

                    Thanks Juergen for your reply. We only do this (for example) in data access operations:
                    Code:
                     public List<SystemCode> getRateSchedule() throws DataAccessException {
                            return (List<SystemCode>) getSqlMapClientTemplate().queryForList("SystemCodeAlias.rateSchedule");
                        }
                    I had tried the set proc to anymode in the stored procedure per sybase docs. I'm not a sybase or stored procedure expert by any means but this did not solve the issue. We made a JDBCTestHarness unit test to execute a failing Spring transactionally-managed nested (Sybase)transactional stored procedure, turned off spring's tx management , and executed directly through JDBC vs Spring and everything worked as expected but turning spring transaction management back on, it errored out every time. The above code changes in the ProxyDataSource and ProxyConnection were the only strategy that solved the problem...oddly enough.

                    thanks!
                    Last edited by Helena; Feb 8th, 2008, 03:16 PM.

                    Comment


                    • #11
                      How does your proc created.

                      I met the same problem .
                      First, I use an ANT task to create the procedure.
                      sth. like below:

                      <sql
                      driver="${sql.jdbcdriver}"
                      url="${sql.dburl}" userid="${sql.dbuser}" password="${sql.dbpwd}" classpathref="dbunit.depend"
                      src="${spsql.src}" keepformat="true"/>

                      and I get the same error message.

                      Second,I use isql to create it,and it works fine.

                      Confused!!
                      I try again and again,once I change the ANT task

                      <sql
                      driver="${sql.jdbcdriver}"
                      url="${sql.dburl}" userid="${sql.dbuser}" password="${sql.dbpwd}" classpathref="dbunit.depend"
                      src="${spsql.src}" keepformat="true" autocommit="true"/>

                      Wow~~~ fine too.
                      --
                      strangelly. sybase....

                      Comment


                      • #12
                        Not so strange - Sybase connection transaction mode defaults differently depending on state of autocommit. And procedure shall be run in the same transaction mode in which it was created. So behavior observed by you is completely explainable.

                        For details see previous posts (and especially links to the Sybase documentation inside them).

                        Regards,
                        Oeksandr

                        Originally posted by leafgray View Post
                        I met the same problem .
                        First, I use an ANT task to create the procedure.
                        sth. like below:

                        <sql
                        driver="${sql.jdbcdriver}"
                        url="${sql.dburl}" userid="${sql.dbuser}" password="${sql.dbpwd}" classpathref="dbunit.depend"
                        src="${spsql.src}" keepformat="true"/>

                        and I get the same error message.

                        Second,I use isql to create it,and it works fine.

                        Confused!!
                        I try again and again,once I change the ANT task

                        <sql
                        driver="${sql.jdbcdriver}"
                        url="${sql.dburl}" userid="${sql.dbuser}" password="${sql.dbpwd}" classpathref="dbunit.depend"
                        src="${spsql.src}" keepformat="true" autocommit="true"/>

                        Wow~~~ fine too.
                        --
                        strangelly. sybase....

                        Comment


                        • #13
                          Adaptive Server tags all procedures with the transaction mode ("chained" or "unchained") of the session in which they are created.

                          --
                          Thank you,al0

                          I should read that more caful....

                          Comment


                          • #14
                            Originally posted by leafgray View Post
                            ...
                            I should read that more caful....
                            And write more careful as well

                            Comment


                            • #15
                              I run also in this problem too and tried to solve it with an hibernate interceptor, by
                              implementing afterTransactionBegin(). But i cannot get the right connection , on which the transaction starts or the exception org.springframework.transaction.CannotCreateTransa ctionException.
                              Has someone experiences by trying to solve the problem with this approach, because writing and wiring a ProxyDatasource and ProxyConnection is very laborious

                              Comment

                              Working...
                              X