Session E-TIER

Developing 3-tier-applications with Active Servers

Rod Paddock
Dash Point Software, Inc


Introduction

There has been a lot of talk about 3 tiered development. But just what does this 3 tiered development idea really mean. This session will explore the concepts of developing effective 3 tiered applicaions using VFP with DBF data and then with SQL Server Data. In this session you will learn:

What is 3 Tiered Development

The truth about three tiered development is an architectural decision on how to construct an application The multiple tiered approach dictates that the system architecure will consist of multiple tiers commonly consisting of User Interface, Business Rules and Data. Each of these tiers is developed in with the idea that  each can be changed without affecting the other layer. In other words if you want to remove SQL Server and to to ORACLE you should be able to do this without any reworking either the UI Layer or the Business Rules layer. In reality you will probably need to make minor changes. With a proper design these changes will be minor. To better understand 3 tiered architectures take a look at the following illustration:

As you can see the goal of this architecture is to make your UI separate from your business rules and your business rules separate from your data. In order to pull this off you need to make sure that your interfaces are clean and that you can remove a tier without greatly affecting the other layer. The reason for doing this is to preserve completed work while preparing for changes to newer or better technologies. The remainder of this session will  be spent working on developing objects that can be used to accommodate three tiered development principals.

Developing a Generic API

The first step to developing three tiered applications is to develop the API that developers will use to add, update and view data. The key to developing this interface is to create an API that is both flexible enough to accommodate your user interfaces and powerful enough to accommodate most applications.

When you develop your API you need to look at the basic operations that you perform on data. The most primitive operations that you can perform on data are:

With these basic operations in mind you can begin by creating the definition of a class that will present these basic operations to a user. Your class will look like:

Define Class cusDataClas as Custom
 
Function lFind()
EndFunc
 
Function lAdd()
EndFunc
 
Function lEdit()
EndFunc
 
Function lDelete()
EndFunc
 
Function lSave()
EndFunc
 
Function lCancel()
EndFunc
 
EndDefine

The next step in defining this generic API is to define the data  that this class will need and use. This is where you need to think about how objects can be related to data.  Relational theory and object oriented theory both possess a common principal. One of the basic tenets of relational theory is that all rows in a table must possess a column or set of columns that can be used to define that row as unique, also known as Primary Keys. This concept has a similar idea in Object Oriented Theory in that each object must be unique unto itself. This is the bridge you will use to graft an object onto a relational database. To accomplish this you need a few pieces of information:

Now your class definition will look like:

Define Class cusDataClas as Custom
 
cEditMode = "VIEW"   && editing mode of data
oObjectStore = ""    && objectstore
oCompareObjectStore = ""  && comparison object
 
cTableName = ""      && name of table
cKeyFieldName = ""   && name of primary key
xKeyValue = ""       && value of primary key
 
Function lFind()
EndFunc
 
Function lAdd()
EndFunc
 
Function lEdit()
EndFunc
 
Function lDelete()
EndFunc
 
Function lSave()
EndFunc
 
Function lCancel()
EndFunc
 
EndDefine

Now that you have created this basic structure you can go about the process of developing special classes for each type of data source you are dealing with.

Developing a VFP Data Class

The next class to develop is one for VFP Data. As you can see from the following code, the techniques you use are very SQL Like. Only minor changes will be needed for the SQL Server class to work:

Define Class cusVFPServerDataClas as cusDataClas
 
cEditMode = "VIEW"   && editing mode of data
oObjectStore = ""    && objectstore
oCompareObjectStore = ""  && comparison object
cTableName = ""      && name of table
cKeyFieldName = ""   && name of primary key
xKeyValue = ""    && value of primary key
cPathName = ""      && vfp path to table
 
*-- SQL Server specific information
Function Init(poConnection)
 
*-- open table if it isn't already
If Not Used(This.cTableName)
  Use (This.cPathName + This.cTableName) In 0 Shared
Endif
 
This.lSetFields()
This.GetPrimaryKeyInfo()
 
EndFunc
 
Function Destroy
EndFunc
 
Function lSetFields()
 
*-- Create properties
Select This.cTableName
Scatter Name This.oObjectStore Memo Blank
 
EndFunc
 
Function lAdd()
  This.cEditMode = "ADD"
  This.lSetFields()
 
  *-- Go get a new key
  Local lnExec
 
  *-- Sp_dpsi_newkey is a function in the dbc
  lcCommand = "This.oObjectStore." + This.cKeyFieldName + "=sp_dpsi_newkey()"
  &lcCommand.
 
  *-- set up the key value of the object
  This.xKeyValue = Eval("This.oObjectStore." + This.cKeyFieldName)
 
EndFunc
 
Function lSave()
  Do Case
    *-- adds perform inserts
    Case This.cEditMode = "ADD"
       lcSQL = [Insert Into ] + This.cTableName  + [(]
       lcSql = lcSql + This.cValueString() + ") values ("
       lcSql = lcSql + This.cDataString() + ")"
 
       &lcSQL.  
 
 
    *-- Edits perform updates
  Case This.cEditMode = "EDIT"
       lcSQL = [Update ] + This.cTableName
       lcSql = lcSql + [ SET ] + This.cSetString()
       lcSql = lcSql + [ Where ] + This.cKeyFieldName + [=] + This.lSqlServerString(This.xKeyValue) 
 
       *-- Execute the update
       &lcSQL.
 
  EndCase
  This.cEditMode = "VIEW"
 
EndFunc
 
*-- Create the value list
Function cValueString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields
 
*-- Scan through the field list
lnFields =Amembers(laFields,This.oObjectStore)
For lnKount = 1 to Alen(laFields,1)
  If lnKount > 1
    lcRetVal = lcRetVal + "," + laFields[lnKount]
  Else
    lcRetVal = lcRetVal + laFields[lnKount]
  Endif
EndFor
 
Return lcRetVal
 
EndFunc
 
 
*-- Return the data string
Function cDataString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields, lnKount
 
*-- Scan trough the field list
lnFields =Amembers(laFields,This.oObjectStore)
For lnKount = 1 to Alen(laFields,1)
  If lnKount > 1
    lcRetVal = lcRetVal + "," + This.lSqlServerString(EVAL("This.oObjectStore." + laFields[lnKount]))
  Else
    lcRetVal = lcRetVal + This.lSqlServerString(EVAL("This.oObjectStore." + laFields[lnKount]))
  Endif
EndFor
 
Return lcRetVal
EndFunc
 
 
*-- This method creates the string for a SQL Update clause
Function cSetString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields
 
*-- Scan through the field list
lnFields =Amembers(laFields,This.oObjectStore)
 
 
For lnKount = 1 to Alen(laFields,1)
 
  *-- How many fields have been changed
  Local lnUpdated
  lnUpdated = 0
  *-- Compare current value to old value
  If Eval("This.oObjectStore." + laFields[lnKount]) # ;
     Eval("This.oCompareObjectStore." + laFields[lnKount])
 
    lnUpdated = lnUpdated + 1
    If lnUpdated > 1
      lcRetVal = lcRetVal + "," + laFields[lnKount] + " = " + This.lSqlServerString(Eval("This.oObjectStore." + laFields[lnKount]))
    Else
      lcRetVal = lcRetVal + laFields[lnKount] + " = " + This.lSqlServerString(Eval("This.oObjectStore." + laFields[lnKount]))
    Endif
 
  Endif
 
EndFor
 
Return lcRetVal
 
 
*-- This function converts a data string to the SQL server equiv
Function lSQLServerString(pxData)
 
Local lcRetVal
lcRetVal = ""
 
 
Do Case
  Case Type("pxData") = "C"
    lcRetVal = ['] + pxData + [']
  Case Type("pxData") = "N"
    lcRetVal = Str(pxData)
  Case Type("pxData") = "L"
    lcRetVal = IIF(pxData,[1],[0])
  Case Type("pxData") = "D"
    lcRetVal = ['] + DTOC(pxData) + [']
EndCase
 
Return lcRetVal
 
EndFunc
 
Function lDelete()
 
  lcSQL = [Delete From ] + This.cTableName
  lcSql = lcSql + [ Where ] + This.cKeyFieldName + [=] + This.lSqlServerString(This.xKeyValue) 
  Local lnResults
  
  *-- Execute the delete
  &lcSql.
 
EndFunc
 
Function lEdit()
  This.cEditMode = "EDIT"
 
EndFunc 
 
Function lFindKey(pxKeyValue)
 
Local lcQuery
lcQuery = [Select * From ] + This.cTableName + [ Where ]
lcQuery = lcQuery  + This.cKeyFieldName + "="
 
*-- Either numbers or chars
If Type("pxKeyValue") = "C"
  lcQuery = lcQuery + ['] + pxKeyValue + [']
Else
  lcQuery = lcQuery + Str(pxKeyValue)
Endif
 
lcQuery = lcQuery + " Into cursor c_results "
 
*-- Set the key value
This.xKeyValue = pxKeyValue
 
*-- Go get the record
&lcQuery.
Select c_results
 
*-- Create data entry buffer
Scatter Name This.oObjectStore Memo
 
*-- Compare buffer
Scatter Name This.oCompareObjectStore Memo
EndFunc
 
 
Function GetPrimaryKeyInfo()
 
*-- Flag to make sure we have a PK
Local llHasPK
llHasPK = .f.
 
*-- Select table
Select (This.cParentAlias)
 
*-- Retrieve the key tag and tag expression
For lnKount = 1 To TagCount()
   If Primary(lnKount)
     This.cFieldName = SYS(14,lnKount)
    llHasPK = .t.
    Exit
   Endif
EndFor
 
*-- If there was not a primary key
*-- Then look for a candidate
If Empty(This.ckeyTagName)
  For lnKount = 1 To TagCount()
    If Candidate(lnKount)
       This.cFieldName = SYS(14,lnKount)
     llHasPK = .t.
     Exit
    Endif
  EndFor
Endif
 
EndDefine

Developing a SQL Server Data Class

The next class to develop is one for SQL Server. As you can see from the following code, the techniques you use are basically the same the VFP class.  The code for the SQL Server Class is as follows:

Define Class cusSQLServerDataClas as cusDataClas
 
cEditMode = "VIEW"   && editing mode of data
oObjectStore = ""    && objectstore
oCompareObjectStore = ""  && comparison object
cTableName = ""      && name of table
cKeyFieldName = ""   && name of primary key
xKeyValue = ""    && value of primary key
 
*-- SQL Server specific information
nConnection = 0      && SQL connection handle
cDSN = ""            && SQL Data source name
cUserID = ""         && SQL User ID
cPassWord = ""       && SQL Password
 
 
Function Init(poConnection)
 
This.nConnection = SQLConnect(This.cDSN,This.cUserId,This.cPassWord)
 
If This.nConnection > 0
  This.lSetFields()
  This.GetPrimaryKeyInfo()
Endif
 
EndFunc
 
Function Destroy
  SQLDisconnect(This.nConnection)
 
EndFunc
Function lSetFields()
 
Local lnFields
Private aJunk
 
*-- Get list of fields
lnFields = SQLColumns(This.nConnection,This.cTableName,"FOXPRO","c_fields")
 
*-- Create a structure from those fields
Select c_fields
Copy All to Array aJunk
 
*-- Create a blank structure
Create Cursor c_junk From Array aJunk
Select c_junk
Append Blank
 
*-- Create properties
Scatter Name This.oObjectStore Memo
 
*-- Clean up
Use in c_junk
Use in c_fields
 
Function lAdd()
  This.cEditMode = "ADD"
  This.lSetFields()
 
  *-- Go get a new key
  Local lnExec
  SQLCancel(This.nConnection)
  lnExec = SQLEXEC(This.nConnection,"Exec sp_dpsi_newkey","c_newkey")
  lcCommand = "This.oObjectStore." + This.cKeyFieldName + "=c_newkey.exp"
  &lcCommand.
 
  *-- set up the key value of the object
  This.xKeyValue = Eval("This.oObjectStore." + This.cKeyFieldName)
  SQLCancel(This.nConnection)
 
EndFunc
 
Function lSave()
  Do Case
    *-- adds perform inserts
    Case This.cEditMode = "ADD"
       lcSQL = [Insert Into ] + This.cTableName  + [(]
       lcSql = lcSql + This.cValueString() + ") values ("
       lcSql = lcSql + This.cDataString() + ")"
       Local lnResults
       *-- Make sure we can update identity fields
       lnResults = SQLExec(This.nConnection,"SET IDENTITY_INSERT " + This.cTableName + " ON")
     
       *-- Execute the insert
       lnResults = SQLExec(This.nConnection,lcSQL)
     
       *-- Reset identity updating
       lnResults = SQLExec(This.nConnection,"SET IDENTITY_INSERT " + This.cTableName + " ON")
 
    *-- Edits perform updates
   Case This.cEditMode = "EDIT"
       lcSQL = [Update ] + This.cTableName
       lcSql = lcSql + [ SET ] + This.cSetString()
       lcSql = lcSql + [ Where ] + This.cKeyFieldName + [=] + This.lSqlServerString(This.xKeyValue) 
       Local lnResults
       *-- Make sure we can update identity fields
       lnResults = SQLExec(This.nConnection,"SET IDENTITY_INSERT " + This.cTableName + " ON")
     
       *-- Execute the update
       lnResults = SQLExec(This.nConnection,lcSQL)
     
       *-- Reset identity updating
       lnResults = SQLExec(This.nConnection,"SET IDENTITY_INSERT " + This.cTableName + " ON")
 
  EndCase
  This.cEditMode = "VIEW"
 
EndFunc
 
*-- Create the value list
Function cValueString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields
 
*-- Scan through the field list
lnFields =Amembers(laFields,This.oObjectStore)
For lnKount = 1 to Alen(laFields,1)
  If lnKount > 1
    lcRetVal = lcRetVal + "," + laFields[lnKount]
  Else
    lcRetVal = lcRetVal + laFields[lnKount]
  Endif
EndFor
 
Return lcRetVal
 
EndFunc
 
*-- Return the data string
Function cDataString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields, lnKount
 
*-- Scan trough the field list
lnFields =Amembers(laFields,This.oObjectStore)
For lnKount = 1 to Alen(laFields,1)
  If lnKount > 1
    lcRetVal = lcRetVal + "," + This.lSqlServerString(EVAL("This.oObjectStore." + laFields[lnKount]))
  Else
    lcRetVal = lcRetVal + This.lSqlServerString(EVAL("This.oObjectStore." + laFields[lnKount]))
  Endif
EndFor
 
Return lcRetVal
EndFunc
 
*-- This method creates the string for a SQL Update clause
Function cSetString()
Local lcRetVal, lnKount
lcRetVal = ""
Private laFields, lnFields
 
*-- Scan through the field list
lnFields =Amembers(laFields,This.oObjectStore)
 
For lnKount = 1 to Alen(laFields,1)
 
  *-- How many fields have been changed
  Local lnUpdated
  lnUpdated = 0
  *-- Compare current value to old value
  If Eval("This.oObjectStore." + laFields[lnKount]) # ;
     Eval("This.oCompareObjectStore." + laFields[lnKount])
 
    lnUpdated = lnUpdated + 1
    If lnUpdated > 1
      lcRetVal = lcRetVal + "," + laFields[lnKount] + " = " + This.lSqlServerString(Eval("This.oObjectStore." + laFields[lnKount]))
    Else
      lcRetVal = lcRetVal + laFields[lnKount] + " = " + This.lSqlServerString(Eval("This.oObjectStore." + laFields[lnKount]))
    Endif
 
  Endif
 
EndFor
 
Return lcRetVal
 
*-- This function converts a data string to the SQL server equiv
Function lSQLServerString(pxData)
 
Local lcRetVal
lcRetVal = ""
 
 
Do Case
  Case Type("pxData") = "C"
    lcRetVal = ['] + pxData + [']
  Case Type("pxData") = "N"
    lcRetVal = Str(pxData)
  Case Type("pxData") = "L"
    lcRetVal = IIF(pxData,[1],[0])
  Case Type("pxData") = "D"
    lcRetVal = ['] + DTOC(pxData) + [']
EndCase
 
Return lcRetVal
 
EndFunc
 
Function lDelete()
 
  lcSQL = [Delete From ] + This.cTableName
  lcSql = lcSql + [ Where ] + This.cKeyFieldName + [=] + This.lSqlServerString(This.xKeyValue) 
  Local lnResults
  
  *-- Execute the delete
  lnResults = SQLExec(This.nConnection,lcSQL)
 
EndFunc
 
Function lEdit()
  This.cEditMode = "EDIT"
 
EndFunc 
 
Function lFindKey(pxKeyValue)
 
Local lcQuery
lcQuery = [Select * From ] + This.cTableName + [ Where ]
lcQuery = lcQuery  + This.cKeyFieldName + "="
 
*-- Either numbers or chars
If Type("pxKeyValue") = "C"
  lcQuery = lcQuery + ['] + pxKeyValue + [']
Else
  lcQuery = lcQuery + Str(pxKeyValue)
Endif
 
*-- Set the key value
This.xKeyValue = pxKeyValue
 
*-- Go get the record
lnResults = SQLExec(This.nConnection,lcQuery,"c_results")
Select c_results
 
*-- Create data entry buffer
Scatter Name This.oObjectStore Memo
 
*-- Compare buffer
Scatter Name This.oCompareObjectStore Memo
EndFunc
 
 
Function GetPrimaryKeyInfo()
 
*-- This function gets the unique combination of fields from the
*-- specified table. Currently it only works for a single field column
lnResult = SQLEXEC(This.nConnection,[exec sp_special_columns '] + This.cTableName + ['],"c_keyinfo")
 
*-- Set key field name
This.cKeyFieldName = c_keyinfo.column_name
 
EndDefine

Using The Servers

Using your servers is now as simple as instantiating the servers and calling them from your favorite development environment.

*-- Launch VFP class
oVFPServerData = CreateObject("cusIcMastVFP")
oVFPServerdata.lAdd()
oVFPServerData.oOBjectStore.icm_desc = "GDC Jams"
oVFPServerdata.lSave()
 
*-- Launch SQL Server
oSQLServerData = CreateObject("cusIcMastSQL")
oSQLServerdata.lAdd()
oSQLServerData.oOBjectStore.icm_desc = "GDC Jams"
oSQLServerdata.lSave()
 
To call these objects from VB or another development environment you need to make sure to add the OLEPUBLIC keyword to your class definitions. From Visual Basic you simply change the code to the following:
 
*-- Launch VFP class
Set oVFPServerData = CreateObject("cusIcMastVFP")
oVFPServerdata.lAdd()
oVFPServerData.oOBjectStore.icm_desc = "GDC Jams"
oVFPServerdata.lSave()
 
*-- Launch SQL Server
Set oSQLServerData = CreateObject("cusIcMastSQL")
oSQLServerdata.lAdd()
oSQLServerData.oOBjectStore.icm_desc = "GDC Jams"
oSQLServerdata.lSave()

Conclusion

As you can see developing three tiered applications is not that difficult. In this session you learned: