>>>>Yuck, yuck, yuck. Why do things have to be so crappy? I inherited an application. It's using a mix of SPT and Remote Views. I wanted to use a SQL Server transaction. Nope. The application has one main connection. The views get different connection numbers. I tried to make them share, but that began to cause problems.
>>>>
>>>>I made a bunch of queries to extract data as the views did. I then used the app's built in "SPT-based create an execute an sql insert command". I also made update queries. So, I excluded views. The transaction worked fine. Views and parameterized views have mutated over the years, and were finally replaced with cursor adaptors. But I'm stuck in 6 for the time being.
>>>>
>>>>Views are basically a name given to a bit of SQL code.
>>>>
>>>>I am trying to think through some form of alternative to views, that I can implement easily going forward.
>>>>
>>>>There is the make_updateable (sp) bit of code. That should allow for all inserts and updates. Does it include allowing 2 users who did not touch the same fields to save? Does it also respect the buffer mode? As the user moves off a record, it can update/insert?
>>>>
>>>>To requery this hypothetical view, a safe-select can be used. It can first update any changes in the local cursor from the remote. It can then append all the records not in the local cursor (provided the primary keys are easily obtained).
>>>>
>>>>Once this alternate view is opened, I may want to change the parameters.
>>>>
>>>>Is there an existing VFP6 data object class that does all of the above?
>>>
>>>I have a class that would work in a buffered cursor, before Tableupdate() it creates a SPT to update the fields that have a dirty buffer. So it only updates those fields that have been modified. But that would mean, instead of TableUpdate() you would need to call oTable.Update() or something like that. Not sure if that would help you in any way.
>>
>>Unfortunately, there is no buffering or table update. They rolled their own with secondary edit-specific cursors and/or scatter name.
>>
>>scatter name SomeTable
>>...
>>oApp.tblname='SomeTable'
>>oApp.Insert_Record()
>>select SomeTable
>>requery('SomeTable')
>>
>>Had this been done solely with SPT and not some weird hybrid...
>
>However scatter is somewhat similar to buffering, except that you need to handle the buffer manually. So for each field ! Value == m.Value and construct the update statement from that, and then gather.
>
>Only real problem I see if you edit multiple records, you don't have a getnextmodified().
>
>It's a little bit old and rusty, but could be a workaround. Interestingly performance is nearly identical to CA.
>
>
>LPARAMETERS toBusiness AS ccntBusinessData OF aBasicGen
>*
>LOCAL llFirstRecord, lcFieldlist, lnFields, lcValueList, lcUpdateList, lcFieldName
>LOCAL lnNr, lcKeyFieldValue, llRetVal, lcQuery
>*
>*-- Compare the alias with the table.
>SELECT (THIS.AliasName)
>*
>lcKeyFieldValue = EVALUATE(This.AliasName + "."+ THIS.KeyFieldName)
>*
>llFirstRecord = .T.
>lcFieldList = "("
>lcValueList = "("
>lcUpdateList = ""
>lnFields = 0
>*
>IF THIS.cFieldList = "*"
> *
> *-- If we selected all fields to be updated, create the field list from all fields
> *-- in the current cursor.
> lnFields = FCOUNT(THIS.AliasName)
> *
> FOR lnNr = 1 TO m.lnFields
> *
> lcFieldName = UPPER(FIELD(m.lnNr, THIS.AliasName))
> *
> IF UPPER(m.lcFieldName) $ UPPER(toBusiness.cForeignFieldList)
> *-- The foreign field list with field names that should or cannot be updated.
> LOOP
> ENDIF
> *
> IF m.lcFieldName == UPPER(THIS.KeyFieldName) ;
> OR INLIST(GETFLDSTATE(m.lcFieldName,THIS.AliasName),2,4)
> *
> lcFieldList = m.lcFieldList + IIF(m.llFirstRecord,"",",") + m.lcFieldName
> lcValueList = m.lcValueList + IIF(m.llFirstRecord,"",",") + "?" + m.lcFieldName
> *
> lcUpdateList = m.lcUpdateList + IIF(m.llFirstRecord,"",",") + m.lcFieldName
> lcUpdateList = m.lcUpdateList + "= ?" + m.lcFieldName
> *
> llFirstRecord = .F.
> ENDIF
> *
> ENDFOR
>ELSE
> *
> *-- Only use the fields that are specified in the field list.
> lnFields = ALINES(laFields, THIS.cFieldList,.T.,",")
> FOR lnNr = 1 TO lnFields
> *
> lcFieldName = UPPER(laFields[lnNr])
> *
> IF lcFieldName == UPPER(THIS.KeyFieldName) ;
> OR INLIST(GETFLDSTATE(m.lcFieldName,THIS.AliasName),2,4)
> *
> lcFieldList = m.lcFieldList + IIF(m.llFirstRecord,"",",") + m.lcFieldName
> lcValueList = m.lcValueList + IIF(m.llFirstRecord,"",",") + "?" + m.lcFieldName
> *
> lcUpdateList = m.lcUpdateList + IIF(m.llFirstRecord,"",",") + m.lcFieldName
> lcUpdateList = m.lcUpdateList + "= ?" + m.lcFieldName
> *
> llFirstRecord = .F.
> *
> ENDIF
> ENDFOR
>ENDIF
>*
>lcFieldList = m.lcFieldList + ")"
>lcValueList = m.lcValueList + ")"
>*
>IF THIS.NewRecord
> *
> TEXT TO lcQuery TEXTMERGE NOSHOW
> INSERT INTO <<THIS.TableName>>
> <<lcFieldList>> VALUES <<lcValueList>>
> ENDTEXT
> *
>ELSE
> *-- Update the record.
> TEXT TO lcQuery TEXTMERGE NOSHOW
> UPDATE <<THIS.TableName>>
> SET <<lcUpdateList>>
> WHERE <<THIS.KeyFieldName>> = ?pcRecordKey
> ENDTEXT
> *
> PRIVATE pcRecordKey
> pcRecordKey = THIS.RecordKey
> *
>ENDIF
>*
>SELECT(THIS.AliasName)
>llRetVal = THIS.DoSQL(m.lcQuery)
>*
>IF m.llRetVal
> =TABLEUPDATE(.F.,.T.,THIS.AliasName)
>ENDIF
>*
>RETURN llRetVal
>Yes, that could work. In the case of a set of records, there is a separate cursor of edited records, so I could scan them and do that.
Thanks
