UpdatableRecord and identity / sqlserver

[Denis Miorandi]

Hi,

      I'm using dslCtx.createNewRecord(TABLE) to create a record, mapping from my objectgraph (not jooq pojos) to jooq record. Record is an UpdatableRecord with IDENTITY.

Issue is my mapper map also KEY/IDENTITY that is null so operation that is an insert (due to null key) fail cause jooq specify ID on an identity table.

Question is modify my mapper the only way (avoiding to place identity field on record if value is null) or

are there some other way to do automatically before record.insert(), i.e. removing identity field from record?

All in all can I remove a field from jooq record?

Denis

[Denis Miorandi]

ok, solved myself. Using jooq 3.8 is possible to pass to record.insert(xx) a Collection, so this method do the work of removing identity

preserving all fields except identity

Identity<?, ?> identity=rec.getTable().getIdentity();

return Stream.of(rec.fields())

.filter(x->!x.getName().equals(identity.getField().getName()))

.collect(Collectors.toList());

[Lukas Eder]

Hi Denis,

Thanks for the update. That's indeed one option to prevent setting the identity value. You could also reset it prior to storing using record.reset(Field). Or you could implement a RecordListener that does this prior to each store(), insert() call.

Note, the reason why jOOQ sets the identity value by default is because some databases allow for explicit overriding the identity value. I know that SQL Server is a bit special here, but I'm sure that an "emulated" identity (DEFAULT with sequence generated value) can also be overridden, I think - so jOOQ doesn't make any assumptions and just does what users ask it to do, i.e. set the identity value...

Hope this helps,

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.

[Denis Miorandi]

consider that my record is not created with a refresh() operation, but is created using create.newRecord(MYTABLE)

mapped from another object graph (from rest api).

Does reset work anyway? It seems to depend from record state, may I 

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/hydJX8cNHfY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Dott. Denis Miorandi
via dei Vignai 27
38060 - Nogaredo (TN)

[Lukas Eder]

Hi Denis,

record.reset(Field) does the following two things:

- Unset the internal changed flag for that field (so it won't be considered for INSERT / UPDATE)

- Reset the value of the field back to record.original(Field), which is NULL for new records

So, it should work in your case as well.

Hope this helps,

Lukas

[Denis Miorandi]

ok it sets value to null, but it doesn't remove it from insert query, so on sql server identity it doesn't work.

I suspect my solution with prepend IDENTITY_INSERT TO QUERY is the only solution

public class IdentityListener extends DefaultExecuteListener {

private static final long serialVersionUID = 1L;

public static final String PREPEND_IDENTITY_INSERT="prepend-identity-insert";

public static final String IDENTITY_TABLE_LIST="identityTableList";

/**

* Check if PREPREND mode is enable, if is an insert, and if is an identity table

* If all conditions match enable IDENTITY_INSERT for this transaction

* @param ctx

*/

private void enableIdentityKeySetting(ExecuteContext ctx){

if (!ctx.configuration().family().equals(SQLDialect.SQLSERVER))

return;

Query q=ctx.query();

if (ctx.configuration().data(PREPEND_IDENTITY_INSERT) != null

&& (boolean)ctx.configuration().data(PREPEND_IDENTITY_INSERT)

&& q instanceof Insert) {

@SuppressWarnings("unchecked")

List<String> identityTableList=(List<String>)ctx.configuration().data(IDENTITY_TABLE_LIST);

String fulltableName=new TableNameParser(ctx.sql()).tables().iterator().next();

Pattern p = Pattern.compile("\\[(.*?)\\]");

Matcher m = p.matcher(fulltableName);

while (m.find()) {

fulltableName= m.group(m.groupCount());

}

final String tableName=fulltableName; 

// .replace("dbo","").replaceAll("\\[", "").replaceAll("\\]","").replaceAll("\\.", "")

if (identityTableList.stream().map(String::toLowerCase).anyMatch(x->x.equals(tableName))){

ctx.sql("SET IDENTITY_INSERT " + tableName + " ON " + ctx.sql());

}

}

}

@Override

public void renderEnd(ExecuteContext ctx) {

enableIdentityKeySetting(ctx);

}

}

[Lukas Eder]

Hi Denis,

Thank you very much for your detailed feedback.

Hmm, perhaps I misunderstood your original question. Maybe I can better understand the problem if you can provide some full client code that helps reproduce the issue?

Best Regards,

Lukas

[Denis Miorandi]

to be clear, my solution works (using removeIdentityFields method showed here) . I just would like to know if there is a better way instead removing field from record manually, so:

the missing part is the following. storeToDb is used to persist a record. On my mapper (rest/jackson object graph to releational) I would like always map keys wheter is identity or not. Mapper should just map and not take care about db stuffs.

I would like if it's an identity my store method ignore key / manage it.

Also when run my junit test I want to force insert with id on identity tables to make some repeteable functional tests (with identity should not be repetable), so that this is why I use IdentityListener only in junit tests, all in all:

- on insert into identity tables key should not be set (otherwise sql error)

- on insert into identity tables with IDENTITY_INSERT=ON key should be set.
  In that case I use IdentityListener showed in previus post to allow it

on first scenario happens that key is null because (I want to insert) from my object to relational mapper, and jooq try to do an insert with a null key because null key is in the record.

It's not enough to set field to null (reset) I need also to remove fields from record. 

I know it's a little complicated cause it regards also my internal logic. Is it quite clear?

@Override

public int storeToDb(ApiBaseObject object, UpdatableRecordImpl<?> rec) {

int rows = 0;

if (object == null)

return rows;

ServerOperation op = metadataHelper.checkServerOperation(object);

if (op==null)

return rows;

switch (op) {

case NONE:

break;

case INSERT:

rec.insert(removeIdentityFields(rec));

break;

case UPDATE:

rec.update();

break;

case DELETE:

rows = rec.delete();

break;

}

return rows;

}

/**

* Id identity and identity forced insert not enabled, remove IDENTITY KEY from record

* @param rec

* @return

*/

@SuppressWarnings("unchecked")

public List<Field<?>> removeIdentityFields(UpdatableRecordImpl<?> rec){

if (rec.configuration().data(IdentityListener.PREPEND_IDENTITY_INSERT)!=null &&

((Boolean)rec.configuration().data(IdentityListener.PREPEND_IDENTITY_INSERT))){

return Arrays.asList(rec.fields());

[Lukas Eder]

Hi Denis,

Thank you very much for your additional details. Indeed, when you set a null value, jOOQ will try to insert that NULL value into the database (because that's just what you might have wanted jOOQ to do).

I've tried the following:

CREATE TABLE t_identity_pk (

  id INTEGER IDENTITY(1,1) NOT NULL,

  val int,

  CONSTRAINT pk_t_identity_pk PRIMARY KEY (id)

)

And then:

TIdentityPkRecord rec = create().newRecord(T_IDENTITY_PK);

rec.setId(null);

rec.setVal(null);

rec.reset(T_IDENTITY_PK.ID);

rec.insert();

This seems to work perfectly. The query being executed is this one:

insert into [test].[dbo].[t_identity_pk] ([val])

values (null)

I'm probably still missing some detail in your explanation where you mentioned that using reset is not enough, because it works for me. Why would you need to remove the fields from the record?

Cheers,

Lukas

[Denis Miorandi]

I'm not sure about this, all in all you mean reset also removing field from record or in some way works with identity also from a newRecord?

If reset do this is ok to me.

Probably I didn't tried the right mix. You example seems to be exactly my scenario.

I'll give it a try. Probably next time I need to do an isolated test like yours, my bad.

Tks Lukas

[Lukas Eder]

UpdatableRecord.store() insert() and update() will send only values to the database that were changed. As in UpdatableRecord.changed(field).

UpdatableRecord.reset(field) will reset a value to the "initial value" (null if it's a new record, or the database value if it is a record fetched from the database) and reset the changed(field) flag to false, which means the value will not be sent to the database upon INSERT or UPDATE statements.

In your case, you should call record.reset(identityColumn) in the normal case, and proceed with what you're doing in the JUnit case, and you should be all set.

Indeed, isolated test cases do help :) (but it isn't always easy to isolate the desired behaviour...)

Hope this helps,

Lukas

[Denis Miorandi]

got it!

tks so much Lukas