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

  • Cinquero
    replied
    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...?

    Leave a comment:


  • ratzlow
    replied
    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.

    Leave a comment:


  • whyBish
    replied
    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.

    Leave a comment:


  • msmithi33
    replied
    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.

    Leave a comment:


  • klaus
    replied
    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

    Leave a comment:


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

    Leave a comment:


  • leafgray
    replied
    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....

    Leave a comment:


  • al0
    replied
    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....

    Leave a comment:


  • leafgray
    replied
    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....

    Leave a comment:


  • Helena
    replied
    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, 04:16 PM.

    Leave a comment:


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

    Leave a comment:


  • Juergen Hoeller
    replied
    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

    Leave a comment:


  • Helena
    replied
    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, 04:21 PM.

    Leave a comment:


  • al0
    replied
    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

    Leave a comment:

Working...
X