Solved

[ASP, XML] Problem with Null values in recordset?

Posted on 2003-11-21
32
1,522 Views
Last Modified: 2013-11-19
Okay, full source example...

<%@ LANGUAGE="VBSCRIPT" %>
<%
  Response.Buffer = True
  Response.ExpiresAbsolute = 0
  Response.ContentType = "text/xml"

  dim strQuery
  strQuery = ""

  Sub XMLHeader (strResult, strQuery)
    Response.Write("<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCRLF)
    Response.Write("<?xml-stylesheet type=""text/xsl"" href=""https://wim.ws.conplacer.com/Table.xsl""?>" & vbCRLF)
    Response.Write("<Table Result=""" & strResult & """ Query=""" & strQuery & """>" & vbCRLF)
  end Sub

  Sub XMLFooter
    Response.Write("</Table>" & vbCRLF)
    Response.End
  end Sub

  Sub CheckForErrors (OBJdbConnection)
    if (OBJdbConnection.Errors.Count > 0) then
      Response.Clear
      XMLHeader "false", strQuery
      if (OBJdbConnection.Errors.Count = 1) then
        Response.Write("<Error Index=""0"">There is one database error.</Error>" & vbCRLF)
      else
        Response.Write("<Error Index=""0"">There are " & OBJdbConnection.Errors.Count & " database errors.</Error>" & vbCRLF)
      end if
      for I = 0 to OBJdbConnection.Errors.Count - 1
        Response.Write("<Error Index=""" & I + 1 & """>" & OBJdbConnection.Errors(I).Description & "</Error>" & vbCRLF)
      next
      XMLFooter
    elseif (Err.Number <> 0) then
      Response.Clear
      XMLHeader "false", strQuery
      Response.Write("<Error Index=""0"">" & Err.Description & "</Error>" & vbCRLF)
      Response.Write("<Error Index=""1"">Source = " & Err.Source & "</Error>" & vbCRLF)
      XMLFooter
    end if
  end Sub

  Sub ShowDatabase (OBJdbConnection)
    Set SQLStmt = Server.CreateObject("ADODB.Command")
    SQLStmt.CommandType = 1
    if (Statement = "") then
      strQuery = "select * from " & TableName
    else
      strQuery = Statement
    end if
    SQLStmt.CommandText = strQuery
    Set SQLStmt.ActiveConnection = OBJdbConnection
    Set RS = Server.CreateObject ("ADODB.Recordset")
    RS.Open SQLStmt

    XMLHeader "true", SQLStmt.CommandText
    Response.Write("<Layout>" & vbCRLF)
    For i = 0 to RS.Fields.Count - 1
      Response.Write("<Fields Index=""" & i & """>" & RS(i).Name & "</Fields>" & vbCRLF)
    next
    RecID = 0
    Do While Not RS.EOF
      Response.Write("<Records RecordNumber = """ & RecID & """>" & vbCRLF)
      RecID = RecID + 1
      For i = 0 to RS.Fields.Count - 1
        if RS.Fields(i).Value = "" then
          Response.Write("<Field Index=""" & i & """></Field>" & vbCRLF)
        else
          Response.Write("<Field Index=""" & i & """>" & Server.HTMLEncode(RS.Fields(i).Value) & "</Field>" & vbCRLF)
        end if
      next
      Response.Write("</Records>" & vbCRLF)
      RS.MoveNext
    Loop
    Response.Write("</Layout>" & vbCRLF)
    XMLFooter

  end Sub

  TableName = Request("tablename")
  if TableName = "" then TableName = Request.form("tablename")
  Statement = Request("statement")
  if (Statement = "") then Statement = Request.form("statement")

  if (Statement = "") and (TableName = "") then
    XMLHeader "false", strQuery
    Response.Write("<Error Index=""0"">No table name or statement specified.</Error>" & vbCRLF)
    XMLFooter
  else
    Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
    on error resume next
    OBJdbConnection.ConnectionTimeout = 300
    OBJdbConnection.CommandTimeout = 15
    OBJdbConnection.Open ("Provider=SQLOLEDB.1;UID=Username;PWD=Password;Persist Security Info=False;Initial Catalog=Catalog;Data Source=DataSource")
    ShowDatabase (OBJdbConnection)
    CheckForErrors (OBJdbConnection)

    OBJdbConnection.Close
    Set OBJdbConnection = Nothing
  end if
%>

Okay, explanation. This code connects to a database to select data from it using a query that is specified by the user. There is some nice web page that asks the user to enter either a tablename or query. Above code works and works quite well. The resulting page is not a HTML page but an XML page and this XML file has a related transformation that will turn the raw xml data into a nicely formatted page.
Basically, I'm playing around a bit, seeing the strengths of XML with ASP and XSLT to generate a nice select-what-you-like system. Basically just for testing purposes.

The database I connect to is an SQL Server database. And one of the tables in this database contain fields with the value Null in it. In other words, they are empty. And for some weird reason I get a "Type mismatch" error with "Microsoft VBScript runtime error" as the source of the error. So somewhere in the "For i = 0 to RS.Fields.Count - 1 /.../ Next" loop where I write the field values I get this error.
Now, Type mismatch? I thought ASP was typeless???

Yeah, I am amazed about this too. And yes, it could be rewritten differently and I could use RS(i) instead of RS.Fields(i).Value but that's not the problem. Even with RS(i) it will fail for the same reason. And no, it's not the XML output that generates the error but the ASP code itself.

Now, why does this piece of ASP code hate my null fields so much?
0
Comment
Question by:Wim ten Brink
  • 9
  • 7
  • 7
  • +2
32 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9798222
Why not do this:

    SQLStmt.CommandText = strQuery
    Set SQLStmt.ActiveConnection = OBJdbConnection
    Set RS = Server.CreateObject ("ADODB.Recordset")
    RS.Open SQLStmt
    RS.Save "yourquery.xml" , 1 'adPersistXML

Then you will have your xml file straight away.

FtB
0
 
LVL 15

Assisted Solution

by:deighc
deighc earned 100 total points
ID: 9798283
Could you highlight the line where the error is occuring?? Saves having to look thru all your code ;-)

But....

If you want to dump a recordset to XML there's an easier way. Later versions of ADO natively support persisting data to XML. Once you have a recordset object you can stream an XML'ised version of it directly to the ASP Response buffer or into an XML DOM (if you want to do additional processing on it). This is faster than looping the recordset and making your own xml string by a factor of 100 or more. And it's easy:

<%
Response.ContentType = "text/xml"
' Code in here which connects to database and creates a recordset object called rs
' Write out XML string directly to the Resonse buffer
rs.Save Response, adPersistXML ' ADO constant (value = 1)
' Clean up objects here
%>

But this is not much use for you if you want to have addition XML tags in your output (eg <? xml-stylesheet ?>

In this case you should dump the recordset into an XML DOM

<%
Response.ContentType = "text/xml"
' You may be using a different XML DOM version in which case check the ProgID
set xmlDOM = Server.CreateObject("MSXML2.FreeThreadedDOMDocument.4.0")
xmlDOM.setProperty "SelectionNamespaces", "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'"

' Code in here which connects to database and creates a recordset object called rs

' Write out XML string directly to the Resonse buffer
rs.Save xmlDOM, adPersistXML

' Do additional processing on the XML in here

' Output the XML DOM to the Response buffer
xml.Save Response

' Clean up objects here
%>

The line

xmlDOM.setProperty "SelectionNamespaces", "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'"

is very important because you the XML created by ADO uses namespaces. You won't be able to use XPath queries on the DOM unless you declare the namespaces. And you have to do a similar thing in .xsl files as well. The first line should be:

<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">

I use this method all the time. I'm a big fan of client-side XSLT transformations. The biggest advantage in my opinion is that you have the entire source XML document available to you on the client via script (by using document.XMLDocument).

Hope this helps. And if not, just mention the line where you errors occuring and I'll have a look...
0
 
LVL 15

Expert Comment

by:deighc
ID: 9798286
Oops...

FtB beat me to it....
0
 
LVL 15

Expert Comment

by:deighc
ID: 9798313
FtB,

The only "problem" with you suggestion is that it doesn't allow you to add additional nodes to the XML. And it makes more sense in this case to output directly to the Response buffer rather than to a file (in my humble opinion).
0
 
LVL 15

Expert Comment

by:deighc
ID: 9798334
Oops again...

In my second example (saving the recordset to an XML DOM)

the comment

' Write out XML string directly to the Resonse buffer
rs.Save xmlDOM, adPersistXML

Should say

'Stream the recordset XML into the XML DOM
rs.Save xmlDOM, adPersistXML
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9798404
Well, I am very, very green in the XML area....


If you do the rs.save path, 1

what is the best way to display that data? Is there an easy way to connect the xml file and an xsl template to do this?

Also, is there a way to save this data so that it looks more like a regular xml file?

Should I put this in a different thread so that I am not piggybacking on someone else's question?

FtB
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9798930
FtB, I can't save the recordset just as XML since I have to use a specific XML format, which differs from whatever ADO generates. Furthermore, the ASP page has no write access to the hard disk, just access to the database. Makes it a bit difficult to save the recordset as XML.
The resulting XML file can either be opened from an internet browser which will also trigger the transformation or it will be read by a thin-client (Delphi) application which expects the result to match a specific XML schema.

The error is generated in this block:
     For i = 0 to RS.Fields.Count - 1
        if RS.Fields(i).Value = "" then
          Response.Write("<Field Index=""" & i & """></Field>" & vbCRLF)
        else
          Response.Write("<Field Index=""" & i & """>" & Server.HTMLEncode(RS.Fields(i).Value) & "</Field>" & vbCRLF)
        end if
      next
And it is RS.Fields(i).Value (or RD(i) instead) that fails.

If I get this example to work, I will make it even a bit more complicated by returning multiple recordsets in one go. And basically, as I said before, I am playing around and just want to know why I get a type mismatch error in a typeless language?

However, the xmlDOM solution does look interesting. I might save the ADO recordset into an xmlDOM object, then transform that xmlDOM document on the serverside to the desired output. That output can then be transformed on the client side.
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 200 total points
ID: 9799030
Try:

  if isNull( RS.Fields(i).Value) or RS.Fields(i).Value = "" then


FtB
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9799053
And FtB, I don't mind you piggybacking on this question. :-) I can relatively easy answer them myself...

If you can access the contents of a ADO recordset as XML then you can also transform that XML by using XSLT. If you call this ASP page, the result will just be pure XML so no problem with that. However, Internet Explorer reads the XML stylesheet <?xml-stylesheet type="text/xsl" href="https://wim.ws.conplacer.com/Table.xsl"?>" and will recognise the included transformation. Thus, IE will transform the XML result on the client side by downloading the stylesheet and then transform it. The combination can result in some pretty nice effects... I have been playing around a bit with the transformation stuff and because the XML output I generate contains some additional information I can make it look very nice.
I wished I had a nice example of all this right now but unfortunately the server I'm using is not connected to the outside world...
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9799254
Thanks for the tip. BTW, did the post above help at all with your issue?

Here is the thing. I can do this:

**************
DataContent.xml
**************

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="DataContent.xsl"?>
<DataContent>
 <person>
   <name>Taufeeq Umar</name>
   <detail>lbw b Boje</detail>
   <age>39</age>
 </person>
 <person>
   <name>Salim Elahi</name>
   <detail>c Smith b Ntini</detail>
   <age>18</age>
 </person>
</DataContent>

**************
DataContent.xsl
**************

<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/">
 <html>
 <body>
   <h2>Data Content Display</h2>
   <table border="0" cellspacing="2">
   <tr bgcolor="gray">
     <th align="left" width="100">Name</th>
     <th align="left" width="150">Detail</th>      
      <th align="left" width="50">Age</th>
   </tr>
   <xsl:for-each select="DataContent/person">
   <tr>
     <td><xsl:value-of select="name"/></td>
     <td><xsl:value-of select="detail"/></td>      
      <td><xsl:value-of select="age"/></td>
   </tr>
   </xsl:for-each>
   </table>
 </body>
 </html>
</xsl:template></xsl:stylesheet>

Step two: you now call these two files through an asp page to connect the data and the formatting elements:

**************
DataContent.asp
**************

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%
 sourceFile = Server.MapPath("DataContent.xml")
 styleFile = Server.MapPath("DataContent.xsl")
 
 set source = Server.CreateObject("Microsoft.XMLDOM")
 source.async = false
 source.load(sourceFile)
 set style = Server.CreateObject("Microsoft.XMLDOM")
 style.async = false
 style.load(styleFile)
 Response.Write source.transformNode(style)
%>

and everything works cross browser. However, if I try to work with the results of saving the recordset to xml, I guess that I have to do something different to get that to work. So, can I somehow change the format that ADO exports into the more standard format, or do I need to modify something else?

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9801352
Let me make a totally different suggestion.  If you are using SQL 2K than you can use the FOR XML clause to out put the XML directly from SQL Server. This is by far the most efficient way of creating your XML documents. 90% of our Stored Procedures that return a resultset use For XML and they work extremely well.

Let me know if this is the case and if you need a sample.

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9802691
Anthony--

Thank you for the tip. Is there a similar way to do this with other DBMSs including Access, MySql and etc.?

Also, do you have any wisdom about changing the resulting format of rs.save path adPersistXML?

--FtB
0
 
LVL 28

Assisted Solution

by:sybe
sybe earned 100 total points
ID: 9802762
It looks to me that the error you get is because Server.HTMLEncode() can not take a null value as a parameter

I would do this to prevent it. Change:
=====================
     For i = 0 to RS.Fields.Count - 1
        if RS.Fields(i).Value = "" then
          Response.Write("<Field Index=""" & i & """></Field>" & vbCRLF)
        else
          Response.Write("<Field Index=""" & i & """>" & Server.HTMLEncode(RS.Fields(i).Value) & "</Field>" & vbCRLF)
        end if
      next
=====================
To
=====================
     For i = 0 to RS.Fields.Count - 1
          Response.Write("<Field Index=""" & i & """>" & Server.HTMLEncode("" & RS.Fields(i).Value) & "</Field>" & vbCRLF)
      next
=====================
where the trick is in:
    Server.HTMLEncode("" & RS.Fields(i).Value)
which forces the parameter for Server.HTMLEncode to be a String


BUT, and there's a big but. Server.HTMLEncode will generate invalid XML. For example  "é" will translate to "&eacute;" and that is invalid in XML.

It's a far better method to let the XML-object do the transformation. I use a function like this to transform a recordset into XML:

Function RecordsetToXML(ByVal oRS, ByVal sRootName, ByVal sRecordName, ByVal iXmlFormat)
    ' iFormat is not being used currently, maybe later....
    Dim oXML, aFields, i, oRoot, oRecordNode, oNode

    Set oXML = Server.CreateObject("Msxml2.DOMDocument.3.0")
    Set oRoot = oXML.createElement(sRootName)
    oXML.appendChild oRoot

    ReDim aFields(oRS.Fields.Count-1)
    For i = 0 To oRS.Fields.Count-1
        aFields(i) = oRS.Fields(i).Name
    Next
    oRS.MoveFirst
    Do While Not oRS.EOF
        Set oRecordNode = oXML.createElement(sRecordName)
        For i = 0 To Ubound(aFields)
            Set oNode = oXML.createElement(aFields(i))
            oNode.text = Cstr("" & oRS(i).Value)
            oRecordNode.appendChild oNode
        Next
        oRoot.appendChild oRecordNode
        oRS.MoveNext
    Loop
    Set oNode = Nothing
    Set oRecordNode = Nothing
    Set oRoot = Nothing
    oRS.MoveFirst

    Set RecordsetToXML = oXML
End Function


0
 
LVL 28

Expert Comment

by:sybe
ID: 9802779
>> I wished I had a nice example of all this right now but unfortunately the server I'm using is not connected to the outside world...

look at http://www.tivoli.nl and go to "Agenda", you'll see XML/XSL working. If you do a "view source" on the agenda-page, you'll see it's XML.
Advantages:
- saves bandwidth (the XML and XSL together are a lot smaller then the generated HTML)
- speed on the client: once you have the XML/XSL on the client, searching means that you don't have top return to the server, it can all be handled with javascript on the client.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9802836
>>Thank you for the tip. Is there a similar way to do this with other DBMSs including Access, MySql and etc.?<<
Not that I know of.

>>Also, do you have any wisdom about changing the resulting format of rs.save path adPersistXML?<<
XSLT is the way to go.  In fact I have somewhere an XSLT that converts the XML generated to a simple HTML page, I used to use with SQL Server 7 for basic utility type apps.  This was quite handy, as with my very limited HTML skills it allowed me to worry about the query and not have to focus on the presentation. Again this was not for production stuff, just your regular "quick and dirty" app.

But the questioner, would need to create an XSLT to transform XML generated by ADO into the format that they use.  Not difficult, just time consuming.  With my approach they could skip this extra step entirely and let the server generate the XML in the format that they want.  This IMHO should be more efficient.

Anthony
0
 
LVL 28

Expert Comment

by:sybe
ID: 9802872
Of course Anthony is right that database-generated XML is the easiest. Unfortunately i don't know how ACCESS can generate XML (as SQL Server can do).

I don't really see the need to transform the ADO-generated-XML before it can be applied with an XSL. It's not that hard to create an XSL that works with the ADO-generated-XML which gives the same result. It's just a matter of a small adaptation of the XSL.

Another thing is that XML-as-a-file is faster then generating a recordset from the database and transforming that to XML. Especially because XML is being cached by IIS. The problem of "no write-rights" seems strange if it's an application with an ACCESS database. The IUSR_machinename must have write rights to the directory where the ACCESS database is, in order to make changes to the database. The very same directory can be used to store XML as a file. That directory might be outside of the webroot, or for another reason inaccessible for browsers, but it's easy to write an ASP that can pick the XML up from there.

In the case that the tabe-contents isn't updated frequently, i'd really recommend to store the XML as a file, and only change it whenever the contents of the related table in the database has been changed. That really improves performance.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9803693
>>Unfortunately i don't know how ACCESS can generate XML (as SQL Server can do). <<
You right, I don't believe it can (and I said so in my previous comment).  But the point is, that this question is about using MS SQL Server not MS Access and therefore we should be prepared to offer all alternatives, so that the "best" solution can be reached.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9803696
sybe,

My comments may have sounded harsh.  Trust me, that was not my intention.

Anthony
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9809579
Anthony , I use MS Access right now but my solution needs to be database independant. For me this is just something to play around with. The server decides which database to use but the client provides the query. The result MUST be an xml file that is transformed client-side, because the client might not want to transform the resultset. IE will transform it but the tool I'm working on just wants raw XML. I could use XSLT to transform the ADO recordset into the XML resultset I want to return but the transformation to HTML is purely a client-side thing. I just need a single URL that reads like a webpage in a browser but which returns XML to any other application that loads data from it. Using the XSLT serverside is a nice option but that does not work for clients that just need the raw data.
As I said, I'm still playing around with it since I am just investigating new techniques for a future project. This project will have three components: A server accessing the database, a thin webclient based upon the webbrowser and a binary application running unattended and retrieving data from the server, from the same URL as the webclient.

Judgement time... fritz_the_blank actually provided me the right answer! I now use:
        if isNull(RS.Fields(i).Value) or RS.Fields(i).Value = "" then
          Response.Write("<Field Index=""" & i & """>&lt;Null&gt;</Field>" & vbCRLF)
        else
          Response.Write("<Field Index=""" & i & """>" & Server.HTMLEncode(RS.Fields(i).Value) & "</Field>" & vbCRLF)
        end if
And now I see "<Null>" for the empty fields. :-) Such a simple answer!

deighc gave some nice information about using the XML DOM on the client side. Haven't thought about that and will definitely look into that.

Since FtB wanted to piggyback on this question, I will also award points to sybe and acperkins for their remarks. Sybe of course for explaining why I get the runtime error. Indeed, HTMLEncode is unable to handle Null values and I should have thought about that. And also for suggesting to use the XML DOM again... :-) I tried to avoid that component but it seems quite useful in this case.

So, I suggest the following division of points:
200 points for fritz_the_blank for providing the solution :-D
100 points for deighc for pushing the XML DOM usage in ASP :-)
100 points each for Sybe and acperkins for their educational remarks. :-)
Total 500 points. I'll award them over 48 hours unless someone disagrees with this division.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9810128
This all sounds fair to me.

FtB
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9811204
Fair enough.  However (and as usual) I have some comments:

>> I use MS Access right now but my solution needs to be database independant.<<
I misunderstood (my apologies to sybe, as well), I saw "Provider=SQLOLEDB.1" in your original question and assumed you would always be using SQL Server.  Since this is not the case, the solution I suggested cannot be implemented.  I would like to clarify one thing though, with FOR XML EXPLICIT you generate the "raw" XML you need, it does not transform anything and has nothing to do with XSLT.

Anthony
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811232
@Anthony:

just in case you're bored with time to kill--

http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20806992.html


FtB
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9811981
@Anthony,
SQLOLEDB? Yeah, that's because I copied the connection string from a bit older version of my script file. The current version even has some new functionality now since it returns the amount of time it took for the database server to return the data and to have it parsed to XML. This information is what I want to keep an eye on the performance and is basically one of the reasons why I use my own XML format instead of whatever ADO generates.

@FtB,
Interesting question you posted. :-) Nicely related to this one.
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9811989
And oops. Since I promised to divide 500 points, I should increase the points costs too... :-)
0
 
LVL 15

Expert Comment

by:deighc
ID: 9812071
Workshop_Alex,

>> I want to keep an eye on the performance and is basically one of the reasons why I use my own XML format instead of whatever ADO generates.

You'll never replicate the performance of the native ADO functions. This is hugely optimised code and is EXTREMELY fast. Sure, you end with some funny schema's in there which you have to import into your XML DOM or reference in your XSL file but this is no big deal. The general formatting is perfecty clear and ledgible and you get a nice description of all the fields from your database (data type, length etc) as well as the data itself.

I went thru a similar evaluation process as you about a year ago and I couldn't get anything close to satisfactory performance unless I dumped XML directly from an ADO object. I tried looping thru a recordset and writing XML formatted strings directly to the Response buffer. I tried programatically creating an XML from a recordset and I tried using a VB string buffer to build up an XML string. These were all slower by a factor of 100's and the problems only increase if you're outputting many rows of data.

Maybe you'll discover a super-fast way of doing this in which case I'd like to see your results.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9812198
In reagards to my related question at:

http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20806992.html

Sparkplug has provided some very useful information.

FtB
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 9812379
If you are using the ADO generated XML here is a generic XSLT page I use, (plagiarized from somewhere I don't recall, about three years go and modified to work with XSLT).  The keyword is "generic" as in the results are not pretty, but should work on any XML document generated by ADO:

<?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:template match="/">
            <html>
                  <head>
                        <title>XSL-Formatted ADO Recordset</title>
                  </head>
                  <body>
                        <font face="Tahoma" size="2">
                              <table border="1" color="BLUE">
                                    <tr>
                                          <xsl:for-each select="xml/s:Schema/s:ElementType/s:AttributeType">
                                                <td>
                                                      <strong>
                                                            <font color="red" size="2">
                                                                  <xsl:value-of select="@name"/>
                                                            </font>
                                                      </strong>
                                                </td>
                                          </xsl:for-each>
                                    </tr>
                                    <xsl:for-each select="xml/rs:data/z:row">
                                          <tr>
                                                <xsl:for-each select="@*">
                                                      <td>
                                                            <font color="blue" size="2">
                                                                  <xsl:value-of select="."/>
                                                            </font>
                                                      </td>
                                                </xsl:for-each>
                                          </tr>
                                    </xsl:for-each>
                              </table>
                        </font>
                  </body>
            </html>
      </xsl:template>
</xsl:stylesheet>

Anthony
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9816727
@deighc,
It is true that native ADO is a lot faster, however I am already quite familiar with using native ADO. Normally, I'm using Borland Delphi to develop with, using COM+ as a nice, reasonable secure communication technique. However, COM+ doesn't always provide a good solution and these days web-development seems to become more and more important than before.

For performance I don't have to worry about the hardware. The server will be a dual-pentium IV at over 2 GHz running either W2K or 2003 server and the clients will also be at least Pentium IV's with at least 1 GHz speed. Memory is no problem either. The only problem is the connection the user will have between the server and client. Theoretically, the client could be a laptop with a low-speed connection that will have to pass several firewalls, routers and other nasty network obstacles. In general, the only thing that still works for these clients is HTTP, so a webbased solution would be ideal.
But if the network is slow, I need to collect data in a reasonable compact way and preferably keep the amount of traffic to a minimum. Thus, I cannot ask small amounts of data every time but will have to call groups of related data. My problem is not the time it takes for the server to parse or the client to transform the data. My problem is that the user might be connected through a 33K6 modem with a noisy phoneline.
Basically, I have two options: either let the server return CSV textfiles or use XML. If I use just CSV then my client application will be able to read it and the amount of data traffic is kept to a minimum. But I can't display the results in a webpage. If I use ADO-XML then I actually get a bit more information than I would like. I don't need to know data types or sizes, just the contents. And preferably I return master-detail records within a sngle XML file, which in ADO means using drecordsets with child-recordsets. Not really pretty. So I try to find a balance between CSV and ADO-XML, which is just what I'm trying to do now. Retrieve the records, parse the information to a more compact format and return the result.

Besides, I guess most queries will never have more than 50 to 75 records. The average will be around 10 records.

> Maybe you'll discover a super-fast way of doing this in which case I'd like to see your results.
Use faster hardware :-P

@FtB,
Yes, I've seen the response and it's quite useful. But if you want some other useful information, visit http://xmlspy.com/ and download the XMLSPY application. I use it too at this moment and it's a lot more user-friendly than Visual Studio in my opinion. It has a few nice XML examples and is great if you need to create XML schemas.
Or if you want a good book, buy/borrow/steal/copy the XML Pocket Consultant (ISBN 0-7356-1183-1) or XML in a nutshell (ISBN 0-596-00292-0) which both are lying on my desk already.

@Anthony,
Thanks for the XSL file. :-) Saves me a bit of time already. But XSLT isn't that difficult once you understand the "weird" syntax. Good example, though.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9818387
XMLSpy was one of the original XML utilities and has progressed very nicely over the last 4 years to be the preeminent XML tool today.  Unfortunately, the downside is that it is a complex and costly tool.  If you are just looking for an XML editor to transform your XML documents consider something like Xselerator (http://www.marrowsoft.com) this is a lot less expensive and complex than XMLSpy.  If you are more into the free category <g> than try Cooktop http://www.xmlcooktop.com/.  Finally and if you really have the spare time roll your own.  I have used all four approaches, but probably the one I have most experience with is Xselerator.

Anthony
0
 
LVL 15

Expert Comment

by:deighc
ID: 9818634
>> The only problem is the connection the user will have between the server and client.

True, the ADO generated XML is overkill in some cases. I've only ever used serious XML/XSLT stuff on intranets so bandwidth has never been a consideration for me.

But it's worth considering the fact that browsers SHOULD (which is not to say "definitely will"...) cache the .xsl files so, in theory, they'll only be downloading the source XML document for each page view. This may improve the situation for uses with slow connections. But still, I hear what you're saying.

And as a(nother) small detour...

You can use the <s:Schema> section of the ADO-generated XML do nice things on the client-side like validating form elements against recordset elements with the same name  (you can check their data type, length etc). From here you can build up an XML document on the client and post this back to the server (using XMLHTTP) instead of using a regular form post. You can capture the XML document on the server then use the <s:Schema> section again to set parameters in a stored procedure. Having a definition of the data set you're working with opens up the possibility to build some nice generic functions for working with the data. And as I said in an earlier post, one of the biggest advantages to doing a client side XSL transformation is that you still have full access to the underlying XML document. If this includes a data definition section then you have some nice options.

Just my 2c. Good luck with your project.
0
 
LVL 17

Author Comment

by:Wim ten Brink
ID: 9820157
@Anthony,
Cooktop is a good tool but it can't handle UTF-16 files. It also lacks the nice, graphical interface for schema files which I like a lot with XMLSPY. Xselerator seems to be a good tool to create transformations but doesn't have a good system for Schemas. For study purposes I started with the XMLSPY home edition which does have some limits but made it quite easy for me to quickly learn more about XML. :-)

And I must say, the way XMLSPY allows me to create schemas is wonderful. It converts the schema to a nice diagram showing the complete structure.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9820240
@Workshop_Alex:

Thank you and good luck


@Anthony:

Thank you very much for that generic xsl--studying that can teach one much!

FtB
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CSS HELP 6 74
replace quotes with UTF-8 character 38 69
Problem to file 3 50
ASP Focus problem 3 30
Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now