Single or stand-alone table (part 5)

 Now we are getting to the true meat of the Module! This begins with the Find procedure.  This Procedure retrieves a specific record from the database. 

All Table Modules should have a default Find using the table's primary key to retrieve data. Other finds may be necessary, but they will all look the same only the passed in data and SQL Where clause need to be modified to accommodate the unique value being searched for. 

All Finds should retrieve only 0 or 1 Record, never multiple. When Creating Find methods you should Always Unique Fields, as ensured through the Validation Procedure.

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

 Procedure.i Find(FighterSizeID.i, *Class.FighterSize)
    Protected Sql$, DBID, Result, PictureSize, *picture, BLOBSize
    Protected PassFail = #False 
    Init(*Class)
    DBID = DB::Open(DBTYPE::#Sqlite)
    SetDatabaseLong(DBID, 0, FighterSizeID)
    Sql$ = "SELECT "
    Sql$ =  Sql$ + "ID, "
    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$ + " FROM "
    Sql$ =  Sql$ + "FighterSize "
    Sql$ =  Sql$ + " WHERE "
    Sql$ =  Sql$ + "ID = ? "
    PassFail = DB::Query(DBID, Sql$)
    If PassFail 
      While NextDatabaseRow(DBID)
        With *Class
          \ID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")) 
          \Name = GetDatabaseString(DBID, DatabaseColumnIndex(DBID,"Name")) 
          \UnitDescription = GetDatabaseString(DBID, DatabaseColumnIndex(DBID,"UnitDescription")) 
          \UnitTypeID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"UnitTypeID")) 
          \VisibleWithTechnologyLevelID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"VisibleWithTechnologyLevelID")) 
          \IsHidden = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"IsHidden")) 
          \GameImageID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"GameImageID")) 
          \Space = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"Space")) 
          \ProdCost = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ProdCost")) 
          \ProdTime = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ProdTime")) 
          \Attack = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"Attack")) 
          \Defense = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"Defense")) 
          \State = DB::#Clean 
        EndWith 
      Wend
    EndIf
    DB::Close(DBID)
    ProcedureReturn PassFail 
  EndProcedure

The Find method returns a True or False as to whether the code was successfully executed, now whether or not a record was found.

The Constructor or signature of the Find Procedure takes in the ID (primary Key or Unique Value) and a Pointer to a Structure to Load.

A normal Call to a Find Method should look like this:

Define WorkFighterSize.FIGHTERSIZE::FighterSize
FIGHTERSIZE::Find(FighterSizeID , @WorkFighterSize)

Define the structure then call the find.

The first thing this Procedure does is to initiate the passed in structure with a call to our private Init Procedure.

The We call our DB Module to open the Database, build our SQL Query, and then call the DB Module Query Procedure to execute our Query.

If Our SQL was successfully executed, we the retrieve the data from record set and load back into the prepared structure.

It is beyond the scope of this blog series on Data Layer Modules that I explain how each field is loaded from the record set into our structure.  

After all our fields are loaded the Structure's State is set to DB::#Clean, signifying that this object/Structure is Unchanged.

The Database is then Closed with a call to our DB Module Close Procedure.

And finally, the result of our SQL Execution is returned.