Use XML technology to display owc Databases

Overview: This document describes how to use the XML language to display the data in the database by using owc on the web page. Because XML data can span the firewall, this method can display data on the Internet.
In Web-based database analysis applications, the owc control and HTML are often used to display data tables and charts. Generally, applications use owc to directly connect to the database, which exposes the database connection password on the client and reduces the security of the database. This document describes how to use XML as the data exchange medium between owc and databases, which can avoid this risk of database security. At the same time, this can also bring other benefits, such as reducing the number of interactions between the browser and the Web server and the database, accelerating the response speed of operations such as sorting, and reducing the load on the server.
1. Get XML data
There are many ways to convert the data in the database to XML format. This document does not provide more information. From the perspective of performance and universality, we adopt ADO direct serialization (persistent persist) data. The Code is as follows:
<! -- Getdata. asp -->
<%
Dim strconn
Strconn = "provider = sqloledb.1; persist Security info = false; user id = sa; initial catalog = test; Data Source = jlwz"
'---------- Read data ----------------
Dim Conn, RS
Set conn = server. Createobject ("ADODB. Connection ")
Set rs = server. Createobject ("ADODB. recordset ")
Conn. Open strconn
Rs. Open "select stat_date, call_num, call_future from callstat", Conn

'Convert ADO to XML DOM
Const adpersistxml = 1

Dim objxmldom
Set objxmldom = server. Createobject ("msxml2.domdocument. 3.0 ")
Rs. Save objxmldom, adpersistxml
Set rs = nothing
%>

The XML obtained in this way is not concise enough, including schema information.
Although the datasourcecontrol control in owc can directly use XML data in this form, considering the efficiency of data transmission from the server to the client, we use XSLT to convert this XML data. Therefore, the following clean. XSL file is compiled:
<? XML version = "1.0"?>
<XSL: stylesheet version = "1.0" xmlns: XSL = "http://www.w3.org/1999/XSL/Transform"
Xmlns: S = "UUID: BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
Xmlns: dt = "UUID: C2F41010-65B3-11d1-A29F-00AA00C14882"
Xmlns: rs = "urn: Schemas-Microsoft-com: rowset" xmlns: z = "# rowsetschema">
<XSL: Output omit-XML-declaration = "yes"/>
<XSL: template match = "/">
<XSL: element name = "data">
<XSL: For-each select = "/XML/RS: Data/Z: Row">
<XSL: element name = "row">
<XSL: For-each select = "@ *">
<XSL: element name = "{name ()}">
<XSL: value-of select = "."/>
</XSL: Element>
</XSL: For-each>
</XSL: Element>
</XSL: For-each>
</XSL: Element>
</XSL: Template>
</XSL: stylesheet>

Then, convert the preceding XML data in getdata. asp using the following code:
'Use XSLT to clear and convert XML data
Dim strcleanxml, objxslt

Set objxslt = server. Createobject ("msxml2.domdocument ")
Objxslt. Load (server. mappath ("clean. XSL "))
Strcleanxml = objxmldom. transformnode (objxslt)

At this point, we get the string of the simple XML structure we want. You can simply write it to the client:
<DATA>
<Row>
<Stat_date> 2003-06-01 </stat_date>
<Call_num> 100 </call_num>
<Call_0000> 200 </call_0000>
</Row>
<Row>
<Stat_date> 2003-07-01 </stat_date>
<Call_num> 200 </call_num>
<Call_0000> 400 </call_0000>
</Row>
...
</Data>
 

Ii. Use the owc control and HTML table to display XML data
2.1 Basic functions
Another HTML page is created here. To use the XML data you just obtained, use the XML data island on the HTML page:
<XML id = "dbxml" src = "getdata. asp" onreadystatechange = "Init ()"> </XML>

Then, you can use the HTML table binding function to display data:
<Table datasrc = "# dbxml" style = "width: 100%; border-collapse: collapse;" border = 1 cellpadding = 0 cellspacing = 0>
<Tr>
<TD> <Div type = text data== stat_date> </div> </TD>
<TD> <Div type = text dataworks = call_num> </div> </TD>
<TD> <Div type = text data1_= call_1_> </div> </TD>
</Tr>
</Table>

In the init () function corresponding to the onreadystatechange event in XML data island, we use the following code to implement the owc chart:
<Object ID = CS1 style = "width: 400px; top: 0px; Height: 280px"
Classid = CLSID: 0002e556-0000-0000-c000-000000000046 viewastext>
</Object>
<SCRIPT lanaguage = VBScript>
Sub Init ()
If (dbxml. readystate = "complete") then
Dim strxml
Set strxml = dbxml. xmldocument
Createchart strxml, CS1
End if
End sub
Sub createchart (byref oxml, cspace) 'generates a chart based on the imported XML
Dim xdoc, xroot, ccnt
Dim ndx, cNode, txtdata, txtcat, txtdata2

Set xdoc = dbxml. xmldocument
Set xroot = xdoc.doc umentelement
Ccnt = xroot. childnodes. Length
Txtdata = "": txtcat = ""

'Get the corresponding substring from XML data
For ndx = 0 to ccnt-1
Set cNode = xroot. childnodes (ndx)
Txtcat = txtcat & cNode. childnodes (0). Text
Txtdata = txtdata & cNode. childnodes (1). Text
Txtdata2 = txtdata2 & cNode. childnodes (2). Text
If ndx <> (ccnt-1) then
Txtcat = txtcat &","
Txtdata = txtdata &","
Txtdata2 = txtdata2 &","
End if
Next

'--- Start drawing ---------- below ----------
'Add data sequence 1
Set CH = cspace. charts. Add ()
Set S = CH. seriescollection. Add ()
S. Name = "call fee (RMB )"
S. Caption = S. Name
S. setdata C. chdimcategories, C. chdataliteral, txtcat
S. setdata C. chdimvalues, C. chdataliteral, txtdata
S. type = 8' Graph

'Set the time scale axis format
Set axcategory = cspace. Charts (0). axes (C. chaxispositioncategory)
With axcategory
. Groupingunittype = C. chaxisunitmonth
. Groupingunit = 1' Unit
. Numberformat = "short date" 'short date
End

'Add Data Sequence 2
Set S2 = CH. seriescollection. Add ()
S2.name = "number of calls (times )"
S2.caption = s2.name
S2.setdata C. chdimvalues, C. chdataliteral, txtdata2

'Heading
Ch. hastitle = true
Ch. Title. Caption = "monthly call Situation Report"
Ch. Title. Font. color = "black"
Ch. Title. Font. size = 10
Ch. Title. Font. Bold = true

'Chartspace Property
Cspace. Border = C. chlinedash
Cspace. hasselectionmarks = true
Cspace. allowfiltering = true' allow commands and Groups
Cspace. allowpropertytoolbox = true

'Set the legend and Position
Ch. Legend. Position = C. chlegendpositionright
Ch. haslegend = false

'Group into different groups and display the Dual Axis
S2.ungroup true
Set axincomeaxis = CH. Axes. Add (s2.scalings (C. chdimvalues ))
Axincomeaxis. Position = C. chaxispositionright
Axincomeaxis. hasmajorgridlines = false
S2.type = 0' column chart
End sub

In this way, we can obtain data tables and charts. The final effect is as follows:
In this way, with the help of XML technology and IE binding technology, we have implemented owc to display the data in the database, but no data connection information is exposed on the client.

2.2 Other Functions
Owc can easily save the charts as local images, which greatly facilitates users. At the same time, owc provides a variety of chart types, such as pie chart, curve chart, and column chart, suitable for displaying data in different situations.

With the help of COM components and the flexible use of XSL, we can get better performance and better functions on this page. For example, sorting HTML tables (see the HTML source code in the attachment) and Data paging. In addition, we can also implement common data access and search functions.