Remote Views - Send SQL Updates? 1

[JRB-Bldr]

I have a Database in which I use an ODBC connect to another system's data tables. It seems to work OK.

However I am trying to investigate some mysterious changes to the other systems data contents.

I used the Database Designer to build Remote Views for these other tables. The data is retrieved fine.

Within the Remote View Designer I have the Send SQL Updates checked on the Update Criteria tab. I am not certain if this should be checked or not.

I do NOT want to update the actual remote tables until I programatically issue a TABLEUPDATE()

My previous VFP "advisors" told me to check the box. Then, when questioned about it, was less certain about it.

How can I get my views to work reliably and as expected?

What Remote View Designer - Update Criteria parameters should and/or should not be set so that I can:
* retrieve the remote data into the View
* programatically work with the view record's data within code
* NOT update the original remote table AT ALL
* then update the table intentionally with a TABLEUPDATE()

======================
An associated question - I have created this code within a PRG file and am compiling it into an EXE. Since there is no user input except to launch the EXE I did not think that I needed to use a Form.

Are there reasons that I should consider putting this into a Form (maybe Visible=.F.)? Reasons which would help this run as I need?

Your help and advice is greatly appreciated.

Thanks,
JRB-Bldr

 

[Mike Lewis]

Jrb,

Your understanding of remote views and Send SQL Updates is correct.

In order to send the updates to the server, you definitely need to select Send SQL Updates. You also have to tick the primary key field(s) (put a tick under the key icon) and also flag the specific fields that neeed to be updated (under the pencil icon).

Nothing is sent to the server while you are editing the data. The updates are sent when you issue the TABLEUPDATE().

So, it should all work pretty well as you expected.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[JRB-Bldr]

The reason that I asked the question in the first place is that original remote data table values seem to have changed without my having issued a TABLEUPDATE() (I have that line commented out).

Unfortunately in my rush to a definitive answer I have looked in a variety of places and I continue to get conflicting information on the subject.

From a posting on Experts-Exchange.com ....
"All views are row buffered by default. It means when you move the pointer off an edited record, an implicit tableupdate would occur and data would be updated (SendUpdates marked)."
and
"use myDBC!myView
browse && Edit one row and move off the row - causes data to be updated implicitly"

And from foxdev.com....
"Remote views are, by default, not updateable. Don't misunderstand: you can change the data in the view, but those changes won't be sent to the database server. To make a view updateable, you must check the Send SQL Updates checkbox on the Update Criteria tab, and ensure the primary key settings are correct. With an updateable view, changes to a record will automatically create an SQL Update statement that is sent to the database server; new records will create an SQL Insert statement. With row-buffering, these statements will be sent to the server when the row-pointer changes or you issue a TableUpdate() command. With table-buffering, the statements are sent when the table is closed or a TableUpdate() is issued. Either can be aborted by issuing a TableRevert() command."

Perhaps I am mis-reading what is being said above, but, if not, they are saying the opposite - that if I have Send SQL Updates checked the remote updates are occuring automatically when I move off the View's changed record.

Consequently I do not really know who is correct.

For this specific application, I DO NOT want ANY automatic updates back to the original remote data table until I issue the TABLEUPDATE().

Thanks for your interest. Your continued advice would be greatly appreciated.

Thanks,
JRB-Bldr

 

[Mike Lewis]

Jrb,

All those people you quoted ... they are all pretty well correct. None of that seems to contradict what I said.

You said:
if I have Send SQL Updates checked the remote updates are occuring automatically when I move off the View's changed record.

That is true if the views are row-buffered. It is always the case with row-buffered views and tables that moving the record pointer does an implicit TABLEUPDATE().

If you cannot avoid moving the record pointer, the solution is to use table-buffering. In that case, no update will take place until you explicitly issue TABLEUPDATE().

I hope that makes sense. If not, come back and we'll go into it more deeply.

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[JRB-Bldr]

Thanks Mike for the feedback and confirmation of the understanding.

JRB-Bldr

 

[JRB-Bldr]

Well...

Here we go again....

I followed the advice above and did the following:

OPEN DATABASE myDBC
USE MyTable
CURSORSETPROP('Buffering',5,'MyTable') && Set buffering to optimistic table
SELECT MyTable
INDEX ON LEFT(area_code,3) + phone_number TAG key

BUT when it attempts to do the INDEX I get an error message.

Command cannot be issued on a table with cursors in table buffering mode

Now what??

My application needs to utilize indicies on the Views so as to support using related "tables" operations.

Your advice is welcome and appreciated.

Thanks,
JRB-Bldr

 

[Mike Lewis]

Jrb,

As you have found, you cannot create an index on a view, or table, when it is buffered. That has got nothing to do with remote views. That is true of all cursors in VFP.

You say you need indexes to support relationships. One of the reasons to use views is so that the relationships can be built into the view. So, instead of having two views - one for the parent and another for the child - you have a single view with records from both tables, and a join to connect them.

That is especially true of remote views, where the indexing is done on the server.

Will that meet your needs?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ramani]

Hi Jrb,

OPEN DATABASE myDBC
USE MyTable
INDEX ON LEFT(area_code,3) + phone_number TAG key
SET ORDER TO key
CURSORSETPROP('Buffering',5,'MyTable')

That is to say, do the indexing even before the buffering is set. If that is set already, remove it for a while, index it and then reset that back.





ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/