Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
SQL SERVER >>  INTELLIGENT UPDATE OF ONLY MODIFIED FIELDS WITH SQL SERVER DATABASES

  Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass



Just felt that maybe I would repost this due to the number of folks targeting SQL Server backends within the forum. Lost of new memebers
here in the past 6-8 months.
The purpose of this function is to update to the underlying SQL Server table only the fields that have been modified by the user. This is specific to pass-though sql only.

 --- fSQLupdate.prg

* --- Programmer	: Pete Sass
* --- Date      	: 2006.09.26
* --- Calling Syntax: lnUpdateTest = fSQLupdate("Orders","CustomerID",1)
* --- Assuming 		: You are updating a SQL-Server table called "Orders" and
* ---                 this table has a unique "CustomerID" field and in this case
* ---                 the unique CustomerID is a numeric field and I am passing
* ---                 record #1, so this is the record that will get updated.

* --- This function is used to find out what fields have in fact
* --- changed in a local cursor and update only the changed fields when 
* --- writing to a SQL-Server backend table.

* --- Notes: In order for this to work you pass the SQL-Server table name the 
* --- unique field name and the unique ID for the record you have changed.  
* --- This assumes you have conformed to standard backend database design and have
* --- a unique ID associated with every record in every table... if you
* --- do not shame on you.
* --- This function also assumes your backend connection handle name is
* --- cConn.  If you have a different connection handle name, change it
* --- in the code below to refect your conection handle name.
LPARAMETERS lSQLTable, lSQLuniqueFLDName, lSQLuniqueID

* --- Store the local cursor alias name.
lcAlias = ALIAS()
* --- Create all local memory variables that will be using within this function.
STORE "" TO lcFldVal
STORE "" TO lcDatStr
STORE "" TO lcDatTyp
STORE  0 TO lnConnTest
STORE "" TO lcUpdateStr
STORE  0 TO lnFldCount
STORE  0 TO lnArrayPos
STORE  0 TO lnFLDwidth
STORE  0 TO lnFLDdecimals
STORE  0 TO lnHoursDef
STORE "" TO lcUniqueTYP
STORE "" TO lcUniqueVal

* --- Use the TYPE() function to find out the data type of the lSQLuniqueID as
* --- we could be looking at either a character, or a numeric datas type.  If
* --- the data type is a character type I need to enclose it with single quotes.
lcUniqueTYP = lSQLuniqueID
lcDatTyp = TYPE('lcUniqueTYP')
IF lcDatTyp = "C"
   * --- First, remove amy leading and trailing blank spaces.
   lSQLuniqueID = ALLTRIM(lSQLuniqueID)
   * --- Secondly, enlose the value within single quotes.
   lSQLuniqueID = "'"+ lSQLuniqueID + "'"
   lcUniqueVal = "C"
ELSE
   lcUniqueVal = "N"
ENDIF

* --- You need to set the HOURS to 12 so it conforms to the default SQL-Server
* --- date and time format that is mm/dd/yyyy hr:mi:ss AM, or  mm/dd/yyyy hr:mi:ss PM
* --- Get the current system hour setting and store in lnHourDef memory variable.
lnHourDef = SET("HOURS")

* --- Set the hours to the correct SQL-Server format.
SET HOURS TO 12

* --- Create an array of the currently opened cursor using the AFIELDS() function
* --- call.  This will be used later on in this UDF to build the Update string
* --- command using SQL pass-through commands.
lnFldCount = AFIELDS(UDFTblArray) 

* --- Process all the fields that have in fact been changed using
* --- the GETFLDSTATE() function and adding them to the string.
FOR lnCnt = 1 TO FCOUNT()
  lcField = FIELD( lnCnt )
  nState = GETFLDSTATE(lcField)
   
  * --- The field value has changes save this to my local memory variable lcUpdStr.
  * --- At the same time store the new values in a secondary string called lcDataStr.
  IF nState = 2
     lcFldVal = IIF(EMPTY(lcFldVal), "", "") + lcField 
     * --- Get the value out of the field that has been changed.
     lcDatStr = &lcFldVal
   
     * --- OK now I got the field that was changed and I have the new value associated
     * --- with this field, but I have to take into consideration what type the
     * --- data really is.  This is needed as when I try to build my Update string
     * --- if the field is a character type I have to ensure the value is enclosed
     * --- in single quotes.
     * --- Use the TYPE() function call to obtain the field type, character,
     * --- numeric, or date.  
     
     * --- TYPE() function legend.  
     * --- "C" is a Character or Varchar field type,
     * --- "N" is a Numeric, Float, Double, or Integer,
     * --- "D" is a Date type,
     * --- "T" is a DateTime type.
     lcDatTyp = TYPE('lcDatStr')

     * --- Case statement logic flow below to cover 3 types of data; character, numeric and datetime.
     DO CASE
     * --- If a type return value of "C" then this is a character field value and
     * --- I must enclose the value within single quotes to work correctly in my
     * --- Update string.

        * --- CASE CHARACTER FIELD TYPE.   
        CASE lcDatTyp = "C"
        
        * --- First, remove amy leading and trailing blank spaces.
        lcDatStr = ALLTRIM(lcDatStr)
        
        * --- Secondly, enlose the value within single quotes.
        lcDatStr = "'"+ lcDatStr + "'"
        
        * --- Make the SQL pass-through update string.
        lcUpdateStr = "UPDATE " + lSQLTable + " SET " + lcFldVal + " = " + lcDatStr

        * --- CASE NUMERIC FIELD TYPE.
        CASE lcDatTyp = "N"
           * --- Now in this case we could be dealing with an Integer, or a Numeric value
           * --- that could be accurate to a number of decimal points.  We need to take
           * --- this into consideration when building the string value and in order
           * --- to verify the field value and accuracy we need to use the AFIELDS()
           * --- function call to create an array and test for the numeric field length
           * --- and as mentioned the decimal point accruacy.
           * --- The AFIELDS() function was called and created at the beginning of this
           * --- UDF function and is currently available for us to use now.
        
           * --- Lets find the array element of the field currently being processed.
           * --- Upper function added since array has field names in upper.
           lnArrayPos = ASCAN(UDFTblArray, UPPER(lcFldVal))
        
           * --- Now we know that the field length is 2 elements passed the lnArrayPos
           * --- and we know the decimal point accuracy is 3 elements passed the lnArrayPos
           * --- so lets get these values out now.
           lnFLDwidth = UDFTblArray(lnArrayPos + 2)
           lnFLDdecimals = UDFTblArray(lnArrayPos + 3)
        
           * --- Now we have all the information required to correctly convert the
           * --- numeric value to the correct character expression without loosing
           * --- any accuracy.
           lcDatStr = STR(lcDatStr, lnFLDwidth, lnFLDdecimals)
       
           * --- Now remove any leading and trailing blank spaces.
           lcDatStr = ALLTRIM(lcDatStr)
       
           * --- Make the SQL pass-through update string.
           * --- However, in creating the SQL Update string we need to convert the
           * --- numeric value back again to a numeric, so we enbedd the VAL function
           * --- within the Update string to covert back to a numeric value.
           lcUpdateStr = "UPDATE " + lSQLTable + " SET " + lcFldVal + " = " + lcDatStr

        * --- CASE DATE AND TIME FIELD TYPE.
        * --- Note, since this is trageting a SQL-Server backend no date type
        * --- exists and ony the DateTime field type is being processed here.
        CASE lcDatTyp = "T"   
           * --- First, convert the datetime data to a string so it can be
           * --- added into the SQL Update string.
           lcDatStr = TTOC(lcDatStr)
            * --- Secondly, enlose the value within single quotes.
           lcDatStr = "'"+ lcDatStr + "'"
           
           * --- Make the SQL pass-through update string.   
           lcUpdateStr = "UPDATE " + lSQLTable + " SET " + lcFldVal + " = " + lcDatStr

        * --- CASE LOGIC FIELD TYPE.
        * --- You must pass a "T", or "F" value from Visual FoxPro to update into the
        * --- SQL-Server "bit" type.
        CASE lcDatTyp = "L"   
           
           * --- If the VFP data is .T. change it to a 1 so you can write back into the SQL-Server
           * --- bit field type.
           IF lcDatStr = .T.
              lcDatStr = '1'
           ELSE
              lcDatStr = '0'
           ENDIF
           
           * --- Make the SQL pass-through update string.   
           lcUpdateStr = "UPDATE " + lSQLTable + " SET " + lcFldVal + " = " + lcDatStr

        * --- CASE MEMO FIELD TYPE.
        * --- This can be treated just the same as a character, or varchar field type.
        CASE lcDatTyp = "M"   
        * --- First, remove amy leading and trailing blank spaces.
        lcDatStr = ALLTRIM(lcDatStr)
        
        * --- Secondly, enlose the value within single quotes.
        lcDatStr = "'"+ lcDatStr + "'"
        
        * --- Make the SQL pass-through update string.
        lcUpdateStr = "UPDATE " + lSQLTable + " SET " + lcFldVal + " = " + lcDatStr
     
     OTHERWISE
     * --- Note.  I am not testing for date types since I am assuming this function is only to
     * ---        interface to a SQL-Server backend that does not support a date type field and
     * ---        only uses a datetime field in an "American" standard default format.
     * ---        I am not checking for "O - Object", "Q - Varbinary", or "S - Screen" field types
     * ---        in this function, but an additional needed type could be added with one additional
     * ---        CASE statement with the required conversion applied.  

          =MESSAGEBOX("The system has encountered an unexpected data type, please contact your ITS Department!",0+16,"Data Error")
          * --- Return zero so we know we experienced a problem.

          * --- Reselect the local cursor.
          SELECT (lcAlias)

          RETURN 0
          
     ENDCASE

    * --- Finally add the WHERE clause onto the end of the Update string so the
    * --- correct records is in fact updated using SQLEXE() pass-through.   
    
    * --- Assumes the unique ID key passed is a character value.
    IF lcUniqueVal = "C"
       * --- Create the final Update string with the correct WHERE portion and key to update added.
       lcUpdateStr = lcUpdateStr + " WHERE " + lSQLuniqueFLDName + " = " + lSQLuniqueID
    ELSE
    
       * --- Assumes the unique ID key passed is a numeric value so create taking into account the
       * --- numeric value that one must change to add into this string using the STR() function.   
       * --- The STR(lSQLuniqueID,8,0) assumes 99,999,999, or 99 million unique key values in the
       * --- table.
       lcUpdateStr = lcUpdateStr + " WHERE " + lSQLuniqueFLDName + " = " + STR(lSQLuniqueID,8,0)
       
       * --- Messagebox below is used for debugging and checking the final SQL Update string.
       * ---MESSAGEBOX(lcUpdateStr)
       * --- RETURN
       
    ENDIF
     
    * --- Finally, we can use the SQLEXEC() function calls to preform our backend update using
    * --- SQL pass-through.
     
    lnConnTest = SQLEXEC(cConn, lcUpdateStr)
    * --- I am performing a validation here to ensure I do not get an error with my SQLEXEC()
    * --- update command.
    IF lnConntest < 1
       
       AERROR(lcErr)
       MESSAGEBOX( lcErr[3], 16, "Pulp Sales Connection Problem encountered, contact your ITS department!" )
       
       * --- If the commit failed perform a rollback.
       = SQLEXEC(cConn,"ROLLBACK")              

       * --- Reselect the local cursor.
       SELECT (lcAlias)

       
       * --- Return zero so we know we encountered a problem.
       RETURN 0
    ENDIF
    
    * --- Issue the commit to the SQL-Server backend/
    lnConnTest = SQLEXEC(cConn,"COMMIT")
    * --- I am performing a validation here to ensure I do not get an error with my SQLEXEC()
    * --- commit statement.
    IF lnConntest < 1
       
       AERROR( lcErr )
       MESSAGEBOX( lcErr[3], 16, "Pulp Sales Connection Problem encountered, contact your ITS department!" )
       
       * --- If the commit failed perform a rollback.
       = SQLEXEC(cConn,"ROLLBACK")       

       * --- Reselect the local cursor.
       SELECT (lcAlias)

       * --- Return zero so we know we encountered a problem.
       RETURN 0
    ENDIF  

  * --- End if field state = 2 logic condition.        
  ENDIF

* --- End of main loop FOR lnCnt = 1 TO FCOUNT()
NEXT

* --- Update the local cursor so the field state will be set back to normal and
* --- this program will not recognize changes processed.
* --- the updates.
* --- Reselect the local cursor.
SELECT (lcAlias)

=TABLEUPDATE()

* --- Set the system hours back to it's original setting.
SET HOURS TO lnHourDef

* --- Return 1, so we know the function completed as planned.
RETURN 1

* --- End of this UDF (user definable function).
ENDFUNC


I only reposted this as an example so you can get the idea of what I am trying to accomplish here. If I have 80-100 users all logged into a multi-user system modifying data, I do not what to send the entire record "all fields" down across the network if a record is modified, I only what changed fields updated to the underlaying SQL Server table.
Again this method assumes SQL pass-though where you have a cursor locally where you make your changes and once all the changes are done when the user clicks the Save button, only the modified fields that were changed are updated to the SQL Server table.
This function also assumes you have designed your SQL Server tables with unique keys in every table and if you have not "shame on you-lol".
I am also sure there are many ways to reduce or optimize this code, but the idea is to reduce network traffic on records being modified.
The add and deletions are not an issue, but when you get tables with 200 plus fields in them, you can see the optimization possibilities with a function like this with a hundred users logged in and a lot of the network traffic is in fact updating existing records.
Utilizing this kind of logic in my code has reduced our total network traffic by 40%
across 8-9 large multi-user systems.
ps.. This is not applicable to smaller scale system deployment efforts.
Modify this code, optimize and have fun.

Pete from the Great White North!

FEEDBACK

Joe Davies @ 5/17/2008 8:44:18 PM
Hi Pete,

This is exactly the sort of function I am looking for.
I'm from an Access programming background so am relatively new to SQL Server, but I'm a fast learner.
I have a table with 30 fields and I want to show what fields have changed due to an edit action. I'd like to show who changed it (user name), The date and time changed, the field name that was changed, the old value and new value of the update. This information to then be inserted into a separate audit trail table. Can you give me some pointers for achieving this with your function. I do appreciate your time.

Thanks Joe Davies
mailto:joe@data-flow.co.uk



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: