Solving WSR Class Generation Problems

WSR tries to create classes for any valid WSDL documents. If WSR determines the WSDL document isn't valid or WSR can't process it, the Search Result text box displays a "Your search returned no results" message. In rare cases, you receive an error message when you click WSR's Add button. Some WSDL documents generate classes that won't compile. As an example, the WSDL documents that Microsoft SQLXML 3.0 generates for Web services created from SQL Server 2000 stored procedures produce VBA classes that generate compilation errors. This is an important issue, because SQLXML 3.0 makes providing data-driven Web services a quick and easy process. A typical application for SQL Server 2000 Web services is delivering up-to-date product catalogs or similar reference data via the Internet to Access order processing applications designed for sales agents or customers.

Note

Future versions of SQLXML, WSR, or both might correct the problem you solve in the following sections. The techniques you learn, however, let you rather than WSR manage the processing of any SOAP response message by writing relatively simple VBA procedures.


If the capability to consume problematic Web services is important to your Access application, you must modify the code of WSR-generated classes. Following are the typical steps to handle defective WSR classes for complex types:

  1. Comment the statement(s) that prevent compilation. In most cases, removing the statements causes execution of one or more Web methods to fail.

  2. Add code to the form's class module to create a Web service proxy and invoke one of the methods you plan to use.

  3. Execute the Web method. If you encounter runtime errors or the service doesn't return the data you expected, the problem is likely to be deserializing the soap response message to one or more of the custom types specified in the WSML file.

    Note

    If WSR doesn't interpret the WSDL document correctly or can't generate a VBA-compliant type, the offending struct_ClassName class module often contains members of the MSXML2.IXMLDOMNodeList type. IXMLDOMNodeList is the default type returned by the SoapClient30 object.

  4. Delete or comment all str_WSDL = ... lines to return an empty string, and delete all WSR classes except clsws_ServiceName.

  5. Determine the type returned by clsws_ServiceName's Public Function wsm_FunctionName(Parameters...) As Type. If Type isn't [MSXML2.]IXMLDOMNodeList, change it to this value. (Adding the MSXML2 type library name is optional).

  6. Compile the code and remove statements that generate compile errors.

Completing the preceding steps disables WSR's complex type handling features and requires writing VBA code to process the response message that's contained in the IXMLDOMNodeList instance. You can handle any complex SOAP response message, regardless of its structure, with the IXMLDOMNodeList class.

Understanding the XML Document Object Model

Working with the IXMLDOMNodeList object requires some familiarity with the XML Document Object Model (XML DOM). The XML DOM represents any well-formed XML document as a set of elements (nodes) that contain information (text), other nodes (called child nodes), or both. You extract the document's text values to populate controls or tables with an XML parser. The MSXML2 library (Microsoft XML v5.0, Msxml5.dll), which Office 2003 installs in your \Program Files\Common Files\Microsoft Shared\Office11 folder, was Microsoft's latest COM-based XML parser when this book was written.

Note

MSXML2 is best suited for processing relatively small XML documents, because it loads at least one copy of the entire document into memory. Other XML parsers, such as the Simple API for XML (SAX), don't require loading the document into memory but have a more complex, event-driven programming model.


As its name implies, the IXMLDOMNodeList class provides access (a pointer) to XML DOM nodes in a document from a list. Each node is a member of the IXMLDOMNode class. You iterate the list in a set of nested For...Next loops that return IXMLDOMNode members at each level of the document's hierarchy. The IXMLDOMNode.text property at the lowest level of the hierarchy returns individual data values.

Following is an abbreviated example of a SOAP response message returned by a demonstration Web service (Alpha) that's generated from one of four SQL Server stored procedures exposed as Web methods:

 <?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope  xmlns:xsd="http://www.w3.org/2001/XMLSchema"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"  xmlns:sqltypes=".../SQLServer/2001/12/SOAP/types"  xmlns:sqlmessage=".../SQLServer/2001/12/SOAP/types/SqlMessage"  xmlns:sqlresultstream=".../SQLServer/2001/12/SOAP/types/SqlResultStream"  xmlns:tns="http://www.oakleaf.ws/SQLXML/Alpha">  <SOAP-ENV:Body>   <tns:GetTop10Response>    <tns:GetTop10Result xsi:type="sqlresultstream:SqlResultStream">     <sqlresultstream:SqlXml xsi:type="sqltypes:SqlXml"        sqltypes:IsNested="false">       <SqlXml>        <row>         <SKU>DVDP0345</SKU>         <Category>DVD Players</Category>         <Brand>Onkyo</Brand>         <Model>DV-S939</Model>         <Description>          DVD-Audio/Video/CD Player with Progressive         </Description>         <NetPrice>1275</NetPrice>         <Quantity>516</Quantity>        </row>        <!-- Eight rows not shown -->        <row>         <SKU>DVDP0009</SKU>         <Category>DVD Players</Category>         <Brand>Aiwa</Brand>         <Model>XD-DW5</Model>         <Description>          Portable DVD player with 5.8-inch Color Screen         </Description>         <NetPrice>375</NetPrice>         <Quantity>514</Quantity>        </row>       </SqlXml>      </sqlresultstream:SqlXml>      <sqlresultstream:SqlResultCode         xsi:type="sqltypes:SqlResultCode"         sqltypes:IsNested="false">        0      </sqlresultstream:SqlResultCode>    </tns:GetTop10Result>   </tns:GetTop10Response>  </SOAP-ENV:Body> </SOAP-ENV:Envelope> 

The Alpha Web service delivers a catalog from Alpha Electronics, Inc., a fictitious consumer electronics distributor. The GetTop10Result Web method returns a list of Alpha's 10 most expensive consumer electronics products in one or more of six categories as an sqlresultstream message, which contains first-level SqlXml and SqlResultCode child nodes. The SqlXml node contains a collection (array) of second-level child row nodes, which contain the third-level data elements. The SqlResultCode node has a value of 0 if the request is successful or an error number if it isn't. In the event of an SQL Server error, the response message includes a serialized sqlmessage object, which contains a detailed description of the problem.

Navigating the IXMLDOMNodeList

Before you can write the VBA code necessary to populate a list box or table with data, it's a good practice to perform a test iteration of the IXMLDOMNodeList object to determine the level in the hierarchy at which you obtain a useful representation of the data. "Walking the nodes" also lets you explore the structure of XML data in a SOAP response message when you can't infer the structure from the WSDL document's schema.

Note

graphics/power_tools.gif

The size of the WSDL document for the SQLXML's Alpha service exceeds reasonable publishing limitations. The complete document for the GetTop25 method GetTop25.wsdl.xml is in your \Project Files\Seua11\Chaptr31\Alpha folder.

You can view the WSDL document in IE at http://www.oakleaf.ws/sqlxml/alpha?wsdl. Web services generated by SQL Server stored procedures don't use an .asmx file, so you specify the service name and omit the .asmx suffix in the URL.


Listing 31.1 is the VBA code for a general-purpose subprocedure that prints to the Immediate window the level, nodeName, and text properties of an IXMLDomNodeList object you pass as a parameter. You can use this code as a model for the procedures you write to populate controls or tables from the SOAP response message, as illustrated in the next section. You can add additional For...Next loops if the message contains more than three levels of child nodes.

Listing 31.1 This VBA subprocedure lets you use the Immediate window to explore SOAP response message structure and data.
 Public Sub PrintNodeList(nodList As MSXML2.IXMLDOMNodeList)  'Generates an indented list of node names and  'values in the Immediate window  'This procedure is limited to three levels of child nodes  Dim intNodes As Integer  Dim intMaxNodes As Integer  Dim intMaxLength As Integer  Dim intChild1 As Integer  Dim intChild2 As Integer  Dim intChild3 As Integer  Dim domNode As MSXML2.IXMLDOMNode  Dim domChild1 As MSXML2.IXMLDOMNode  Dim domChild2 As MSXML2.IXMLDOMNode  Dim domChild3 As MSXML2.IXMLDOMNode  'Set the maximum number of child nodes printed at each level  intMaxNodes = 6  'Set the maximum length of node text strings  intMaxLength = 100  With nodList   'The parent node   If .length > 0 Then     For intNodes = 0 To .length - 1       'List nodes (level 0)       Set domNode = .Item(intNodes)       With domNode        Debug.Print "Level 0: nodeName(" & intNodes & ") = " _          & .nodeName        Debug.Print "Level 0: text(" & intNodes & ") = " _          & Left$(.Text, intMaxLength)        If .hasChildNodes Then          'First-level child nodes          For intChild1 = 0 To .childNodes.length - 1           If intChild1 > intMaxNodes Then             Exit For           End If           Set domChild1 = .childNodes(intChild1)           With domChild1            Debug.Print " Level 1: nodeName(" & intChild1 & ") = " _              & .nodeName            Debug.Print " Level 1: text(" & intChild1 & ") = " _              & Left$(.Text, intMaxLength)            If .hasChildNodes Then              'Second-level child nodes         For intChild2 = 0 To .childNodes.length - 1          If intChild2 > intMaxNodes Then            Exit For           End If           Set domChild2 = .childNodes(intChild2)           With domChild2            Debug.Print " Level 2: nodeName(" & intChild2 & ") = " _              & .nodeName            Debug.Print " Level 2: text(" & intChild2 & ") = " _              & Left$(.Text, intMaxLength)           If .hasChildNodes Then             'Third-level child nodes             For intChild3 = 0 To .childNodes.length - 1              If intChild3 > intMaxNodes Then               Exit For              End If              Set domChild3 = .childNodes(intChild3)              With domChild3               Debug.Print "Level 3: nodeName(" & intChild3 & ") = " _                 & .nodeName               Debug.Print "Level 3: text(" & intChild3 & ") = " _                 & Left$(.Text, intMaxLength)              End With             Next intChild3            End If           End With          Next intChild2         End If        End With       Next intChild1      End If     End With    Next intNodes   Else     'There's nothing in the node list   End If  End With End Sub 

The hasChildNodes property returns True if the current node contains child nodes, and the length property returns the number of child nodes. The length property is the counterpart of the Count property for collections. The nodeName property value returns the type (class) or element name, and the text property returns the element value. At the lowest level of the hierarchy, nodeNames are data element tag names and text returns the data values.

Following is the abbreviated result of passing the IXMLDOMNodeList object generated by the SQLXML.GetTop10 method to the PrintNodeList procedure:

 Level 0: nodeName(0) = xsi:type Level 0: text(0) = sqlresultstream:SqlResultStream  Level 1: nodeName(0) = #text  Level 1: text(0) = sqlresultstream:SqlResultStream Level 0: nodeName(1) = sqlresultstream:SqlXml Level 0: text(1) = AMPL0692AmplifiersSonyTA-N9000ES...  Level 1: nodeName(0) = SqlXml  Level 1: text(0) = AMPL0692AmplifiersSonyTA-N9000...   Level 2: nodeName(0) = row   Level 2: text(0) = AMPL0692AmplifiersSonyTA-N9000ES...    Level 3: nodeName(0) = SKU    Level 3: text(0) = AMPL0692    Level 3: nodeName(1) = Category    Level 3: text(1) = Amplifiers    Level 3: nodeName(2) = Brand    Level 3: text(2) = Sony    Level 3: nodeName(3) = Model    Level 3: text(3) = TA-N9000ES    Level 3: nodeName(4) = Description    Level 3: text(4) = Five-channel Amplifier (ES)    Level 3: nodeName(5) = NetPrice    Level 3: text(5) = 1000    Level 3: nodeName(6) = Quantity    Level 3: text(6) = 503   Level 2: nodeName(1) = row   Level 2: text(1) = AMPL0697AmplifiersNilesSI-1230...    Level 3: nodeName(0) = SKU    Level 3: text(0) = AMPL0697    Level 3: nodeName(1) = Category    Level 3: text(1) = Amplifiers    Level 3: nodeName(2) = Brand    Level 3: text(2) = Niles    Level 3: nodeName(3) = Model    Level 3: text(3) = SI-1230 FG00737    Level 3: nodeName(4) = Description    Level 3: text(4) = Multi-room 12 x 30-Watt Amplifier    Level 3: nodeName(5) = NetPrice    Level 3: text(5) = 750    Level 3: nodeName(6) = Quantity    Level 3: text(6) = 511 'Remaining row nodes omitted for brevity Level 0: nodeName(2) = sqlresultstream:SqlResultCode Level 0: text(2) = 0  Level 1: nodeName(0) = #text  Level 1: text(0) = 0 

The preceding sample output demonstrates that the data you need to populate a list box or table is contained in the third-level child nodes.

Populating a List Box with XML Data

graphics/power_tools.gif

The AlphaTest.mdb sample application in your \Program Files\Seua11\Chaptr31\Alpha folder executes the Alpha service's four Web methods to populate a list box with 10, 25, 50, or 100 items (see Figure 31.9). The products in the list are real, but the prices are fictitious. AlphaTest.mdb has five WSR-generated classes, but uses the clsws_Alpha class only. The AlphaCopy.mdb example doesn't include unneeded class modules.

Figure 31.9. The frmAlphaTest form of the AlphaTest.mdb application consumes the four methods of the online Alpha Web service and displays the data in an Access list box.

graphics/31fig09.jpg

Tip

If you can't connect to the public Alpha Web service at http://www.oakleaf.ws/sqlxml/alpha[?wsdl], the "Creating and Consuming a Local Alpha Web Service" section, near the end of the chapter, shows you how to provide the service from your computer.


The following event handler generates the SOAP request message for the GetTop10 Web method:

 Private Sub cmdGetTop10_Click()    'Event handler for Alpha SQLXML 3.0 stored procedure XML Web services    Me.Caption = "Alpha Electronics, Inc. - Top 10 Amplifiers by Net Price"    Dim wsAlpha As New clsws_Alpha    Set objResponse = wsAlpha.wsm_GetTop10("AMPL", "XXXX", _      "XXXX", "XXXX", "XXXX", "XXXX")    Call PrintNodeList(objResponse)    Call IterateNodes End Sub 

The other three event handlers replace XXXX with valid category codes, such as DVDP (DVD players), TVRC (TV receivers), and CDPL (CD players). The event-handler invokes the PrintNodeList procedure, which is contained in the modNodeList module.

Listing 31.2 demonstrates a simple approach to iterating the <row> nodes and adding data items to a list box. In this case, you add a list box item with a semicolon-separated string that contains multiple child element data (text) values. This approach for filling multicolumn list boxes requires you to replace commas and semicolons in the returned values with another character, such as a hyphen.

Listing 31.2 The IterateNodes subprocedure tests the validity of the SOAP response message and adds an item to the list box for each <row> element.
 Private Sub IterateNodes()   'Iterate nodes and display results in a list box   Dim nodXml As MSXML2.IXMLDOMNode   Dim nodRow As MSXML2.IXMLDOMNode   Dim intRows As Integer   Dim intRow As Integer   Dim intCol As Integer   Dim strItem As String   Dim strTemp As String   intRows = lstData.ListCount - 1   On Error Resume Next   For intRow = 0 To intRows     'Clear the list box     lstData.RemoveItem (0)   Next intRow   On Error GoTo 0   With objResponse    If .length = 3 Then      'Test sqlResultStream:SqlResultCode     If .Item(2).Text = "0" Then        'sqlResultStream:SqlXml is second item       Set nodXml = objResponse.Item(1).childNodes(0)     Else       MsgBox "SQL Server returned an error.", _        vbOKOnly + vbExclamation, "Request Failed"       Exit Sub     End If    Else     MsgBox "Incorrect node list length (" & _      .length & "); should be 3.", _      vbOKOnly + vbExclamation, "Request Failed"     Exit Sub    End If   End With   With nodXml     'Iterate by row     For intRow = 0 To nodXml.childNodes.length - 1      Set nodRow = nodXml.childNodes.Item(intRow)      strItem = ""      With nodRow        'Iterate by column to generate the list entry        'with semicolon-separated values       For intCol = 0 To nodRow.childNodes.length - 1         'Replace , and ; with -         strTemp = Replace(nodRow.childNodes.Item(intCol).Text, ",", "-")         strTemp = Replace(strTemp, ";", "-")         If intCol = 5 Then            'Add currency format            strTemp = Format$(CSng(strTemp), "$0.00")           End If           strItem = strItem + strTemp + ";"        Next intCol       End With       strItem = Left$(strItem, Len(strItem) - 1)       'Add the  row to the Access list box       lstData.AddItem strItem     Next intRow   End With End Sub 

TheSet nodXml = objResponse.Item(1).childNodes(0) statement retrieves the SqlXml node. SqlXml's child nodes are <row> elements assigned to the nodRow variable, which represents a list box item, in the first For...Next loop. The second For...Next loop generates the semicolon-separated item string.

Filling a Table with a Complex SOAP Response Message

Populating local Jet or SQL Server tables with data from Internet-accessible Web services lets you update remote Access applications with new reference information. As an example, you can provide customers, sales agents, distributors, and others in the supply chain with up-to-date information on new or discontinued products, price changes, inventory levels, and related product information. The process is similar to one-way Access briefcase replication or SQL Server merge replication. Many firewalls block binary replication traffic; using a Web service makes the updates available to anyone with an Internet connection. Usernames and passwords in the SOAP request message can authenticate service consumers. HTTPS provides security by encrypting the SOAP request and response messages between endpoints.

graphics/globe.gif

SQLXML3Alpha.mdb is a sample Web service consumer that's similar to AlphaTest.mdb (see Figure 31.10). The application's unbound form and bound subform design are based on a demonstration ASP.NET Web service consumer page at http://www.oakleaf.ws/SQLXML3/ (see Figure 31.11). Both consumers have option buttons to select one of the four GetTop### stored procedures and check boxes to determine the product categories included in the list. The primary difference between the two projects is the ASP.NET page's substitution of a Visual Studio .NET DataGrid, which the SOAP response message's XML payload populates directly, for the Access version's table and bound subform.

Figure 31.10. The SQLXML3Alpha.mdb application's frmTopNProducts form has controls to select the number of records and categories. The bound sbfTopN subform displays the records added to its record source, tblTopNProducts.

graphics/31fig10.jpg

Figure 31.11. The original version of frmTopNProducts is this ASP.NET Web form that displays the product information in a DataGrid control, which expands vertically to display all returned rows.

graphics/31fig11.jpg

Code to populate a table from an IXMLDomNodeList object is similar to that of the preceding section's IterateNodes subprocedure. Listing 31.3 is the VBA code to add records to the tblTopNProducts table. Code for adding rows to or updating the table is simpler than that for adding items to a list box.

Listing 31.3 The PopulateTable subprocedure is a simplified version of AlphaTest.mdb's IterateNodes code.
 Private Sub PopulateSubform(objResponse As _    MSXML2.IXMLDOMNodeList)   'Iterate nodes and display results in a subform   Dim nodXml As MSXML2.IXMLDOMNode   Dim nodRow As MSXML2.IXMLDOMNode   Dim intRows As Integer   Dim intRow As Integer   Dim intCol As Integer   Dim strData As String   With objResponse    If .length = 3 Then      'Test sqlResultStream:SqlResultCode      If .Item(2).Text = "0" Then         'sqlResultStream:SqlXml is second item         Set nodXml = objResponse.Item(1).childNodes(0)      Else       DoCmd.Hourglass False       MsgBox "SQL Server returned an error.",        vbOKOnly + vbExclamation, "Request Failed"       Exit Sub      End If    Else     DoCmd.Hourglass False     MsgBox "Incorrect node list length (" & .length & _       "); should be 3.", _      vbOKOnly + vbExclamation, "Request Failed"     Exit Sub    End If   End With   Me.sbfTopN.Form.AllowAdditions = True   With Me.sbfTopN.Form.Recordset    'Iterate by row    For intRow = 0 To nodXml.childNodes.length - 1     'New row     .AddNew     Set nodRow = nodXml.childNodes.Item(intRow)     'Add data to table by column     For intCol = 0 To nodRow.childNodes.length - 1      strData = nodRow.childNodes.Item(intCol).Text      .Fields(intCol).Value = strData     Next intCol     .Update    Next intRow   End With   Me.sbfTopN.Form.AllowAdditions = False End Sub 

The table defines the data type for each column, so you don't need to use the CCur or CInt functions to change the String datatype to Currency or Integer for the NetPrice and Inventory fields. VBA handles the Variant type change, which developers call Evil Type Coercion (ETC), automatically.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net