Announcement Announcement Module
Collapse
No announcement yet.
Problem using SimpleJdbcCall within 'multi-statement' transaction over Sybase Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem using SimpleJdbcCall within 'multi-statement' transaction over Sybase

    I am trying to use SimpleJdbcCall to invoke sequences of Sybase stored procedures within the same ('multi-statement') transaction. I'm also leveraging SimpleJdbcCall's ability to obtain the stored procedure's metadata to obtain parameter name/type/position information. Initially this appeared to work fine, but then I noticed that a roll-back triggered after say the second stored procedure call was not rolling back changes made in the first call. The stored procedures have been written to detect the presence a transaction (by inspecting @@trancount) and when there is none to create their own transaction and commit changes so clearly I need to ensure that a global transaction exists prior to invoking them. In addition, all the stored procedures are defined to be run in so-called 'un-chained' mode (sybases default), meaning that transactions must be started with an explicit 'begin tran' statement in contrast to the SQL standards-compatible mode, called chained mode, that implicitly begins a transaction before any data retrieval or modification statement.

    So, to satisfy the requirements of using a set of un-chained stored procedures within a global transaction, at the start of the transaction I am executing:

    _txDataSource.getConnection().createStatement()
    .execute("SET CHAINED OFF ");
    _txDataSource.getConnection().createStatement()
    .execute("begin tran ");
    Then within that transaction SimpleJdbc objects are created and compiled, which in turn accesses database metadata. However, as the database is configured not to permit use of CREATE TABLE operations within tempdb, which seems to be used by SybaseCallMetaDataProvider it indicates a problem obtaining the metadata...:

    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: System_id
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Person_id
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Per_personCode
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Alt_alternateCodeValue
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Alt_AltCodeName_id
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Alt_alternateCodeName
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Alt_system_id
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Alt_system_code
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: Sys_systemCode
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: asAtDate
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: effectiveDate
    6750 [main] DEBUG SimpleJdbcCall - Added declared parameter for [DBUser_gsh]: id
    55015 [main] DEBUG SimpleJdbcCall - JdbcCall call not compiled before execution - invoking compile
    55078 [main] DEBUG CallMetaDataProviderFactory - Using org.springframework.jdbc.core.metadata.SybaseCallM etaDataProvider
    55093 [main] DEBUG CallMetaDataProvider - Retrieving metadata for null/null/DBUser_gsh
    55437 [main] WARN CallMetaDataProvider - Error while retreiving metadata for procedure columns: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
    55437 [main] DEBUG SimpleJdbcCall - Compiled stored procedure. Call string is [{call DBUser_gsh()}]
    55437 [main] DEBUG SimpleJdbcCall - SqlCall for procedure [DBUser_gsh] compiled
    Which then fails a bit later due to lack of correct parameter binding info.

    Does anyone know if there is any way to leverage the metadata retrieval abilities of Spring JDBC within a multi-statement transaction over Sybase?

    I am using Spring 2.5

  • #2
    I have created a rather ugly work around for the above problem by ensuring that the SimpleJdbcCall object is created and compiled on a separate thread - seems to work.

    Comment

    Working...
    X