Re: Dynamic SQL Column Names
Or go runa query on the table with 1 record using a select * and loop through the columnlist returned. On Wed, Apr 14, 2010 at 8:39 PM, Doug Ford doug.e.f...@gmail.com wrote: Hi Folks - I am wracking my brain trying to figure out how to get the results I am looking for. Here's the background - I am dealing with dynamic environment allowing losers, I mean users, to enter in field names. This is a business product so anything is possible. When a user of the product creates the columns, they have no knowledge of coldfusion or sql, so if a person enters in VIN # as a piece of information they wish to capture, VIN # becomes a column header. Now then, on any given random form my system would be creating, I won't know how many fields or their names. If a person creates 5 or 500 fields, I have no clue, and I won't know what column names they could ultimately create. I have been toying with getColumnList() to display the column titles, but how would I get the actual data out of it? I have tried using evaluate on the field names, but when it comes across VIN #, the system blows up. Any and info is appreciated. Thanks, Doug ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332903 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Dynamic SQL Column Names
You could create one table that will store dynamic columns and another to store the dynamic columns data. Then if you're using MSSQL 2005+ than you can use SQL Pivots to extract the data dynamically.. if your using anything less than MSSQL 2005 than you could use dynamic SQL and build a temp table. Pivots http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-c olumns-in-sql-server-2005-2008.aspx Regards, Paul Alkema -Original Message- From: Brian Thornton [mailto:vegasthorn...@gmail.com] Sent: Thursday, April 15, 2010 5:21 AM To: cf-talk Subject: Re: Dynamic SQL Column Names Or go runa query on the table with 1 record using a select * and loop through the columnlist returned. On Wed, Apr 14, 2010 at 8:39 PM, Doug Ford doug.e.f...@gmail.com wrote: Hi Folks - I am wracking my brain trying to figure out how to get the results I am looking for. Here's the background - I am dealing with dynamic environment allowing losers, I mean users, to enter in field names. This is a business product so anything is possible. When a user of the product creates the columns, they have no knowledge of coldfusion or sql, so if a person enters in VIN # as a piece of information they wish to capture, VIN # becomes a column header. Now then, on any given random form my system would be creating, I won't know how many fields or their names. If a person creates 5 or 500 fields, I have no clue, and I won't know what column names they could ultimately create. I have been toying with getColumnList() to display the column titles, but how would I get the actual data out of it? I have tried using evaluate on the field names, but when it comes across VIN #, the system blows up. Any and info is appreciated. Thanks, Doug ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332904 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Hi Leigh - When I was saying System, I meant the CF Server/ web page was producing an error screen. I don't think that having the [col name] will work since a field name might contain a # sign within it. And that would again cause the CF page to fail. Thanks. Doug I am not really clear on your system... But to answer the part I do understand, you can use array notation. #queryName[colName][rowNumber]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332914 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Hi Jason - Thanks for the idea, but I also thought about evaluate, and when CF tries to evaluate the field of Vin #, the server produces an error page. Invalid CFML construct Thanks Also, to address the first example you gave, if this is blowing up on Vin #: #evaluate(columnName)# Then try this: #evaluate(de(columnName))# (if I recall correctly) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332915 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Did you actually try it? Leigh's solution is generally the best way to do what you're after (you don't need evaluate). If it really doesn't, you could try (untested): cfset colname = replace(colName,##,,all) / #queryName[colname][rowNumber]# But I'd be surprised if you really need to go that far. :den -- Dolores: I would have been here right after you called, but I had to shake the weasels. On Thu, Apr 15, 2010 at 10:49 AM, Doug Ford wrote: Hi Leigh - When I was saying System, I meant the CF Server/ web page was producing an error screen. I don't think that having the [col name] will work since a field name might contain a # sign within it. And that would again cause the CF page to fail. Thanks. Doug I am not really clear on your system... But to answer the part I do understand, you can use array notation. #queryName[colName][rowNumber]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332917 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
You should do some filtering before they go in for script issues and #s unless that's something you absolutely don't want to do.. It's a security thing On Thu, Apr 15, 2010 at 1:29 PM, denstar valliants...@gmail.com wrote: Did you actually try it? Leigh's solution is generally the best way to do what you're after (you don't need evaluate). If it really doesn't, you could try (untested): cfset colname = replace(colName,##,,all) / #queryName[colname][rowNumber]# But I'd be surprised if you really need to go that far. :den -- Dolores: I would have been here right after you called, but I had to shake the weasels. On Thu, Apr 15, 2010 at 10:49 AM, Doug Ford wrote: Hi Leigh - When I was saying System, I meant the CF Server/ web page was producing an error screen. I don't think that having the [col name] will work since a field name might contain a # sign within it. And that would again cause the CF page to fail. Thanks. Doug I am not really clear on your system... But to answer the part I do understand, you can use array notation. #queryName[colName][rowNumber]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
@Doug, Did you try evaluate(de(yourVariable)) ? The Dynamic Evaluation [DE()] method should cover you there. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332922 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Did you try evaluate(de(yourVariable)) ? The Dynamic Evaluation [DE()] method should cover you there. That approach doesn't seem to make any sense to me. DE prevents a string from being evaluated in places where evaluation happens automatically (Iif, Evaluate). So evaluate(de(yourVariable)) should be identical to yourVariable. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332924 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Ah, yeah, true. Just used to using the Evaluate(DE()) combo back in the day when outputting blocks of dynamic content, because otherwise the double-quotes in content killed the Evaluate() function. With something like 'Vin #', the DE() adds nothing to the party. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332925 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Dynamic SQL Column Names
Hi Folks - I am wracking my brain trying to figure out how to get the results I am looking for. Here's the background - I am dealing with dynamic environment allowing losers, I mean users, to enter in field names. This is a business product so anything is possible. When a user of the product creates the columns, they have no knowledge of coldfusion or sql, so if a person enters in VIN # as a piece of information they wish to capture, VIN # becomes a column header. Now then, on any given random form my system would be creating, I won't know how many fields or their names. If a person creates 5 or 500 fields, I have no clue, and I won't know what column names they could ultimately create. I have been toying with getColumnList() to display the column titles, but how would I get the actual data out of it? I have tried using evaluate on the field names, but when it comes across VIN #, the system blows up. Any and info is appreciated. Thanks, Doug ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332895 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
I am not really clear on your system... But to answer the part I do understand, you can use array notation. #queryName[colName][rowNumber]# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332897 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
On 4/14/2010 8:39 PM, Doug Ford wrote: Hi Folks - I am wracking my brain trying to figure out how to get the results I am looking for. Here's the background - I am dealing with dynamic environment allowing losers, I mean users, to enter in field names. This is a business product so anything is possible. When a user of the product creates the columns, they have no knowledge of coldfusion or sql, so if a person enters in VIN # as a piece of information they wish to capture, VIN # becomes a column header. Now then, on any given random form my system would be creating, I won't know how many fields or their names. If a person creates 5 or 500 fields, I have no clue, and I won't know what column names they could ultimately create. I have been toying with getColumnList() to display the column titles, but how would I get the actual data out of it? I have tried using evaluate on the field names, but when it comes across VIN #, the system blows up. Any and info is appreciated. Thanks, Doug Consider abstracting the database schema. Hold the column names in a table with an ordinal key. Store the column contents in another table with a foreign key back to the columns. Never trust user input. That is how I would start to break it down. -- LinkedIn: http://www.linkedin.com/pub/8/a4/60 Twitter: http://twitter.com/RogerTheGeek MissHunt: http://www.misshunt.com/ (Home of the Clean/Dirty Magnet) NCDevCon: http://ncdevcon.com/ 2010 Raleigh ColdFusion Conference ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332898 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Dynamic SQL Column Names
Also, to address the first example you gave, if this is blowing up on Vin #: #evaluate(columnName)# Then try this: #evaluate(de(columnName))# (if I recall correctly) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332899 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
