Parent Child Table (part 4)

 Welcome back!

Continuing on with the Save Procedure.  In order for the children to be linked to their Parent, the Parent MUST be saved prior to the children. 

So, lets focus in on the bottom end of the SQL INSERT:

        PassFail = DB::Update(DBID, Sql$)

        If PassFail 
          *Class\State = DB::#Clean
          *Class\ID = DB::getLastID(DBID, "Galaxy")
        EndIf 

        DB::Close(DBID)


        ;-----------------------------------------------
        SaveSolarSystemCollection(*Class)
        ;-----------------------------------------------

As you can see once we close the database and retrieve the Parent's ID from the database, we then call the Save Children Procedure. 

On the SQL UPDATE, we are free to call the Child Save Procedure immediately after the Close Procedure.

        PassFail = DB::Update(DBID, Sql$) 

        If PassFail 
          *Class\State = DB::#Clean
        EndIf 

        DB::Close(DBID) 

        ;-----------------------------------------------
        SaveSolarSystemCollection(*Class)
        ;-----------------------------------------------
      EndIf 

And now we will take a look at the Save Child Collection:

  Procedure SaveSolarSystemCollection(*Class.Galaxy)
    Protected Index, *WorkSolarSystem.SOLARSYSTEM::SolarSystem
    Index = 0
    ForEach *Class\DeleteSolarSystemCollection()
      If *Class\DeleteSolarSystemCollection() > 0
        If SOLARSYSTEM::Delete(*Class\DeleteSolarSystemCollection()) = #False
          THEMEDMESSAGE::MessageBox("Error","Error Deleting Xref Collection 'SolarSystem' !") 
        EndIf
      EndIf
    Next 
    ForEach *Class\SolarSystemCollection()
      ;Link child back to parent
      *Class\SolarSystemCollection()\GalaxyID = *Class\ID
      ;Reset status
      If *Class\SolarSystemCollection()\State = DB::#NewDirty
        *Class\SolarSystemCollection()\State = DB::#New
      EndIf
      ;Save them all
      If *Class\SolarSystemCollection()\State <> DB::#New 
        *Class\SolarSystemCollection()\State = DB::#Dirty
      EndIf

      Select *Class\SolarSystemCollection()\State
        Case DB::#New
          *WorkSolarSystem = *Class\SolarSystemCollection()
          If SOLARSYSTEM::Save(*WorkSolarSystem) = #False
            THEMEDMESSAGE::MessageBox("Error","Error Inserting field 'SolarSystem' !" ) 
          EndIf
        Case DB::#Dirty
          *WorkSolarSystem = *Class\SolarSystemCollection()
          If SOLARSYSTEM::Save(*WorkSolarSystem) = #False
            THEMEDMESSAGE::MessageBox("Error","Error Updating field 'SolarSystem' !") 
          EndIf
        Case DB::#Clean
          ;all good ignore
      EndSelect 

    Next
  EndProcedure

Let's take a detailed look at the Child Save. Our First step is to iterate the DeleteSolarSystemCollection() and perform the Database delete of these Children, by calling their Delete Procedure.  Where did these records come from? You are probably asking.  I've avoided going into the GUI side of things, I promise to get to that in the next series of Blog Entries.  So, for now, take it a face value that when a Child Record is "Deleted" by the user on a screen, that it is ONLY moved into the Delete Collection, to await its fate until the user decides to Save the Parent, and Commit the Deleted Children to being deleted. 

Next, we need to look at the records in the Child Collection. As we iterate each record and evaluate, based on record STATE what to do with it. I have this in here in case you are a MUCH better developer than I. Ideally this is the "Proper" way things should be done; however, I can NEVER remember to change the Child Status to DB::#Dirty. I screw this up every time. So, you will note that I merely set ALL records to DIRTY, which are Not NEW. And I Always Save ALL Children with their Parent. 

I'll leave it up you, which way you desire to go here.  

A comment on DB::#New vs DB::#NewDirty.  These are Both New Records, only one has been Created and then updated by the user prior to saving the record to the Database.  This, again, is a GUI thing I'll point out in the next series of Blog posts.