Insert fails when trying to retrieve the inserted row

Question

I have an entity whose id is a generated GUID in a SQL Server db (it's a third-party db for which I can't change the schema) and the field is defined thus;

@Id
@GenericGenerator(name="generator", strategy="guid", parameters = {})
@GeneratedValue(generator ="generator")
@Column(name="FarmID")
public String getId() {
    return id;
}

When I create a new entity and save it, the id is first correctly generated by the GUIDGenerator calling newid(), then the row is inserted using the generated id as a parameter value. However, after the insertion Hibernate tries to retrieve the newly inserted row but fails and it turns out that it's using a blank id value - here's the trace log;

[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
select
    newid()
[main]  AbstractBatcher                TRACE preparing statement
[main]  GUIDGenerator                  DEBUG GUID identifier generated: 5B5495B4-FFE3-4112-B079-4FB799320BA7
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  ConnectionManager              TRACE registering flush begin
[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
insert 
into
    Farm
    (FarmBusinessID, FarmName, PortalFarm, FarmID) 
values
    (?, ?, ?, ?)
[main]  AbstractBatcher                TRACE preparing statement
[main]  BasicBinder                    TRACE binding parameter [1] as [VARCHAR] - F2542B84-82E4-4DF6-BDBB-E830BFE5DDC1
[main]  BasicBinder                    TRACE binding parameter [2] as [VARCHAR] - XXXXXX XXXXX (XXX)
[main]  BasicBinder                    TRACE binding parameter [3] as [BIT] - true
[main]  BasicBinder                    TRACE binding parameter [4] as [VARCHAR] - 5B5495B4-FFE3-4112-B079-4FB799320BA7
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  ConnectionManager              TRACE registering flush end
[main]  HQLQueryPlan                   TRACE find: select id from agronomy.model.hub.Farm where id = :id
[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
select
    farm0_.FarmID as col_0_0_ 
from
    Farm farm0_ 
where
    farm0_.FarmID=?
[main]  AbstractBatcher                TRACE preparing statement
[main]  BasicBinder                    TRACE binding parameter [1] as [VARCHAR] - 
[main]  AbstractBatcher                DEBUG about to open ResultSet (open ResultSets: 0, globally: 0)
[main]  AbstractBatcher                DEBUG about to close ResultSet (open ResultSets: 1, globally: 1)
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  JDBCExceptionReporter          WARN  SQL Error: 8169, SQLState: S0002
[main]  JDBCExceptionReporter          ERROR Conversion failed when converting from a character string to uniqueidentifier.

I'm not aware that I'm explicitly invoking that last SELECT (that fails), so I'm not sure where I can look to see why it's failing. THE SQL error roughly translates as "you can't use an empty string as a uniqueidentifier value", which rather makes sense.

score 0 · Answer 1 · answered Aug 16 '11 at 09:44

I could never get to the bottom of this, but as luck would have it, I'd incorrectly thought it was a third-party db which was giving me this problem, when in fact the schema was under our control.

Simply by changing the primary keys on the tables to BIGINT (set as identity columns) and changing the datatypes of the entity ids to Long, I was able to re-run the test code and the entities were correctly persisted to the database. Everything worked as expected and I could stop tearing my hear out and swearing.

It would have been nice to get to the bottom of why GUIDs weren't working, but in this instance life is simply too short and the alternative is acceptable. Best of luck to anyone who runs up against a similar problem in the future.

Insert fails when trying to retrieve the inserted row

1 Answers1

Linked