Re: UpdatableRecord and identity / sqlserver
got it!
tks so much Lukas
2016-10-21 16:59 GMT+02:00 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
>
>
> 2016-10-21 16:53 GMT+02:00 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
>>
>> 2016-10-21 16:34 GMT+02:00 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
>>>
>>> 2016-10-19 16:18 GMT+02:00 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> *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());
}
Identity identity=rec.getTable().getIdentity();
return Stream.of(rec.fields())
.filter(x->!x.getName().equ
Re: UpdatableRecord and identity / sqlserver
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
2016-10-21 16:53 GMT+02:00 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
>
> 2016-10-21 16:34 GMT+02:00 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
>>
>> 2016-10-19 16:18 GMT+02:00 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> *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());
>>> }
>>> Identity identity=rec.getTable().getIdentity();
>>> return Stream.of(rec.fields())
>>> .filter(x->!x.getName().equals(identity.getField().getName()))
>>> .collect(Collectors.toList());
>>> }
>>> }
>>>
>>>
>>> 2016-10-19 15:50 GMT+02:00 Lukas Eder :
>>>
Hi Denis,
Thank you very much for your detailed feedback.
Hmm, perhaps I misun
Re: UpdatableRecord and identity / sqlserver
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
2016-10-21 16:34 GMT+02:00 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
>
> 2016-10-19 16:18 GMT+02:00 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> *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());
>> }
>> Identity identity=rec.getTable().getIdentity();
>> return Stream.of(rec.fields())
>> .filter(x->!x.getName().equals(identity.getField().getName()))
>> .collect(Collectors.toList());
>> }
>> }
>>
>>
>> 2016-10-19 15:50 GMT+02:00 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
>>>
>>> 2016-10-18 12:22 GMT+02:00 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="prepe
nd-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
Re: UpdatableRecord and identity / sqlserver
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
2016-10-19 16:18 GMT+02:00 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> *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());
> }
> Identity identity=rec.getTable().getIdentity();
> return Stream.of(rec.fields())
> .filter(x->!x.getName().equals(identity.getField().getName()))
> .collect(Collectors.toList());
> }
> }
>
>
> 2016-10-19 15:50 GMT+02:00 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
>>
>> 2016-10-18 12:22 GMT+02:00 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="prepe
>>> nd-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 identityTableList=(List>> >)ctx.configuration().data(IDENTITY_TABLE_LIST);
>>> String fulltableName=new TableNameParser(ctx.sql()).tab
>>> les().iterator().next();
>>> Patter
Re: UpdatableRecord and identity / sqlserver
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> *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());
}
Identity identity=rec.getTable().getIdentity();
return Stream.of(rec.fields())
.filter(x->!x.getName().equals(identity.getField().getName()))
.collect(Collectors.toList());
}
}
2016-10-19 15:50 GMT+02:00 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
>
> 2016-10-18 12:22 GMT+02:00 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="prepe
>> nd-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 identityTableList=(List)ctx.configuration().data(
>> IDENTITY_TABLE_LIST);
>> String fulltableName=new TableNameParser(ctx.sql()).tab
>> les().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).anyMatc
>> h(x->x.equals(tableName))){
>> ctx.sql("SET IDENTITY_INSERT " + tableName + " ON " + ctx.sql());
>> }
>> }
>> }
>> @Override
>> public void renderEnd(ExecuteContext ctx) {
>> enableIdentityKeySetting(ctx);
>> }
>> }
>>
>> 2016-10-18 11:29 GMT+02:00 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
>>>
>>> 2016-10-18 8:52 GMT+02:00 Denis Miorandi :
>>>
consider that my record is not created with a refre
Re: UpdatableRecord and identity / sqlserver
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
2016-10-18 12:22 GMT+02:00 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 identityTableList=(List)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);
> }
> }
>
> 2016-10-18 11:29 GMT+02:00 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
>>
>> 2016-10-18 8:52 GMT+02:00 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
>>>
>>> 2016-10-17 20:52 GMT+02:00 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
2016-10-14 13:09 GMT+02:00 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());
>
>
>
>
> Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha
> scritto:
>>
>> 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
>>
> --
> You received this message because you are subscribed to the Google
> Groups "jOOQ User Group" group.
> To unsubscr
Re: UpdatableRecord and identity / sqlserver
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
identityTableList=(List)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);
}
}
2016-10-18 11:29 GMT+02:00 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
>
> 2016-10-18 8:52 GMT+02:00 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
>>
>> 2016-10-17 20:52 GMT+02:00 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
>>>
>>> 2016-10-14 13:09 GMT+02:00 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());
Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha
scritto:
>
> 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
>
--
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.
>>>
>>> --
>>> 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/to
>>> pic/jooq-user/hydJX8cNHfY/unsubscribe.
>>> To unsubscribe from this group and all its to
Re: UpdatableRecord and identity / sqlserver
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 2016-10-18 8:52 GMT+02:00 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 > > 2016-10-17 20:52 GMT+02:00 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 >> >> 2016-10-14 13:09 GMT+02:00 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()); >>> >>> >>> >>> >>> Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha >>> scritto: 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 >>> -- >>> 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. >>> >> >> -- >> 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/to >> pic/jooq-user/hydJX8cNHfY/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> jooq-user+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Dott. Denis Miorandi > via dei Vignai 27 > 38060 - Nogaredo (TN) > > > > -- > 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. > -- 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.
Re: UpdatableRecord and identity / sqlserver
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 2016-10-17 20:52 GMT+02:00 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 > > 2016-10-14 13:09 GMT+02:00 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()); >> >> >> >> >> Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha >> scritto: >>> >>> 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 >>> >> -- >> 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. >> > > -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- Dott. Denis Miorandi via dei Vignai 27 38060 - Nogaredo (TN) -- 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.
Re: UpdatableRecord and identity / sqlserver
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 2016-10-14 13:09 GMT+02:00 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()); > > > > > Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha > scritto: >> >> 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 >> > -- > 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. > -- 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.
Re: UpdatableRecord and identity / sqlserver
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()); Il giorno giovedì 13 ottobre 2016 17:27:53 UTC+2, Denis Miorandi ha scritto: > > 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 > -- 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.
