As mentioned in my earlier post the below code does not work because of the PK Problem. So I added a dummy where clause and still it failed. After much testing this is what I found:
The DMLCode needs a NameList and a FieldList setup before SQL will even look at it. Without these two props being filled in the DMLCode nothing would work.
2. Name list needs square brackets as:
TEXT TO lcNameList NOSHOW PRETEXT 2
CLIENTNO Catester.[CLIENTNO],
PLAN Catester.[PLAN],
TYPE Catester.[TYPE],
DATE Catester.[DATE]
ENDTEXT
3. No Need for FieldList
4. The InsertCmd and UpdateCmd need square brackets:
TEXT TO lcInsertCmd NOSHOW PRETEXT 2
insert into catester (
[CLIENTNO],
[PLAN],
[TYPE],
[DATE])
values (
?RTRIM(crsCatester.CLIENTNO),
?RTRIM(crsCatester.PLAN),
?RTRIM(crsCatester.TYPE),
?crsCatester.DATE)
ENDTEXT
lcInsertCmd = STRTRAN(lcInsertCmd, VFP_CRLF, [ ])
TEXT TO lcUpdateCmd NOSHOW PRETEXT 2
update catester set
[CLIENTNO]= ?RTRIM(crsCatester.CLIENTNO),
[PLAN]= ?RTRIM(crsCatester.PLAN),
[TYPE]= ?RTRIM(crsCatester.TYPE),
[DATE]= ?crsCatester.DATE
where [clientno] = ?RTRIM(crsCatester.CLIENTNO)
ENDTEXT
Deletecmd does not. With these changes and with a proper where clause in the Updatecmd (generated if you have PK's but leaves a trailing where if not and does not then work) this code works.
As I also mentioned, I left this section alone and made the changes to the _ca_autoupdate code. I also provided for some properties and extra methods. The source of what I did is given below with a bit of old code before and after as placeholders.
Thanks again for the excellent class.
*****************************************
* changes shown here
*****************************************
If Wvisible('Properties')
Hide Window Properties
Endif
With loClass
*********************************************************************
* 1 - BABExtraCode - add new props here/set vals here
.FetchAsNeeded = .F.
.FetchSize = -1
.ConflictCheckType = 4
.ConflictCheckCmd = " IF @@ROWCOUNT=0RAISERROR('Update conflict.',16,1)"
*********************************************************************
* old other code comes here
*********************************************************************
* 2 - BABExtraCode - put new methods here
.WriteMethod([savedata], This.cSavedataCode,.T.)
.WriteMethod([makeupdatable], This.cmakeupdatableCode,.T.)
.WriteMethod([makenotupdatable], This.cmakenotupdatableCode,.T.)
.WriteMethod([filldata], This.cfilldataCode,.T.)
*********************************************************************
Endwith
loClass = .Null.
Release loClass
Keyboard 'Y'
*********************************************************************
*********************************************************************
+ [*cSelectOtherClauses_Assign Assign method that fires the _BuildSelectCmd if the Property value is changed.] + VFP_CRLF
* 3 - BABExtraCode - need to add your descriptions here or the methods will disappear!
lcProps = lcProps +;
+ [*savedata Saves the data using tableupdate] + VFP_CRLF;
+ [*makeupdatable Makes a cursor updatable] + VFP_CRLF;
+ [*makenotupdatable Makes a cursor NOT updatable] + VFP_CRLF;
+ [*filldata Calls Cursorfill to fill a cursor] + VFP_CRLF
lcMethods = ;
*********************************************************************
*********************************************************************
cCmdHavingCode = []
*!* End - New As of July 17, 2003
* 4 - BABExtraCode neededfor extra procedures and methods added
www=0
cSavedataCode = []
cmakeupdatableCode = []
cmakenotupdatableCode = []
cfilldataCode = []
* ECH
Protected Procedure Init
********************************************************************
*********************************************************************
tcNew = Alltrim(tcNew)
This.cSelectOtherClauses = tcNew
This._BuildSelectCmd()
Endtext
* 5 - BABExtraCode - add here the actual code that will fill each new method
*code is formatted with tabs and spaces because oof Pretext 1
TEXT TO THIS.csavedataCode NOSHOW PRETEXT 1
lsuccess=TABLEUPDATE(1,.F.,this.alias)
* Error handling function. Displaying the error message if update conflict occurs.
IF !lsuccess
=AERROR(lar)
IF "Update conflict"$lar[2]
MESSAGEBOX("Update conflict!-Reverting changes")
=TABLEREVERT(.f.,This.alias)
ELSE
MESSAGEBOX( lar[2])
ENDIF
ELSE
WAIT WINDOW NOWAIT "Table Updated: "+This.Alias
ENDIF
ENDTEXT
TEXT TO THIS.cmakeupdatableCode NOSHOW PRETEXT 1
LPARAMETERS cTablename,cAlias
*BAB* IF PCOUNT() # 2
WAIT WINDOW "Call this and pass it 1. The SQLTable name 2. The alias of the cursor"
*BAB* RETURN
*BAB* ENDIF
SELECT (cAlias)
* set buffering to Optimistic Table
CURSORSETPROP("Buffering" ,5)
* set updated on
CURSORSETPROP("sendupdates",.t.)
clear
* set the table name to be updated
CURSORSETPROP("tables",cTablename)
* get a list of fields into an array
bb=AFIELDS(aTemp)
cUpdatename = ""
cUpdatablefieldlist = ""
cKeyfieldlist = ""
FOR nCnt = 1 TO bb
IF LEFT(ALLTRIM(aTemp[nCnt,1]),1) # "["
cUpdatename = cUpdatename + ALLTRIM(aTemp[nCnt,1]) + " " + cTablename + "." + ALLTRIM(aTemp[nCnt,1])+ + IIF(nCnt = bb,"",",")
cUpdatablefieldlist = cUpdatablefieldlist + ALLTRIM(aTemp[nCnt,1]) + IIF(nCnt = bb,"",",")
ENDIF
ENDFOR
cKeyfieldlist = cUpdatablefieldlist
*?cUpdatename
CURSORSETPROP("updatename",cUpdatename)
*?cUpdatablefieldlist
CURSORSETPROP("updatablefieldlist",cUpdatablefieldlist)
* set keyfield list.
CURSORSETPROP("keyfieldlist",cKeyfieldlist)
RETURN
ENDTEXT
TEXT TO THIS.cmakenotupdatableCode NOSHOW PRETEXT 1
WITH This
IF NOT .isupdatable
.UpdatableFieldList = ""
.UpdateNameList = ""
.ConversionFunc = ""
.KeyFieldList = ""
.CursorSchema = ""
ENDIF
ENDWITH
ENDTEXT
TEXT TO THIS.cfilldataCode NOSHOW PRETEXT 1
LPARAMETERS tcdataonload
IF EMPTY(tcdataonload)
* if no param passed, then we get an empty cursor
.lnodataonload = .T.
ENDIF
* tcodataonload if "NO" then NO data will be loaded but cursor will be created empty
WITH This
.lnodataonload = IIF(tcdataonload = "YES",.F.,.T.)
DO WHILE SQLGETPROP(oConn,"ConnectBusy")
IF INKEY(.25,"H") = 27
EXIT
ENDIF
ENDDO
IF !.CursorFill()
AERROR(aErMsg)
MESSAGEBOX(aErMsg[2],16,"Error",0)
RETURN .F.
ENDIF
ENDWITH
ENDTEXT
* ECH
Endproc
Procedure Release
Release This
*********************************************************************
*********************************************************************
* 6 - BABExtraCode - Need square barckets if SQL around fieldname
* OldCode - THIS.cNameList = THIS.cNameList + lcFName + [ ] + THIS.cTableName + [.] + lcFName
If !This.lOracle And !This.cConnectType = [NATIVE]
This.cNameList = This.cNameList + lcFName + [ ] + This.cTableName + [.] + '['+lcFName+']'
Else
This.cNameList = This.cNameList + lcFName + [ ] + This.cTableName + [.] + lcFName
Endif
***************************************************
* End of changes made
***************************************************
Have you had the time to convert the class to a visual Form?
Cheers
Bernard
>In the Cr8_DMLCode procedure, look for:
>
>lcInsert = lcInsert + [ ] + lcFName
>lcUpdate = lcUpdate + [ ] + lcFName + [ =]
>
>Try changing this to:
>
>lcInsert = lcInsert + " [" + lcFName + "]"
>lcUpdate = lcUpdate + " [" + lcFName + "] ="
>
>I will look at putting this in a CASE statement so the brackets are not used for VFP or Oracle tables. Let me know if those changes work. Thanks!
>
>
>>Hi Mark
>>
>>I have a problem with the system I am working on. Some of the fields in SQLServer are keywords like TABLE and DATE etc. This was from a legacy system I am converting to CS using your CA generator.
>>
>>I asked the Database Admin to change these fields and his reply made sense. He said that todays fields may become tomorrow's keywords. He has no problem accessing these fields provided he uses square brackets [TABLE] around the field in SQLQueries. He suggested why not I change the CA code so that square brackets went around ALL fields to make it future proof!
>>
>>This is good thinking, except I dont know how to do this. I know it must be in the _ca_autoupdate section or the _ca_dmlcode section.
>>
>>Could you point me in the right direction to implement this in your CA builder? I need to be able to query as well as update the table.
>>
>>TIA
>>
>>Bernard
