Stored Procedures in Visual FoxPro


Procedural code called stored procedures are stored within a database container. Such procedures are loaded into memory when the database opens, improving performance. Because stored procedures are stored in the database, they assist in making your databases more portable. When the database is moved, the stored procedure goes with it. Use stored procedures to create user defined functions that you can call from other stored procedures, and from within validation rules at the field and record level.

You can create a stored procedure in three ways. The first way is click on Edit Stored procedures on the Database from within the Database Designer. The second way is to open the Project Manager and expand the nodes Data, Databases, Database, Stored Procedures, and then click on New. And the third way is to use the MODIFY PROCEDURE command.

Use the COPY PROCEDURES and APPEND PROCEDURES commands to copy and append stored procedures to a text file. For more information see PROCEDURE command.

Advertisements

The Visual FoxPro Database Container


The file extension for a database container is .DBC. Database containers are used to manage tables, relationships, views, and stored procedures. The properties, rules, triggers, relations, views, connections, and stored procedures are described within such a file. You can view the database schema via the database designer. Connections, tables, relations, triggers, rules, views, and stored procedures can be added, modified, and deleted from within the database designer.

File Extensions Associated with Databases

Extension Description
.DBC Database container file
.DBT Memo file
.DBX Index file

Commands and Functions Associated with Databases

Command Functions
.DBC Database container file
CREATE DATABASE Create a new database container
OPEN DATABASE Open an existing datbase container
SET DATABASE TO Set the current database to one that is already opened
MODIFY DATABASE Opens the database designer for an open database
MODIFY PROCEDURE In the current database, create or modify a stored procedure
PACK DATABASE Remove deleted records from the database.
VALIDATE DATABASE Check the validity of a database or recover an invalid database.
DBC() Returns the full path of the current database.
DBUSED() Is the database specified open?
ADATABASES() Get an array of all open databases
ADBOBJECTS() Get an array of objects in the current database
DBGETPROP Get a database property
DBSETPROP Set a database property
CLOSE DATABASE Closes the current database, all its tables, and all its open free tables.
DELETE DATABASE Delete a database from disk, and optionally delete or free its tables.

Visual FoxPro Objects


Overview

Visual FoxPro is a relational database management system developed by Microsoft. The current version is version 9.0, and Microsoft has clearly stated that this is to be the final release. Official support for Visual FoxPro 9.0 will cease from 2015. If you want to continue using and extending FoxPro beyond this point you will want to download VFPX (SEDNA) from codeplex.

Visual FoxPro supports the following file types: Project, Database, Table, Query, Connection, View, Remote View, Form, Report, Label, Program, Class, Text File, and Menu. Let’s take a look at each one of these file types in turn.

Project

A project file consists to two files. One file is the project file with the .pjx extenstion, and the other is the associated memo file with the .pjt extension. There are two ways to view the information that is contained within a project. The easiest way is to use the project manager, and the second is to use the command window. To use the command window to view the tables contents of the database file, type the following commands:

USE .PJX EXCLUSIVE NOUPDATE
BROWSE LAST

A project file is used to store a list of objects used by the project. The complete list of objects that can be stored in a project file consists of the header, screen tables, screen programs, programs, menus, queries, report forms, labels, libraries, formats, tables, indexes, files, and applications.

Project files can be used to organise files in a manner that makes them easy to work with. Using project files enables you to compile all the project files into a standalone application that can be deployed without the need to create an installation file.

Database

A database file is a container for database tables and their relationships. Within a database you can create a database schema to define tables and relationships between those tables.

Table

A table is a file that is used to hold data. You can create a table by using the table wizard or the table designer.

Data Type Default Width Default Decimal Places Change Defaults? Used To Store
Character 8 N/A Yes From 1 to 254 characters
Currency 8 N/A No Monetary amounts
Numeric 8 0 Yes Numbers used in calculations
Float 8 0 Yes Numbers used in calculations
Date 8 N/A No Dates
DateTime 14 N/A No Dates and times
Double 8 0 Yes Numeric values where extremely accurate calculation is needed
Double 8 0 Yes Numeric values where extremely accurate calculation is needed
Logical 1 N/A No .T.(true) or .F. (false)
Memo 10 N/A No Text of any length
General 10 N/A No OLE objects
Character (binary) 8 N/A Yes Text or binary up to 254 characters
Memo (binary) 10 N/A No Text or binary data of any length

Query

A query is a file that allows you to extract information from one or tables.

Connection

You can only create a connection when using a Visual FoxPro database. A connection file allows you to save database connection information. The database can be a local or remote database.

View

You can only create a view when using a Visual FoxPro database. A view file lets you create updatable queries on local tables. You cannot define triggers for a view.

Remote View

You can only create a remote view when using a Visual FoxPro database. A remote view file lets you create updatable queries on remote queries.

Form

A form is a data-entry screen that makes it easy to view, add, edit, and delete information in a database.

Report

A report file contains the design for a printed report.

Label

A label file contains the specification for mailing labels.

Program

An executable program that performs operations on FoxPro databases and tables

Class

A class file is an object-oriented programming file used to store class information. A class is a template or blueprint used to create objects. An object is an instance of a class.

Text File

A text file is a plain text file.

Menu

A menu file contains the menu specification for the menu system within a custom application.

Application

An application is a compiled Visual FoxPro application. An application with the .app extension can be run in a Visual FoxPro environment, a .exe file can run independently of the Visual FoxPro environment, and a .dll file can be called from anywhere in a Microsoft Windows environment.

Lightswitch Beta


I have just downloaded and had an initial play with the Lightswitch beta from MSDN, and I can say from my initial experience that I’m impressed. The beta allows you to create either a VB.NET or C# project. You can create new data sources, queries, and forms. The wizards allow you to create details, master-details, list, and report forms. When you run the application, a main form is created that has a menu bar that lists each form. Forms are displayed in tab format, and there is a ribbon menu. You can export data created in the form into Excel. All this and not a line of code in sight. I think this warrants further investigation as the technology looks very promising in its capabilities.

If you are not a programmer and want to create a business application, then this is one technology that you may well want to learn. Should you wish to learn more then you can download the software and have a play around yourself by visiting the Visual Studio LightSwitch Developer Center. If you are a developer, then this technology looks like it will save you a lot of time

Learn By Example


People say that a good way to learn is by example. That being the case, here are some examples to assist you in your learning:

Speed Up Development with the Built-In Wizards


Visual FoxPro 9.0 has a number of built in wizards to speed up the development process. These wizards are the application wizard, cross-tab wizard, database wizard, documenting wizard, form wizard, graph wizard, import wizard, label wizard, local view wizard, mail merge wizard, one-to-many form wizard, one-to-many report wizard, pivottable wizard, remote view wizard, report wizard, SQL Server upsizing wizard, table wizard, and web publishing wizard.

You can find help on using these wizards at MSDN.

Visual FoxPro 9.0 – An Introduction


Visual FoxPro 9.0 is a Relational Database Management System (RDBMS) and object-oreiented (Integrated Development Environment) IDE developed by Microsoft. Version 9.0 is the final release of Visual FoxPro. Microsoft has clearly stated that there are to be no more versions of Visual FoxPro released in the future, and commercial support for Visual FoxPro will cease in 2015.

Although there are to be no further releases of Visual FoxPro, Microsoft has released an open source project on codeplex called VFPX. You are free to use VFPX to extend Visual FoxPro in anyway that suites your needs. You can access VFPX here.

The main components of Visual FoxPro consist of the IDE, project manager, applications, databases, tables, queries, forms, and reports. There are two ways to create a variety of items with FoxPro, these are manually and via the use of wizards. As I am in the process of learning Visual FoxPro 9.0 in preparation for Opera III Developer certification, I thought it would be a good idea to post my training notes on the web in case you find them useful. I will be posting a number of articles on the various aspects of Visual FoxPro in the coming weeks.