CREATE SQL VIEW, DELETE VIEW

These commands add and remove both local and remote views from a database. CREATE SQL VIEW is rather long-winded, but it must be, because CREATE VIEW is an old (useless) Xbase command.

Usage

CREATE SQL VIEW [ ViewAlias ] [ REMOTE ]
        [ CONNECTION Connection [ SHARE ]
         | CONNECTION DataSource ]
        [ AS SQLSelect ]

This command has two modes. In one, it opens the View Designer so you can specify the view visually. In the other, it lets you define a view programmatically (and invisibly). There are so many other ways to open the View Designer, and CREATE SQL VIEW is such a long command (15 characters, counting embedded blanks) that we think its importance lies in programmatic use. Behind the scenes, you can specify either a local or remote view and have it added to the OPEN DATABASE.

Omitting both the REMOTE keyword and the CONNECTION clause results in a local view. Including either gives you a remote view. You can specify CONNECTION without REMOTE and still keep it behind the scenes. If you specify REMOTE and don’t indicate a connection, you’ll be prompted to choose one.

In VFP 7, the connection information specified when you define a remote view can be overridden when you open the view; see Use for details.

SQLSelect can be any valid query. It doesn’t get an INTO clause, though, because view data is put in a cursor automatically. Don’t wrap the query in quotes. Strange as it seems to us, too, you explicitly issue the query as part of this command.

One warning on queries for remote views: Because they’ll be interpreted by the server, make sure you use only standard SQL syntax and functions understood by the server, and not VFP’s extensions or functions.

The View Designer actually does more than CREATE SQL VIEW. To specify the items on the Update Criteria tab of the View Designer, use DBSetProp().

Don’t confuse CREATE SQL VIEW with the lame Xbase CREATE VIEW, which attempts to save all current environmental settings to a VUE file.

Although CREATE VIEW FileName creates FileName.VUE, CREATE VIEW alone brings up the View Designer. So, don't fret about being confused over these two commands; it appears Microsoft is confused, too!

One of the really cool things about Visual FoxPro is the parameterized view. A parameter is simply a variable that belongs to the view. You can use it in the WHERE clause of the query that defines the view—just precede it with “?” to make it a parameter. If the variable exists when you USE (or REQUERY()) the view, its current value is substituted. If the variable doesn’t exist, the user is prompted (with a pretty decent-looking dialog) to supply a value.

Coolest of all, if you change the parameter’s value and REQUERY(), the view is re-created with the records that now match the condition. Parameterized views let you write a query once, but use it for a wide range of conditions.

Parameterized views can't be accessed from the VFP OLE DB provider, new in VFP 7. If you try to open a parameterized view through the provider, you get a "SQL column parameter name not found" error. Creating an ADO Parameter object with the proper name and value doesn't help. The reason for this problem is that the way VFP handles parameters is incompatible with the way OLE DB handles them. The only workaround is to issue the SQL SELECT command that the view uses as a query to the provider.

Example

CREATE SQL VIEW EmployeesByBirthDate ;
   AS SELECT * FROM TasTrade!Employee ;
         WHERE Birth_Date >= ?Birthdate

The example creates a parameterized view called EmployeesByBirthDate that chooses employees from TasTrade’s employee table who were born on or after a specified date. When you open the view with USE EmployeesByBirthDate, if the variable Birthdate exists, its current value is substituted into the query. If it doesn’t exist, a dialog appears, prompting for a value.

This one's really cool. When you use a parameterized view as the source for a control in a form (say, a grid or list), the parameter can be something like "ThisForm.SomeProperty." You can set up the view just as you want it on the form and not have to use a separate variable for the parameter. When you open the view outside the form, you get prompted for the parameter just as you do in any other case. This is the only place we know of in Visual FoxPro where you can use ThisForm notation in something other than method code or a property definition. Here's an example, letting you filter Employee on country.

 
CREATE SQL VIEW EmpsByCountry AS ;
   SELECT First_Name,Last_Name FROM TasTrade!Employee ;
      WHERE Country = ?ThisForm.Country

In the form that displays this view, you’d create a Country property and, perhaps, have a combo box with ThisForm.Country for a ControlSource. The combo’s InteractiveChange method could REQUERY() the view and Refresh the form.

In VFP 7, the CREATE SQL VIEW command fires the database’s BeforeCreateView and AfterCreateView events if Database Events are turned on.

Usage

DELETE VIEW [ ViewName ]

This command is just what it sounds like. It removes an existing view from a database. If Database Events are turned on, the BeforeDropView and AfterDropView events are fired.

Although Help says otherwise, you can omit ViewName and you're prompted with a list of views in the current database. Help also states that DELETE VIEW requires exclusive access to the database, but that's not true.

DELETE VIEW and DROP VIEW are essentially identical commands; DROP VIEW was added in VFP 5 because it’s the ANSI standard command.

Example

DELETE VIEW EmployeesByBirthDate

See Also

AfterCreateView, AfterDropView, BeforeCreateView, BeforeDropView, Create Connection, Create Database, Create View, CursorSetProp(), DBSetProp(), Drop View, Select-SQL, Use