Problem: Updates with generated DAO or xxxRecord object dont work on MS SQL Server Express with tabeles with auto increment IDs

[onlinegameing78 via jOOQ User Group]

 

 

Hi,


I use the express version of jooq with MS SQL Server Express 2012. All 
tables have one int ID with auto increment (Identity in MS SQL) as primary 
key. Insert work as expected, but I have problems with updates. I tried 
several different approaches. Here are short summary:

 

*1. Use generated DAO class to store an updated POJO instance. The POJO 
also generated. (withExecuteWithOptimisticLocking = fales)*

XDao dao= new XDao(config);

dao.update(pojo);


Result:

Update is successful, but I get an exception “Exactly one row expected for 
refresh. Record does not exist in database.“. Reason: After the update the 
DAO tries to reload the entry but use for this query:

Select [XXX].YYY from XXX where XXX.YYY is null. I have no idea why this ID 
reload happens on an update. For insert this is correct and work. Also I 
don’t know why IS NULL is used. The ID was for the update call correct set 
in the POJO and is also correct used for the update.

 

*2. Use generated DAO class to store an updated POJO instance. The POJO 
also generated. (withExecuteWithOptimisticLocking = true)*

XDao dao= new XDao(config);

dao.update(pojo);

 

Result:

jooq now tries to check if the object was changed, what in my point of view 
make for a POJO no sense (because no change tracking like record object). I 
get a exception:

[select…. Where [XXX] = ?]; SQL state [24000]; error code [0]; Column 1 / 
XXX is read-only.; nested exception is java.sql.SQLException: Column 1 / 
XXX is read-only.

If I try this SQL statement direct in the DB it works. I assume that there 
is also a prepared update part that is not printed. 

 

*3. Use the generated Record class to store an updated The POJO also 
generated. (withExecuteWithOptimisticLocking = true)*

BpmAsyncExternalTaskQueueEntryRecord entry = create.newRecord(*ZZZ*,  pojo);

entry.update();

 

Result:

Exception

Access database using jOOQ; uncategorized *SQLException* for SQL [select …

 where XXX = ?]; SQL state [24000]; error code [0]; Column 1 / XXX is 
read-only.; nested exception is *java.sql.SQLException*: Column 1 / XXX is 
read-only.

 

*4. Use the generated Record class to store an updated The POJO also 
generated. (withExecuteWithOptimisticLocking = false)*

BpmAsyncExternalTaskQueueEntryRecord entry = create.newRecord(*ZZZ*,  pojo);

entry.update();

 

Result:

Access database using jOOQ; SQL [update YYY set 
[prioCenter_PROD].[dbo].[BpmAsyncExternalTaskQueueEntry].[BpmAsyncExternalTaskQueueEntryID]
 
= ?, 

… (other fields)

where XXX.YYY = ?]; Cannot update identity column YYYY.; nested exception 
is *java.sql.SQLException*: Cannot update identity column YYY.

 

jooq tries to update the auto increment field. I can bypass this with 
entry.changed(XXX.YYY, false), but this can’t be the indented use or?

 

Had anyone an idea what the problem is? Or is this the normal behavior? In 
the insert case I have no problems. Also the generated ID will correct 
loaded into the object after the insert.


I tried com.microsoft.sqlserver.jdbc.SQLServerDriver and 
net.sourceforge.jtds.jdbc.Driver. Same result, only the error code is 
changed.

 

I also tried to load the record based on the id directly (no generation 
based of the POJO), make a change and then call update. But the result is 
the same.

 

I thought maybe the generator don’t identify the key successful. But inside 
works fine and also the generated table looks good:

@Override

    public Identity<YYY, Integer> getIdentity() {

        return Keys.XXX;

    }

 

Currently the only way I find, was to manually code this statement

create

.update(T_BPM_ASYNC_EXTERNAL_TASK_QUEUE_ENTRY)

.set(entry)

.where(YYY.XXX

.eq(entry.getXXX()))

.execute();


Ideas?

 

Thanks

noname

-- 
You received this message because you are subscribed to the Google Groups "jOOQ 
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.