SQL statement not working when placed in ColdFusion CFC

Question

I have the following table join that runs fine in Microsoft SQL Server and returns the expected results.

SELECT d.id1, c.content_type FROM Document2 AS d INNER JOIN Content2 AS c ON d.content_id = c.content_id WHERE (d.class_id = 1)

However when I place the statement into a ColdFusion CFC, the statement will not execute and I am not getting anything to return. Does the syntax change within the CFC file? Is the Microsoft SQL syntax different from the ColdFusion CFC syntax? Or am I missing something else here?

This is the relevant function. I can get this code to work if I use a simple SQL statement that is not a table join. However, when I insert the table join statement nothing will return.

 remote array function getcontent() { var q = new com.adobe.coldfusion.query(); q.addParam( name="searchParam", value="#searchName#" ); q.setDatasource("Document"); q.setSQL("SELECT d.id1, c.content_type FROM Document2 AS d INNER JOIN Content2 AS c ON d.content_id = c.content_id WHERE (d.class_id = 1)"); var data = q.execute().getResult(); var result = []; for(var i=1; i<= data.recordCount; i++) { arrayAppend(result, {"id"=data.d.id1[i], "Type"=data.c.content_type[i]}); } return result; }

have you tried simply dumping "data" to see if the query contains any records? — Jason Dean
– Jason Dean, Commented Aug 29, 2012 at 14:34
"Is the Microsoft SQL syntax different from the coldfusion cfc syntax?" - there is no "coldfusion cfc syntax". ColdFusion passes the SQL you provide to the database. Make sure your datasource is pointing to the same database as your MSQL Studio is pointing at. — Peter Boughton
– Peter Boughton, Commented Aug 29, 2012 at 14:41
Actually, what's going on with "data.d.id1" - that'll be what's not working - see answer below for details... — Peter Boughton
– Peter Boughton, Commented Aug 29, 2012 at 14:42

Peter Boughton · Accepted Answer · 2012-08-29 14:45:56Z

The problem is this line of code:

arrayAppend(result, {"id"=data.d.id1[i], "Type"=data.c.content_type[i]});

You don't refer to database variables like that - the table alias is not part of the column alias.

You should simply use:

data.id1[i]

If you did actually have a . in the column alias, then you would need to refer to it using bracket notation, like this:

data['d.id1'][i]

But again, the table alias isn't part of the column alias, so that's not needed.

You are right, this was the problem. It works perfect now. Thanks for your help!
@stat8: If this answer solved the problem for you, don't forget to "Accept" it so that it acts as a signpost for future readers. See also: Accepting Answers. How does it work?

Collectives™ on Stack Overflow

SQL statement not working when placed in ColdFusion CFC

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related