[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-485) DYNAMIC_PREPARE and @@identity

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Jeff Turner
    Created: Wed, 26 Nov 2003 3:53 PM
       Body:
> Can you alter the code that is generated for the Sybase case so that
> it works in either case?  Sybase support provided the following, which
> seems to address the issue.  I combines the insert and the select of
> the new identity value into a single prepared statement so that both
> are in the same compiled object on the server.
> 
> String nl=System.getProperty("line.separator");
> PreparedStaement ps=conn.prepareStatement(
>         "insert into Yadd (val1,val2) values ( 1,2 )"
>         + nl
>         + "select @@identity");

(on behalf of Richard Scranton, whose reply went to leg...@at...)
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-485


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-485
    Summary: DYNAMIC_PREPARE and @@identity
       Type: Bug

     Status: Unassigned
   Priority: Major

    Project: Hibernate2
 Components: 
             core

   Assignee: 
   Reporter: Richard Scranton

    Created: Thu, 20 Nov 2003 1:22 PM
    Updated: Wed, 26 Nov 2003 3:53 PM

Description:
Using the Sybase jConnect jdbc driver version 4.5 or 5.5, if the connection property DYNAMIC_PREPARE is set to "true", "select @@identity" will always return a "0".  This is an artifact of the Sybase implementation of prepared statements.  Statements compiled at the server become stored procedures marked by the "DYN" token, and appear named as dyn100, dyn101, etc in the
captured TDS stream.  The value of @@identity is lost at the end of a procedure, so a sequence of prepared statements like:

insert into Yadda (val1,val2) values ( 1,2 )
select @@identity

will execute as expected when DYNAMIC_PREPARE is false.  If it is set to true, the statement "select @@identity" will return "0".  Sybase suggests something like this workaround to get the expected behavior:

String nl=System.getProperty("line.separator");
PreparedStaement ps=conn.prepareStatement(
        "insert into Yadd (val1,val2) values ( 1,2 )"
        + nl
        + "select @@identity");

Leaving DYNAMIC_PREPARE set false gives away throughput if the queries being sent and parsed are of significant size.  TDS dumps show they are being sent and parsed with each invocation.  Setting DYNAMIC_PREPARE to true allows reuse of the parsed queries.



---------------------------------------------------------------------
JIRA INFORMATION:
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

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira