Hello,

We're using Oracle 10g and LLBL Gen Pro 2.0.

I'm trying to save two related entities with a single .Save() call, and the child entity is not getting a value from the primary key from the parent. Example:

UserEntity user = new UserEntity ();

user.Userfirstname = "first";
user.Userlastname = "last";
user.Userlogin = "login";

_adapter.FetchEntityCollection(user.Userrole, user.GetRelationInfoUserrole());

UserroleEntity userRole = new UserroleEntity();
userRole.Idrole = 1;

user.Userrole.Add (userRole);

_adapter.SaveEntity (user);

The error I get is: "ORA-01400: cannot insert NULL into ("WINGS"."USERROLES"."IDUSER")"

User.IDUser is a primary key field and is sequenced and increments with a trigger. I had to add the following to the app.config file to get this to work:

<add key="OracleTriggerSequences" value="true" />

and set it in the designer as an identity/sequence field and select the sequence used.

UserRoles.IDUser has a (m:1) relation back to User.IDUser which is defined both in the database and in the designer. UserRoles.IDUser is not a primary key or sequenced field.

This same scenario worked when using SQL Server. How can I get UserRoles.IDUser to populate on the save?

Thanks,

William

UserEntity user = new UserEntity ();

user.Userfirstname = "first"; user.Userlastname = "last"; user.Userlogin = "login";

_adapter.FetchEntityCollection(user.Userrole, user.GetRelationInfoUserrole());

UserroleEntity userRole = new UserroleEntity(); userRole.Idrole = 1;

user.Userrole.Add (userRole);

_adapter.SaveEntity (user);

Did you post the real code?

What does the fetchEntityCollection line is for?

Is user a sequenced entity? (i.e. the pk has a sequence/should have a sequence) ? If so, did you specify the sequence for the PK field in the designer?

Did you post the real code?

Yes I did.

What does the fetchEntityCollection line is for?

Oops, that was an artifact that doesn't belong. However, I have the problem whether this line is present or not.

Is user a sequenced entity? (i.e. the pk has a sequence/should have a sequence) ? If so, did you specify the sequence for the PK field in the designer?

Yes, the User.IDUser is a primary key field and is sequenced and increments with a trigger and is set in the designer as an identity/sequence field and I selected the sequence used. Additionally, the sequence works just fine and, if I remove the line

user.Userrole.Add (userRole);

the parent entity is added and the correct value for IDUser is returned in code.

Again, this scenario worked with LLBL 1.1 and .NET 2003 using SQL Server so I suspect the problem is Oracle related?

William

William wrote:

Did you post the real code?

Yes I did.

What does the fetchEntityCollection line is for?

Oops, that was an artifact that doesn't belong. However, I have the problem whether this line is present or not.

Is user a sequenced entity? (i.e. the pk has a sequence/should have a sequence) ? If so, did you specify the sequence for the PK field in the designer?

Yes, the User.IDUser is a primary key field and is sequenced and increments with a trigger and is set in the designer as an identity/sequence field and I selected the sequence used. Additionally, the sequence works just fine and, if I remove the line

user.Userrole.Add (userRole);

the parent entity is added and the correct value for IDUser is returned in code.

Don't use a trigger for the sequence. If you specify the sequence on the entity field, LLBLGen Pro code will grab the nextval by itself.

If you MUST use the trigger (e.g. you're not able/allowed to remove the trigger), be sure to specify: <add key="OracleTriggerSequences" value="true" />

in the appSettings in your application's .config file. Though this means ALL sequences are done by triggers.

Again, this scenario worked with LLBL 1.1 and .NET 2003 using SQL Server so I suspect the problem is Oracle related?

William

It's the trigger that makes it impossible to load the new id back and the sync then goes wrong, as llblgen pro assumes there's no trigger on the table

If you MUST use the trigger (e.g. you're not able/allowed to remove the trigger), be sure to specify: <add key="OracleTriggerSequences" value="true" />

Yes, as I stated in the original post I am using this line in my app.config. I could not get the proper IDUser value returned until I did this. Is there a way to do this in code by the way? We really don't want to use app.configs if we don't have to.

And yes, I must use triggers and we're using triggers on all our tables.

The proper IDUser value is being returned in code, why is it not being populated in the child collection?

William

William wrote:

If you MUST use the trigger (e.g. you're not able/allowed to remove the trigger), be sure to specify: <add key="OracleTriggerSequences" value="true" />

Yes, as I stated in the original post I am using this line in my app.config. I could not get the proper IDUser value returned until I did this. Is there a way to do this in code by the way? We really don't want to use app.configs if we don't have to.

Not directly. The parameter in the Oracle DQE is private static. You could change it into public in the DQE sourcecode and build your own ormsupportclasses + dqe dlls. Then you can set it through code.

And yes, I must use triggers and we're using triggers on all our tables. The proper IDUser value is being returned in code, why is it not being populated in the child collection? William The thing is this: when you DON'T specify that setting. LLBLGen Pro will use a NEXTVAL call on the sequence before the insert statement. This value is then used to insert the row. Your trigger is executed then, and also does a NEXTVAL and inserts that value into the row instead. The value LLBLGen pro knows isn't valid anymore, so reading back the entity won't work: the ID isn't found.

When you specify that setting, LLBLGen pro will use a CURRVAL call to the sequence After the insert, and the id is then thus the same as the one inserted by the trigger.

when you DON'T specify that setting. LLBLGen Pro will use a NEXTVAL call on the sequence before the insert statement. This value is then used to insert the row. Your trigger is executed then, and also does a NEXTVAL and inserts that value into the row instead. The value LLBLGen pro knows isn't valid anymore, so reading back the entity won't work: the ID isn't found.

When you specify that setting, LLBLGen pro will use a CURRVAL call to the sequence After the insert, and the id is then thus the same as the one inserted by the trigger.

I'm sorry I'm lost.

With the app.config setting and just saving the parent entity 'user' then the row is inserted in the DB with the correct PK value for IDUser and I can get that value returned correctly in code. Everything is fine.

But when I try to insert a parent AND child entity using the app.config setting, you're saying that the trigger assigns the NEXTVAL and LLBL throws the value away before assigning the IDUser value to the child entity 'userRole'?

Why does the app.config setting work with just a parent entity but not the parent and child?

William

William wrote:

when you DON'T specify that setting. LLBLGen Pro will use a NEXTVAL call on the sequence before the insert statement. This value is then used to insert the row. Your trigger is executed then, and also does a NEXTVAL and inserts that value into the row instead. The value LLBLGen pro knows isn't valid anymore, so reading back the entity won't work: the ID isn't found.

When you specify that setting, LLBLGen pro will use a CURRVAL call to the sequence After the insert, and the id is then thus the same as the one inserted by the trigger.

I'm sorry I'm lost.

With the app.config setting and just saving the parent entity 'user' then the row is inserted in the DB with the correct PK value for IDUser and I can get that value returned correctly in code. Everything is fine.

But when I try to insert a parent AND child entity using the app.config setting, you're saying that the trigger assigns the NEXTVAL and LLBL throws the value away before assigning the IDUser value to the child entity 'userRole'?

Why does the app.config setting work with just a parent entity but not the parent and child?

William

So you're saying with the app.config setting the recursive save still doesn't work? (I haven't read the whole thread in depth, I'll do that now)

(edit). It's indeed strange, as you don't specify a refetch (you just call SaveEntity(), which specifies false for refetch, so the save of the parent always succeeds if there's no error, so the sync will go ok.

I have no problems saving entities in oracle in my unittests, also hierarchies like you do.

One question for you: the relation userrole -> user, is that relation hidden? (or the field on the relation, is that one hidden?)

I.o.w., if you do: userrole.User = user; is that possible or do you get a compile error?

I have no problems saving entities in oracle in my unittests, also hierarchies like you do.

I can save hierarchies too, just not when I need to insert a PK value of the parent entity into a child or joined entity on a single save. Can you get that to work in your unit tests? Would getting you the generated SQL be helpful?

One question for you: the relation userrole -> user, is that relation hidden? (or the field on the relation, is that one hidden?)

I checked and their are no fields or relations hidden.

I.o.w., if you do: userrole.User = user;

I tried this though it compiled I got the same error.

Any suggestions?

Wm

Okay,

This works

            
UserEntity user = new UserEntity ();

user.Userfirstname = "first";
user.Userlastname = "last";
user.Userlogin = "login";

UserroleEntity userRole = new UserroleEntity();
userRole.Idrole = 62;

userRole.User = user;
userRole.User_ = user;

_adapter.SaveEntity (userRole, true);

Originally when I tried setting userRole.User = user; like you suggested I was still saving user not userRole. Once I changed to saving userRole, I got the next error about trying to insert a null into userRole.IDUserLastUpdatedBy field, so by assigning userRole.User_ = user; that error went away and now it works.

So why does this work but user.Role.Add (userRole); does not?

Is this the recommended way to be doing this?

William

P.S. Thanks for the help thus far!

aha, do you have 2 relations or so between userrole and user? Could you tell me which fields mapped onto relations you have in userrole? Because you have User and User, which means there are two fields mapped onto a relation with User, and because you can't have fields with the same name LLBLGen Pro appends a ''. You can check in the designer which relation "User_" is mapped on and which relation "User" is mapped on.

The second relation 'User_' is for the field 'userRole.IDUserLastUpdatedBy' which relates back to User.IDUser.

So does the fact that I have two similiarly named relations - and in fact there is also another relation to a 3rd table - have anything to do with my original problem?

Also you said,

Not directly. The parameter in the Oracle DQE is private static. You could change it into public in the DQE sourcecode and build your own ormsupportclasses + dqe dlls. Then you can set it through code.

Is there a way we can change this so that the designer won't overwrite our change on subsequent code generations?

Thanks,

Wm

William wrote:

The second relation 'User_' is for the field 'userRole.IDUserLastUpdatedBy' which relates back to User.IDUser.

So does the fact that I have two similiarly named relations - and in fact there is also another relation to a 3rd table - have anything to do with my original problem?

The problem is: if you have 2 relations from userrole to user, and one of the fk fields is nullable and the other one isn't, and you set the relation with the nullable fk to user, then you have a problem savingthe userrole entity. You should set BOTH relations to a user instance, so both fk fields will be set. It's best to rename the User and User_ fields to something more describing

So if you could give me an overview of userrole and user and the relations between them + the fk fields / fields mapped onto these relations, I have a better overview what's going on.

LLBLGen Pro names the fields mapped on a relation by default using the patterns defined in the project properties, so if 2 names end up being the same, it appends a '_'.

Also you said,

Not directly. The parameter in the Oracle DQE is private static. You could change it into public in the DQE sourcecode and build your own ormsupportclasses + dqe dlls. Then you can set it through code.

Is there a way we can change this so that the designer won't overwrite our change on subsequent code generations?

It's in the DQE so you build your own DQE build using the sourcecode provided to you and that's not overwritten. I can also make the field public, I think that's a better solution