record in remote view "deletes" after second tableupdate

[opticalman]

I am using VFP 8.0
I am using remote views
I am using SQL 2005 as the backend

If I edit a record and do a TABLEUPDATE( .t. , .t. ), everything is OK. But if I edit it a SECOND time and do a TABLEUPDATE( .t., .t. ). The record :
1)is marked as deleted in the grid
2)dissappears if I click on another record.
3)dissappears if I issue a TABLEREVERT()
4)is still gone after closing the view then re-open it
5)re-apears if I close the database then start again

TABLEUPDATE() allways returns a value of .T.
Multiple TABLEUPDATE() do not cause a problem.
2 edits of the same record cause a problem.
Any suggestions?

Jim Rumbaugh

 

[alvechurchdata]

I seem to recall having a similar problem.Is the remote view set up to do updates by "Delete and Insert"?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

I have the same assumption as alchevurdata. For VFP it's not good to use the "Delete and Insert" strategy of updating data. Also not for remote databases, as this causes these issues with the view cursor. You'll only see the record back as a new one then, when you requery the data, the view reflects correctly, that the old version of the record is deleted, the record is now a new record in the SQL database and needs to be queried as a new record.

Therefor it's recommended to use the default UpdateType 1 - update old data with new data.

Bye, Olaf.

 

[opticalman]

alvechurchdata
I am not using SQL_DELETE then INSERT
I am using SQL UPDATE

OlafDoschke
GETCURSORPROP("UpdateType") = 1
But when I read the word "requery" in your post, it sparked an idea. I can do some thing like:

nThisRecord = RECNO()
TABLEUPDATE( .T. , .T. )
REQUERY()
GOTO nThisRecord

That will let me do multiple edits with no problems. It just seems like overkill. If I do not get a better answer, that is what I will do.

 

[opticalman]

Well.... it's still a work in progress...
that n = recno() , and goto n stuff worked fine, but causes BIG CONFUSION if you edit something that moves the record's recno() in the grid. Instead of making a general purpose MyTableUpdate wich remembers the record position and goes to it again, I'll have to make something that can identify the record and seek it or locate it. This is NOT what I consider a good solution. I hope someone has a better explanation as to why the record disappears if edited and tableupdated twice (which looses the second info added to the record).

 

[Olaf Doschke]

You generally have an identifier in each record: The primary key. So instead of GOTO Recno use SEEK ID.

Recnos of new records are negative by design, as they are not yet saved to disk/remote database. Nobody, especially not foxpro can know which recno they will have after tableupdate(). That's your problem with that.

If you do tableupdates by SQL UPDATE you should not have the problem of double editing - after a TABLEUPDATE() the table buffer of the view is emptied by the tableupdate - the buffer is written - and the view reflects what is stored in the database, except other users change the same records.

I'm stumped, what's causing the disappearance of the record. A Tableupdate() does not delete rows in the view - never ever. It's just a refresh after a tableupdate with the DELETE & INSERT update type that would explain a deleted view record. The reason must be something else than the Tableupdate().

Bye, Olaf.

 

[opticalman]

Olaf
Thanks for your input.
Perhaps I should not use the word DELETE, perhaps I should use disapear.

I found out if I SET DELETED OFF, the record does not disapear. It stays in the grid with the DELETED MARK highlighted, but I still have a saving problem.

I cannot edit any record in the remote view a second time and get it to save the information. This may be a buffering or update conflict issue. Perhaps I should restate my question or start another thread.

If I do not issue a TABLEUPDATE()between edits, I cannot change records without an UPDATE CONFLICT after a record is edited a second time. If I do a TABLEUPDATE()after edits, I can go between 2 records and make as many edits as I wish, BUT ony the first edits are saved.

Is it standard to do a REQUERY() after every TABLEUPDATE() when using remote view?

 

[Olaf Doschke]

Well, if you see the Deletion mark in the grid, the record is deleted, at least in the view cursor. I know that there is no such state as DELETED() in SQL Server, but somehow the view record must get it's deleteion mark, and this is not done by tableupdate(), not the first update, neither the second one. Tableupdate() dooes neer influence the deletion status of a view cursor's records.

You can find out if and what conflicts you have during the Tableupdate() with AERROR(). But you say Tableupdate returns .T., which means there are no conflicts.

That's why I'm stumped. Still don't know what's going on. More details, especially your coding would perhaps help. If you could make a reproduction of the problem with some remote view on a sample SQL table, I have SQL 2005 and VFP9 available and I don't think it's a VFP8 bug you're having there.

Bye, Olaf.

 

[opticalman]

Thanks Olaf

I'll try to put some code together to replicate the problem, but it will be next week. This is not urgent, but I would like to resolve this curious problem

bye, Jim

 

[opticalman]

I think I have solved the mystery. The Remote view has KEY FIELDS and a TIMESTAMP field. The problem occurs when the TIMESTAMP field is marked as a KEY FIELD. In the View Designer, under the Update Criteria tab, there is a place to check mark for “SQL WHERE clause includes” I have tried all 4 possibilities. The problem only goes away when I unmark the TIMESTAMP as a key field.

OR, if I select “UPDATE using SQL DELETE then INSERT”, it works fine, even with the TIMESTAMP field marked as a KEY FIELD. I find this curious, because this was was one of the suggested possible problems, not a cure.

Do you suggest I leave the TIMESTAMP as a regular field and not a KEY FIELD, or go with “UPDATE using SQL DELETE then INSERT”.??? or is there a third possibility???

 

[Olaf Doschke]

the key field should be the key field. A timestamp surely is no key field. So better set the key field correctly. It doesn't make sense to work with a timestamp field as the key just because it works.

Bye, Olaf.

 

[opticalman]

Olaf

Thank you. I appears I have not been using the TIMESTAMP field properly. I will remove it as a key field.

what I've learned: SQL tables should have a TIMESTAMP field for processing, BUT, the TIMESTAMP should not be a key field of the index or unexpected results can happen.

I consider this problem solved.