Views - why would I need one?

[florindaniel]

Hello,

I'm using all my data in an old fashioned way, I mean free tables not database.
I used to work with databases but it didn't pay off, finally (for me at least).

My question is that in the title: what is the benefit of using views? (and how do you use it? )

Thank you,
Daniel

 

[Olaf Doschke]

Views are queries with advantages. Espeically if you define them updatable.

They help establish a single view cursor combining data from several tables (also free tables by the way).
The updatability goes beyond queries with READWRITE, because you can define keyfields, updatable fields and more to save back to the original tables.

You can parameterizwe a view, which means one query, many ways to use it with filters, low/hight limits etc.

You can requery a view cursor and it doesn't break a grid, by REQUERY("alias")

With remote views this also works with remote data, eg SQL Server.

This is actually superseeded by cursoradapters, which allow all a view allows and more.

Bye, Olaf.

 

[Mike Lewis]

Hi Daniel,

Think of a view as the result of a SQL SELECT statement. It's a set of date which the SELECT generates. The data set itself is not stored permanently, but the defining SELECT is.

Let's suppose your database contains a customer table, a related invoice table, and a related invoice details table. You frequently need to work with a list of the current month's invoices, including the names and addresses of the relevant customers. Apart from those customer details, you only need to see the invoice ID, the number of invoice lines, and the total amount.

You could get the required data by running a SELECT, which would join the three tables and filter the invoices on the date (to show only the current month). The SELECT would send the data to a cursor, which you can then use more-or-less just like a table.

The problem is that, if you needed that data in more than place, you would have to code and run the SELECT multiple times. A view saves you the trouble of doing that. In effect, you are storing the SELECT itself (that is, the view definition) in the database, and having it run automatically each time you need it.

Each time you open the view, you are getting the most up-to-date version of the data. After that, you can refresh the data at any time to bring it up to date again.

The advantages are:

- You can treat the view almost exactly like a table, so you don't need to learn any new code (or not much).

- Once you've created the view, you don't need to code the SELECT again.

- You can also parameterise a view, which means that you don't need to re-create it if, say, the filter condition changes.

Views are also useful if you want to restrict access to certain items of information by specific users. To continue with the above example, suppose that some of the customer details are confidential; a user needs to generate an invoice report, but is not allowed to see those confidential fields. If you didn't have a view, the user would need full access to the customer table, and therefore would see the fields in question. With a view, you can simply omit the relevant fields, and that particular user would never see them. (This is an over-simplification; in practice, it's much harder to hind information that's stored in a Foxpro database, but a view lets you do that up to a point.)

Finally, a view makes it easier to update the base tables. If you were using your view to populate a form, the user would be able to edit the view directly, and you can arrange for those edits to work their way back to the underlying tables. That's harder to do if you were using a cursor generated by a SELECT to populate the form. (Again, this is an over-simplification.)

So, that in a nutshell is why you might want to use a view. No doubt other people here will give you other reasons for and against.

(By the way, I assume you are asking about local views, but most of what I have said here applies equally to remote views, that is, views on a back-end database like SQL Server.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Here is a very simplistic example of a paramaterized View into a Free table that is manually created through the VFP Command Window (typically used for one-time View builds)

OPEN DATABASE MyDBC  && Open Database 'container' to hold View

* --- Create View ---
CREATE SQL VIEW Changes AS;
   SELECT * ;
   FROM "F:\Data\ChngQuota.dbf" ;
   WHERE Agent_Code = [b]?cAgentCode[/b];
   AND !DELETED();
   ORDER BY Effective DESC

* --- NOTE - this simplistic View, when built this way, will initially be NOT Updateable --

That code will manually Build the View into the Open Database.
The referenced table is Free.
And it uses the parameter - cAgentCode

In order to manually modify the View you would Open the associated VFP Database and MODIFY VIEW <viewname>

Within the Modify View utility you can manually change the Update properties and a variety of other parameters.

You can also build a View through your VFP code if you need something dynamically built on-the-fly.
That code is a good deal more complex - depending on the complexity of your table(s) and paramters, but it also works.

Good Luck,
JRB-Bldr

 

[florindaniel]

Thank you all for the great information.

Daniel

 

[Mike Lewis]

Another possible use for a view is where you are accessing a FoxPro database from an external program. I'm thinking particularly of Crystal Reports, although this doesn't only apply to that product.

Placing some appropriate views in your database (that is, your DBC) is a way to hide complexity. You (the database designer) can give the end-user (the person creating the report) a pre-packaged set of data, with all the difficult details taken care of. For example, if the data involves a complex join, this can be built into the view, and the user need not be aware of it. Similarly, a view can hide any fields that are only used internally by your application. That way, you keep things simple for the user.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads