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

Some digression

A good recent phenomenon-more and more Visual FoxPro users are starting to notice the importance of client/server applications, and they are starting to install SQL Server. As a developer, there's no need to get a deep understanding of SQL Server (I personally think that's what the database administrator-dba), and we're going to work out how to make Visual FoxPro run smoothly with SQL Server and how to play both of these advantages.

The "Remote View" chapter has covered a number of deep-seated issues, but unfortunately (perhaps the article is too boring) few people pay attention to it! The author still thinks, "remote View" is absolutely the best available on the Internet in Simplified Chinese learning materials, no article will give you in-depth introduction of "Update conflict principle", no article will explain to you "Visual FoxPro send update to SQL Server principle", No article will explain "the meaning of the various connection attributes" ...

Among the many Visual FoxPro books in the country, my favorite author is Mr. Zhang Limin of Taiwan. I personally think that Visual FoxPro's Zhang Limin, Delphi, and Visual Basic Wangguorong are the kind of authors that most readers should buy when they see the work they write. I have read three of people's books, thinking: The best is Mr. Zhang Limin, the least famous is Mr. Zhang Limin (perhaps because of Visual FoxPro relationship). China Railway Publishing House has done a good deed for the cause of the domestic visual FoxPro--published four books of Mr. Zhang Limin before and after 99: The visual FoxPro 6.X Chinese version of the program--Teaching guide, visual FoxPro 6.X Chinese version of Program design--Foundation strengthening, visual FoxPro 6.X Chinese version Program design--Problem set, visual FoxPro 6.X Chinese version Program design-application practice. In addition to the teaching guide, the other three are advanced application of good books, but it is strange that they sell in the market is not ideal, until the first half of this year in the Shanghai book market can also find, the contrary some shoddy things are selling hot, really paradox! I do not know Mr. Zhang Limin, and China Railway Publishing house is not related, but I am willing to advertise for them, if Zhang Limin for visual FoxPro 7 write better things, we will be grateful, but also hope that the domestic publishing house actively participate in the introduction! This article in the written reference to the "Visual Foxpro 6.X Chinese version of the program design-application Practice" content, but is not plagiarism, we are interested in buying a book to read.

Why to introduce the concept of SPT

We've covered the remote view, and you may have found a shortage of remote views, and the drawbacks of the remote view are the advantages of SPT:

1. Execute SQL statements other than SELECT, such as INSERT and update

2. Performing stored procedures for the backend database

3. Perform special functions, commands for the backend database

4. Get more than one data collection at a time

These are the implementation issues that we are going to encounter when we develop the system, and the Visual FoxPro team takes note, and then puts forward the concept of SPT. The SPT is the shorthand for SQL pass through, which together with the remote view makes up the Visual FoxPro Remote Data Access system, which allows you to develop a complete client\server architecture. The SPT technology also has the following drawbacks compared to remote view:

1. No graphical user interface

2. Connection must be maintained manually

3. The data collection is a "read-write cursor" and must be set to make it an "updatable cursor"

From my experience to see "Remote View" and SPT, I think both are Visual FoxPro Remote data Access system of arms, complement each other. Without SPT, the system becomes retarded and stupid; without remote views, system development can become slow and cumbersome. I thought, "Remote View" is the component SPT, it is the image encapsulation of the SPT part of the company, so it appears to be more productive, and because the system is much more functional, the remote view can do that--read a result set from the data source, maintain it on the client, and automatically updates the results of the changes to the data source. However, as a system development, we will encounter a wide range of practical, strange needs, only "remote View" Independent play is obviously not, so the emergence of SPT has its inevitability. Similar concepts are also available in other remote data processing components, such as the Recordset in ADO equivalent to a remote view of Visual FoxPro, which is the equivalent of the Visual FoxPro SPT.

13 SPT functions

The Visual FoxPro has 13 built-in functions that start with SQL, which we call the SPT function. This is the 13 functions that complete all the SQL pass though features of Visual FoxPro. Functionally, we can divide them into five parts:

Connection functions

Connection Establishment function: SqlConnect (), Sqlstringconnect ()

Disconnected function for connection: SQLDisconnect ()

The core execution function

SQL statement transfer and execution functions: SqlExec (), Sqlprapare ()

Acquisition function for more datasets in batch mode: SQLMoreResults ()

function to undo an executing SPT in asynchronous mode: SqlCancel ()

Transaction processing functions

Sqlcommit (), Sqlrollback ()

Connection Channel Property function

Sqlgetprop (), Sqlsetprop ()

Information interception function for data source

SQLTables (), SQLColumns ()

Connecting to SQL Server

Connection

Two ways to use SqlConnect ()

Generally speaking, there are two uses for SQLConnect: direct invocation of user-or system-type DNS in the operating system, and connection objects using the current database DBC.

If you have made a system-type DNS named LocalServer in the operating system, in the command window we can simply type:

SQLCONNECT ("LocalServer", "sa", "")

If there is a connection object in the current database, we can call it this way:

SQLCONNECT ("NorthWind")

Use of the Sqlstringconnect () function

After using the SQLConnect, we may find that it is not too free, can we establish the connection in time? Can be, with Sqlstringconnect () on it:

Sqlstringconnect ("Driver=sql Server; Server=see-you; uid=sa;pwd=;d Atabase=northwind ")

Let's explain the meaning of the parameter string, driver indicates which ODBC driver to use, this is SQL Server;server is the server name of SQL Server, my server is called: See-you;uid is the user name in SQL Server, The use of sa;pwd here is the user password, which is null; finally we specify the name of the database to connect to, and this time we still use the Northwind database. All of these conditions consist of a string, separated by semicolons, and my experiment shows that this is not a very high case, and of course does not include user passwords and user names.

Two big questions about joins

The connection is simple, but there are still problems to be solved: how do you determine if the connection was successful? How to screen SQL Server login dialog box?

The first is a good solution, as long as SQLConnect () or Sqlstringconnect () returns a positive integer (the number of >0) means the connection succeeds, and the resulting positive integer is important--that is, the connection handle!

STORE SQLCONNECT (' LocalServer ', ' sa ', ' bad password ') to gnConnHandle
IF gnconnhandle <= 0
= MESSAGEBOX (' connection error ', 16, ' Connect to SQL Server ')
ELSE
= MESSAGEBOX (' connection succeeded ', 48, ' Connect to SQL Server ')
ENDIF

In some cases, a connection error pops up the following two boxes (sometimes the previous one, sometimes the latter, sometimes continuously):

Connection error Prompt

SQL Server login Prompt

This is a confusing question, I think: the first "connection error hint" is limited to the Visual FoxPro or ODBC level, that is, it is not related to SQL Server, this error often occurs because the SQL Server server and other problems, such as the following statement:

Sqlstringconnect ("Driver=sql Server; Server=see-y1ou; uid=sa;pwd=;d Atabase=northwind ")

This is to allow the user to correct the connection information, but no programmer in actual development wants this dialog to be exposed to the user. If the connection we are using is a DBC connection, the problem is better solved, because we can set the rules of the connection dialog when we make the connection:

Similarly, we can control the "show ODBC login Prompt" by command:

Dbsetprop ("Connect1", "CONNECTION", "Displogin", 1) && pops up when a connection error occurs
Dbsetprop ("Connect1", "CONNECTION", "Displogin", 2) && always when the error pops up
Dbsetprop ("Connect1", "CONNECTION", "Displogin", 3) && hair never pops up

But when we use the connection string, directly using the operating system's DNS, they are not in the Visual FoxPro of the DBC of the connection object, we can not control the above method, we could set the Visual FoxPro environment:

Sqlsetprop (0, "Displogin", 3)

Note that I assign a value of 0 to the first parameter, which indicates that the Visual FoxPro system environment is set. It is noteworthy that the system environment setting is only valid for this instant-generated connection and does not work for the connection objects stored in the DBC, depending on the connection properties established by the connection object or through the Dbsetprop ().

Disconnect

The established connection should be disconnected in time and disconnected using the SQLDisconnect () function.

Local Hconn
Hconn=sqlconnect ("NorthWind")
? SQLDisconnect (Hconn)
Return 1 indicates a successful connection fracture

Here are three questions to draw attention to. First, if Hconn is a valid connection handle, performing the Sqldisconn () function on it will result in a successful return of 1, while the other indicates that the fracture operation failed; second: If Hconn is not a valid connection handle, Visual FoxPro will trigger an error. Error code 1466, how to determine whether a connection handle is not a valid method, we are introduced in the following article; third, if you want to disconnect all connections at once, you can use this method:

SQLDisconnect (0)

Data set from back end

Get a data set

SqlExec (Nconnectionhandle, [Csqlcommand, [Ccursorname]])

Let's explain the SqlExec () function: The parameter nconnectionhandle represents the connection handle; The argument Csqlcommand represents the statement to be delivered, and note that this statement must be a statement that the data source recognizes, not a Visual FoxPro statement. This can be seen from the name of SQL Pass though, as its name suggests: The Visual FoxPro is simply to send someone else's language to others, which is particularly prominent in the SqlExec () function; parameter ccursorname represents the name of the resulting result set , if omitted, the returned result set is named Sqlresult.

If it returns 1, the execution succeeds, if 0 indicates that it is executing, or if a negative number indicates execution failure.

If we want data from the Employees table in the Northwind database, we can execute the following command:

SqlExec (Hconn, "select * from EMPLOYEES", "MyCursor")
Browse

Determine connection validity

This is a very practical question, after we set up a connection, how to know that the Visual FoxPro communication with SQL Server is normal, the machine connection is really effective, there is only one way-to apply this connection, you can do this:

? SqlExec (Hconn, "")
&& an empty command to send, return 1 words proof connection available!

The transfer skill of large statement

If you want a more complex set of results, we can send a description based on the large sql-select. Here are some experiences to give to you:

1. If the delivered statement is too large (about 256 characters or more), in fact, there is no need to count as long as you think the statement is very large should consider my proposal, otherwise you have no way to transfer large statements, you directly to transfer the statement into SqlExec (), Visual FoxPro will be the error, Said unable to recognize this statement, so we have more defamation of Visual FoxPro a handle! (According to my research, there is also this problem in Delphi) the solution is to put the long statement first in a variable, and then assign this variable as a parameter to the SqlExec () function.

2. For tips on using delimiters, the delimited string in Visual FoxPro uses three symbols: double quotes, single quotes, square brackets. Be sure to use double quotes as the delimiter for the string when you configure the statement to be routed, and the other two symbols should be used for other purposes.

The above recommendations are absolutely classic, we use the following examples to illustrate:

The Sql-select statement to get detailed sales in SQL Server is as follows (see the detailed knowledge I wrote "Select-sql JoJo"):

The

SELECT ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. Shipregion,orders. Shippostalcode,
Orders.shipcountry,orders.customerid,customers.companyname as Customernam,
EMPLOYEES. Firstname+ ' +employees. LASTNAME as salesperson,
ORDERS. Orderid,orders. Orderdate,orders. RequiredDate,
Orders.shippeddate,shippers.companyname as Shippername,
[order DETAILS]. Productid,products. Productname,[order DETAILS]. Unitprice,[order DETAILS]. Quantity,[order DETAILS]. DISCOUNT,
[order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, ORDERS. FREIGHT
from ((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID)
INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID)
INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID)
INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID)
INNER JOIN products in [order DETAILS]. Productid=products. PRODUCTID

It's a 12-line, large statement that does not count for a space of 864 characters and is now routed through the Visual FoxPro SqlExec () function:

Local CSQ
&& Configure the statements to be delivered
csql=csql+ "Select ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. SHIPREGION, "
csql=csql+ "ORDERS. Shippostalcode,orders. Shipcountry,orders. CUSTOMERID, "
csql=csql+ "Customers.companyname as Customernam,"
csql=csql+ "EMPLOYEES. Firstname+ ' +employees. LASTNAME as Salesperson,orders. Orderid,orders. ORDERDATE, "
csql=csql+ "Orders.requireddate,orders.shippeddate,shippers.companyname as Shippername,"
Csql=csql+ "[Order DETAILS]. Productid,products. Productname,[order DETAILS]. UnitPrice, "
Csql=csql+ "[Order DETAILS]. Quantity,[order DETAILS]. DISCOUNT, "
Csql=csql+ "[Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, "
csql=csql+ "ORDERS. FREIGHT "
Csql=csql+ "From" (((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID) "
csql=csql+ "INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID) "
csql=csql+ "INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID) "
csql=csql+ "INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID) "
csql=csql+ "INNER JOIN" [Order DETAILS]. Productid=products. PRODUCTID "
&& transfer, the resulting cursor name is the default Sqlresult
SqlExec (Hconn,csql)

Explanation: Because the statement is very long, I spent more than one assignment statement to complete the configuration of this string. It's been noted that I use double quotes as a string separator, which is a learned one. A table name that waits for space in SQL Server, such as order Details, must be marked with square brackets when it is used for SQL statements, so if you split the string with square brackets in Visual FoxPro, there would be a conflict! ; single quotes are symbols that delimit strings, date values in SQL Server, and if you use single quotes to separate strings in Visual FoxPro, there is also a conflict, of course, in the above statement we do not see single quotes, but we make a change, we will feel the existence of the problem!

Local CSQ
&& Configure the statements to be delivered
csql=csql+ "Select ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. SHIPREGION, "
csql=csql+ "ORDERS. Shippostalcode,orders. Shipcountry,orders. CUSTOMERID, "
csql=csql+ "Customers.companyname as Customernam,"
csql=csql+ "EMPLOYEES. Firstname+ ' +employees. LASTNAME as Salesperson,orders. Orderid,orders. ORDERDATE, "
csql=csql+ "Orders.requireddate,orders.shippeddate,shippers.companyname as Shippername,"
Csql=csql+ "[Order DETAILS]. Productid,products. Productname,[order DETAILS]. UnitPrice, "
Csql=csql+ "[Order DETAILS]. Quantity,[order DETAILS]. DISCOUNT, "
Csql=csql+ "[Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, "
csql=csql+ "ORDERS. FREIGHT "
Csql=csql+ "From" (((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID) "
csql=csql+ "INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID) "
csql=csql+ "INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID) "
csql=csql+ "INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID) "
csql=csql+ "INNER JOIN" [Order DETAILS]. Productid=products. PRODUCTID "
csql=csql+ "WHERE ORDERS." ORDERDATE BETWEEN ' 1996-10-01 ' and ' 1997-09-30 '
Csql=csql+ "and ORDERS. CUSTOMERID like '%c% '
Csql=csql+ "and [order DETAILS]. Quantity>50 "
&& transfer, the resulting cursor name is the default Sqlresult
SqlExec (Hconn,csql)

Passing variables

The previous example amply demonstrates my experience with string delimiters, and then I have a new topic for you: How to pass variables to SQL Server. In the example above, we add the WHERE clause to the statement, and then we change the conditional value into a variable so that the SQL pass becomes truly meaningful!

I think there are two ways to pass on a variable, as described below.

The first is to use: the question mark + variable to pass, this is a common method, it is also very simple to use, do not worry about the type of variables, ODBC will automatically transform. The following example involves date, numeric, and character variables, and we don't have to worry about the conversion of variable types, as long as you fill in the statement with the question mark above:

Local CSQ
&& the variables to be transferred
DDATE1={^1996-10-01}
DDATE2={^1997-09-30}
Ccustomerid= "%c%"
Nqty=50
&& Configure the statements to be delivered
csql=csql+ "Select ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. SHIPREGION, "
csql=csql+ "ORDERS. Shippostalcode,orders. Shipcountry,orders. CUSTOMERID, "
csql=csql+ "Customers.companyname as Customernam,"
csql=csql+ "EMPLOYEES. Firstname+ ' +employees. LASTNAME as Salesperson,orders. Orderid,orders. ORDERDATE, "
csql=csql+ "Orders.requireddate,orders.shippeddate,shippers.companyname as Shippername,"
Csql=csql+ "[Order DETAILS]. Productid,products. Productname,[order DETAILS]. UnitPrice, "
Csql=csql+ "[Order DETAILS]. Quantity,[order DETAILS]. DISCOUNT, "
Csql=csql+ "[Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, "
csql=csql+ "ORDERS. FREIGHT "
Csql=csql+ "From" (((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID) "
csql=csql+ "INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID) "
csql=csql+ "INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID) "
csql=csql+ "INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID) "
csql=csql+ "INNER JOIN" [Order DETAILS]. Productid=products. PRODUCTID "
csql=csql+ "WHERE ORDERS." ORDERDATE BETWEEN dDate1 and DDate2 "
Csql=csql+ "and ORDERS. CUSTOMERID like? Ccustomerid "
Csql=csql+ "and [order DETAILS]. Quantity>?nqty "
&& transfer, the resulting cursor name is the default Sqlresult
SqlExec (Hconn,csql)

Analysis: Let's take a look at the writing specifications, see:

csql=csql+ "WHERE ORDERS." ORDERDATE BETWEEN dDate1 and DDate2 "
Csql=csql+ "and ORDERS. CUSTOMERID like? Ccustomerid "
Csql=csql+ "and [order DETAILS]. Quantity>?nqty "

The question mark + variable is placed in the SQL statement as a parameter, and you may wonder: How is the question mark + variable placed inside the double quotation mark of the Visual FoxPro, not as a string? The first thing we need to know is that Visual FoxPro SqlExec () can only pass a string, so the question mark + variable should be part of the string! SqlExec () will give the string to Odbc,odbc to parse the string, he will find the question mark + variable special writing, so it passed to the SQL Server statement is (the whole sentence is too long, I chose the last paragraph):

WHERE ORDERS. ORDERDATE BETWEEN @P1 and @P2 and ORDERS. CUSTOMERID like @P3 and [order DETAILS]. Quantity> @P4 ', N ' @P1 datetime, @P2 datetime, @P3 varchar (3), @P4 float ', ' Oct 1 1996 12:00am ', ' Sep 1997 12:00am ', '%c % ', 5.000000000000000e+001

See, all variables are renamed and SQL Server is used to code!

The second approach is to use Visual FoxPro to combine variables into a statement to produce a string so that the statement passed to SQL Server is a complete string with no variables, and the benefit of doing so is more efficient because ODBC does not have to handle the passing of variables, SQL The server also does not have to process variable information.

Local CSQ
&& the variables to be transferred
DDATE1={^1996-10-01}
DDATE2={^1997-09-30}
Ccustomerid= "%c%"
Nqty=50
&& Configure the statements to be delivered
csql=csql+ "Select ORDERS. Shipname,orders. Shipaddress,orders. Shipcity,orders. SHIPREGION, "
csql=csql+ "ORDERS. Shippostalcode,orders. Shipcountry,orders. CUSTOMERID, "
csql=csql+ "Customers.companyname as Customernam,"
csql=csql+ "EMPLOYEES. Firstname+ ' +employees. LASTNAME as Salesperson,orders. Orderid,orders. ORDERDATE, "
csql=csql+ "Orders.requireddate,orders.shippeddate,shippers.companyname as Shippername,"
Csql=csql+ "[Order DETAILS]. Productid,products. Productname,[order DETAILS]. UnitPrice, "
Csql=csql+ "[Order DETAILS]. Quantity,[order DETAILS]. DISCOUNT, "
Csql=csql+ "[Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as EXTENDEDPRICE, "
csql=csql+ "ORDERS. FREIGHT "
Csql=csql+ "From" (((EMPLOYEES INNER JOIN ORDERS on EMPLOYEES. Employeeid=orders. EMPLOYEEID) "
csql=csql+ "INNER JOIN CUSTOMERS on ORDERS. Customerid=customers. CUSTOMERID) "
csql=csql+ "INNER JOIN shippers on ORDERS. Shipvia=shippers. ShipperID) "
csql=csql+ "INNER JOIN [order DETAILS] on ORDERS. Orderid=[order DETAILS]. ORDERID) "
csql=csql+ "INNER JOIN" [Order DETAILS]. Productid=products. PRODUCTID "
ccsql=csql+ "WHERE ORDERS." ORDERDATE BETWEEN ' "+transform (dDate1) +" ' and ' "+transform (dDate2) +" "
Csql=csql+ "and ORDERS. CUSTOMERID like '% ' +ccustomerid+ '% '
Csql=csql+ "and [order DETAILS]. Quantity> "+transform (Nqty)
&& transfer, the resulting cursor name is the default Sqlresult
SqlExec (Hconn,csql)

Here's how to do this by trying to imitate the specification of SQL Server to shape strings:

ccsql=csql+ "WHERE ORDERS." ORDERDATE BETWEEN ' "+transform (dDate1) +" ' and ' "+transform (dDate2) +" "
Csql=csql+ "and ORDERS. CUSTOMERID like '% ' +ccustomerid+ '% '
Csql=csql+ "and [order DETAILS]. Quantity> "+transform (Nqty)

If you calculate the descendants into the variable, you will find the following string:

"WHERE ORDERS. ORDERDATE BETWEEN ' 1996-10-01 ' and ' 1997-09-30 ' and ORDERS. CUSTOMERID like '%c% ' and [order DETAILS]. Quantity>50 "

See this is the previous article we do not involve variable delivery of the sentence of the SQL statement, very simple--merge variables into the SQL statements, mold the pure SQL statement! Is entirely Visual FoxPro's skill in string computing, here is worth mentioning is the transform () function, this is a very useful function, it can transform various types of values into a standard string, especially like the date type to do in a variety of data, he can easily specification!