Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Default value sequence
 

Pages: 1
Bugs & Issues
Default value sequence
Page:1/1 

  Print all messages in this thread  
Poster Message
dg1
User



Location:

Joined on:
15-Nov-2017 15:30:32
Posted:
11 posts
# Posted on: 20-Nov-2017 14:33:21.  
Hi,

When inserting or updating entity that has column with default value from sequence (nextval function in postgresql) value I pass for that column is ignored.

In update query, parameter has this value: <undefined value>.

In insert query, there is no parameter at all, in VALUES in place of value is: nextval('"public"."MySeq"').

When inserting new entity I would expect that only if I pass null, not if I pass value.

Is this intentionally? If so maybe field should be readonly in LLBLGen designer.

LLBLGen 4.2
PostgreSQL 94.

Thank you for your help,
David.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8150 posts
# Posted on: 20-Nov-2017 15:14:26.  
It sounds like the table metadata is telling LLBLGenPro that the field is sequenced, so it's treated like that. How does the DDL script of that table look like?

BTW, you could remove the sequence from the field mappings tab of the entity in LLBLGen Pro Designer. That way, it will be used by your DB engine only if it's not specified in your .net code.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
dg1
User



Location:

Joined on:
15-Nov-2017 15:30:32
Posted:
11 posts
# Posted on: 21-Nov-2017 09:29:10.  
DDL:

ALTER TABLE "public"."MyTable" ALTER COLUMN "MyField" SET DEFAULT nextval('"MySeq"'::regclass);

When I remove sequence, on insert I can set value, but when I pass null sequence is not used. Update doesn't work, field is not in query, I can't update row, I'm using uow.AddUpdateEntitiesDirectlyCall.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 21-Nov-2017 14:51:34.  
The default sequence (the sequence in the default value) is used in inserts. When a sequence is set, like in your table, the value specified is ignored, as the sequence is to be used, this is by design. So you don't have to specify a value, when inserting, it will insert a value from the default sequence.

When updating, sequences are never used, a field is updated with the value you specify. So if that's null, null is the new value.

The sequence is read as metadata when you refresh the catalog in the designer, and when you create entities, the sequence should be set as the default sequence for the field, but it's only used for inserts.

I'm not entirely sure what the order of events is that you want to perform, could you give a small code example what you tried to and what failed? Thanks

For databases, they use default values on insert, not update. At least I never heard of a database which will use a default value if you update a field to null (it will instead simply give an error), they will only use defaults when a new row is inserted and the field is NULL


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
dg1
User



Location:

Joined on:
15-Nov-2017 15:30:32
Posted:
11 posts
# Posted on: 21-Nov-2017 15:24:46.  
Scenario is following:

For records on import I have values and I want to keep them on insert, not to use new value generated by sequence.

For new record I want to use new value generated by sequence.

Sequence has offset so there is no overlapping.

Update doesn't work, I don't want to use sequence value on update I want to set value manually for records that I have values and can't set them on insert.

Code:

newEntity.SomeOtherField = "test";
newEntity.SequenceField = -100;
uow.AddUpdateEntitiesDirectlyCall(newEntity, bucket);


SomeOtherField is update and SequenceField is not, it keeps old value. In sql update statement I don't see SequenceField.

I don't see anything special about this field in LLBLGen designer, only sequence in field mappings.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14639 posts
# Posted on: 22-Nov-2017 05:25:02.  
Reproduced...

LLBLGen v.5.2.2
PostgreSQL v.10
Npgsql v.3.2.5

DDL
Code:
CREATE TABLE public."Customer"
(
    "Id" integer NOT NULL,
    "Name" text COLLATE pg_catalog."default" NOT NULL,
    "Code" integer DEFAULT nextval('"defaultCode"'::regclass),
    CONSTRAINT "CustomerPK" PRIMARY KEY ("Id")
)


Insert Succeed with default value set on the database:
Code:
var customer = new CustomerEntity();
customer.Name = "ABC";
customer.Id = 1;
adapter.SaveEntity(customer);


Then I removed the Read-Only flag on the "Code" field from the Designer.
The following insert also succeeds
Code:
var customer = new CustomerEntity();
customer.Name = "DEF";
customer.Id = 2;
customer.Code = 2;
adapter.SaveEntity(customer);


The following update fails, whether the "Code" is set to null or to another int value:
Code:
var customer = new CustomerEntity(2);
adapter.FetchEntity(customer);

customer.Code = 3;
customer.Name = "DZY";
adapter.SaveEntity(customer);


Generated SQL:
Code:
UPDATE "public"."Customer" SET "Name"=:p1 WHERE ( "public"."Customer"."Id" = :p2)
    Parameter: :p1 : String. Length: 1073741824. Precision: 0. Scale: 0. Direction: Input. Value: "DZY".
    Parameter: :p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.




  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 22-Nov-2017 09:50:51.  
Looking into it.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 22-Nov-2017 10:40:17.  
The reason this happens is because a sequence is assigned to the field, and it's therefore seen as an 'identity' field. Even if it's not marked as 'readonly' it's still seen as an identity field, and the runtime skips changed identity fields (as it would cause errors in some databases). In general this isn't a problem as fields which have a sequence assigned are never written to, other than the value obtained from the sequence.

However, in your case this is a problem.

I understand from your last post you import data which is then written to the DB? Only for inserts you want to use the sequence, and updates should always use the value specified?

I don't know how many entity types you have this with, if it's a couple, you could try to map those entities again (but now without a sequence) and e.g. rename them with an 'ForUpdate' suffix (so CustomerForUpdate) so you can use that entity to update the underlying rows.

If all the entities in your project are affected then it's of course a bigger problem. It's not common to have a sequenced field which isn't a PK, and also if it's sequenced to update it. I guess you have this because of data which is e.g. 'synced' from a different location with a main database?

In theory, there's another way (modify the persistence info object when it's obtained from the persistence provider for a given scenario) but that's quite complex and as it hides things, it's not recommended, as it's not really clear from looking at your code what might happen. (it requires a class derived from DataAccessAdapter which overrides InsertPersistenceInfo )


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
dg1
User



Location:

Joined on:
15-Nov-2017 15:30:32
Posted:
11 posts
# Posted on: 23-Nov-2017 08:30:13.  
When we import from old DB we want to keep old value, when we insert new we want to use sequence. We noticed it didn't use value we were passing on import so we tried update after that, but it didn't work. We end up using raw sql to update to old values after import, because it's just a few fields.

Thank you.
  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 15-May-2018 23:16:38.  
Otis wrote:
I don't know how many entity types you have this with, if it's a couple, you could try to map those entities again (but now without a sequence) and e.g. rename them with an 'ForUpdate' suffix (so CustomerForUpdate) so you can use that entity to update the underlying rows.


Brilliant idea for a workaround.Laugh

But as I know you can do better: May I kindly ask, whether we can have some kind of switch on the adapter or the entity to enable inserting and updating sequenced fields?

No offense taken, if you answer this with "No, you may not!". Cool


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 16-May-2018 11:32:50.  
What's your use case for this? the thing with a flag on adapter is that saves are recursive, so it then is for all entities in the graph. If the flag is on the entity, it's rather cumbersome though, as the sequence isn't tied to the entity but to the mapping. So all in all it's a bit cumbersome to fix this, and in general it's not common to have this, other than importing data.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 16-May-2018 11:47:00.  
Otis wrote:
What's your use case for this?


Currently using LLBLGen to replicate two identical PostgreSQL databases.
Replicated DB is meant for readonly access and as backup.
Was too lazy to dig deeper into postgress log shipping documentation and to actually set it up.

Currently using your above workaround. It's working fine if you remove the sequence and the readonly flag from the sequenced id fields in the "ForSync" version of the entity.

Some kind of switch still would be nice to have. (Perhaps in the DQE?)
So it's just a "Nice to have" feature, not more. No worries, if it seems too difficult to get implemented. Regular Smiley



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 16-May-2018 12:31:46.  
I'll see what we can do in the future Regular Smiley But it likely won't make it in, but who knows Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 16-May-2018 12:33:12.  
It's all good.

  Top
kamiwa
User



Location:
Neuss, Germany
Joined on:
12-Jun-2007 13:05:59
Posted:
137 posts
# Posted on: 16-May-2018 12:49:27.  
Just a thought:

Another way of achieving at least identity insert:

If LLBLGen runtime gave us access to the sequences, user could set the currentval of the table's sequence in code before inserting a new record.

Does that sound a cunning plan? Not sure.

More cunning plans:
http://blackadderquotes.com/i-have-a-cunning-planRegular Smiley
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38088 posts
# Posted on: 16-May-2018 13:17:29.  
You can pull the currentvalue (or better, the NEXTVAL) for the sequence using a plainsql query of course, then assign that value to an entity field. Not that great I think, as it requires a lot of overhead to get that value inserted... Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.