In some cases you may want to send a binary stream to a server. One way to do so is to use the IXMLHTTPRequest object. This article demonstrates how to retrieve an ADO recordset from a server, modify it, and send it back as a stream of binary data.

This example uses the ADODB.Stream object to hold the binary data that is to be sent back to the server. If a newer version of MSXML has been installed in Side-by-Side mode, then to run the sample code with that specific version, you must explicitly use the GUIDs or ProgIDs for that version. For example, MSXML version 4 only installs in side-by-side mode. Please refer to the following article in the Microsoft Knowledge Base to see what code changes required to run the sample code with the MSXML 4.0 parser: Q305019 INFO: MSXML 4.0 Specific GUIDs and ProgIds.

For example, in the code below, you would create objects with MSXML 4.0 with the following statements:

• var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP.4.0");
• xmldoc = new ActiveXObject("Msxml2.DOMDocument.4.0");
• var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP.4.0");

To use XMLHTTP to send a binary stream to a server, follow these steps:

1. Paste the following code into a file in your default Web folder and name the file Receiver.asp.

   <%
   dim Connection
   dim rs
   Connection = "Provider=SQLOLEDB.1;Data Source=servername;User Id=username;Password=password;Initial Catalog=Northwind;"
   sql =  "Select * from Customers"
   
   
   set rs = server.CreateObject("ADODB.Recordset")
   
   if Request.QueryString("getRecordset") = "YES" then
   	rs.ActiveConnection = Connection
   	rs.CursorLocation = 3 'Client Side
   	rs.CursorType = 3 'Static Recordset
   	rs.LockType = 4 'Batch Optimistic
   	rs.Open sql
   	rs.Save response, 1 'persist adPersistXML
   	Response.End
   else
   	rs.open Request '.BinaryRead(Request.TotalBytes)
   	rs.activeconnection = Connection 'Reconnect
   	rs.updatebatch 'Update adAffectAll
   	rs.close
   	Response.Write "Recordset Saved" 'Send back response
   	Response.End
   end if
   
   %>

2. Paste the following code into a file in your default Web folder and name the file Sender.asp

   <SCRIPT ID=clientEventHandlersJS LANGUAGE=javascript>
   <!--
   
   var rs;
   var xmldoc; 
   var xmlstream;
   
   function SendRS_onclick() {
   	xmlstream = new ActiveXObject("ADODB.Stream");
   	xmlstream.Mode = 3; //read write
   	xmlstream.Open();
   	xmlstream.Type = 1; // adTypeBinary
   	rs.Save(xmlstream,0); //adpersistadtg
      	var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
      	xmlhttp.Open("POST","http://localhost/Receiver.asp?getRecordset=NO",false);
   	xmlhttp.setRequestHeader("Content-Length",xmlstream.Size); //set the length of the content
      	xmlhttp.send(xmlstream.Read(xmlstream.Size)); //Send the stream
      	alert(xmlhttp.responseText);
   }
   
   function getRS_onclick() {
   	rs = new ActiveXObject("ADODB.Recordset");
   	xmldoc = new ActiveXObject("Msxml2.DOMDocument");
      	var xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
      	xmlhttp.Open("Get","http://localhost/Receiver.asp?getRecordset=YES",false);
      	xmlhttp.send();
      	xmldoc.loadXML(xmlhttp.responseText); //load the returned stream into the dom document
      	rs.Open(xmldoc); //load the dom document into the recordset
      	alert("Recordset Loaded");
   }
   
   function Update_onclick() {
   	alert("before: " + rs.Fields(2).Value);
   	rs.Fields(2).Value = rs.Fields(2).Value + "!";
   	rs.Update();
   	alert("after: " + rs.Fields(2).Value);
   }
   
   //-->
   </SCRIPT>
   <INPUT type="button" value="Get Recordset" id=getRS name=getRS LANGUAGE=javascript onclick="return getRS_onclick()">
   <INPUT type="button" value="Update" id=Update name=Update LANGUAGE=javascript onclick="return Update_onclick()">
   <INPUT type="button" value="Send Recordset" id=SendRS name=SendRS LANGUAGE=javascript onclick="return SendRS_onclick()">
   					

3. Modify the Receiver.asp page so that the connection variable contains a Microsoft SQL Server name and a valid SQL userid and password.
4. Start Microsoft Internet Explorer and browse to http://localhost/sender.asp.
5. Click Get Recordset. A message box appears and tells you that the recordset was loaded successfully.
6. Click Update. A message box appears and shows you the value before the update. A second message box appears and shows you the value after the update.
7. Click Send Recordset. A message box appears and tells you that the recordset was updated.

Known Limitations and Recommendations

• Although this allows you to use the persist mechanism to pass the data back and forth to the client, it is recommended that you use UpdateGrams or OpenXML with SQL Server 2000 to pass and send recordset data in XML format.
• There are limitations in shaped recordsets. Edited shaped recordsets cannot be persisted in XML format. Also, parameterized shaped commands cannot be persisted at all. For additional information on persisting and limitations, see the following Microsoft Developer Network (MSDN) Web site:
  XML Persistence Format
  http://msdn.microsoft.com/en-us/library/ms676547.aspx (http://msdn.microsoft.com/en-us/library/ms676547.aspx)