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

  • #16
    Solved for read-only

    I ran into this same issue for a read-only stored proc and solved it simply with the @Transactionl annotation:

    Code:
    @Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
    I got the idea from this article:
    http://www.ibm.com/developerworks/ja...ary/j-ts2.html

    See the paragraph about the 'Not Supported' attribute.

    Comment


    • #17
      Setting NOT_SUPPORTED may not be a good idea. This means that if you have multiple calls each one potentially have a different spid. If you have stored procs that are using the spid as keys (stateful stored procs ) then you will get random behaviour. NOT_SUPPORTED is only useful if you truely don't want transactions.

      Comment


      • #18
        Hi,

        we struggled with the same problem and came up with the following solution:
        add to the connection jdbc URL the param IS_CLOSED_TEST=INTERNAL and declare your stored procs with EXEC sp_procxmode 'your_sp', 'anymode'

        This works perfectly fine with Springs TX Mgmt.

        For some background info have a look at
        http://infocenter.sybase.com/help/in...c/BHCIAIBF.htm

        and especially
        http://manuals.sybase.com/onlinebook...iew/55096;hf=0.

        Comment


        • #19
          The trick is to disable server-initiated transactions:

          Code:
          <?xml version="1.0" encoding="utf-8"?>
          <!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
          <hibernate-configuration>
              <session-factory>
                  <!-- Enable Hibernate's automatic session context management -->
                  <property name="current_session_context_class">thread</property>
                  <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
                  <property name="hibernate.show_sql">true</property>
                  <property name="hibernate.format_sql">true</property>
                  <!--property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
                  <property name="hibernate.connection.url">jdbc:jtds:sybase://127.0.0.1:5033/unittest;TDS=5.0;prepareSQL=1</property-->
                  <property name="hibernate.connection.driver_class">com.sybase.jdbc4.jdbc.SybDriver</property>
                  <property name="hibernate.connection.url">jdbc:sybase:Tds:127.0.0.1:5033/unittest</property>
                  <!--property name="hibernate.connection.url">jdbc:sybase:Tds:127.0.0.1:5033/unittest</property-->
                  <property name="hibernate.connection.username">sa</property>
                  <property name="hibernate.connection.password"></property>
                  <!-- sufficient if (!) we use optimistic offline locking -->
                  <property name="hibernate.connection.isolation">1</property>
                  <!-- don't like no leaks -->
                  <property name="hibernate.connection.SQLINITSTRING">set string_rtruncation on</property>
                  <!-- fix it baby -->
                  <property name="hibernate.connection.CHARSET">iso_1</property>
                  <!-- needed to run stored procedures against Sybase ASE ... otherwise the server initiates CHAINED TX MODE -->
                  <property name="hibernate.connection.SERVER_INITIATED_TRANSACTIONS">false</property>
                  <property name="hibernate.connection.autocommit">false</property>
                  <!-- Hibernate has some problems when jconnect4 tries to be too JDBC compliant ... -->
                  <property name="hibernate.connection.BE_AS_JDBC_COMPLIANT_AS_POSSIBLE">false</property>
                  <!-- @TODO where do I put the timezone setting?? (-Duser.timezone=GMT ... not that great) -->
                  <property name="hibernate.default_schema"></property>
                  <property name="hibernate.dialect">org.hibernate.dialect.SybaseASE15Dialect</property>
                  <property name="hibernate.c3p0.min_size">1</property>
                  <property name="hibernate.c3p0.max_size">10</property>
                  <property name="hibernate.c3p0.timeout">10</property>
                  <property name="hibernate.c3p0.max_statements">500</property>
                  <mapping class="uk.co.pookey.hibernate.model.Blog" />
                  <mapping class="uk.co.pookey.hibernate.model.SysObject" />
                  <mapping class="uk.co.pookey.hibernate.model.SysColumn" />
                  <mapping class="uk.co.pookey.hibernate.model.SysProcedure" />
                  <mapping class="uk.co.pookey.hibernate.model.SysReference" />
                  <mapping class="uk.co.pookey.hibernate.model.SysType" />
              </session-factory>
          </hibernate-configuration>
          However, my testing results in some strange results where updates done using stored procedures seemingly get ignored sometimes...?

          Comment

          Working...
          X