Single or stand-alone table (part 6)

  Now we are getting to the true meat of the Module! Though SQL separates the Insertion from the Update, I feel it is NOT the Form code's responsibility to make this decision. The Goal is to remove this from the Form and put this decision into the Data Layer. Therefore, these two are combined into a single and simple Save Method.

The steps necessary to Insert and Update are the same, combining the is the only way to not have redundant code.

in pseudo code the steps are pretty straight forward:

If Validate Data = TRUE

    IF New, then Insert

        Update Data ID, set to Clean

    ELSE Update

        Set to Clean

ELSE Sent Error Message(s)

Let's take a Look at the Save Method, and then I'll walk you through it.


  Procedure.i Save(*Class.FighterSize)
    Protected Sql$, Result, DBID, Length, *MemoryGraphicID 
    Protected PassFail = #False 
    If Validate(*Class) = #False
      ProcedureReturn #False
    Else 
      DBID = DB::Open() 
      With *Class
        SetDatabaseString(DBID, 0, \Name)
        SetDatabaseString(DBID, 1, \UnitDescription)
        SetDatabaseLong(DBID, 2, \UnitTypeID)
        SetDatabaseLong(DBID, 3, \VisibleWithTechnologyLevelID)
        SetDatabaseLong(DBID, 4, \IsHidden)
        SetDatabaseLong(DBID, 5, \GameImageID)
        SetDatabaseLong(DBID, 6, \Space)
        SetDatabaseLong(DBID, 7, \ProdCost)
        SetDatabaseLong(DBID, 8, \ProdTime)
        SetDatabaseLong(DBID, 9, \Attack)
        SetDatabaseLong(DBID, 10, \Defense)
      EndWith 
      If *Class\State = DB::#New 
        Sql$ = "INSERT "
        Sql$ = Sql$ + " into "
        Sql$ = Sql$ + "FighterSize" 
        Sql$ = Sql$ + " ("
        Sql$ = Sql$ + "      Name, "
        Sql$ = Sql$ + "      UnitDescription, "
        Sql$ = Sql$ + "      UnitTypeID, "
        Sql$ = Sql$ + "      VisibleWithTechnologyLevelID, "
        Sql$ = Sql$ + "      IsHidden, "
        Sql$ = Sql$ + "      GameImageID, "
        Sql$ = Sql$ + "      Space, "
        Sql$ = Sql$ + "      ProdCost, "
        Sql$ = Sql$ + "      ProdTime, "
        Sql$ = Sql$ + "      Attack, "
        Sql$ = Sql$ + "      Defense"
        Sql$ = Sql$ + " )"
        Sql$ = Sql$ + " VALUES "
        Sql$ = Sql$ + " (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"
        PassFail = DB::Update(DBID, Sql$)
        If PassFail 
          *Class\State = DB::#Clean
          *Class\ID = DB::getLastID(DBID, "FighterSize")
        EndIf 
        DB::Close(DBID)
      Else ;>>>>>> UPDATE <<<<<<
        SetDatabaseLong(DBID, 11, *Class\ID) 
        Sql$ = "UPDATE "
        Sql$ = Sql$ + "FighterSize "
        Sql$ = Sql$ + "SET "
        Sql$ = Sql$ + "Name = ? , "
        Sql$ = Sql$ + "UnitDescription = ? , "
        Sql$ = Sql$ + "UnitTypeID = ? , "
        Sql$ = Sql$ + "VisibleWithTechnologyLevelID = ? , "
        Sql$ = Sql$ + "IsHidden = ? , "
        Sql$ = Sql$ + "GameImageID = ? , "
        Sql$ = Sql$ + "Space = ? , "
        Sql$ = Sql$ + "ProdCost = ? , "
        Sql$ = Sql$ + "ProdTime = ? , "
        Sql$ = Sql$ + "Attack = ? , "
        Sql$ = Sql$ + "Defense = ? "
        Sql$ = Sql$ + " WHERE "
        Sql$ = Sql$ + "ID = ? "
        PassFail = DB::Update(DBID, Sql$) 
        If PassFail 
          *Class\State = DB::#Clean
        EndIf 
        DB::Close(DBID) 
      EndIf 
    EndIf 
  ProcedureReturn PassFail  
  EndProcedure

A standard call to the Save method should look like this:


If FighterSize::Save(@WorkFighterSize) = #True
    If State = DB::#New  ;insert record
      ListEx::AddItem(#Gadget_frmList_liList,-1, WorkFighterSize\Name, "", 0)
      ListEx::SetItemData(#Gadget_frmList_liList, ListEx::CountItems(#Gadget_frmList_liList)-1, WorkFighterSize\ID)
      ListEx::SetState(#Gadget_frmList_liList , ListEx::CountItems(#Gadget_frmList_liList)-1)
    Else ;update selected record
      ListEx::SetItemText(#Gadget_frmList_liList, ListEx::GetState(#Gadget_frmList_liList), WorkFighterSize\Name,0)
    EndIf
    FighterSizeClose()
  EndIf

I want to point out that data validation is done in the Data-layer, not in the form code. the form code only cares if the call to the save procedure was successful, if it was than the form can do what he needs to do and close.

The very first thing our Save Procedure does is validate the Passed in Structure or Object.

If Validate(*Class) = #False
      ProcedureReturn #False
    Else 
      DBID = DB::Open()

If the call to Validate Fails than he aborts any further processing and returns False to the Calling form code. We will touch on this when we get there, but I should mention that the displaying of errors is the job of the Validate Procedure, not the Save Procedure.

Continuing on with the Save Procedure, the next step, now that we know our data is valid is to Make a Call to our DB Module to open the database, and feed in our Values that our SQL will use in the Insert or Update. 

     With *Class
        SetDatabaseString(DBID, 0, \Name)
        SetDatabaseString(DBID, 1, \UnitDescription)
        SetDatabaseLong(DBID, 2, \UnitTypeID)
        SetDatabaseLong(DBID, 3, \VisibleWithTechnologyLevelID)
        SetDatabaseLong(DBID, 4, \IsHidden)
        SetDatabaseLong(DBID, 5, \GameImageID)
        SetDatabaseLong(DBID, 6, \Space)
        SetDatabaseLong(DBID, 7, \ProdCost)
        SetDatabaseLong(DBID, 8, \ProdTime)
        SetDatabaseLong(DBID, 9, \Attack)
        SetDatabaseLong(DBID, 10, \Defense)
      EndWith

Then Build our SQL. To know what SQL to build we need only look at the Structure's state Flag. If the State Value id DB::#New then we know this is an insert, otherwise it will be an Update. There are a couple important distinctions between Insert and Update Code

It is EXTREMELY important to order your fields in the SQL to Exactly match the order you entered them in Values section.

For the Insert We out SQL and use the "?" place holder for Pure Basic to supply the Value.

        Sql$ = "INSERT "
        Sql$ = Sql$ + " into "
        Sql$ = Sql$ + "FighterSize" 
        Sql$ = Sql$ + " ("
        Sql$ = Sql$ + "      Name, "
        Sql$ = Sql$ + "      UnitDescription, "
        Sql$ = Sql$ + "      UnitTypeID, "
        Sql$ = Sql$ + "      VisibleWithTechnologyLevelID, "
        Sql$ = Sql$ + "      IsHidden, "
        Sql$ = Sql$ + "      GameImageID, "
        Sql$ = Sql$ + "      Space, "
        Sql$ = Sql$ + "      ProdCost, "
        Sql$ = Sql$ + "      ProdTime, "
        Sql$ = Sql$ + "      Attack, "
        Sql$ = Sql$ + "      Defense"
        Sql$ = Sql$ + " )"
        Sql$ = Sql$ + " VALUES "
        Sql$ = Sql$ + " (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"

If this is an Update we must FIRST provide the final Value, the Primary ID, that we are Updating, the we can build out the Update SQL Once again we will use the "?" place holder for PureBasic to supply the actual value.

        SetDatabaseLong(DBID, 11, *Class\ID) 
        Sql$ = "UPDATE "
        Sql$ = Sql$ + "FighterSize "
        Sql$ = Sql$ + "SET "
        Sql$ = Sql$ + "Name = ? , "
        Sql$ = Sql$ + "UnitDescription = ? , "
        Sql$ = Sql$ + "UnitTypeID = ? , "
        Sql$ = Sql$ + "VisibleWithTechnologyLevelID = ? , "
        Sql$ = Sql$ + "IsHidden = ? , "
        Sql$ = Sql$ + "GameImageID = ? , "
        Sql$ = Sql$ + "Space = ? , "
        Sql$ = Sql$ + "ProdCost = ? , "
        Sql$ = Sql$ + "ProdTime = ? , "
        Sql$ = Sql$ + "Attack = ? , "
        Sql$ = Sql$ + "Defense = ? "
        Sql$ = Sql$ + " WHERE "
        Sql$ = Sql$ + "ID = ? "

At this point whether we are Inserting or Updating our SQL$ Variable is ready for execution.  We call our DB Module Update Method, to update our underlying database. There is a Very Important last step in the Insert execution. This is the call to our DB Module GetLastID to retrieve the ID of the record just inserted and update our Structure ID field with its Value.

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

the State of our Structure is set to DB::#Clean, as it now matches the database.

Finally, we close the Database, and return the results of our SQL Execution.


Comments

Post a Comment

Popular posts from this blog

How to build this without writing Code

I'm not sure if I should do this now, because I have not explained a number of additional aspects to the code, but for those of you who are interested.  The foundation of what I shown you here, IS the foundation of code my PBXClassBuilder-Standard generates for you.  And I truly mean generates  for you. You can have all this code generated for you without writing a single line of it yourself. Now PBXClassBuilder-Standard is not perfect, it is approximately 90=95% accurate. And I am constantly tweaking it edging this number ever higher. To use PBXClassBuilder-Standard, You should start with Forms already laid out and built.  Within PBXClassBuilder-Standard you use intuitive screens to define your table structure, while linking the Table Fields to their intended form Fields.  Then link the Tables together as Parent Child, and finally you simply click buttons to generate the code, Including the SQL Code to create the tables. The Whole development process, post For...
Read more

Connecting the Forms to the Data-Layer (part 9)

Here is the whole View Code Sheet Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrmSolarSystem quitfrmSolarSystem = #False If Window_frmSolarSystem() SolarSystemCustom() SetActiveWindow(#Window_frmSolarSystem) ;Set Screen Data SetGadgetText(#Gadget_frmSolarSystem_txtName, WorkSolarSystem\Name) SetGadgetText(#Gadget_frmSolarSystem_txtGrow, Str(WorkSolarSystem\GRow)) SetGadgetText(#Gadget_frmSolarSystem_txtGCol, Str(WorkSolarSystem\GCol)) SetActiveGadget(#Gadget_frmSolarSystem_txtName) Repeat EventID =WaitWindowEvent() MenuID =EventMenu() GadgetID =EventG...
Read more

Connecting the Forms to the Data-Layer (part 8)

 So now we are down to just the SolarSystem Code Sheet. This is NOT a rubberstamp of the Galaxy. The MAIN reason is that the Galaxy Records are Sourced directly from the Database, As you will recall our SolarSystem is Sourced from the Galaxy Record's Solar System Collection NOT the Database. This makes for a VERY Significant difference between in the code. Our Procedure are the save ALL the Steps are the same, the differences are in the details. Starting once again at the top, we have our declares: Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() And then, as before the SetScreen Procedure, and Control-Loop. Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrm...
Read more