|
Thanks for getting back with my question. I
had tried the SP idea, I even just did it using Query Analyzer and it didn't
work. I couldn't alias it no matter the combo. SP3 docs said
something about a scalar return value when using FOR XML, but I discovered
that was only when using ADO.
Thanks for the idea though, it's good to know I'm
not totally off base in my thinking.
Brian LeGros Web Applications Manager Foreign Currency Exchange
Corp P: 954-733-2239 E: [EMAIL PROTECTED]
----- Original Message -----
Sent: Sunday, March 23, 2003 12:18
AM
Subject: RE: [cf-xml] CF5, SQL Server 2K
Question
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
|
