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

An object relational-mapping (ORM) library for Java

Brought to you by: davidedalto, dreab8, epbernard, gsmet, and 5 others

hibernate-issues

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

From: <leg...@at...> - 2003-11-21 01:39:24

The following comment has been added to this issue:

     Author: Gavin King
    Created: Thu, 20 Nov 2003 7:38 PM
       Body:
I'm not quite sure what you are trying to tell us here....
---------------------------------------------------------------------
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: Thu, 20 Nov 2003 7:38 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

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

From: <leg...@at...> - 2003-11-21 15:29:24

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

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

From: <leg...@at...> - 2003-11-24 13:02:24

The following comment has been added to this issue:

     Author: Gavin King
    Created: Mon, 24 Nov 2003 7:00 AM
       Body:
I mean, I still can't tell what you want me to do about this....

so sybase is broken ... how can we help?
---------------------------------------------------------------------
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: Mon, 24 Nov 2003 7:00 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

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

From: <leg...@at...> - 2003-11-26 21:53:36

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

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

From: <leg...@at...> - 2004-01-07 10:31:34

The following comment has been added to this issue:

     Author: YR
    Created: Wed, 7 Jan 2004 4:30 AM
       Body:
Hi,

I have asked a similar question in the Hibernate forum :
http://forum.hibernate.org/viewtopic.php?t=926010&highlight=identity+jconnect

This is a major problem. If it can't be solved by adding a sybase-specific handling of identity keys, then, you have to document it :
- or Sybase Identity feature is not supported by Hibernate (please be clear on this point)
- or Hibernate doesn't support precompilation of statements AND the use of Sybase Identity keys.

Thanks for your feedback and long life to Hibernate !


---------------------------------------------------------------------
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, 7 Jan 2004 4:30 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

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

From: <leg...@at...> - 2004-01-08 07:02:26

The following comment has been added to this issue:

     Author: Gavin King
    Created: Thu, 8 Jan 2004 1:01 AM
       Body:
I'm sorry - I simply do not see how it is *our* problem that Sybase has a bug in their JDBC driver. You pay money to Sybase, make them fix their bugs.
---------------------------------------------------------------------
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: Thu, 8 Jan 2004 1:01 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