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

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

The following comment has been added to this issue:

     Author: Richard Scranton
    Created: Fri, 21 Nov 2003 9:29 AM
       Body:
Hibernate is generating code that behaves differently depending on whether you allow Sybase (via the connection property DYNAMIC_PREPARE) to precompile prepared statements at the server.

If you insert into a table having an identity column, the value of the recently added identity is available as @@identity.  If Hibernate generates a string of SQL statements like this:

insert ...
select @identity

as distinct prepared statements, "select @@identity" will not return the expected value if the connection property DYNAMIC_PREPARE=true, because on the server side, the insert statement becomes a dynamically-generated stored procedure, and the value of the recently added identity is not available outside the stored procedure that produced the insert.  Leaving DYNAMIC_PREPARE=false gives the expected behavior, but gives performance no better than sending unprepared strings of SQL text to the server.

---------------------------------------------------------------------
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: Fri, 21 Nov 2003 9:29 AM

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