Wiki Home

Remote Views


Namespace: WIN_COM_API
A view that uses data sources outside of the current database, such as Microsoft SQL Server.
CREATE SQL VIEW [ViewName ] [REMOTE]
  [CONNECTION ConnectionName [SHARE]
  | CONNECTION DataSourceName]
  [AS SQLSELECTStatement]

Local Views
Remote Views use SQL code native to the server to create views.

There seems to be fairly wide agreement and concensus that SQL Pass - Through is superior to Remote Views for Client Server applications.-- Steven Black

It does not mean that anything has been proven, just that many people share that opinion. Some people disagree. Don't bother voting here.

Remote Views have some advantages, notably ease of use.

I use both, as appropriate to the situation. I find remote views very convenient and flexible. I find SPT very powerful and somewhat more complex. Each has its place. Ray Kirk

Conditions that may favor Remote Views:
  • Convenience & Ease of creation - you can use Visual tools to create them and use the visual tools to resolve data type differences.
  • Switchability between local and remote data - it's easy either by having separate DBCs with remote view and local views or by using a framework switching mechanism to switch between local and remote views.
  • Ability to take views offline.
  • Using offline views allows you to assign the task of view creation to a specific developer.
  • Support for remote views in third party utilities such as eView and DBCX.
  • It's easier to allow remote developers to work on apps using local data through views that will access remote data at deployment because they don't have to have access to the servers or have SQL Server/Oracle installed at their sites.
  • The ability to switch between local and remote views makes is possibly for commercial product developers to provide systems that use either VFP or back-end data but share the same code base.
  • Using remote views requires less knowledge of the back-end syntax.
  • Switching between back-ends. Just like you can switch between local and remote views, it's equally as easy to provide a DBC with SQL Server views and another with Oracle views.
    I have no benchmarks to share, but try running an ODBC trace on the same operation performed using both methods. You will see an astonishing number of calls generated by VFP to support the remote view. -- Zahid Ali

    Actually, there is an article that I recall in VFA that compares the speed of views with the speed of spt, and it found the time diferences inconsequential. Unfortunatly I can't find the article, and Whil's article database is down again! -- Bob Archer


    Facts about Remote Views (with thanks to Andy Kramek)
    Remote Views are a wrapper around SQL Pass - Through. This has a number of consequences for views as follows:
    • Views can only exist in a VFP Database Container.
    • Remote views have built in mechanisms to detect update conflicts.
    • Remote views have built in mechanisms to find the original values for modified data.
    • Remote views only send changed data to the server. With SPT you either have to send everything or write code to see what's changed and then write code to only send what has changed.
    • Remote views allow partial refresh of large cursors using the refresh() command

    Would it be safe to say that SQL Pass - Through is more appropriate where OLTP is the goal vs. OLAP ? I would say that it is probably appropriate to use both technologies where appropriate. For batch type processing or situations where a high volume of transactions will occur, I would choose SQL Pass - Through. To eliminate a huge number of Remote Views for reporting, I would use SQL Pass - Through. For trivial data entry and maintenance screens, I would choose Remote Views, primarily because that is what our framework is designed to handle natively and gives us the biggest bang for the buck (ie. RapidApplicationDevelopment and Code Re Use). If the Data Tier objects had all the code necessary to reduce hand coding all my transactions with SQL Pass - Through and if I could still take advantage of the DBCX2 data dictionary with SQL Pass - Through, I would probably use it in most cases for the reasons described above. But it doesn't so I'm not. I'll punt on 4th down sometimes. ;-) -- Randy Jean
    OK, now based on the findings in Client / Server Techniques Performance, maybe I'll just use SQL Pass - Through for reports and OLAP and use Remote Views for everything else. Doesn't seem to be much difference. However, I am somewhat concerned about remote views always running synchronously. What are some of the implications of this? -- Randy Jean
    We have an application that can use either Oracle or SQL server at the back end and uses a mixture of SQLPT and Remote views. What we have found is that performance can be improved significantly by setting batchupdatecount to a higher value but this causes loss of udates with SQLPT and Oracle. Has anyone else experienced this? -- Mike Boulton
    I can open a remote view on a very large table and code continues to execute. I think you may have "Fetch As Needed" turned on. In this situation, the connection is held until the entire data set is fetched, or until the view is closed. Ray Kirk
    We've just encountered a major problem with a VFP8 remote view with SQL Server 2000. Here is the scenario:
    1. We use NULL for "empty" dates in SQL.
    2. We map the datetime to date(8) in the view for dates.
    3. We use "Key and Modified" for our update wheretype.

    What has just started happening in one of our databases and in one table (as far as we know) is that we will randomly get an "Update Conflict" when a date field has changed from NULL to a value. Not all records, not all databases and is not readily reproducible. However, when it does occur, SQL profiler shows that it is using a char(23) (with a value of SPACE(23)) in the where clause instead of a NULL. However, OLDVAL() on that field in the view definitely shows NULL. Selecting on the key and "date_field IS NULL" returns the correct record. Question: what the heck is making the RV (or ODBC) send back a char(23) as the original value when it is clearly NOT a char in any way shape or form? Again, this has just started happening (to the best of our knowledge) at random in one table in one database that has been live for over 9 months. (We've also reproduced it in our local development and test database) We use this same scheme (nullable dates with key and modified wheretype) in all of our VFP/SQL apps. I suspect some type of corruption but I don't know how to fix or what is causing it. Any help or ideas greatly appreciated. We've tried re-creating the view, tickling the date fields in question through SQL Analyzer, etc. - nothing so far. Can't find anything in MSKB related to this either.

    More on this. I just re-queried a "good" record along with the "bad" record. The original value of the NULL dates in the bad record are NOT NULL but rather blank dates. The good record original value does show NULL as the orginal value. However, in SQL Analyzer, they both come back as NULL.

                 date_1    date_2          date_3
    Good record: .NULL.	.NULL.        	.NULL.
    Bad record:    /  /       /  /        	  /  /
    


    Bad record should be showing NULL instead of empty dates. Why is it not when clearly in SQL all the blank dates are NULL?

    Now, I just did the same query using SPT. Guess what? All the same date fields in the bad record show as NULL. How is the remote view screwing this record up? I'm getting ready to test this with VFP6 & 7 to see if same problem exists.

    Does the same thing in VFP6 & 7

    PROBLEM SOLVED! It turns out the last column in this remote view is also a datetime mapped to a date field. Strangely, if it contains a date value (is not null) then all the other null date fields in that row will mis-behave in the view as described above. If that last column IS null, the other date fields behave just fine. I just re-located that date column in the view to not be at the end and it all works fine now. Sure hope this helps someone else. Maybe I should report this to Microsoft? Is this a known issue? I re-produced with a new table and view. I will post the table properties and view code to re-produce when I have some time. -- Randy Jean

    Anyone else run across the above problem yet? We had another view with the same problem recently and I totally forgot I had already determined the last column issue. Actually googled and this topic came up at the top of the hit list! Just curious. Seems like a nasty bug. I can't remember if I reported it to Microsoft or not. -- Randy Jean

    This is most of 'it', but could use some help.

    well I make a "create filter form" that will return the an SQL Select Stat.. named cSearchParameters
    USE myview NODATA
    x = CURSORGETPROP("ConnectHandle")
    =SQLEXEC(x,cSearchParameters,'TempResults')
    =CURSORSETPROP("SendUpdates",.F.)
    APPEND FROM DBF('TEMPRESULTS')
    =tableupdate()
    =CURSORSETPROP("SendUpdates",.T.)

    and you use the view just to transport the data back, right?
    yep
    then why "sendupdates = .f."?
    so you dont get it to think you are adding new records to the table

    it goes by the PK
    View Parameters when SQL has a Group By clause.

    (SQL Server 2000 SP3, VFP 9)

    If the sql statement for a parameterised remote view contains a Group By clause, then the user will
    be prompted for the parameter values (or a variable with that name must exist) when the view is
    created.

    create sql view titleauthor remote connection myconn1 shared as ;
    select a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract, COUNT(*) ;
    from pubs..authors a ;
    join pubs..titleauthor t on a.au_id = t.au_id ;
    where a.au_id = ?MyAu_Id ;
    group by a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract
    


    If the sql statement for a parameterised remote view does not contain a Group By clause, then the
    user will only be prompted for the parameter values (or a variable with that name must exist)
    when the view is used.

    create sql view titleauthor remote connection myconn1 shared as ;
    select a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract;
    from pubs..authors a ;
    join pubs..titleauthor t on a.au_id = t.au_id ;
    where a.au_id = ?MyAu_Id
    

    VFP sends a number of commands to the server when creating the views, but one in particular differs
    between the two examples above.

    With Group By clause :
    exec sp_executesql N'select a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract, COUNT(*)
    from pubs..authors a  join pubs..titleauthor t on a.au_id = t.au_id  where a.au_id = @P1
    group by a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract', N'@P1 varchar(1)', ''
    

    Without Group By clause :
    SET FMTONLY ON select a.au_id, au_lname, au_fname, phone, address, city, state, zip, contract
    from pubs..authors a  join pubs..titleauthor t on a.au_id = t.au_id where 1=2 SET FMTONLY OFF
    

    Is this behaviour by design or is it a bug?
    -- Rhodri C Evans

    I'd say it is an an anomaly- no obvious reason to request a parameter at design time. Unless this is for an updateable view you might be better to use SPT in any case...

    See also More On Remote Views Remote View Of SP Client / Server Techniques Performance Sql Bench Marks
    Category VFP Commands Category Data Category Application Design Category Client / Server
  • ( Topic last updated: 2005.04.04 09:38:43 PM )