Hi,

I'm very new to LLBLGen, and I'm having issues with inserting PK/FK. My tables have the following settings:

Table A

PKA

Table B

FKAB PKB (FKAB references A.PKA and (FKAB, PKB) comprise a primary key for Table B -- it's a setup I can't change by the way)

Table C

PKC FKA (of table A.PKA)

A row set from C already exists; now, what I need to do is to create a row in A and B, and assign B.FKAB to C.FKAB.

CEntity c = new CEntity(cId);

AEntity a = new AEntity(); // (1) a.Save()? BEntity b = new BEntity(); // (3) b.FKAB = a.PKA; // (4) b.A = A; b.Save(); // (2) c.FKA = a.PKA; c.A = a; c.Save(true);

If I uncomment (1), I get a unique constraint exception for PKA. If I comment it out, line (2) throws an exception that it lacks B.FKAB. Lines (3) and (4) don't help either.

What's the right way to approch this?

At least can somebody tell me how I can insert two rows into two different tables and update the third having:

Table A

PKA (new insertion)

Table B

PKB (new insertion) FKA

Table C

PKC (already exists) FKB

What's the procedure?

I wonder why do you have an exception on...

AEntity a = new AEntity();
a.Save()?

Is the A.Pka autoident?

If you can't save A, then you will have problems saving B and C. I see no problem for the rest of the code.

AEntity a = new AEntity();
// ... set the A's fields
a.Save();

BEntity b = new BEntity();
b.FKAB = a.PKA;
b.Save();

CEntity c = new CEntity(cId);
c.FKA = a.PKA;
c.Save(true);

So the problem to solve is why you can't save "A". Please post the real structure of A (a DDL script).

Thank you daelmo.

After some wrangling, I realized that the table (Oracle) does not have the constraint in my third table (C) (Argh!).

The reason is that I have two FK references in C to A. I am not going to have a chance to run LLBLGen until tomorrow, but let me ask ahead of time, how would my C's entity can reference the second A's FK? Right now, I can do:

AEntity a = new AEntity(); // Other stuff a.Save();

CEntity c = new CEntity(cId); c.A = a;

But what if, as I said, we had two A's FK's? After running LLBLGen, would it generate two entity references of A for c?

Thanks.

Two FK's on the same FK-side field is not supported, also that doesn't make sense. Maybe you have two different FK fields, in that case LLBLGen will generate two navigators, say A and A_.

daelmo,

Can you clarify your last statement? So, you are saying if I have two FK's in the same table which both reference a single table, then LLBLGen doesn't support it?

Table A

PKA

Table B

PKB FKA1 FKA2

LLBLGen only supports FKA1, but not FKA1 and FKA2?

CEntity c = new CEntity(cId); c.A = a;

But what if, as I said, we had two A's FK's? After running LLBLGen, would it generate two entity references of A for c?

David wrote:

in that case LLBLGen will generate two navigators, say A and A_.

And hence c.A = a; c.A_ = a2;

You'd better rename these navigators in the designer to better differentiate them . You can do that in the designer, in the project explorer.

Yes, the LLBLGen indeed creates another property appended with "_". However, my original issue remains somehow.

Table A

PKA

Table B

FKA PKB

where (FKA, PKB) comprises a primary key for Table B (don't ask why but something I can't change), and when I try to create BEntity, or even AEntity, it throws an exception complaining about violation of unique constraint (PKA being duplicated).

AEntity a = new AEntity(); a.property1 = something; // (1) a.Save(), it fails

BEntity b = new BEntity(); b.A = a;

b.Save(true); // (2), if (1) is absent, (2) causes the same issue. I've checked the constraints on both tables (Oracle) and see no reason why this should happen.

Any idea?

I'm interested in your A tables as that seems to be the problem (since A doesn't save, neither will B or C, which reference it).

Please post the exact DDL for your A table, and whether or not the A.PK is identity and how it's reflected on LLBLGen entity. If possible, attach your DDL script for A table and your LLBLGen project. You can do that in a HelpDesk thread, which is private.

daelmo,

CREATE TABLE "SCHSYS"."COMMENT_SET"
  (
    "CMT_SET_ID"    NUMBER(9,0) NOT NULL ENABLE,
    "POST_FOR" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    CONSTRAINT "PK_COMMENT_SET" PRIMARY KEY ("CMT_SET_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "USERS" ;

CREATE UNIQUE INDEX "SCHSYS"."IDX_COMMENT_SET" ON "SCHSYS"."COMMENT_SET"
  (
    "CMT_SET_ID"
  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
  (
    INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "USERS" ;

LLBLGen's entity's Mapped entity fields shows that field name "CmtSetId" of COMMENT_SET.CMT_SET_ID is of .Net type of System.Int23, DB type of NUMBER, is a PK, is readonly and generated by sequence/identity name "SCHSYS.COMMENT_SET_SEQ".

And in Fields on relations, there is indeed a field name for Table B's reference to CMT_SET_ID (with relation COMMENT_SET - Table B (1:n)).

Now, Table B (COMMENT), LLBLGen shows CmtSetId with Target CMT_SET_ID, DB type of Number, Is PK, "not" readonly and no sequence number associate with it (that's FKA I illustrated in my previous posts). COMMENT (Table B) also contains another PK, CMT_ID which is not associated with any Sequence name either and all the other entries are identical to COMMENT.CMT_SET_ID (CommntEntity.CmtSetId).

If there is any consolation, this is the exception I get no matter how I Save().

{"An exception was caught during the execution of an action query: ORA-00001: unique constraint (SCHSYS.PK_CMT_SET) violated\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}
    [SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException]: {"An exception was caught during the execution of an action query: ORA-00001: unique constraint (SCHSYS.PK_COMMENT_SET) violated\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: null
    InnerException: {"ORA-00001: unique constraint (SCHSYS.PK_COMMENT_SET) violated\n"}
    Message: "An exception was caught during the execution of an action query: ORA-00001: unique constraint (SCHSYS.PK_COMMENT_SET) violated\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."
    Source: "SD.LLBLGen.Pro.ORMSupportClasses.NET20"
    StackTrace: "   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteActionQuery(IActionQuery queryToExecute, ITransaction containingTransaction)\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.AddNew(IEntityFields fields, ITransaction containingTransaction)\r\n   at BusinessEntities.EntityClasses.CommentSetEntity.InsertEntity() in C:\\Code\\BL\\BusinessEntities\\_generated\\EntityClasses\\CommentSetEntity.cs:line 16796\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CallInsertEntity()\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, ITransaction transactionToUse, Int32& totalAmountSaved)\r\n   at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save(IPredicate updateRestriction, Boolean recurse)"
    TargetSite: {Int32 Execute()}

I even tried:

CommentSetEntity cs = new CommentSetEntity();

CommentEntity c = cs.Comment.AddNew();
// Set other c's properties
c.Save(true);

Or even the third table involved:

AnotherEntity a = new AnotherEntity(aId);
CommentSetEntity cs = new CommentSetEntity();

CommentEntity c = cs.Comment.AddNew();
// Set other c's properties

// Or even
// CommentEntity c = new CommentEntity();
// c.CommentSetEntity = cs;
// c.Save(true);

a.CommentSetEntity = cs;
a.Save(true);

They all throw that exception on PK_COMMENT_SET constraint for some reason. I can verify that a's field is null in the database.

Could it be that COMMENT.CMT_ID (second COMMENT's PK) not being a sequence an issue? But that still doesn't explain why CommentSetEntity fails to create a PK for itself!

Have I understood LLBLGen fundamentally wrong?

Please enable LLBLGen tracing in order to see the sql executed and the PK retrieved by the sequence. Then post the executed SQL here.

The exception says that you are violating PK_COMMENT_SET, so you are duplicating a value in CMT_SET_ID. Please check the sequence's CURRVAL to see if there is a inconsistency there (like the sequence returning an already assigning PK value).

Also, you didn't mention your LLBLGen version and runtime library version. Please post them here. (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722).

Thank you Daelmo,

The query I see in VS' output window when exception occurs is the following:

Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "COMMENT"."CMT_SET_ID" AS "CmtSetId", "COMMENT"."CMT_ID" AS "CmtId", "COMMENT"."CMT_TEXT" AS "CmtText" FROM "COMMENT" WHERE ( ( "COMMENT"."CMT_SET_ID" = :CmtSetId1))
    Parameter: :CmtSetId1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreateInsertDQ
Method Enter: CreateSingleTargetInsertDQ

Generated Sql query: 
    Query: INSERT INTO "COMMENT_SET" ("CMT_SET_ID", "POST_FOR") VALUES (:CmtSetId1, :PostFor2)
    Parameter: :CmtSetId1 : VarNumeric. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>.
    Parameter: :PostFor2 : AnsiString. Length: 9. Precision: 0. Scale: 0. Direction: Input. Value: "FINC_PYMT".

    Sequence query: SELECT "COMMENT_SET_SEQ".NEXTVAL FROM DUAL
    Executes before INSERT: True


Method Exit: CreateSingleTargetInsertDQ
Method Exit: CreateInsertDQ
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL

I don't understand why the value is "undefined!" I ran the CURRVAL of the sequence object right before and after the execution of trouble part, and apparently it doesn't get incremented:

SELECT "COMMENT_SET_SEQ".CURRVAL FROM DUAL -- returns 500007283 right before and after queries being executed above

And running NEXTVAL on the sequence does indeed increment it accordingly. Obviously, for some reason unknown to me, when the sequences is run, the value, as you mentioned, must have been set to 0 or some other value which causes violation of PK constraint. I don't understand the first query; why is the filter asks for CMT_SET_ID that's "0"!

Other pertinent information requested:

Version: LLBLGen Pro v2.6 Final

Runtime Library: 2.6.11.1105

Using Selfservicing with .NET 4.0

Database: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Does this field shown as readOnly in the Designer? And did you specify the sequence in th Designer?

Walaa,

No, it (COMMENT_SET.CMT_SET_ID) has been marked as a sequence (COMMENT_SET_SEQ) since the first time I tried to interact with the table. In either case, the catalog has been regenerated ever since.

And yes, it has been marked as "Is readonly" True.

P.S. Also running:

select * from user_sequences where sequence_name = 'COMMENT_SET_SEQ'

COMMENT_SET_SEQ 500000000   9999999999999999999999999999    1   N   N   1000    500007283

Jalvin wrote:

Version: LLBLGen Pro v2.6 Final

Runtime Library: 2.6.11.1105

Please update to the latest version as you are using a quiet old one, then fix the references and try again.

Yes, it's odd that the sequence isn't incremented after the query, like if it's never called. Mmm, maybe is something else.

Please the exact code that you run to obtain the above executed sql.

If all above doesn't help, please open a HelpDesk thread and provide a repro solution, which should contain: your .llblgenproj file, a DDL schema we can run here on our test DBs (please try to make it as small as possible, so only relevant tables/entities), and a code snippet that reproduces the problem. All zipped, without .dll's, so just code.

I'm trying to see whether the sequence objects have been setup properly, because I'm using them in different tables, and they correctly set other tables' PK (not the same sequence object with the discussed tables/PK's).

Meanwhile I created a new one for some other table (again, mutually exclusive from what we have discussed here), and now it's complaining about NULL insertion into the PK that I just assigned the new sequence object to.

What I did was manually created a sequence in Oracle, tested it, did the Unattented Refresh for the project, went to Mapped entity fields of the targetted entity, selected the PK, checked Is Identity/Sequence Field, selected the newly created sequence object from the list, Generated the entities, yet I get NULL insertion when I try to create the entity of that table!

I'm probably missing something in the process that's causing this.

So, anyone sees anything wrong with the way I tried to let a PK to be generated via an associated sequence object? I, again, refreshed the catalog, checked to see the PK is set to Identity/Sequence with the sequence objected selected from the menu, generated everything, but when the code is executed, all other fields are being inserted except the PK, let alone with current value of the assigned sequence! The whole PK is missing from the INSERT statement according to the SQL statement in the VS' Output window.

But, it's working with other entity inserts, right? Did you use a special trigger on that table that calculates the sequence or do something else? Does it works if you run the generated sql in a DB script?

Yes, there are plenty of other entities with sequences that work just fine; it's just a new one that I created and selected with my PK that doesn't work. As a matter of fact, the PK doesn't even get inserted at all let alone have a sequence object generates value for it (all other fields do).

And yes, when I run the seqence object's NEXTVAL in SQL script, it generates a proper PK.

Is the seq. nextVal generated by a trigger?

Walaa,

No. I can verify it by a simple insertion statement which throws an error if seq.nextval is not explicitly called to generate the PK.

The thing is: as other entities work ok, this could be some code in a trigger o something like that. Could you please verify that making a DB script of the insertion and running it directly on you DB? It should be something behind the scenes like a default value of a instead-of-insert trigger.

(Edit) ... and as I mentioned earlier:

daelmo wrote:

If all above doesn't help, please open a HelpDesk thread and provide a repro solution, which should contain: your .llblgenproj file, a DDL schema we can run here on our test DBs (please try to make it as small as possible, so only relevant tables/entities), and a code snippet that reproduces the problem. All zipped, without .dll's, so just code.

I don't quite understand your question. A simple insertion with nexval works just fine.

I don't think I'm running the generator correctly. I tried to rename a table directly from SQL script and generate the entities. The database shows the table has been renamed, the designer shows that there is a correct mapping between the supposed entity and the newly renamed table, however the entity doesn't show up in Visual Studio.

Am I supposed to have set a flag in properties for this?

Let's focus on the main issue, could you please share the llblgen project file.