Microsoft KB Archive/258497

= How to:Transmit Data between Commerce Servers with Commerce Interchange Pipeline (CIP) =

Article ID: 258497

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft Site Server 3.0 Standard Edition

-



This article was previously published under Q258497



SUMMARY
This article gives an example of the steps necessary to transmit an ActiveX Data Objects (ADO) recordset on a Commerce Server computer to a Microsoft Excel spreadsheet on a different Commerce Server computer by using the Commerce Interchange Pipelines (CIPs).



MORE INFORMATION
Before you create the Active Server Pages (ASP) or the pipeline files, create a virtual application on the sending Commerce Server computer named "Transmitado" and a virtual application on the receiving Commerce Server computer named "ReceiveStandard." This example requires that you create a data source name (DSN) named "pubs" on the sending Commerce Server computer that maps to the SQL Server pubs database. This example requires the creation of the following four files:
 * Transmitado.asp - This file creates a recordset from the SQL server pubs database and parses the recordset into a Commerce Dictionary object that is composed of several Commerce SimpleList objects. There is one SimpleList object for each field in the recordset.
 * Transmit.pcf (called from the Transmitado.asp file) - This file maps the Commerce Dictionary object to XML, and then posts the Commerce Dictionary object to a specified Web server using the SendHTTP component.
 * Receivepipe.asp - This file receives the transmitted Commerce Dictionary object, loads a receiving Commerce Interchange Pipeline (CIP), and then passes the Commerce Dictionary object to the receiving CIP.
 * Receivehttp.pcf (called from the Receivepipe.asp file) - This file maps the Commerce Dictionary object from XML, and then parses the Commerce Dictionary object into a Microsoft Excel spreadsheet.

The Transmitado.asp file contains the following ASP code:

<%@ LANGUAGE=vbscript enablesessionstate=false LCID=1033 %> <% pipelinelocation = "d:\Inetpub\wwwroot\transmitado\transmit.pcf" pipeloglocation = "d:\Inetpub\wwwroot\transmitado\transmit.log"

Set MSCSMessageManager = Server.CreateObject("Commerce.MessageManager") Call MSCSMessageManager.AddLanguage("usa", &H0409) MSCSMessageManager.defaultLanguage = "usa" Set MSCSDataFunctions = Server.CreateObject("Commerce.DataFunctions") MSCSDataFunctions.Locale = &H0409 Set MSCSQueryMap = Server.CreateObject("Commerce.Dictionary") Set rsOrderItems = CreateObject("ADODB.Recordset")

CommandText = "Select emp_id, fname, lname, hire_date from employee" cursor = rsOrderItems.CursorLocation rsOrderItems.Cursorlocation = 3 rsOrderItems.Open CommandText,"DSN=pubs; UID= ; PWD= "

If rsOrderItems.RecordCount>0 then

Set dictionary=Server.CreateObject("Commerce.Dictionary") rsOrderItems.MoveFirst

Set dictionary.emp_id = Server.CreateObject("Commerce.Simplelist") Set dictionary.fname = Server.CreateObject("Commerce.Simplelist") Set dictionary.lname = Server.CreateObject("Commerce.Simplelist") Set dictionary.hire_date = Server.CreateObject("Commerce.Simplelist") Do Until rsOrderitems.EOF = True dictionary.emp_id.Add (rsOrderitems.Fields(0).Value) dictionary.fname.Add (rsOrderitems.Fields(1).Value) dictionary.lname.Add (rsOrderitems.Fields(2).Value) dictionary.hire_date.Add (rsOrderitems.Fields(3).Value) rsOrderitems.MoveNext Loop

Set pipeline = Server.CreateObject("Commerce.MtsTxPipeline") pipeline.LoadPipe(pipelinelocation) pipeline.SetLogFile(pipelog)

Set pipeContext = Server.CreateObject("Commerce.Dictionary") Set pipeContext("MessageManager")     = MSCSMessageManager Set pipeContext("DataFunctions")      = MSCSDataFunctions Set pipeContext("QueryMap")           = MSCSQueryMap pipeContext("SiteName")               = "TransmitADO" pipeContext("DefaultConnectionString") = "DSN=pubs; UID= ; PWD= " pipeContext("Language")               = "USA"

Set TransportDictionary = CreateObject("Commerce.Dictionary") Set TransportDictionary.object = dictionary errorLevel = pipeline.Execute(1,TransportDictionary, pipeContext, 0)

IF errorlevel = 0 then Response.Write("Transfer of records unsuccessful") Else Response.Write("Successfully transferred records") End if   Response.End else %> No Records to Process... <% end if %>

Save the Transmitado.asp file in the physical directory that maps to the Transmitado virtual application that you created on the sending Commerce Server computer.

The Receivepipe.asp file contains the following code:

<%   REM -- receive the orderform that is posted thru HTTP. Dim TransportDictionary set TransportDictionary = server.createobject("commerce.dictionary") TransportDictionary.working_data = request.form

Dim pipe set pipe = server.createobject("commerce.MtsTxPipeline") REM call pipe.SetLogfile("c:\InetPub\wwwroot\receivestandard\receive.log") call pipe.loadpipe("c:\InetPub\wwwroot\receivestandard\receivehttp.pcf")

Set ctx = Server.CreateObject("Commerce.Dictionary") errorlevel = pipe.execute(1, TransportDictionary, ctx, 0)

If Err Then '  Response.Status = "500 Receive Pipeline failed" Response.Write = Err.Description Response.End End If  On Error Goto 0 %>

Save the Receivepipe.asp file in the physical directory that maps to the ReceiveStandard virtual application that you created on the receiving Commerce Server computer.

Create the Transmit.pcf file by running the Win32 Pipeline Editor and creating a new CIP based on the Transmit.pct pipeline CIP template. To open the Win32 Pipeline Editor, perform one of the following:


 * Click Start, click Run, and then type Pipeeditor.exe. -or-


 * Click Start menu to Start, point to Programs, click Microsoft Site Server, click Commerce, and then click Pipeline Editor.

In Win32 Pipeline Editor, click File, click New, select the Transmit.pct template from the list of CIP templates, and then click OK.

When the transmit CIP template is running, add the following components to the CIP:


 * 1) Insert the MapToXML component in the Map stage.
 * 2) Insert the AddHeader component in the Add Header Stage.
 * 3) Insert the SendHTTP component in the Transport Stage.

Save the Transmit.pcf file in the physical directory that maps to the Transmitado virtual application that you created on the sending Commerce Server computer.

Configure the SendHTTP component by performing the following:   The URL: field contains the destination URL where the XML data is posted. (For example: ;/receivestandard/receivepipe.asp                    The Field to be posted: field contains the text "working_data" (without the quotation marks) and the As Type: option is set to Text. Click File, click Save As, and then save the CIP the Transmit.pcf file in the same directory as the Transmitado.asp file.

Create the ReceiveHTTP.pcf file by running the Win32 Pipeline Editor and creating a new CIP with the Receive.pct CIP template.

Open the Win32 Pipeline Editor, click File, click New, select the Receive.pct template from the list of CIP templates, and then click OK.

When the receive CIP template is running, add the following components to the CIP:

 Insert the OpenHeader component into the Open Header stage. Insert the MapFromXML component into the Map stage. Double-click the MapFromXML component to edit and verify that the XML Source Key is specified as "working_data" and that the Result Object Key is specified as an object.</li>  Insert the Scriptor component into the Application Integration stage, and then edit the internal VBScript code as follows: function MSCSExecute(config, orderform, context, flags) Dim xlapp Dim xlsheet Set xlapp = CreateObject("Excel.Application") spreadsheetlocation = "C:\Inetpub\wwwroot\ReceiveStandard\Book1.xls" xlapp.workbooks.open(spreadsheetlocation) xlapp.sheets.Add Set xlsheet = xlapp.sheets(1) xlsheet.cells(1,1)="emp_id" xlsheet.cells(1,2)="fname" xlsheet.cells(1,3)="lname" xlsheet.cells(1,4)="hire_date" for i = 1 to orderform.object("emp_id").count xlsheet.cells(i+1,1) = orderform.object("emp_id")(i-1) xlsheet.cells(i+1,2) = orderform.object("fname")(i-1) xlsheet.cells(i+1,3) = orderform.object("lname")(i-1) xlsheet.cells(i+1,4) = orderform.object("hire_date")(i-1) next xlapp.activeworkbook.Save xlapp.quit Set xlapp = Nothing MSCSExecute = 1 MSCSExecute = 1 end function </li> After you configure the CIP, click File, click Save As, and then save the Receivehttp.pcf file in the same directory as the Receivepipe.asp file.</li> Save an empty Microsoft Excel workbook "Book1.xls" in the same directory as the Receivepipe.asp file.</li></ol>

To test this example, point your browser to http:// /transmitado/transmitado.asp. The contents of the recordset that were generated on the sending Commerce Server computer and saved in the Book1.xls file in the ReceiveStandard virtual directory are displayed on the receiving Commerce Server computer.

Keywords: kbhowto kbfix KB258497

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.