Hi all...

This might be a lack of understanding with Firebird since I am more used to MS SQL, but here goes.

I have two tables, USER and DEVICE, for which there is a 1:m (one user can own many devices).

I am saving the USER entity first, then attempting to save the DEVICE entity with DEVICE.UserId under the same transaction. Unfortunately, USER.UserId is still 0 (null) when it attempts to save to DEVICE. So an exception is raised.

Seems like with Firebird, there are triggers that provide the Auto-Increment on the primary key, so my thought is that I might not be able to save both in the same transaction (which would be a little bit of a bummer) since the primary key isn't 'triggered' until a record is actually inserted.

Does this sound like what's going on? In SqlServer, this didn't seem like a problem. If you need code, I can provide.

Thanks!

Todd

Please check the Trigger based sequence values (Oracle/Firebird) section in the LLBLGen Pro manual, under "Using the generated code -> Application configuration through .config files"

Thanks for pointing me in the right direction. I added that config key to appSettings in App.config and it didn't work, User.UserId is still 0 even after it has been saved, but not committed.

Here is the code to clarify:

         if (useLocalTran)
            tran = new Transaction(System.Data.IsolationLevel.ReadCommitted, this.TRAN_NAME);

         tran.Add(this.UserEntity);
         tran.Add(this.DeviceEntity);


         try
         {
            if (result)
            {
               result = this.UserEntity.Save();
            }

            if (result)
            {
               this.DeviceEntity.SerialNumber = this.plusId.DeviceManager.ReadOnlySettings.SerialNumber;
               this.DeviceEntity.Revision = this.plusId.DeviceManager.ReadOnlySettings.SoftwareRevision;
               this.DeviceEntity.MacAddress = this.plusId.DeviceManager.ReadOnlySettings.DeviceAddress;
               this.DeviceEntity.UserId = this.UserEntity.UserId;

               result = this.DeviceEntity.Save();
            }

            if (!result)
            {
               if (useLocalTran)
                  tran.Rollback();
            }
            else
            {
               if (useLocalTran)
                  tran.Commit();
            }
         }
         catch
         {
            if (useLocalTran)
               tran.Rollback();
            throw;
         }
         finally
         {
            if (useLocalTran)
               tran.Dispose();
         }

         return result;

When I set this.DeviceEntity.UserId = this.UserEntity.UserId, UserId = 0 and hence the following exception.

An exception was caught during the execution of an action query: violation of FOREIGN KEY constraint "FK_DEVICE1" on table "DEVICE".

Maybe my auto-increment trigger and/or generator is messed up, but it looks good to me. It was created with EMS Studio, here is the DDL if it helps...

CREATE TRIGGER BI_USER_USER_ID FOR "USER"
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
  IF (NEW.USER_ID IS NULL) THEN
      NEW.USER_ID = GEN_ID(USER_USER_ID_GEN, 1);
END;


CREATE GENERATOR USER_USER_ID_GEN;

SET GENERATOR USER_USER_ID_GEN TO 9;

Thanks again, your support is second to none!

To update progress, as a temporary hack, I was able to add the following code to 'refresh' the saved entity...

         if (!this.HasErrors)
         {
            this.userEntity.FetchUsingUCFirstNameLastName(this.userEntity.FirstName, this.userEntity.LastName);
         }

You can see that this technique would only work so long as the table has a unique constraint other than the primary key. This is because I know first name and last name up front, therefore I can get the new User.Pk before DeviceEntity needs it when it saves.

I tried doing UserEntity.Refetch, but that just yeilds a sql statement like:

SELECT "USER"."USER_ID" AS "UserId", "USER"."FIRST_NAME" AS "FirstName", "USER"."MIDDLE_INITIAL" AS "MiddleInitial", "USER"."LAST_NAME" AS "LastName", "USER"."EMPLOYEE_NUMBER" AS "EmployeeNumber", "USER"."CREATE_DATE" AS "CreateDate", "USER"."CREATE_USER_ID" AS "CreateUserId", "USER"."UPDATE_DATE" AS "UpdateDate", "USER"."UPDATE_USER_ID" AS "UpdateUserId" FROM "USER" WHERE ( "USER"."USER_ID" = @UserId1)
    Parameter: @UserId1 : Int32. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: 0.

UserId in this statement is not 0, so nothing gets found.

Still looking for a better solution.

Thanks!

Can you confirm that Trigger works always? If you only save the User entity, it is saved to DB with the correct new id value? This condition:

IF (NEW.USER_ID IS NULL) THEN ...

is valid for Firebird?, and if the trigger is BeforeInsert, why are you condition the new value generation? Do you want sometimes set manually the value?

Hi thanks for the response Daelmo...

The trigger does work each time, otherwise I would not be able to fetch the primary key off the unique constraint by calling the following within the same transaction...

this.userEntity.FetchUsingUCFirstNameLastName(this.userEntity.FirstName, this.userEntity.LastName);

If I were to save just the userEntity, close the transaction, then get the userEntity I would have the primary key... but this doesn't support saving multiple dependent entities in a single transaction.

Here is a Unit test to illustrate (setting <add key="FirebirdTriggerSequences" value="true" /> does not affect the NUnit test).

   [TestFixture]
   public class UserEntityTest
   {
      private UserEntity userEntity;
      private UserBizEntity userBizEntity;

      public UserEntityTest()
      {
         this.SetupUserEntity();

      }

      private void SetupUserEntity()
      {

         this.userEntity = new UserEntity();
         this.userEntity.FirstName = "A";
         this.userEntity.MiddleInitial = "B";
         this.userEntity.LastName = "C";
         this.userEntity.EmployeeNumber = "10501";

         // Clear existing from database if present.
         UserEntity deleteEntity = new UserEntity();
         deleteEntity.FetchUsingUCFirstNameLastName(this.userEntity.FirstName, this.userEntity.LastName);
         deleteEntity.Delete();

      }

      [Test]
      public void InsertUserEntityTest()
      {
         Assert.IsTrue(this.userEntity.Save());
         Assert.IsFalse((this.userEntity.UserId > 0)); //   UserEntity.UserId was not updated... bummer!

         this.userEntity.FetchUsingUCFirstNameLastName(this.userEntity.FirstName, this.userEntity.LastName);

         Assert.IsTrue((this.userEntity.UserId > 0)); //    UserEntity.UserId was updated... hurray!
      }

Which build of the LLBLGen Pro runtime library are you using?

You can request the version of the runtime libraries you're currently using in your code using:

// C# string version = SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Version + "." + SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Build; ' VB.NET Dim version As String = SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Version & "." & _ SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Build

The runtime libraries also use a file version attribute, which is visible when you rightclick in windows explorer on the assembly dll (one of the DQE assemblies or the ORM Support classes assembly) and select 'Properties' and after that view the Version tab. This version has the following format: 2.0.0.yymmdd, where yymmdd is the date the assembly was released.

NOTE: Starting with builds made in 2007, we had to change this format into 2.0.yy.mmdd, as an element of the version can only be 16bits and a number 7xxyy needs 17 bits at least.

SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Version = "2.0.0.0.04242007"

SD.LLBLGen.Pro.DQE.Firebird.NET20.dll = 2.0.7.308

Hi,

Maybe you can link DeviceEntity and UserEntity with this code :

this.DeviceEntity.User = this.UserEntity

and save only DeviceEntity with recurse set to true ?