SQL2000, XML, ADO and SOAP 
Author Message
 SQL2000, XML, ADO and SOAP

Sorry for the cross-post.

We are designing a desktop application that uses remote procedure calls. Our
remote COM objects are currently retrieving data from SQL Server 2000 into
an ADO Recordset, persisting it as XML, passing it back to the client as
string data through SOAP. The client then reconstructs the recordset for
binding to a grid (or whatever). The problem is that performance sucks.

On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
boost our benchmark tests, but performance is still much slower then a DCOM
approach. (Their are too many authentication and security issues to make
DCOM a viable approach on various OS's).

The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that is
not compatible with that required by ADO to reconstruct a recordset.

Therefore, the question is...Is there a way to pass an ADO Recordset, the
persisted ADO recordset, or SQL Server XML that can reconstruct a recordset
through SOAP efficiently?



Sat, 28 Feb 2004 20:34:32 GMT
 SQL2000, XML, ADO and SOAP

First: If you use the FOR XML, XMLDATA clauses in your SQL, you shouldn't
receive that data in a recorset but in an Stream object.
Second: You can use the .Save method in any recordset to obtain the
subjacent XML.

Greetings.

Gabriel Culasso.



Quote:
> Sorry for the cross-post.

> We are designing a desktop application that uses remote procedure calls.
Our
> remote COM objects are currently retrieving data from SQL Server 2000 into
> an ADO Recordset, persisting it as XML, passing it back to the client as
> string data through SOAP. The client then reconstructs the recordset for
> binding to a grid (or whatever). The problem is that performance sucks.

> On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
> boost our benchmark tests, but performance is still much slower then a
DCOM
> approach. (Their are too many authentication and security issues to make
> DCOM a viable approach on various OS's).

> The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that
is
> not compatible with that required by ADO to reconstruct a recordset.

> Therefore, the question is...Is there a way to pass an ADO Recordset, the
> persisted ADO recordset, or SQL Server XML that can reconstruct a
recordset
> through SOAP efficiently?



Sun, 29 Feb 2004 12:10:50 GMT
 SQL2000, XML, ADO and SOAP
Gabriel,

Thank you for your response, however, perhaps I was not clear.

You are correct that when you use the FOR XML, XMLDATA clause you can send
that through the stream. However, it is imperative that on the client-side I
be able to reconstruct the ADO recordset. The schema that is supplied by SQL
Server does not allow that functionality.

Secondly, I mentioned that we are using the .Save() method of the recordset
to generate the XML, however, this seems to be the root of the performance
problems.

Thanks again,

Phil


Quote:
> First: If you use the FOR XML, XMLDATA clauses in your SQL, you shouldn't
> receive that data in a recorset but in an Stream object.
> Second: You can use the .Save method in any recordset to obtain the
> subjacent XML.

> Greetings.

> Gabriel Culasso.



> > Sorry for the cross-post.

> > We are designing a desktop application that uses remote procedure calls.
> Our
> > remote COM objects are currently retrieving data from SQL Server 2000
into
> > an ADO Recordset, persisting it as XML, passing it back to the client as
> > string data through SOAP. The client then reconstructs the recordset for
> > binding to a grid (or whatever). The problem is that performance sucks.

> > On the SOAP server, we have turned off "Nagling" as noted by Microsoft
to
> > boost our benchmark tests, but performance is still much slower then a
> DCOM
> > approach. (Their are too many authentication and security issues to make
> > DCOM a viable approach on various OS's).

> > The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema
that
> is
> > not compatible with that required by ADO to reconstruct a recordset.

> > Therefore, the question is...Is there a way to pass an ADO Recordset,
the
> > persisted ADO recordset, or SQL Server XML that can reconstruct a
> recordset
> > through SOAP efficiently?



Sun, 29 Feb 2004 20:13:17 GMT
 SQL2000, XML, ADO and SOAP
Hi Phil,

You may consider connecting directly to SQL Server through SOAP using XML
for Analysis. The XML/A ISAPI can handle SQL and MDX queries. No server-side
code for you to write. You will get back XML in the form of:
<row>
  <colunm1>value1</column1>
  <colunm2>value2</column2>
</row>
which is not in the ADO XML format. But you can use client-side XSLT to
transform it into the correct format (assuming you don't need the extra
meta-data present in the ADO format. Assuming your current performance
problems are server-side, this may be a solution.

If you are looking for sophisticated XML-driven graphical views for use in
portals and dashboards, you may want to take a look at our IntraSight
product (www.intrasight.com)

Chris Harrington


Quote:
> Sorry for the cross-post.

> We are designing a desktop application that uses remote procedure calls.
Our
> remote COM objects are currently retrieving data from SQL Server 2000 into
> an ADO Recordset, persisting it as XML, passing it back to the client as
> string data through SOAP. The client then reconstructs the recordset for
> binding to a grid (or whatever). The problem is that performance sucks.

> On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
> boost our benchmark tests, but performance is still much slower then a
DCOM
> approach. (Their are too many authentication and security issues to make
> DCOM a viable approach on various OS's).

> The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that
is
> not compatible with that required by ADO to reconstruct a recordset.

> Therefore, the question is...Is there a way to pass an ADO Recordset, the
> persisted ADO recordset, or SQL Server XML that can reconstruct a
recordset
> through SOAP efficiently?



Mon, 01 Mar 2004 10:34:19 GMT
 SQL2000, XML, ADO and SOAP
Did you try saving the XML into the Response (ASP) object and then catching
the answer into a dataisland in the browser?  The data island let you
navigate the XML as a recordset.

Greetings.

Gabriel Culasso.



Quote:
> Gabriel,

> Thank you for your response, however, perhaps I was not clear.

> You are correct that when you use the FOR XML, XMLDATA clause you can send
> that through the stream. However, it is imperative that on the client-side
I
> be able to reconstruct the ADO recordset. The schema that is supplied by
SQL
> Server does not allow that functionality.

> Secondly, I mentioned that we are using the .Save() method of the
recordset
> to generate the XML, however, this seems to be the root of the performance
> problems.

> Thanks again,

> Phil



> > First: If you use the FOR XML, XMLDATA clauses in your SQL, you
shouldn't
> > receive that data in a recorset but in an Stream object.
> > Second: You can use the .Save method in any recordset to obtain the
> > subjacent XML.

> > Greetings.

> > Gabriel Culasso.



> > > Sorry for the cross-post.

> > > We are designing a desktop application that uses remote procedure
calls.
> > Our
> > > remote COM objects are currently retrieving data from SQL Server 2000
> into
> > > an ADO Recordset, persisting it as XML, passing it back to the client
as
> > > string data through SOAP. The client then reconstructs the recordset
for
> > > binding to a grid (or whatever). The problem is that performance
sucks.

> > > On the SOAP server, we have turned off "Nagling" as noted by Microsoft
> to
> > > boost our benchmark tests, but performance is still much slower then a
> > DCOM
> > > approach. (Their are too many authentication and security issues to
make
> > > DCOM a viable approach on various OS's).

> > > The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema
> that
> > is
> > > not compatible with that required by ADO to reconstruct a recordset.

> > > Therefore, the question is...Is there a way to pass an ADO Recordset,
> the
> > > persisted ADO recordset, or SQL Server XML that can reconstruct a
> > recordset
> > > through SOAP efficiently?



Tue, 02 Mar 2004 14:52:29 GMT
 SQL2000, XML, ADO and SOAP

Quote:

>Sorry for the cross-post.

>We are designing a desktop application that uses remote procedure calls. Our
>remote COM objects are currently retrieving data from SQL Server 2000 into
>an ADO Recordset, persisting it as XML, passing it back to the client as
>string data through SOAP. The client then reconstructs the recordset for
>binding to a grid (or whatever). The problem is that performance sucks.

>On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
>boost our benchmark tests, but performance is still much slower then a DCOM
>approach. (Their are too many authentication and security issues to make
>DCOM a viable approach on various OS's).

>The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that is
>not compatible with that required by ADO to reconstruct a recordset.

>Therefore, the question is...Is there a way to pass an ADO Recordset, the
>persisted ADO recordset, or SQL Server XML that can reconstruct a recordset
>through SOAP efficiently?

If you're have performance problems why not try persisting the Recordset
using IPersistStream instead? I'm going to guess the
serialization/deserialization of this format give you better
performance. As long as all you want to do with it is turn it back into
the Recordset this will probably be the most efficient way to do so.
You'll have to pass it as binary data of course. Also, I'm not sure how
you'd do this in VB but in C++ you can just do:

bool serialize_rs(_Recordset *rs, HGLOBAL *hgp, unsigned int *szp)
{
    try {
        IStreamPtr streamPtr;
        IPersistStreamPtr persStreamPtr;
        HRESULT hr = 0;

        // first we create a stream which will write to memory we can get at
        hr = CreateStreamOnHGlobal(NULL, FALSE, &streamPtr);
        if (FAILED(hr)) {
            // [ deal with error ]
            return false;
        }

        ///////////////
        // then we query the ipersist stream interface from the record set
        ///////////////
        hr = rs->QueryInterface(__uuidof(IPersistStream),
reinterpret_cast<void **>(&persStreamPtr));
        if (FAILED(hr)) { /* deal with error */ }

        ///////////////
        // save the object to a stream using the IStream interface
        ///////////////
        hr = OleSaveToStream(persStreamPtr, streamPtr);
        if (FAILED(hr)) {
            /* do something about error */
            return false;
        }

        ///////////////
        // now we can extract the HGLOBAL where the memory is that the
recordset was
        // persisted to and also get the size of data in it
        ///////////////
        STATSTG streamStat;
        hr = streamPtr->Stat(&streamStat, STATFLAG_NOMEM);
        if (FAILED(hr)) {
            // deal with it
            return false;
        }

        // ok, now we have the size from the stream stat
        *szp = (unsigned int) streamStat.cbSize; // this assumes the
size is less than 2**32 bytes. you better hope it is

        // now just get the bytes and we're done
        hr = GetHGlobalFromStream(streamPtr, hgp);
        return true;
    }
    catch (com_error &ce)
    {
        // do something about this COM error
    }

Quote:
}

--
Jens B. Jorgensen



Thu, 08 Apr 2004 07:06:06 GMT
 SQL2000, XML, ADO and SOAP

Does someone have an example of sending binary data through SOAP?



Quote:

> >Sorry for the cross-post.

> >We are designing a desktop application that uses remote procedure calls.
Our
> >remote COM objects are currently retrieving data from SQL Server 2000
into
> >an ADO Recordset, persisting it as XML, passing it back to the client as
> >string data through SOAP. The client then reconstructs the recordset for
> >binding to a grid (or whatever). The problem is that performance sucks.

> >On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
> >boost our benchmark tests, but performance is still much slower then a
DCOM
> >approach. (Their are too many authentication and security issues to make
> >DCOM a viable approach on various OS's).

> >The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that
is
> >not compatible with that required by ADO to reconstruct a recordset.

> >Therefore, the question is...Is there a way to pass an ADO Recordset, the
> >persisted ADO recordset, or SQL Server XML that can reconstruct a
recordset
> >through SOAP efficiently?

> If you're have performance problems why not try persisting the Recordset
> using IPersistStream instead? I'm going to guess the
> serialization/deserialization of this format give you better
> performance. As long as all you want to do with it is turn it back into
> the Recordset this will probably be the most efficient way to do so.
> You'll have to pass it as binary data of course. Also, I'm not sure how
> you'd do this in VB but in C++ you can just do:

> bool serialize_rs(_Recordset *rs, HGLOBAL *hgp, unsigned int *szp)
> {
>     try {
>         IStreamPtr streamPtr;
>         IPersistStreamPtr persStreamPtr;
>         HRESULT hr = 0;

>         // first we create a stream which will write to memory we can get
at
>         hr = CreateStreamOnHGlobal(NULL, FALSE, &streamPtr);
>         if (FAILED(hr)) {
>             // [ deal with error ]
>             return false;
>         }

>         ///////////////
>         // then we query the ipersist stream interface from the record set
>         ///////////////
>         hr = rs->QueryInterface(__uuidof(IPersistStream),
> reinterpret_cast<void **>(&persStreamPtr));
>         if (FAILED(hr)) { /* deal with error */ }

>         ///////////////
>         // save the object to a stream using the IStream interface
>         ///////////////
>         hr = OleSaveToStream(persStreamPtr, streamPtr);
>         if (FAILED(hr)) {
>             /* do something about error */
>             return false;
>         }

>         ///////////////
>         // now we can extract the HGLOBAL where the memory is that the
> recordset was
>         // persisted to and also get the size of data in it
>         ///////////////
>         STATSTG streamStat;
>         hr = streamPtr->Stat(&streamStat, STATFLAG_NOMEM);
>         if (FAILED(hr)) {
>             // deal with it
>             return false;
>         }

>         // ok, now we have the size from the stream stat
>         *szp = (unsigned int) streamStat.cbSize; // this assumes the
> size is less than 2**32 bytes. you better hope it is

>         // now just get the bytes and we're done
>         hr = GetHGlobalFromStream(streamPtr, hgp);
>         return true;
>     }
>     catch (com_error &ce)
>     {
>         // do something about this COM error
>     }
> }

> --
> Jens B. Jorgensen




Sun, 11 Apr 2004 21:18:37 GMT
 SQL2000, XML, ADO and SOAP
Hi Phil and all!

We are running into the same kind of problems here...we are using XMLHTTTP
(with the IXMLHTTPRequest from msxml)
to query the SQL server 2000 database, but we can't find a way of constructing
an ADO recordset from the XML string returned. We tried with the FOR XML AUTO,
EXPLICIT,.., clause, nothing seems compatible with ADO.

I found a couple of articles and example using the DOMDocument::loadXML and
then opening a recordset ADO from that DOMDocument, but then again, the format
of string XML returned by the query cannot be understand by ADO recordset.

We are using ADO 2.6.

So my question to you is "Is there a way of making the XML string compatible
with ADO recordset, so that the client could reconstruct the recordset from that
XML string returned by the query?

Does anybody have ideas???

Thanks,

Marie-Eve

Quote:


> >Sorry for the cross-post.

> >We are designing a desktop application that uses remote procedure calls. Our
> >remote COM objects are currently retrieving data from SQL Server 2000 into
> >an ADO Recordset, persisting it as XML, passing it back to the client as
> >string data through SOAP. The client then reconstructs the recordset for
> >binding to a grid (or whatever). The problem is that performance sucks.

> >On the SOAP server, we have turned off "Nagling" as noted by Microsoft to
> >boost our benchmark tests, but performance is still much slower then a DCOM
> >approach. (Their are too many authentication and security issues to make
> >DCOM a viable approach on various OS's).

> >The FOR XML, XMLDATA clause of SQL Server 2000 returns an XML schema that is
> >not compatible with that required by ADO to reconstruct a recordset.

> >Therefore, the question is...Is there a way to pass an ADO Recordset, the
> >persisted ADO recordset, or SQL Server XML that can reconstruct a recordset
> >through SOAP efficiently?

> If you're have performance problems why not try persisting the Recordset
> using IPersistStream instead? I'm going to guess the
> serialization/deserialization of this format give you better
> performance. As long as all you want to do with it is turn it back into
> the Recordset this will probably be the most efficient way to do so.
> You'll have to pass it as binary data of course. Also, I'm not sure how
> you'd do this in VB but in C++ you can just do:

> bool serialize_rs(_Recordset *rs, HGLOBAL *hgp, unsigned int *szp)
> {
>     try {
>         IStreamPtr streamPtr;
>         IPersistStreamPtr persStreamPtr;
>         HRESULT hr = 0;

>         // first we create a stream which will write to memory we can get at
>         hr = CreateStreamOnHGlobal(NULL, FALSE, &streamPtr);
>         if (FAILED(hr)) {
>             // [ deal with error ]
>             return false;
>         }

>         ///////////////
>         // then we query the ipersist stream interface from the record set
>         ///////////////
>         hr = rs->QueryInterface(__uuidof(IPersistStream),
> reinterpret_cast<void **>(&persStreamPtr));
>         if (FAILED(hr)) { /* deal with error */ }

>         ///////////////
>         // save the object to a stream using the IStream interface
>         ///////////////
>         hr = OleSaveToStream(persStreamPtr, streamPtr);
>         if (FAILED(hr)) {
>             /* do something about error */
>             return false;
>         }

>         ///////////////
>         // now we can extract the HGLOBAL where the memory is that the
> recordset was
>         // persisted to and also get the size of data in it
>         ///////////////
>         STATSTG streamStat;
>         hr = streamPtr->Stat(&streamStat, STATFLAG_NOMEM);
>         if (FAILED(hr)) {
>             // deal with it
>             return false;
>         }

>         // ok, now we have the size from the stream stat
>         *szp = (unsigned int) streamStat.cbSize; // this assumes the
> size is less than 2**32 bytes. you better hope it is

>         // now just get the bytes and we're done
>         hr = GetHGlobalFromStream(streamPtr, hgp);
>         return true;
>     }
>     catch (com_error &ce)
>     {
>         // do something about this COM error
>     }
> }

> --
> Jens B. Jorgensen




Wed, 14 Apr 2004 02:40:16 GMT
 SQL2000, XML, ADO and SOAP
Marie-Eve,

To the best of my knowledge, there is no way of taking the XML returned by
SQL Server 2000's FOR XML Clause and building an ADO Recordset. Thus, my
decision to use the ADO Recordset.Save() to persist it as XML, or persisting
it directly to an ADO Command Stream.

Phil


Quote:
> Hi Phil and all!

> We are running into the same kind of problems here...we are using XMLHTTTP
> (with the IXMLHTTPRequest from msxml)
> to query the SQL server 2000 database, but we can't find a way of
constructing
> an ADO recordset from the XML string returned. We tried with the FOR XML
AUTO,
> EXPLICIT,.., clause, nothing seems compatible with ADO.

> I found a couple of articles and example using the DOMDocument::loadXML
and
> then opening a recordset ADO from that DOMDocument, but then again, the
format
> of string XML returned by the query cannot be understand by ADO recordset.

> We are using ADO 2.6.

> So my question to you is "Is there a way of making the XML string
compatible
> with ADO recordset, so that the client could reconstruct the recordset
from that
> XML string returned by the query?

> Does anybody have ideas???

> Thanks,

> Marie-Eve



Mon, 19 Apr 2004 00:59:35 GMT
 SQL2000, XML, ADO and SOAP
I needed to transform XML from SQL Server into a recordset for
consumption by the Datasource property in a VB data report.  I wrote a
messy procedure that did what I needed by transforming the SQL XML
into something that conforms to the ADO XML schema.

I would really appreciate any tweeks for this, or if someone could do
this via an XSLT transformation.

Public Function XML_TO_ADO(xmlString As String, RowNode As String) As
Recordset
    Dim MSXML As MSXML2.DOMDocument30, xmlNode As MSXML2.IXMLDOMNode,
ADOXML As String
    Dim NumAttributes As Integer, srmXMLStream As Stream, strData As
String
    Set MSXML = New MSXML2.DOMDocument30

    MSXML.loadXML xmlString
    xmlString = ""

    Set xmlNode = MSXML.selectSingleNode(RowNode).childNodes(0)
    If Not xmlNode Is Nothing Then
    '<XML> and Opening Schema Tags
        ADOXML = _
        "<xml xmlns:s=" & Chr(34) &
"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" & Chr(34) & " " & _
        "xmlns:rs=" & Chr(34) & "urn:schemas-microsoft-com:rowset" &
Chr(34) & " " & _
        "    xmlns:z=" & Chr(34) & "#RowsetSchema" & Chr(34) & "> " &
_
        "  <s:Schema id=" & Chr(34) & "RowsetSchema" & Chr(34) & "> "
& _
        "    <s:ElementType name=" & Chr(34) & "row" & Chr(34) & "
content=" & Chr(34) & "eltOnly" & Chr(34) & "> "

    'Attributes Go Here
        For NumAttributes = 0 To xmlNode.Attributes.Length - 1
            ADOXML = ADOXML & "<s:AttributeType name=" & Chr(34)
            ADOXML = ADOXML &
xmlNode.Attributes(NumAttributes).nodeName
            ADOXML = ADOXML & Chr(34) & " />"
        Next NumAttributes

    'Closing Schema Tags & Opening Data Tag
        ADOXML = ADOXML & _
        "<s:Extends type=" & Chr(34) & "rs:rowbase" & Chr(34) & "/>" &
_
            "</s:ElementType>" & _
          "</s:Schema><rs:data>"

    'Data Tags
        Do While Not xmlNode Is Nothing
            ADOXML = ADOXML & "<z:row "
            For NumAttributes = 0 To xmlNode.Attributes.Length - 1

                ADOXML = ADOXML &
xmlNode.Attributes(NumAttributes).nodeName & "=" & Chr(34)
                'ADOXML = ADOXML & "<![CDATA[" &
xmlNode.Attributes(NumAttributes).Text & "&]]>" & Chr(34) & " "
                strData = xmlNode.Attributes(NumAttributes).Text
                strData = Replace(strData, "&", "&")
                strData = Replace(strData, "<", "<")
                strData = Replace(strData, "> ", ">")
                strData = Replace(strData, Chr(34), "&")
                strData = Replace(strData, "'", "'")
                strData = Replace(strData, "", """)
                '<   <    3C
                '&   &  26
                '>   >   3E
                '"   "  22
                '   '  27
                ADOXML = ADOXML & strData & Chr(34) & " "
            Next NumAttributes
            ADOXML = ADOXML & "/>"
            Set xmlNode = xmlNode.nextSibling
        Loop
    'Close Data and XML Tags

        ADOXML = ADOXML & _
         "</rs:data>" & _
        "</xml>"

        Set srmXMLStream = New ADODB.Stream
        srmXMLStream.Open
        srmXMLStream.WriteText ADOXML
        srmXMLStream.Position = 0

        Set XML_TO_ADO = New ADODB.Recordset
        XML_TO_ADO.Open srmXMLStream  'srmXMLStream

    End If

End Function

Quote:

> Hi Phil and all!

> We are running into the same kind of problems here...we are using XMLHTTTP
> (with the IXMLHTTPRequest from msxml)
> to query the SQL server 2000 database, but we can't find a way of constructing
> an ADO recordset from the XML string returned. We tried with the FOR XML AUTO,
> EXPLICIT,.., clause, nothing seems compatible with ADO.

> I found a couple of articles and example using the DOMDocument::loadXML and
> then opening a recordset ADO from that DOMDocument, but then again, the format
> of string XML returned by the query cannot be understand by ADO recordset.

> We are using ADO 2.6.

> So my question to you is "Is there a way of making the XML string compatible
> with ADO recordset, so that the client could reconstruct the recordset from that
> XML string returned by the query?

> Does anybody have ideas???

> Thanks,

> Marie-Eve



Sun, 25 Apr 2004 00:31:31 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. SQL2000, XML, ADO and SOAP

2. SQL2000, XML, ADO and SOAP

3. ADO Problem? - VB6, ADO2.6, XML, SQL2000

4. SQL2000, SQLXML, SOAP toolkit, MSXML, SP install order?

5. SQL2000 and SOAP

6. XML and SOAP

7. Need help building SOAP client for XML for Analysis server

8. US-NJ: Englewood-Delphi XML SOAP Oracle Programmer/Analyst

9. Strange results when migrating from Soap Toolkit 2 to Soap Toolkit 3

10. Calling Web Services through Excel / VB/ SOAP Toolkit / XML

11. soap and xml

12. XML for Analysis using soap toolkit


 
Powered by phpBB® Forum Software