[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3685) Sybase - SchemaImport cannot create stored procedures in chained transaction mode

[Gail Badner (JIRA)]

Sybase - SchemaImport cannot create stored procedures in chained transaction mode
---------------------------------------------------------------------------------

                 Key: HHH-3685
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3685
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
            Reporter: Gail Badner


In order to create a Sybase stored procedure that can be executed in a transaction (not autocommit), either:
1) the stored procedure needs to be created in a transaction
or 2) the system stored procedure, sp_procxmode, has to be called (sp_procxmode <stored-proc-name> 'chained')

SchemaExport executes DDL using autocommit, so Sybase stored procedures created with SchemaExport cannot be used in a transaction.

It is not possible to run SchemaExport with autocommit=false. 

This causes the unit tests for Sybase stored procedures to fail with a message like:
"Stored procedure 'simpleScalar' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.'

A workaround is to add a <database-object> tag containing the command to change the stored procedure to chained mode. This tag needs to follow the tag that defines the new stored procedure.

For example, adding the following after the definition of paramHandling in the mapping for SybaseCustomSQLTest will change the paramHandling stored procedure so that it can be used in a transaction.

   <database-object>
        <create>
        sp_procxmode paramHandling, 'chained'
        </create>
        <drop/>
    </database-object>


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira