Using VFP and SQL Server to build client/server application (remote View) (2)

Establishing a connection through a tool

We've all established the connection by command, and now we will use the connection designer provided by Visual FoxPro to establish the connection. As we mentioned above, there are two types of connection objects: "DSN based Connection object" and "String based Connection object". Figure 5:

1. Selecting a data source, user ID, password in the connection designer indicates that you are setting up a DSN-based connection object, and you can select the currently available user-type, system-type DSN in the Data Source list box.

2. Selecting "Connection Strings" in the connection designer indicates that in establishing a "string based Connection object", to quickly and correctly design the connection string, we can open a File DSN reference, noting that a "string based connection object" does not rely on a file-type DSN.

Figure 5. Connection Designer

Setting of connection Properties

We can see a lot of connection properties from the Connection Designer's screen, what do they mean? Before I talk about these questions, I want to tell you that all of these properties can be set through the Dbsetprop () function, and all of these property values can be obtained through the Dbgetprop () function. Below, we briefly describe several properties:

1. Asynchronous execution. The default value is. F.. Synchronous execution means that the command that is passed through a connection to the back end is executed in one sentence, the preceding sentence is not executed after execution; synchronous execution means that when a command is issued over a connection, the client program can continue to go down, regardless of whether the backend completes and returns the result.

2. Timeout Interval-Idle (minutes). The default value is 0. When you set a value other than 0, if a connection is not in use during that time, visual FoxPro makes it hibernate, and visual FoxPro activates it when you use the connection again. Note that this property is used because it often causes your application to produce shady errors, such as "connectivity error:unable to retrieve specific error information." Driver is probably out of. In fact, ODBC now provides the function of POOL, these problems can be solved by ODBC itself, we do not worry.

3. Timeout Interval-Connection (sec). The default value is 0. When you set a value other than 0, Visual FoxPro will attempt to connect to the server during this time period, and if not, Visual FoxPro will produce an error.

4. Displays the ODBC logon prompt. In the application, we always do not want to have a system pop-up login window, so that the image is unfavorable to the security of the program, especially the design of COM objects, due to unattended, but also do not want to login window pop-up, Visual FoxPro for us to provide three kinds of choices, the author often use "Never show."

5. Displays an error message. If set to. T, any ODBC errors will be displayed in the Messagebox window, whereas ODBC errors are handled by the user. Obviously we basically use the default settings. F..

Tip: Many of the settings for data processing in Visual FoxPro, such as set Delete, are limited to data working periods, and connections are enabled to be public during multiple data working periods.

Remote View

Weak water 3,000, take one scoop drink--condition view

In the previous article we have built two remote views, you may find that the whole of the remote table read over the implementation of the effect is very good, but it is not. I have two main reasons:

1. Imagine if the back-end table was large (Customers only 91 records), there were tens of thousands of or hundreds of thousands of records, and the result would be this?

2. Imagine if there are a lot of internet users and the Internet is busy, what will happen?

An important task of client\server design is to minimize network traffic, so consider this problem when designing a remote view. Our idea is to make the remote view download only the necessary information, this is called: weak water 3,000, take a scoop drink. For example:

CREATE SQL VIEW vcustomers
REMOTE CONNECTION Northwind SHARE;
As SELECT * from Customers
WHERE customerid Like ' ALFKI '

We add the condition "CustomerID Like ' ALFKI" to the WHERE clause and now we find that only one record is downloaded from the remote. You also have found this in the middle of the shortage: This remote view is too rigid-only for Customerid similar to ' ALFKI ' records to provide services, is not the ' ALFKI ' as a parameter for making this remote view more flexible, more extensive service surface.

Can, Visual FoxPro supports such remote views:

CREATE SQL VIEW vcustomers;
REMOTE CONNECTION Northwind SHARE;
As SELECT * from Customers;
WHERE CustomerID Like Ccustomerid

If you open a remote view or perform the Requery (), REFRESH () function on an open remote view, Visual FoxPro looks for a variable that is named Ccustomerid. If present, visual FoxPro automatically fills the variable value into the remote view's SQL statement, and if the variable does not exist, visual FoxPro will pop up the following window to inquire about the value of the variable.

In the actual development application, we will often implement set remote view parameters, in the need for other information, change the parameter value, and then use the Requery () function to refresh the client's data; The remote view parameter can not only fill the normal data, but also use the wildcard character, as follows:

Ccustomerid= ' ALFKI '
Use Vcustomers
BROWSE
Ccustomerid= '%b% '
REQUERY (' vcustomers ')
BROWSE

Here are the following questions to note:

The

1. Remote View parameter can only be set in the WHERE clause, and other join clauses, group words, and sort clauses do not support this feature. For example, you may not be able to appear order by. Corder. (Rumor has it that Visual FoxPro 7 supports parameters to add a sort clause, but I haven't tried it yet).

2. A remote view can set multiple parameters. See the following code for an instance. The Select-sql statement for the

3. Remote View must be a standard for the syntax of a later-end database. For example, SQL Server confirms strings in single quotes, and Visual FoxPro can use double quotes, brackets, and single quotes to confirm strings, and the following statement is an error:
   CREATE SQL VIEW vcustomers REMOTE CONNECTION Northwind SHARE;
   as SELECT * from Customers WHERE customerid like "ALFKI"
   Also, for example, to remove the null character field from the Visual FoxPro You can use the alltr () function, but you cannot use any of the Visual FoxPro functions in the remote view's select-sql, so to do this, you should use SQL Server functions as follows:
   CREATE SQL View Vcustomers REMOTE CONNECTION Northwind SHARE;
   as SELECT customerid,ltrim (RTRIM (CompanyName)) from Customers WHERE to CustomerID like ' In the 3rd ALFKI '

4. , we emphasized that the remote view build time must use a backend database to recognize Select-sql. When a remote view is opened and becomes the Visual FoxPro cursor, you can use the Visual FoxPro commands and functions on it.

CREATE SQL VIEW vemployees;
REMOTE CONNECTION Northwind SHARE
As SELECT employeeid,birthdate from Employees
WHERE birthdate >?dbirthdate1 and birthdate <?dbirthdate2
dbirthdate1= ' 19540101 '
Dbirthdate2= ' 19601231 '
Use Vemployees
BROWSE
* This example uses two parameters for date-Type field birthdate, and note the passing of date-type parameters.
CREATE SQL VIEW VEmployees1;
REMOTE CONNECTION Northwind SHARE;
As SELECT EmployeeID, birthdate, Country from Employees;
WHERE birthdate > dbirthdate and Country =? ccountry
Dbirthdate= ' 19601231 '
Ccountry= ' UK '
Use VEmployees1
BROWSE
* This example sets parameters for two fields

Download only useful fields

We used the SELECT * To download all the columns from the distal end of the brain to the client, which is not good. The reasons are as follows:

1. Not all columns (fields) are used for remote data operations, especially Memo fields, sophomore fields.

2. In the view phase, you can get more useful information by calculating the relevant columns, as shown in the following code.

3. Specifying column information explicitly helps Visual FoxPro version control of remote data tables. If you use SELECT *, Visual FoxPro only knows when a remote datasheet adds a field, and other such as reducing, changing the structure of the column will not be known to Visual FoxPro.

For example: There are firstname,lastname columns in the remote table, we need the full name when we make the report, so we can add them to a new column.

CREATE SQL VIEW VEmployees1;
REMOTE CONNECTION Northwind SHARE;
As SELECT EmployeeID, FirstName + ' + LastName as Name, Title;
From Employees

Remote view formed by multiple table joins

Remote view not only supports the operation of a remote table, it also supports multiple table joins as follows:

CREATE SQL VIEW vemployeeterritories;
REMOTE CONNECTION Northwind SHARE;
As SELECT Territories.territoryid, Territories.territorydescription, Employees.EmployeeID,;
Employees.lastname, Employees.firstname;
from territories INNER JOIN;
Employeeterritories on;
Territories.territoryid = Employeeterritories.territoryid INNER JOIN;
Employees on employeeterritories.employeeid = Employees.EmployeeID

The remote view also supports a self connection, as follows:

CREATE SQL VIEW Vemployeereportto;
REMOTE CONNECTION Northwind SHARE;
As SELECT Employees.EmployeeID as leader,;
Employees.firstname+ ' + Employees.lastname as leader,;
Employees_a.firstname + ' + employees_a.lastname as subordinate,;
Employees_a.reportsto as superior leader of the work number;
From Employees Employees INNER JOIN;
Employees employees_a on;
Employees.EmployeeID = Employees_a.reportsto;
ORDER BY Employees_a.reportsto

You can see from the above statement that SQL in Visual FoxPro is very similar to SQL SQL Server, which is convenient for learning, but don't fall into the "gentle trap"!