Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1794
  • Last Modified:

CFOUTPUT QUERY (column names contains spaces)

OK, I'm trying to run a select query from a database that I'm connecting to (of which I have no control over the table design).

The column name is TestCode.

My query is as follows:

<cfquery name="test_query" datasource="dsn">
select TestCode from TestProc where TestITN=1
</cfquery>

<cfdump var="#test_query#"> returns the following:

Test Code
ABC

Notice the space between Test and Code.  I find out that it's not returning the column name (in the column list), rather the column description field from the database, which is no good.

So, I try to alias the column (ie, select TestCode as test_code) ...nothing.  It seems to ignore my alias and returns "Test Code" each time (which again appears to be the description in the db for the column) which I cannot reference when looping the recordset.

Any ideas as to how I can get to the data?  Why would my alias not be working?

Please help...Thanks.
0
gtfly
Asked:
gtfly
  • 5
  • 3
  • 2
1 Solution
 
trailblazzyr55Commented:
Try this

<cfquery name="test_query" datasource="dsn">
select TestCode AS MyCode
from TestProc where TestITN='1'
</cfquery>

<cfoutput query="test_query">
#MyCode#<br>
</cfquery>


<cfdump var="#test_query#">
0
 
trailblazzyr55Commented:
You say that the name of the column in the "TestProc" table you're trying to retrieve data from is "TestCode" right?
0
 
mrichmonCommented:
Yes try an alias like trailblazzy suggested.  Also as a note you can access columns with spaces in the name like this:

<cfoutput query="test_query">
#[Test Code]#<br>
</cfquery>

or this:

<cfoutput query="test_query">
#test_query["Test Code"]#<br>
</cfquery>

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gtflyAuthor Commented:
Yes, the name of the column is TestCode, but the description is "Test Code"...I tried the alias again as you've suggested, but it still does not work (gives cf column not found message).

Also tried mrichmon's two suggestions, but they did not work either.  The 2nd option #test_query["Test Code"]# gave me an error: Complex object types cannot be converted to simple values.

Any other ideas?  I've never had this problem with any other database, and this db is a little strange, as it's an old MUMPS database converted to a relational db.....

thanks for the help thus far.
0
 
gtflyAuthor Commented:
One other thing to note...When running the query:

select TestCode as MyCode from TestProc where TestITN='1'

in another app (WinSQL), it returns the TestCode value as MyCode correctly...so it seems that the sql statement is correct, it's just that CFMX thinks the column descriptions are the column names, and ignores my 'AS' and alias completely.
0
 
trailblazzyr55Commented:
I'm pretty sure there's an issue then with your datasource, you may want to make sure your connection is good.

Try..

SELECT * FROM TestProc WHERE TestITN='1'

and try

<cfdump var="#datasource#">
0
 
trailblazzyr55Commented:
I guess I posted right after yours, try changing the column name to a different value and see if that works? there shouldn't be that issue, but at least you could rule out naming as your problem? Don't add spaces in your column names either, think you don't have that issue, but worth a mention...
0
 
mrichmonCommented:
try this:

<cfloop from="1" to="#test_query.RecordCount#" index="i">
#test_query["Test Code"][i]#<br>
</cfloop >
0
 
gtflyAuthor Commented:
BINGO!  Thanks mrichmon!  The cfloop tag worked great...

Thanks to you as well trailblazzyr55 for your ideas...
0
 
trailblazzyr55Commented:
Glad on of us could get you on the right direction :o)

Regards,
~trail
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now