Converting query to comma separated list
My first question, though I suspect this might not be the best approach . Is it possible to turn a query (cfdirectory in this case) into a comma separated list? The reason I ask is bcos currently I have a hardcoded list of folders in my action file, and I loop through and pull all the files from each. Like so... cfloop index = thisItem list = maps,districtPlans,mgmtPlans cfdirectory name=getAllFiles action=""> directory=#serverdir##thisItem# sort=name ASC cfquery dbtype=query name=getAllFiles_#thisItem# SELECT * FROM getAllFiles /cfquery /cfloop and in the display page something like... ol cfoutput query=getAllFiles_maps #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol ol cfoutput query=getAllFiles_mgmtPlans #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol etc etc This is all working fine. BUT, instead of hardcoding the list, I can do another directory call like so... cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC which will give me all the folders that are in the above list (ie maps,districtPlans etc). So, how to get the query to a list so I don't need to hardcode? OR, is this the wrong approach and is it possible to use the getAllFolders query directly in some fashion? TIA Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Converting query to comma separated list
Why don't you just CFLOOP over the query? You don't need to convert the query to a comma list to loop over it... CFLOOP query=qry_name) ...cf code... /CFLOOP Andrew. - Original Message - From: Mark Henderson [EMAIL PROTECTED] Date: Thu, 23 Sep 2004 21:07:07 +1200 Subject: Converting query to comma separated list To: CF-Talk [EMAIL PROTECTED] My first question, though I suspect this might not be the best approach . Is it possible to turn a query (cfdirectory in this case) into a comma separated list? The reason I ask is bcos currently I have a hardcoded list of folders in my action file, and I loop through and pull all the files from each. Like so... cfloop index = thisItem list = maps,districtPlans,mgmtPlans cfdirectory name=getAllFiles action=""> directory=#serverdir##thisItem# sort=name ASC cfquery dbtype=query name=getAllFiles_#thisItem# SELECT * FROM getAllFiles /cfquery /cfloop and in the display page something like... ol cfoutput query=getAllFiles_maps #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol ol cfoutput query=getAllFiles_mgmtPlans #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol etc etc This is all working fine. BUT, instead of hardcoding the list, I can do another directory call like so... cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC which will give me all the folders that are in the above list (ie maps,districtPlans etc). So, how to get the query to a list so I don't need to hardcode? OR, is this the wrong approach and is it possible to use the getAllFolders query directly in some fashion? TIA Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Converting query to comma separated list
Mark, Not sure if I understand your question but maybe ValueList() May be of some help? Kola -Original Message- From: Mark Henderson [mailto:[EMAIL PROTECTED] Sent: 23 September 2004 10:07 To: CF-Talk Subject: Converting query to comma separated list My first question, though I suspect this might not be the best approach . Is it possible to turn a query (cfdirectory in this case) into a comma separated list? The reason I ask is bcos currently I have a hardcoded list of folders in my action file, and I loop through and pull all the files from each. Like so... cfloop index = thisItem list = maps,districtPlans,mgmtPlans cfdirectory name=getAllFiles action=""> directory=#serverdir##thisItem# sort=name ASC cfquery dbtype=query name=getAllFiles_#thisItem# SELECT * FROM getAllFiles /cfquery /cfloop and in the display page something like... ol cfoutput query=getAllFiles_maps #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol ol cfoutput query=getAllFiles_mgmtPlans #replace(listFirst(name), _, , ALL)#br / /cfoutput /ol etc etc This is all working fine. BUT, instead of hardcoding the list, I can do another directory call like so... cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC which will give me all the folders that are in the above list (ie maps,districtPlans etc). So, how to get the query to a list so I don't need to hardcode? OR, is this the wrong approach and is it possible to use the getAllFolders query directly in some fashion? TIA Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Converting query to comma separated list
Thank you for the swift replies Andrew and Kola, and you're right Andrew. I'd already tried something similar, but was making it more complicated than necessary. Here's what works cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC cfloop query = getAllFolders cfdirectory name=getAllFiles action=""> directory=#serverdir##name# sort=name ASC cfquery dbtype=query name=getAllFiles_#name# SELECT * FROM getAllFiles /cfquery /cfloop adieu Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Converting query to comma separated list
That look like what I was thinking of. Just make sure that if you are using an older version of CF (CF4/5 I think) that CFDIRECTORY returned the . and .. directories, but CFMX doesn't do this. As you say it works I'm guess you are on CFMX. Andrew. - Original Message - From: Mark Henderson [EMAIL PROTECTED] Date: Thu, 23 Sep 2004 22:14:06 +1200 Subject: Re: Converting query to comma separated list To: CF-Talk [EMAIL PROTECTED] Thank you for the swift replies Andrew and Kola, and you're right Andrew. I'd already tried something similar, but was making it more complicated than necessary. Here's what works cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC cfloop query = getAllFolders cfdirectory name=getAllFiles action=""> directory=#serverdir##name# sort=name ASC cfquery dbtype=query name=getAllFiles_#name# SELECT * FROM getAllFiles /cfquery /cfloop adieu Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Converting query to comma separated list
Mark, I posted a CFC called queryTool to my site (http://clearsoftware.net - downloads - queryTool) and to cfcZone.org that converts queries to delimited formats, XML, arrays, and structures.Perfectly free, seems to work pretty well. Cheers, Joe - Original Message - From: Mark Henderson [EMAIL PROTECTED] Date: Thu, 23 Sep 2004 22:14:06 +1200 Subject: Re: Converting query to comma separated list To: CF-Talk [EMAIL PROTECTED] Thank you for the swift replies Andrew and Kola, and you're right Andrew. I'd already tried something similar, but was making it more complicated than necessary. Here's what works cfdirectory name=getAllFolders action=""> directory=#serverdir# sort=name ASC cfloop query = getAllFolders cfdirectory name=getAllFiles action=""> directory=#serverdir##name# sort=name ASC cfquery dbtype=query name=getAllFiles_#name# SELECT * FROM getAllFiles /cfquery /cfloop adieu Mark [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Converting query to comma separated list
Joe,
Got the following error trying to access your site
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Could not allocate space for
object 'clearsoftware_visitors' in database 'clearsoftware_net_01' because
the 'PRIMARY' filegroup is full.
The error occurred in
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc: line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
Called from D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc:
line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
19 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.firstName# /
20 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.lastName# /
21 : cfprocresult name=result maxrows=1 /
22 :/cfstoredproc
23 :
_
SQL
{call clearsoftware_createVisitor( (param 1) , (param 2) , (param 3) ,
(param 4) , (param 5) , (param 6) )}
DATASOURCE
clearsoftware_glass
VENDORERRORCODE
1105
SQLSTATE
HY000
Please try the following:
* Check the ColdFusion http://www.macromedia.com/go/proddoc_getdoc
documentation to verify that you are using the correct syntax.
* Search the Knowledge http://www.macromedia.com/support/coldfusion/
Base to find a solution to your problem.
Browser
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Remote Address
65.242.127.130
Referrer
Date/Time
23-Sep-04 09:21 AM
Stack Trace (click to expand) _javascript_:;
Scott A. Stewart,
Web Application Developer
Engineering Consulting Services, Ltd. (ECS)
14026 Thunderbolt Place, Suite 100
Chantilly, VA 20151
Phone: (703) 471-8400
Fax: (703) 834-5527
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 09:37 am
To: CF-Talk
Subject: Re: Converting query to comma separated list
Mark,
I posted a CFC called queryTool to my site (http://clearsoftware.net
- downloads - queryTool) and to cfcZone.org that converts queries to
delimited formats, XML, arrays, and structures.Perfectly free, seems
to work pretty well.
Cheers,
Joe
- Original Message -
From: Mark Henderson [EMAIL PROTECTED]
Date: Thu, 23 Sep 2004 22:14:06 +1200
Subject: Re: Converting query to comma separated list
To: CF-Talk [EMAIL PROTECTED]
Thank you for the swift replies Andrew and Kola,
and you're right Andrew. I'd already tried something similar, but was making
it more complicated than necessary. Here's what works
cfdirectory
name=getAllFolders
action="">
directory=#serverdir#
sort=name ASC
cfloop query = getAllFolders
cfdirectory
name=getAllFiles
action="">
directory=#serverdir##name#
sort=name ASC
cfquery dbtype=query name=getAllFiles_#name#
SELECT *
FROM getAllFiles
/cfquery
/cfloop
adieu
Mark
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Converting query to comma separated list
My first question, though I suspect this might not be the best approach . Is it possible to turn a query (cfdirectory in this case) into a comma separated list? Mark, There's a UDF on CFLib called QueryToCsv that does exactly that, turn query results into a CSV list: http://www.cflib.org/udf.cfm?ID=556. very simple to use cfset whatever = QueryToCsv(YourQueryNameGoesHere) hth, larry -- Larry C. Lyons Web Analyst BEI Resources American Type Culture Collection email: llyons(at)atcc(dot)org tel: 703.365.2700.2678 -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Converting query to comma separated list
Thanks Scott,
This pops up every 24-36 hours until I run a script to empty some
logs, as CF-Dynamics still hasn't completed a help desk ticket I left
with them a while back...
I'll be moving to a completely new service over the next few weeks,
and this should all go away.
Cheers,
Joe
- Original Message -
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Thu, 23 Sep 2004 10:18:25 -0400
Subject: RE: Converting query to comma separated list
To: CF-Talk [EMAIL PROTECTED]
Joe,
Got the following error trying to access your site
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Could not allocate space for
object 'clearsoftware_visitors' in database 'clearsoftware_net_01' because
the 'PRIMARY' filegroup is full.
The error occurred in
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc: line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
Called from D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc:
line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
19 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.firstName# /
20 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.lastName# /
21 : cfprocresult name=result maxrows=1 /
22 :/cfstoredproc
23 :
_
SQL
{call clearsoftware_createVisitor( (param 1) , (param 2) , (param 3) ,
(param 4) , (param 5) , (param 6) )}
DATASOURCE
clearsoftware_glass
VENDORERRORCODE
1105
SQLSTATE
HY000
Please try the following:
* Check the ColdFusion http://www.macromedia.com/go/proddoc_getdoc
documentation to verify that you are using the correct syntax.
* Search the Knowledge http://www.macromedia.com/support/coldfusion/
Base to find a solution to your problem.
Browser
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Remote Address
65.242.127.130
Referrer
Date/Time
23-Sep-04 09:21 AM
Stack Trace (click to expand) _javascript_:;
Scott A. Stewart,
Web Application Developer
Engineering Consulting Services, Ltd. (ECS)
14026 Thunderbolt Place, Suite 100
Chantilly, VA 20151
Phone: (703) 471-8400
Fax: (703) 834-5527
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 09:37 am
To: CF-Talk
Subject: Re: Converting query to comma separated list
Mark,
I posted a CFC called queryTool to my site (http://clearsoftware.net
- downloads - queryTool) and to cfcZone.org that converts queries to
delimited formats, XML, arrays, and structures.Perfectly free, seems
to work pretty well.
Cheers,
Joe
- Original Message -
From: Mark Henderson [EMAIL PROTECTED]
Date: Thu, 23 Sep 2004 22:14:06 +1200
Subject: Re: Converting query to comma separated list
To: CF-Talk [EMAIL PROTECTED]
Thank you for the swift replies Andrew and Kola,
and you're right Andrew. I'd already tried something similar, but was making
it more complicated than necessary. Here's what works
cfdirectory
name=getAllFolders
action="">
directory=#serverdir#
sort=name ASC
cfloop query = getAllFolders
cfdirectory
name=getAllFiles
action="">
directory=#serverdir##name#
sort=name ASC
cfquery dbtype=query name=getAllFiles_#name#
SELECT *
FROM getAllFiles
/cfquery
/cfloop
adieu
Mark
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Converting query to comma separated list
No problem Joe,I saw that you're CFC will do something that I need, and
when the page choked
But I downloaded it from CFZone, I'll let ya know how it works out.
sas
Scott A. Stewart,
Web Application Developer
Engineering Consulting Services, Ltd. (ECS)
14026 Thunderbolt Place, Suite 100
Chantilly, VA 20151
Phone: (703) 471-8400
Fax: (703) 834-5527
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 01:37 pm
To: CF-Talk
Subject: Re: Converting query to comma separated list
Thanks Scott,
This pops up every 24-36 hours until I run a script to empty some
logs, as CF-Dynamics still hasn't completed a help desk ticket I left
with them a while back...
I'll be moving to a completely new service over the next few weeks,
and this should all go away.
Cheers,
Joe
- Original Message -
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Thu, 23 Sep 2004 10:18:25 -0400
Subject: RE: Converting query to comma separated list
To: CF-Talk [EMAIL PROTECTED]
Joe,
Got the following error trying to access your site
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Could not allocate space for
object 'clearsoftware_visitors' in database 'clearsoftware_net_01' because
the 'PRIMARY' filegroup is full.
The error occurred in
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc: line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
Called from D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\tracker.cfc:
line 21
Called from
D:\Inetpub\wwwroot\clearsoftware.net\wwwroot\client\Application.cfm: line 26
19 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.firstName# /
20 : cfprocparam cfsqltype=cf_sql_varchar
value=#arguments.lastName# /
21 : cfprocresult name=result maxrows=1 /
22 :/cfstoredproc
23 :
_
SQL
{call clearsoftware_createVisitor( (param 1) , (param 2) , (param 3) ,
(param 4) , (param 5) , (param 6) )}
DATASOURCE
clearsoftware_glass
VENDORERRORCODE
1105
SQLSTATE
HY000
Please try the following:
* Check the ColdFusion http://www.macromedia.com/go/proddoc_getdoc
documentation to verify that you are using the correct syntax.
* Search the Knowledge http://www.macromedia.com/support/coldfusion/
Base to find a solution to your problem.
Browser
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Remote Address
65.242.127.130
Referrer
Date/Time
23-Sep-04 09:21 AM
Stack Trace (click to expand) _javascript_:;
Scott A. Stewart,
Web Application Developer
Engineering Consulting Services, Ltd. (ECS)
14026 Thunderbolt Place, Suite 100
Chantilly, VA 20151
Phone: (703) 471-8400
Fax: (703) 834-5527
-Original Message-
From: Joe Rinehart [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 23, 2004 09:37 am
To: CF-Talk
Subject: Re: Converting query to comma separated list
Mark,
I posted a CFC called queryTool to my site (http://clearsoftware.net
- downloads - queryTool) and to cfcZone.org that converts queries to
delimited formats, XML, arrays, and structures.Perfectly free, seems
to work pretty well.
Cheers,
Joe
- Original Message -
From: Mark Henderson [EMAIL PROTECTED]
Date: Thu, 23 Sep 2004 22:14:06 +1200
Subject: Re: Converting query to comma separated list
To: CF-Talk [EMAIL PROTECTED]
Thank you for the swift replies Andrew and Kola,
and you're right Andrew. I'd already tried something similar, but was making
it more complicated than necessary. Here's what works
cfdirectory
name=getAllFolders
action="">
directory=#serverdir#
sort=name ASC
cfloop query = getAllFolders
cfdirectory
name=getAllFiles
action="">
directory=#serverdir##name#
sort=name ASC
cfquery dbtype=query name=getAllFiles_#name#
SELECT *
FROM getAllFiles
/cfquery
/cfloop
adieu
Mark
_
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
RE: Converting query to comma separated list
Got the tool Joe thanks! Ironically it appeared that even after doing what I did it was still nice to have them in a list for use elsewhere, and this is now working with your cfc. Andrew,I'm testing on mx and 5, in which case I usually just do something like cfoutput query=queryname cfif NOTname is . AND NOT name is .. li#replace(listFirst(name, .), _, , ALL)#/li /cfif /cfoutput Regards Mark H [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
