Ask a Jedi: Spry, Database, and XML Question

This post is more than 2 years old.

Michael asks:

I actually have a question about spry. This is my first time using spry ability and quite stuck with what I'm doing at the moment. I have implemented the spry function to my code with reading it from the xml and it's working fine. However, is there any way of spry to read straight from the SQL server? The program that I am creating has the ability to display in multiple languages. Once the xml is produced and able to be displayed on the screen, when I tried to change the language, it will not work as the xml is not changed. So I might have to read straight from the sql instead of going through xml first. Please let me know how to read it from the sql server with using spry.

I'm not SQL Server expert, but I know that there is built-in functionality to generate XML in a query. This is done using a FOR XML command. (See some examples at 15 Seconds.) That isn't the issue though. You want to point Spry directly at SQL Server, which as far as I know isn't possible. Your database server isn't a web server. You can't just point at it with a URL and get XML back. Even if SQL Server (or any db server) allowed for that, it would be pretty scary to let a URL run any query on your database.

What you want to do (if I read your question right) is to make Spry load a new URL everytime you change the language. You can hit one common CFM page that accepts a language URL and passes it on to the SQL Server via CFQUERY.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA

Archived Comments

Comment 1 by Boyan Kostadinov posted on 3/11/2008 at 3:59 PM

Actually, there is a way to expose the database through HTTP endpoints but only in SQL Server 2005. Check out this blog post:

Comment 2 by Joel Watson posted on 3/11/2008 at 4:31 PM

This could get expensive, depending on the number of languages and amount of data returned, but one could also use a filter, correct? That way, one could switch back and forth between languages without having to reload the dataset.

Comment 3 by Ray Buechler posted on 3/11/2008 at 4:44 PM

There a Dreamweaver extension called XML Export. It allows you export a recordset as XML. You are still generating XML but the XML is generated on the fly.

The extension is free when you purchase Adobe CS3.


Comment 4 by Raymond Camden posted on 3/11/2008 at 5:04 PM

@Boyan - I have 0 experience with that - but it scares me. ;) I assume they have security as to what you can expose? Seems like just one more thing to worry about.

@Ray - Generating XML is easy enough I wouldn't bother buying an extension. :) You can always use toxml.cfc, my XML making component.

Comment 5 by Boyan Kostadinov posted on 3/11/2008 at 5:22 PM

@Ray, yup, security is defined at the database level, check out the security section in this article:

Comment 6 by Raymond Camden posted on 3/11/2008 at 5:27 PM

Doesn't this just mean one more thing to secure? Like for example, if we say users of type X can get data Y, I'd secure this in my web app, and now I have to secure it at the db instead.

Comment 7 by Andrew posted on 3/11/2008 at 6:14 PM

I know exactly what the sender of this post is going through. I had the same issue when I was first introduced to Spry. If you are using Dreamweaver CS3 the free extension is the way to go in my opinion. If you're using CFEclipse, the toxml() is a good alternative. If you're building/maintaining a dynamic site using a database and CF8 I'm not sure why you'd want to use Spry if you're interacting up data.

Comment 8 by Raymond Camden posted on 3/11/2008 at 6:21 PM

@Andrew - While CF8 has very cool Ajax features, it doesn't mean that everyone wants to use them. I know I still use Spry for example. Some people avoid the UI features in CF8 because they don't like the overhead. And to be clear, using Spry doesn't mean NOT using CF8 features. I can use Spry to talk to a CFC that returns JSON (thanks to the new returnFormat in CF8). It is definitely not an 'either/or' situation.

Comment 9 by Ray Buechler posted on 3/11/2008 at 11:01 PM

Ray: I didn't actually buy the extension. I bought Dreamweaver and the extension came with it free. ;)