Search Forum Home   Visual FoxPro  Client/Server  INTELLIGENT UPD...  INTELLIGENT UPDATE OF ONLY MODIFIED FIELDS WITH SQL SER    6/3/2009 2:35:49 PM vicos 139 posts INTELLIGENT UPDATE OF ONLY MODIFIED FIELDS WITH SQL SER  (N/A) Scopul aceastei funcţii este de a actualiza in tablela din baza SQL Server numai campurile care au fost modificate de catre utilizator. Reduce trafic reţea cu 40%. http://www.foxite.com/faq/default.aspx?id=66 --- 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  6/3/2009 7:47:29 PM Grigore Dolghin 3590 posts www.class-software.eu Re: INTELLIGENT UPDATE OF ONLY MODIFIED FIELDS WITH SQL SER  (N/A) Bucata asta de cod are o mare problema de design: ====================== * --- 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 ====================== Daca lcDatStr contine un apostrof o sa dea erori. Pe de alta parte, obtinerea instructiunii sql prin concatenare e o mare gaura de securitate. Sa zicem ca lcDatStr este urmatorul text: ';DROP TABLE users; --- Comanda rezultata o sa arate asa: Update numetabela SET numecamp = ''; DROP TABLE users; ----' Ghici ce-o sa se intample. Si faza e ca textul ala il scrie userul, deci.... Nota: sintaxa de mai sus este pentru MySQL, da' merge pe orice server SQL cu ajustarile de rigoare. Grigore Dolghin Visual FoxPro MVP 2006 - 2010 Class Software My blog  6/3/2009 11:23:15 PM vicos 139 posts Re: INTELLIGENT UPDATE OF ONLY MODIFIED FIELDS WITH SQL SER  (N/A) Am gasit codul asta pe foxite si mie mi se pare interesat ca si concluzie: CAND MODIFICI DATE DE PE SERVER SA FACI UPDATE NUMAI PE CAMPURILE MODIFICATE DE USER. De ex aduci date de pe server pt modificarea unei facturi. Userul modifica decat cantitatea. Atunci faci update numai pt cantitate, pret, tva, valoare totala, etc (campuri ce se modifica in functie de cantitate). Celelate campuri nu mai le ridici pe server (UPDATE), de ex. nrfactura, datafactura, datascadenta, codpartener, numepartener, adresapartener.., codprodus, numeprodus, etc. Autorul mentiona ca mai mult ca sigur codul respectiv poate fi optimizat.  Page 1 of 1   Visual FoxPro  Client/Server  INTELLIGENT UPD... Flat View Tree View Oldest To Newest Newest To Oldest