In our Oracle database there are 1 to 1 relationships. These 1 to 1 relationship are enforced from a database perspective by using the same primary key for both tables. In the .NET code you all were able to save a record to the 1st table and it somehow knew that it needed to use that same primary key when the insert was done for the 2nd table with all the values except the primary key. I'm thinking you all must have set up some type of relationship between the tables and the sequences in LLBLGen.

In Oracle, tables and sequences are completely separate. Unless the database developer uses good naming conventions for the sequence names you would never know what sequence belongs to what table. When you create the sequence you do not relate that sequence with any particular table in Oracle.

Right now when we attempt to create a create a record in the 1st table of the 1 to 1 relationship in our database it throws an error on the second insert (the insert to the 2nd table of the 1 to 1 relationship) because it is attempting to insert a record without any primary key (LLBLGen is sending sql to the db that does not include the pk). Your thoughts?

Any help is appreciated, Wes

Hi Wes,

Could you post some code snippet where you save both new entities?

That 1:1 relation is intended to represent an inheritance?

CaseEntity caseEntity = new CaseEntity(); .... fill caseEntity object...

CrscDecisionEntity crscdecision = new CrscDecisionEntity(); .... fill caseEntity object...

caseEntity.CrscDecision = crscdecision;

DataAccessAdapter adapter = new DataAccessAdapter(); caseEntity.IsNew = true; adapter.SaveEntity(caseEntity, true);

caseEntity and crscdecision have a 1 to 1 relationship. It is not saving recursively correctly. It tries to insert crscdecision table without the primary key.

Not really sure why they chose a 1 to 1 relationship in this case, the data would be better in a single table in my opinion. I'm really curiouse how the original .NET developers were able to set up llblgen so that it grabbed the appropriate primary key that was inserted into the first table for the record that went into the 2nd of the 1-to-1 database relationship. Right now the sql generated and being sent to do the 2nd insert is every column of the table minus the pk, as a result a Oracle exception is being thrown.

In the currently running production environment where this is working, the code inserts records into both tables. There is not a database trigger on the 2nd table of the 1-to-1 relationship that populates the primary key. I believe that LLBLGen was somehow configured that after a record was inserted into the 1st table, it somehow knows to use that same primary key when generating the sql for the 2nd table of the 1-to-1 relationship.

Wes

If a trigger was used to set the sequence of the 1st table, then add the following line to your application config file:

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

Trigger based sequence values (Oracle/Firebird) Some Oracle schemas or Firebird databases use triggers to set the sequence value when a record is inserted. LLBLGen Pro's generated code for Oracle or Firebird uses a second query to first determine the next value of the sequence (using NEXTVAL on Oracle, using 1 instead of 0 in GEN_ID on Firebird), then pass that value to the insert query as a normal value. If that code is then used to insert a row in a table with a trigger for sequence values, the trigger will update the inserted value with a new value, making the value returned to the LLBLGen Pro runtime core not the value the trigger inserted into the sequenced field. To overcome this, the Oracle and Firebird Dynamic Query Engines (DQE) can be configured to execute a sequence retrieval query which checks for the current value (using CURRVAL on Oracle, using 0 instead of 1 in GEN_ID on Firebird) after the insert took place, instead of the sequence retrieval query which checks for the next value before the insert takes place.

To do that, add the following tag to the configuration's appSettings section:

Oracle Firebird <add key="OracleTriggerSequences" value="true" /> <add key="FirebirdTriggerSequences" value="true" />

A value of false or omitting the tag will cause normal behavior, a sequence call which asks for the next value before the insert.

Thanks for the last responses,

Our situation: We have a working application on the client site. We were recently hired to provide enhancements for the application. So we are in the process of setting up a development environment (offsite from the production system). The Oracle schema was exported, so we have a identical copy of that Oracle schema used by the application. We believe (are pretty sure) that the C# codebase that we have is not the codebase that is at the production site (but it is a very close version). As a result, we are trying to find out how the developers originally got this C#\LLblgen configured to insert into this Oracle 1-to-1 relationship. The two tables with this 1-to-1 relationship do not have triggers, so this was not used to populate the primary key. It looks like the primary key had to come from the C# (or the generated LLblgen sql code). From a llblgen perspective, when dealing with a 1-to-1 Oracle table relationship, when both tables have the same primary key field name and they both use the exact same Oracle sequence, how can llblgen generate a sql statement for the 2nd table of the 1-to-1 relationship using the same primary key value that was used for the first table of the 1-to-1 relationship (meaning, how can it grab the pk value that was inserted with the first table?). Is their a LLblgen config way to do this? Adding triggers in the db is not an option that we want to pursue. Is the only way is to use Oracle <sequence_name>.currval within the C# code after the insert to the first table is done, then use that <sequence_name>.currval with the insert to the 2nd table of the 1-to-1 relationship?

what would happen if a value of false is used, like below: <add key="OracleTriggerSequences" value="false" />

Thank you, Wes

when dealing with a 1-to-1 Oracle table relationship, when both tables have the same primary key field name and they both use the exact same Oracle sequence

This statement is confusing. If the 2nd table has a PK that is also a FK to the 1st table's PK. Then the 2nd table's PK can't be using a Sequence at all.

The second table isn't using a sequence at all, your correct. This is a 1-to-1 relationship, a record is inserted into the 1st table using a sequence value, then a record is inserted into the 2nd table using the pk value that was generated from the first table insert.

Sorry, worded that bad, they are not both using the sequence, only the 1st table insert is.

Wes

Then LLBLGen Pro should sync the PK-FK for you. Which version of the LLBLGen Pro runtime library are you using? (ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725)

Make sure you pass true for recursive save:

adapter.SaveEntity(caseEntity, true, true);

Also, would you please inspect the generated queries? (And post them here). (ref: LLBLGen Pro manual "Using the generated code -> Troubleshooting and debugging")

Were using version: 1.0.2005.1

We tried the solution:

adapter.SaveEntity(caseEntity, true, true);

but we didn't have any luck with it.

The generated queries are attached.

The inserts are on line: 178 and 421 (2nd table of 1-to-1 relationship, you'll see that the primary key (CRSC_CASE_ID) is missing from the insert, which is currently causing the Oracle error that occurs).

Thanks again for your help, Wes

That's not a correct RTL version, please refer to the following guidelines to know how to get the correct RTL version. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

Sorry, it's:

1.20051.7.307

The inserts are on line: 178 and 421 (2nd table of 1-to-1 relationship, you'll see that the primary key (CRSC_CASE_ID) is missing from the insert, which is currently causing the Oracle error that occurs).

I wonder why there are many Inserts and selects to/from other tables between lines 178 and 421?

If the code used was the same as you posted before, those inserts should have been done successively.