February 22 2007 by 
Andrew Powell
Posted in ColdFusion | XML | AJAX | 
0 comments
February 13 2007 by Andrew Powell
Posted in ColdFusion | Flex | General | Conferences | Spry | Speaking | 0 comments
February 12 2007 by Andrew Powell
Posted in ColdFusion | General | Spry | ColdSpring | Mach-II | MachBlog | 5 comments
February 07 2007 by Andrew Powell
SELECT name, position FROM players
This, in ColdFusion, will return us a query with two columns, one for "name" and one for "position". We can make MSSQL server do most of the work in converting this to XML for us. I say "most of the work" because there is still some work needed to make this ready to send back to the client as true XML. If we want MSSQL Server to return us XML, we modify the above query like so:
SELECT name, position FROM players FOR XML AUTO
This will return us our query results as normal in ColdFusion, but there will not be a two-column query. There will be one column, populated with the returned XML. This statement arranges the data into xml attributes, like so:
<players name="player1" position="position1"/>
<players name="player2" position="position2"/>
<players name="player3" position="position3"/>
We can also return the data as XML elements using the syntax below:
SELECT name, position FROM players FOR XML AUTO, ELEMENTS
This will give us XML that looks like this:
<players>
<name>player1</name>
<position>position1</position>
</players>
<players>
<name>player2</name>
<position>position2</position>
</players>
<players>
<name>player2</name>
<position>position2</position>
</players>
Using the "elements" argument obviously results in much more verbose results. Typically, I try to stay away from using elements because it results in a much larger payload across the wire. Part of the philosophy of AJAX is that we want to improve the user experience. The less data we have to send down the wire means that the less time we have to wait for that data to load. The more we can do to reduce user wait times and improve the experience, the better.
Back to the issue at hand... The XML that we get back from MSSQL Server is not true XML, it is a XML FRAGMENT. This means that it does not have a root tag. We need to apply a root tag to this string to make it valid XML.
Another obstacle that you might run into when using this is that if your results are more than 8000 characters long, MSSQL Server will break your result into rows. This causes malformed XML when you try to apply root tags to the string, and that's no fun for anyone. We need a way to add that root tag and concatentate those rows, if we have them.
There is already such a solution. Russ Brown has created a nifty little function, that is available on CFLIB.org. It will take your raw query of XML returned from MSSQL Server (no matter if it's one row, or broken to many) and a string for the root tag you wish to use. The function will then return to you valid XML. This XML can then be returned to the browser with a content type of "text/xml".
Why would you do this? Let's say you have a data set of hundreds upon hundreds of rows with many columns. Do you want to loop over that query in CF and generate the XML with CFSAVCONTENT, or do you want to push that work off to the database? Since most databases are, typically, underutilized pushing the work to the database seems to be a better option. It reduces the work CF has to do and frees up resources much quicker for other processes, thereby enhancing the user experience by reducing executioin times.
Enhancing the user experience is what AJAX is all about.
Posted in ColdFusion | General | Spry | XML | AJAX | 9 comments