Brian,
Actually, you should be able to get your query back without any trouble
using the following code (example uses PUBS database on SQL2K).
<cfquery name="xmlQuery" datasource="localpubs">
select au_id, au_lname, au_fname
from authors
FOR XML AUTO
</cfquery>
<cfset xmlString="">
<cfloop index="i" from="1" to="#xmlQuery.RecordCount#">
<cfset xmlString=xmlString & xmlQuery[xmlQuery.ColumnList][i]>
</cfloop>
<cfoutput>#htmleditformat(xmlString)#</cfoutput>
Regards,
Chris Lomvardias
-----Original Message-----
From: Brian LeGros
To: [EMAIL PROTECTED]
Sent: 3/23/03 2:21 AM
Subject: Re: [cf-xml] CF5, SQL Server 2K Question
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
From: Simon <mailto:[EMAIL PROTECTED]> Horwith
To: '[EMAIL PROTECTED]' <mailto:'[EMAIL PROTECTED]'>
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 "
<http://msdn.microsoft.com/library/en-us/xmlsql/ac_xml1_6a5o.asp>
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
-----Original Message-----
From: Simon Horwith [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 23, 2003 0:18
To: '[EMAIL PROTECTED]'
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
-----Original Message-----
From: Brian LeGros [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 22, 2003 23:58
To: [EMAIL PROTECTED]
Subject: Re: [cf-xml] CF5, SQL Server 2K Question
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 -----
From: Simon <mailto:[EMAIL PROTECTED]> Horwith
To: '[EMAIL PROTECTED]' <mailto:'[EMAIL PROTECTED]'>
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
<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
-----Original Message-----
From: Brian LeGros [mailto:[EMAIL PROTECTED]
Sent: Friday, March 21, 2003 17:50
To: [EMAIL PROTECTED]
Subject: [cf-xml] CF5, SQL Server 2K Question
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
-----------------------+
cf-xml mailing list
http://torchbox.com/xml/list.cfm
