Using VFP and SQL Server to build client/server application (remote view) (1)

This article is part of the "Building a client/server application with Visual FoxPro and SQL Server" series, as the usual "remote view" should not be the opening chapter, but we found that there are too many theoretical things in the articles we prepare for the site, in order to mitigate this contradiction, We decided to write "Remote View" first.

Today's most popular ADO is derived from visual FoxPro, in the actual use of both have characteristics, so do not underestimate the ability of visual FoxPro in the remote data processing, it is absolutely powerful! Readers can refer to the "Microsoft Visual FoxPro Developer ADO QuickStart" and "The visual FoxPro ramble" at this site.

We hope to get your valuable advice by completing the article "Building a Client/server application with Visual FoxPro and SQL Server" in a serialized format.

Say in front

Since this article was driven out in two days, the idea is not tight enough, I made an error in selecting the SQL Server database: Most of the "string" Fields (columns) in the Northwind database use the NVarchar type, Visual FoxPro Support for this is not good (specific reasons for this site will be discussed in another article). This may cause the demo program to not work properly, so we can change the type of the field from NVarchar to Varchar in SQL Server Enterprise Manager. I suggest changing the type of CustomerID and phone fields in the Customers table. Again, we can change the Employees Notes field type to text.

It's not my fault

The Visual FoxPro View Designer can perform many annoying remote view operations, but may be poorly supported for particularly complex remote views. Please do not blame the author when you are experiencing the above problems when reading this experiment. The remote view of Visual FoxPro is very powerful, and large to the tool "View Designer" is not very well supported!

Objective

Visual FoxPro provides two built-in solutions for the application of the Client/server architecture: remote views and SPT (SQL pass through). Of course in Visual FoxPro we can use other methods of remote data connection, such as ADO. All of these solutions have their pros and cons, and the best thing about remote view is that it's easy to bind to the Visual FoxPro built-in controls. Each remote view is an object stored in the Visual FoxPro database Container (DBC), which is a sql-select statement. Remote views communicate with heterogeneous databases through ODBC (Open database Connectivity).

Although the example in this article uses SQL Server as the back-end database, you can also use other data sources as back-end databases, such as ORACLE,IBM Db2,informix,sybase,microsoft Access,excel, Even the Visual FoxPro itself. Using remote views to manipulate remote data is like manipulating Visual FoxPro local data, and here we will introduce the primary application of this great tool.

Connect-connections

The first step in using remote views to manipulate remote data is to establish communication with remote data sources, and there are several options available, and note that all of these methods use ODBC to connect to remote data.

Here's a very simple remote view that reads records from the Customers table in the Northwind database to the remote view vcustomers.

CREATE SQL VIEW vcustomers;
REMOTE CONNECTION Northwind;
As SELECT * from Customers
* Do not try this statement first, because the connection has not been established

In the second line of the above command we told Visual FoxPro to use connection Northwind to communicate with SQL Server. When you run the above statement, Visual FoxPro will look for this connection in two places:

1. The current database container (DBC) to see if the database "Connection object"--northwind exists. The author calls it a DSN based connection object.

2. If not found, Visual FoxPro will see the presence of a connection Northwind in the client's OCBC Data Source Names (DSNs). The author calls it a DSN connection.

Establish a DSNs connection

The quickest and easiest way to establish a connection is to establish DSNs, which you can open in Control Panel, as shown in Figure 1.

Figure 1. ODBC Data Source Administrator Panel

We found that there are three types of DSN connections:

User-type DSN, System DSN, file-type DSN. The user type is only valid for the user who created it, for example, if you log on to the system as an administrator and create a user DSN, you cannot use this DSN unless you log on to the system with the administrator, and the system type takes effect for all users of the current machine. No matter what identity you log on to the system; A file DSN is actually a text file with the suffix name of DSN. From the Visual FoxPro point of view, the author divides the three kinds of DSN into two categories:

1. User-type, system-type DSN. It can be used separately as a connection for remote views, or as a basis for DSN-connected objects, depending on the user-or system-based DSN.

2. FILE-type DSN. It may not be used as a connection for remote views alone; It can provide information for designing a string-based connection object, but a "string based connection object" does not depend on a file-type DSN.

Let's build a system DSN first.

In the ODBC Data Source Administrator Panel, select the System DSN page, press the "ADD" key, and then choose the SQL Server driver and press the "Finish" button. Appears in Figure 2.

Figure 2. Set the name of the DSN and the server

Here we select "(local)", note if the system will attempt to connect to the target server if the failure to connect will be an error, if it goes well you will see the picture in Figure 3, ask to select the user authentication method, here we choose SQL Server and Windows hybrid authentication mode, and enter login SQL Server's username: "sa", the password is blank.

Figure 3. Set Logon information for DSN

If the smooth screen 4 will appear on the screen, here is one option to pay special attention to is to select the target database. Of course we choose "Northwind" here.

Figure 4. Setting up a DSN login database

Needless to say, if something goes well, the system DSN called "Northwind" is built.

Create the following statement in the Visual FoxPro Command window:

CREATE DATABASE Northwind
* Create a local database container to store database objects.
CREATE SQL VIEW vcustomers;
REMOTE CONNECTION Northwind;
As SELECT * from Customers
* Establish a remote view, note that this view is an object of the database (DBC), so you must first establish the DBC
Use Vcustomers
* Open Remote View
BROWSE
* Browse Remote View

To establish a connection object in the Database container (DBC)

Remember in the previous article we talked about the connection can exist in two places, that is DSNs and DBC, so what is the difference between DSN and DSN-based connection objects?

A DSN-based connection object can be shared for several remote views, and a DSN connection is not available. We know that every connection to SQL Server has to be charged, and shared connections can save money; we also know that SQL Server manages about 24K of memory space per connection, someone says: 24K is not much, but don't forget that SQL Server is a server, It's not just you who use it--a connection to the east, a connection to the West--system efficiency is bound to be affected. Therefore, whether from the development cost or the system efficiency angle, the shared connection is the inevitable choice of the developer.

To demonstrate what a shared connection is, in order to prove that the DSN connection cannot be attempted to share, we do the following experiments:

CREATE SQL VIEW vorders;
REMOTE CONNECTION Northwind SHARE;
As SELECT * from Orders
* Create a new remote view and still use a DSN connection with the name Northwind
Dbsetprop (' vcustomers ', ' View ', ' shareconnection ',. T.)
Use Vcustomers in 0
* Open the view that you just made
Use Vorders in 0
* Open a new remote view
? Cursorgetprop ("Connecthandle", "Vcustomers")
* Show 1
? Cursorgetprop ("Connecthandle", "Vorders")
* Show 2
* The connection handle for these two remote views (cursors) is not the same. If a connection is shared, the connection handle of the two cursors should be the same number, although the two views use the same DSN but they do not actually share the connection.

To connect a feature with a share, we create a "DSN-based Connection object":

CREATE CONNECTION Northwind DATASOURCE Northwind

The above connection is based on the DSN connection we established above, and is connected to an object in the Database container. Note: This connection object is not free from DSN control, and its connection information is still stored in DSN.

In order to share a connection between views, we must set the properties of the shared connection for the diagram, and the shared connection must have properties for the remote view that participates in the shared connection, otherwise the sharing connection will not be implemented. There are two ways to set up a shared connection:

1. Create a view design using keyword SHARE. Such as:
   CREATE SQL VIEW vorders;
   REMOTE CONNECTION Northwind SHARE;
   As SELECT * from Orders

2. Set the Shareconnection property of the view to. T. Such as:
   Dbsetprop (' vcustomers ', ' View ', ' shareconnection ',. T.)

To demonstrate the characteristics of connection Sharing, we execute the following code:

Use Vcustomers in 0
Use Vorders in 0
? Cursorgetprop ("Connecthandle", "Vcustomers")
* Display: 1
? Cursorgetprop ("Connecthandle", "Vorders")
* Display: 1

We've learned how to build "DSN-based Connection objects" and learn about Connection sharing. If you think about it, you may find the following issues with DSN-based connection objects:

1. The controllability of runtime (run time) is poor. For example, do we want the application to use different users to log on to the database server, or different databases, to create n DSN and n "DSN based connections" under different conditions? It's terrible!

2. Because DSN-based connections are highly dependent on DSN, such as when a user deletes or changes DSN information, our application does not function properly!

To solve the above problem, Visual FoxPro the concept of a connection string. The author calls it: a string based connection. A connection string basically consists of five main elements: ODBC drives, servers, user names, logins, databases. (For SQL Server)

CREATE CONNECTION Northwind2;
ConnString "DRIVER=SQL; Uid=sa;database=northwind; server= (local); Pwd= "