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

[onlineg...@googlemail.com]

 

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

[TheGPLOG]

I also tried this
YYYRecord logEntry = create.fetchOne(XXX,
        XXX.YYY
            .equal(yyy));
    logEntry.setZZZ(zzzz);
    logEntry.store(); (or logEntry.update();)

Update/ Store works, but the "auto refresh" fails also
org.jooq.exception.NoDataFoundException: Exactly one row expected for refresh. Record does not exist in database.
select ... where XXX.YYYY is null

I tried also to set settings.withUpdatablePrimaryKeys(false); manual to false (default)

CREATE TABLE [dbo].[YYYY](
    [XXXX] [int] IDENTITY(1,1) NOT NULL,
...
 CONSTRAINT [PK_YYY] PRIMARY KEY CLUSTERED
(
    [XXX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[Lukas Eder]

Hello,

Thank you very much for your messages. What jOOQ version are you using?

Cheers,

Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

... sorry, and what SQL Server SQLDialect version?

[TheGPLOG]

JOOQ 3.8.4. After your question I tried (not all) but many combination of the variants of my 2 post with SQLDialect.SQLSERVER2012, SQLDialect.SQLSERVER2008 and SQLSERVER. I can’t see differences in this fast test. Maybe there is a problem with the generated classes or I use them wrong. I am not sure what is the normal behavior.

here are some other setting which I have added after testing.

jooqConfiguration.set(SQLDialect.SQLSERVER);
Settings settings = new Settings();
settings.withExecuteWithOptimisticLocking(false);
settings.withUpdatablePrimaryKeys(false);
// http://www.jooq.org/doc/3.8/manual-single-page/#statement-type
settings.withStatementType(StatementType.PREPARED_STATEMENT);

jooqConfiguration.setSettings(settings);

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[TheGPLOG]

Hi,

I can provide more information, if necessary. I tried also a new DB with a small Demoproject, but same result.

Greetings

[nazymko....@gmail.com]

I'm late, but probably you have timestamp in your table and set configure it as part of primary key,

and because of some reason(can find it in database specification ) it was changed , 

so now you have absolutely different entities (regards to the primary keys) and trying to update 'not existing' entity