Re: [cf-xml] CF5, SQL Server 2K Question

Sat, 22 Mar 2003 20:58:53 -0800

Title: Message
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

-----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

Reply via email to