|
In terms of XML support for SP3, in regards to this
topic, I did find out that you can get a scalar value back from SQL Server
2K when using FOR XML but only when utilizing ADO or ADO.NET. I
was kinda disappointed that they didn't offer something more like the aliasing
idea. I guess they figure everyone should use ADO right ;)
Oh well, my new focus is going to be on connection
pooling using ADO and CF. You wouldn't happen to know if COM+ is built
into the latest MDAC would you? I figure maybe with object pooling turned
on, I might not have to make my own CF solution. Any ideas, I'm just
brainstorming?
Thanks again.
-Brian
Sent: Sunday, March 23, 2003 12:43
AM
Subject: RE: [cf-xml] CF5, SQL Server 2K
Question
though it's still worth a try - I don't think you'll be able to do what
I've suggesed....unless the docs on MSDN are wrong. I found two
statements there that lead me to this conclusion:
1. "FOR XML cannot be used in a
selection that requires further processing in a stored
procedure"
2."Generally, FOR XML cannot be used for any
selections that do not produce direct output to the Microsoft® SQL Server(tm)
2000 client."
What's more, many areas of the SQLXML docs point the developer to the
"Executing SQL Statements Using HTTP" section of the site...
which implies that my first hunch was right - FOR XML is really
designed for HTTP access use. That said, the docs don't appear to be
updated for SP3, so they may not be applicable when running SP3. Don't
know - I haven't had time to test SP3 yet. Sorry.
~Simon
Simon Horwith
Macromedia Certified Instructor
Certified Advanced ColdFusion MX Developer
Certified Flash MX Developer
CFDJList - List Administrator
Fig Leaf Software
1400 16th St NW, # 220
Washington DC 20036
202.797.6570 (direct line)
www.figleaf.com
there are illegal characters in the name of the variable that is
returned (it's a UUID), which is why you cannot reference it. I just
opened the article with no problem. I don't blame you about the HTTP
request - but that's the only way to directly return the XML without use of
a COM object (that I know of). I also don't know of any way to rename
that "column" when you select it - it's system-defined. I suppose you
could write an SP that declares a private variable, sets the variable equal
to a "FOR XML" query, then runs a second SELECT that selects that
variable AS some valid column name... I've never actually tried to do
that... so you may want to give that a shot. Let us know if that
works?
~Simon
Simon Horwith
Macromedia Certified Instructor
Certified Advanced ColdFusion MX Developer
Certified Flash MX Developer
CFDJList - List Administrator
Fig Leaf Software
1400 16th St NW, # 220
Washington DC 20036
202.797.6570 (direct line)
www.figleaf.com
Thank you for the advice. It's good to
know I exhausted most every option. I had looked at the use of the
SQLXML API, but had ruled it out due to the sensitive nature of my
application. In addition, I'm not too partial to making an extra
HTTP requests when I may have other low level protocols available to me. I
guess I'm just gonna use ADO via CF.
On a side note I was unable to pull up your
article, they may have taken it down. In your article, did you talk
about the reasons why CF and this feature of SQL 2K, won't
interoperate? Do you know if my assumption about the column name
being too long for CF's use, is correct? Just being over curious I
guess.
Thanks again for the help. If anyone
else has any other ideas or opinions, feel free to chime in.
-Brian
----- Original Message -----
Sent: Saturday, March 22, 2003
10:25 PM
Subject: RE: [cf-xml] CF5, SQL
Server 2K Question
that is correct - you cannot use data selected as XML like
this. In order to use the XML data, you need to make an http
request of a "SQL Server Over HTTP" virtual mapping (either pass the SQL
over the URL or embed it within an MS - SQL XML packet). You
can find out more about this in an article I wrote a year and a half ago
for CFDJ: http://www.sys-con.com/coldfusion/article.cfm?id=331
Good Luck!
~Simon
Simon Horwith
Macromedia Certified Instructor
Certified Advanced ColdFusion
MX Developer
Certified Flash
MX Developer
CFDJList - List
Administrator
Fig Leaf
Software
1400 16th St NW, #
220
Washington DC 20036
202.797.6570 (direct line)
www.figleaf.com
I'm currently using CF 5.0 with SQL
Server 2K. I'm using <cfquery> to write the following
query:
SELECT pi.*, pli.*, pf.*
FROM
((PURCHASE_INVOICE pi INNER JOIN PURCHASE_LINE_ITEMS pli
ON
pi.Invoice_ID = pli.Invoice_ID)
INNER JOIN PURCHASE_FEE pf
ON pli.Invoice_ID = pf.Invoice_ID AND pli.LineItem_ID =
pf.LineItem_ID)
FOR XML AUTO, ELEMENTS
I get back a recordset with one col and
one row. The column name is crazy long (40 chars) and is not
something I can't predict (i.e. - XML_F52E2B61-18A1-11d1-.....).
I write the following code:
<cfloop
type="query"><cfoutput>#Evaluate(ListGetAt(temp.columnlist,
1))#</cfoutput></cfquery>
I get an error saying that the variable
name after "A1-" is invalid. I'm guessing this is some kind of
constraint on CF variable name lengths.
I tried reading up on SQL 2K and found
that if it was updated to SP3, a string could be returned instead of a
recordset when using FOR XML. Unfortunately, the only code I've
found to utilize this is using the ADO COM API.
Does anyone know of a way using the
FOR XML clause in SQL Server 2K to get that XML doc back into CF
using <cfquery> or stored procedures? Any advice / help
would be greatly appreciated. I'm really stuck
here.
-Brian
|
