Writing web Service with VFP (i.)

Written in front

For half a year, I've been trying to write an article about using Visual FoxPro 7 to write Web Service, but it's not always a wish. This time I am not lazy, in order to understand this new technology, my friends and I experienced a variety of "hardships." Here a little memory, is also considered as the Spring Festival to "previous years" review-is not wasted too much life.

Tribulation One: The Publishing Wizard does not publish the Web Service correctly.

July to get the Visual FoxPro 7, check the sample, did not find a Web Service on the Demo. But in Help as well as Microsoft's publicity for Visual FoxPro7, we have vowed to support the development of Web Service ... Fortunately, I saw an article in Msdn called "Creating Web Services with Visual FoxPro." Unfortunately, a step-by-step experiment does not properly publish a Web Service. After repeated research, I think this is a bug, but fortunately found a way to bypass the bug. This time is already October ...

Tribulation two: The original Web Service is stateless!

It is a waste of life ah, fortunately I have not professional Visual FoxPro programmers, amateur leisure, no pressure. You remember what I wrote on the BoE, "Visual FoxPro 7 new debut--The meaning of XML in the transmission of COM component datasets"? The example of that article was originally intended for the Web service of this article: When the code was compiled for COM only, everything was in my imagination, and the further issue of the Web service was that the--web service could not remember me to command it. After studying the original Web Service is "stateless" ("stateless" has been seen in the past few years, it was not understood at that time.) I didn't expect the Web Service to help me understand this concept, and it's a blessing in disguise.

The Tribulation Three: Xmltocursor () has the question to the Chinese support!

Since I thought I had been able to write some small Web service applications with Visual FoxPro, I preached to a netizen: Use Web service to do your application! People are very careful, the next day told me a big problem: the use of xmltocursor () can not correctly handle the XML document containing Chinese, a lot of characters will be truncated! fainted, another bug!. At that time, Microsoft had announced that it was going to release the SP1 of Visual FoxPro7, so I waited for SP1 to find other solutions. Huang Tian not negative, west-wind unexpectedly provided such a set of Class library Wwxml, the function is more powerful than the visual FoxPro7 to XML, support for Chinese is also very good!

(After testing, Visual FoxPro 7 SP1 has solved the problem of handling Chinese characters)

Tribulation Four: SP1 unexpectedly so difficult to install!

January 16, Microsoft released the SP1. Ghosts know, their installation program is how to do-only in the Win9x under the correct installation, Win2000, XP is not good (I heard someone under the Win2000 successfully installed SP1, really admire their luck ... ）。 Fortunately the forum of Comrade Qxf, put his win98 under the installation of the SP1 found after the update of the package, distributed to everyone. Now, if you can't fit the SP1, just do the following on the computer.

1. Close Visual FoxPro7
2. Copy file DW15.EXE, Dwintl. DLL to the Visual FoxPro7 Home () directory.
3. Find and replace VFP7.EXE, vfp7r in the computer. DLL, vfp7t. DLL, Vfp7renu. DLLs, Vfp7runtime.msm, Vfpoledb. DLL, Vfpoledb. Msm

So much for the gossip, let's get started!

System Requirements

1. Various versions of Win2000 and Win Xp, and install IIS (no other version of Windows has tried)
2. Install visual FoxPro7, recommend installation SP1
3. Install SOAP Toolkit 2.0 (available in Visual FoxPro 7 installation disk)
4.SQL server 7 or SQL Server 2000

Example Introduction

Data source

This Web Service provides two methods for querying data: the ability to retrieve all invoices in the system and the ability to query for total sales over a period of time.

I used the SQL Server demo database Northwind as the data source, in order to highlight the power of Visual FoxPro, I have to convert this database to a Visual FoxPro local DBC library. The name of the database is: WEB_SERVICE.DBC, which contains 13 tables, which is consistent with the Northwind database in SQL Server!

Well, we now have two of the same data sources, one is local DBC, and the other is a remote SQL Server database. In the pending trials, we will be querying the data at the same time in both data sources, and you will see the simplicity and flexibility of Visual FoxPro in remote (heterogeneous) data processing.

There is still some work to be done. We know that Visual FoxPro access to remote databases is remote view and SPT. Remote View is very unique, and its data source is remotely data, but it itself is a member of the local DBC, so that the Visual FoxPro to remote data sources of fast management, perfect fusion.

Here we need to build three remote views, corresponding to orders, order Details tables, and view invoices in the Northwind database: You can do this by following these commands:

CREATE SQL VIEW orders_sql REMOTE CONNECTION localsqlserver as SELECT * FROM Orders
CREATE SQL VIEW order_details_sql REMOTE CONNECTION localsqlserver as SELECT * FROM [Order Details]
CREATE SQL VIEW invoice_sql REMOTE CONNECTION localsqlserver as select * from Invoices

This use of the connection "LocalSqlServer" can be generated using the following command: (everyone can establish a legitimate connection according to the different circumstances of each system!) )

CREATE CONNECTION localsqlserver connstring "Driver=sql Server; Server=boeworks; Uid=sa; pwd=;D Atabase=northwind "

In the experiment, we also use local View (native attempt) generated by native data, which has the same effect as invice_sql, except that the former data originates from the local DBC, which originates from the Northwind database of SQL Server. This can be done using the following code: (If you design this view with the View Designer, you will notice that the View Designer "complains" because: the Visual FoxPro View Designer does not support too complex SQL statements.) So when you design a complex view, you should direct handwriting code, in fact, this is the common practice of many SQL experts! )

CREATE SQL VIEW INVOICE_VFP as;
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 Northwind!employees INNER JOIN northwind!orders;
INNER JOIN northwind!customers;
INNER JOIN northwind!shippers;
INNER JOIN northwind!order_details;
INNER JOIN northwind!products;
on order_details.productid = Products.ProductID;
on orders.orderid = Order_details.orderid;
on orders.shipvia = Shippers.shipperid;
On;
Orders.CustomerID = Customers.CustomerID;
On employees.employeeid = Orders.EmployeeID

Base class

#define CL chr (+CHR) (10)
DEFINE CLASS Foxbaseclass as session
datasession=2
Databasepath= "D:\Data\"

PROCEDURE Init ()
Local Ctext as String
Ctext= "+transform" (Time ())
Strtofile (Ctext, ' c:\FoxWebService.txt ',. T.)

Endproc

PROCEDURE Destroy
Local Ctext as String
ctext= "Off" +transform (Time ())
Strtofile (Ctext, ' c:\FoxWebService.txt ',. T.)
Endproc


PROCEDURE OpenDatabase ()
OPEN DATABASE this. databasepath+ "Northwind.dbc" SHARED

Endproc

PROCEDURE CloseDataBase ()
Close DATABASES

Endproc

FUNCTION Connectsqlserver () as Integer
Local iconn as Integer
Iconn=sqlconnect ("LocalSqlServer")
Return iconn

Endfunc

PROCEDURE Disconnectsqlserver (iconn as Integer)
SQLDisconnect (iconn)

Endproc

PROCEDURE Error (nerror as integer,cmethod as String, nline as Integer)
Local Ctext as String
ctext=;
' Error time: ' +transform (DateTime ()) +cl+;
' Error code: ' +str (nerror,4) +cl+;
' ERROR hint: ' +message () +cl+;
' Wrong way: ' +cmethod+cl+;
' ERROR line number: ' +transform (nline) +cl+cl
Strtofile (Ctext, ' c:\FoxWebService.txt ',. T.)
Comreturnerror ("Fox Web Service", Ctext)
Endproc
Enddefine

Analysis of the above code, there are several points to explain:

1. Control the path where local data resides

Databasepath= "D:\Data\"

2. Open and close the local database:

PROCEDURE OpenDatabase ()
PROCEDURE CloseDataBase ()

3. Connect to SQL Server and disconnect from SQL Server (in fact, these two methods are not used in this case, because we use a remote view.) Remote view can automatically control the connection! ）

FUNCTION Connectsqlserver () as Integer
PROCEDURE Disconnectsqlserver (iconn as Integer)

4. Error control

PROCEDURE Error (nerror as integer,cmethod as String, nline as Integer)

5. The establishment and destruction of the recording object (this is to prove that the Web service is "stateless", specifically we will say later)

Specific implementation code

define CLASS Foxwebservice as Foxbaseclass olepublic

FUNCTION getinvoice (Itype as Integer) as Str ing
Local cXML as String

this. OpenDatabase ()

IF itype=0 &&sql Server datasheet
Use invoice_sql ALIAS invoice
ELSE &&fox database br> use INVOICE_VFP ALIAS invoice
ENDIF

Cursortoxml ("Invoice", "CXML", 3,48,0, "")
use in invoice

This. CloseDataBase ()

return CXML

Endfunc

FUNCTION getsumsales (DStart as date,dend as Date) as Double

this. OpenDatabase ()
SELECT sum (b.unitprice*b.quantity* (1-b.discount)) as Sumsalse from orders a INNER JOIN Order_details_ SQL B on A.orderid=b.orderid;
where A.orderdate>=dstart and a.orderdate<=dend into ARRAY result
IF _tally>0 then
return result[1]< br> ELSE
return 0
ENDIF

this. CloseDataBase ()
Endfunc


Enddefine

From the code we can see that Foxwebservice is a subclass of Foxbaseclass that inherits all the Foxbaseclass attributes. It is also given the Olepublic keyword, indicating that it can be compiled into COM objects!

Foxwebservice has only two methods for implementing different two functions, as follows:

Function One: query all invoice information and the user can specify whether to return information from the DBC local database or from SQL Server.

FUNCTION Getinvoice (Itype as Integer) as String

When the parameter Itype equals 0 o'clock, return invoice information from SQL Server, and in other cases, return invoice information from the local DBC. This is done by calling two different views:

IF itype=0 &&sql Server data table
Use Invoice_sql ALIAS Invoice
ELSE &&fox Database
Use INVOICE_VFP ALIAS Invoice
ENDIF

Here, you may ask, how to get a data collection (table) back out, this question I have in the "Visual FoxPro 7 new debut-XML in the COM component data set in the meaning of the transmission" is very clear in the article: With Cursortoxml () Converts a data collection into an XML string to return:

Cursortoxml ("Invoice", "CXML", 3,48,0, "")

Function Two: Returns the total sales amount in a time period

This query is easy to complete if you have studied the structure of the database. But here I add an interesting requirement: The data from the sales main file (orders) comes from the local DBC, and the sales detail (order Details) data comes from the remote SQL Server table. And regardless of whether this requirement is reasonable (here, we only talk about technology implementation), this is obviously an associative query between heterogeneous databases: The visual FoxPro can be easily implemented:

SELECT sum (b.unitprice*b.quantity* (1-b.discount)) as Sumsalse from orders a INNER JOIN order_details_sql B on a.orderid=b. OrderID;
where A.orderdate>=dstart and a.orderdate<=dend into ARRAY result
IF _tally>0 Then
return result[1]
ELSE
return 0
ENDIF

Did you see it? It's simple--I'm actually associating the local Orders table with the remote view order_details_sql. When Order_details_sql is turned on, Visual FoxPro automatically reads the relevant data on SQL Server through ODBC, so the local Orders table and remote view Order_details_sql are connected directly to heterogeneous data tables. This is difficult to implement in other languages, they can also read data from different data sources, but they cannot support the local SQL connection of heterogeneous data, and they can only connect heterogeneous data in a "cyclic + conditional" manner.

Compiling and publishing a Web Service

Compiling Web Service

This process should be very familiar to everyone, in fact, is compiled into the usual COM. The following command enables you to:

Build Mtdll First_web_service from First_web_service

Okay, so it's easy to--com component ready, we can call it in the command window:

* Please ensure that the C-packing directory does not exist FoxWebService.txt files, if there is delete it (our goal is to prove that COM components are "stateful")
* Set up COM objects
Ox=createobject ("First_web_service.") Foxwebservice ")
* Test Getinvoice
Xmltocursor (Ox. Getinvoice (0), "test")
* Test Getsumsales
? Ox. Getsumsales ({^1997-01-01},{^1997-3-30})
* Destroy COM objects
Rele Ox

If the test succeeds, please check the FoxWebService.txt file in the C packing directory, the contents of my file are (depending on the test time, the results will be different):

Opening time 23:20:54 Closing Time 23:21:25

This is generated by the code in the Init and destory events of the Foxwebservice object: Init fires when the class is instantiated as an object, and Destory when the object is destroyed. In COM applications, CreateObject () and Rele each trigger these two events!

Well, remember the results here. We continue to ...