?
Solved

How to QuerySetCell when ColumnName has spaces

Posted on 2004-04-21
8
Medium Priority
?
611 Views
Last Modified: 2013-12-24
With a QuerySetCell statement, ColdFusion does not allow you to enter a column name to change that contains spaces. However, I am currently generating queries that have Alias values for column names and these Alias column names sometimes contain spaces. After the query is made sometimes I need to take the data out of a query cell, performan an operation on it, and then put the new value back into the cell (converting retrieved database datetime values into UTC).

For example:

<cfset tempAlias = "My Date Column" />

The following isn't possible because the columname, tempAlias, has spaces and is an invalid column name (even though that is the Column name that was created after querying the database with "SELECT myDateField AS [My Date Column]" statement:
<cfset QuerySetCell(myQuery,tempAlias,newValue,CurrentRow) />

Any ideas on how to get around this?
0
Comment
Question by:libel_vox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 10884677
Hi libel_vox,

Have you tried.

<cfset tempAlias = "[My Date Column]" />

Other than that, I think it's bad pratice to use spaces in aliases, just makes everything more complicated. This is much better.

SELECT myDateField AS MyDateColumn

or

SELECT myDateField AS My_Date_Column

or

SELECT myDateField AS myDateColumn

which is what most coders use today, this is a standard variable naming convention.

Regards
Plucka
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 10884711
I think you will have to refer to the actual column name instead of the Alias.
So "myDateField" instead of "My date column".
0
 
LVL 18

Expert Comment

by:Plucka
ID: 10884802
Tacobell777,

That wouldn't work if the field is referenced more than once, which is the most common reason AS is used.

Regards
Plucka
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 17

Expert Comment

by:Tacobell777
ID: 10884821
I never tried what he is trying to do, can anyone of you actually confirm that when you create aliases you can do a setCell and insert a value by referencing to the Alias? Test it and see if it works for

SELECT column AS test1, column AS test2

I personally think it won't work, by the way, what is the difference anyway, no matter if you alias your columns as
columnName AS test1
columnName AS test2
the insert will still be in the same column so why reference to it via the ALIAS?
0
 
LVL 18

Expert Comment

by:Plucka
ID: 10884852
Sounds right. Haven't tested it, by the question I assumed that it worked for aliases just not aliases with spaces.

From your other post Taco, you have more experience with newQuery stuff than me.
0
 
LVL 5

Expert Comment

by:heathprovost
ID: 10885226
I just did some test code, and I dont see the problem you are describing... Here is the test code Im using:

<cfquery name="test" datasource="test">
  SELECT somefield AS [Some Field]
  FROM sometable
</cfquery>
<cfset tempalias = "Some Field">
<cfloop query="test">
  <cfset void = querysetcell(test, tempalias, "New Value", test.currentrow)>
</cfloop>
<!---The following dump works correctly and displays the changed value--->
<cfdump var="#test#">

What am I missing?

Heath
0
 
LVL 5

Accepted Solution

by:
heathprovost earned 2000 total points
ID: 10885305
Ok... I was using CF5. I switched to MX and the same test code fails, so I figured you are using CFMX. Anyway, I played around with it a bit and found a way to do this that works, but to be honest it is VERY scary looking. I had no idea you could do this. Anyway, this is the above example modified to work on CFMX:

<cfquery name="test" datasource="test">
  SELECT somefield AS [Some Field]
  FROM sometable
</cfquery>
<cfset tempalias = "Some Field">
<cfloop query="test">
  <cfset test[tempalias][test.currentrow] = "New Value">
</cfloop>
<!---The following dump works correctly and displays the changed value--->
<cfdump var="#test#">

And yes, thats right... Im directly setting the value in a query field using only a cfset statement. I always thought that was not possible, but it sure seems to work just fine. I went back and tested this method on CF5 and it works there as well. I knew it was possible to address querycols using array notation, but I never would have guessed that they were assignable that way. Learn something new everyday...

Heath
0
 

Author Comment

by:libel_vox
ID: 10889400
" but to be honest it is VERY scary looking"

It's Not scary if it works. :D Yes, Heath, as you deducted I was using ColdFusion MX. I tried your solution and it works great (I briefly tried changing values with array notation but didn't go far enough). You get the points.

As a programmer, Pluka, I understand the extra complexity that comes with using Alias names with spaces. However, because I'm allowing a user of a system name the fields what they want, it is important to support names that are the most readable. Hence, support for spaces.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question