Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

print @<variable> problem 1

Status
Not open for further replies.
DBAssam

DBAssam

Programmer
Feb 8, 2002
19
0
0
GB
I have a SQL query that runs perfectly under MS SQL Query Analyzer, but will not run in CF. I normally limit CF to simple select, insert, update queries, but this is quite an important component on a project.

The labels coming out should be 1 2 3 etc

Can anyone tell me why this isn't running. Does CF not understand print variables? I would be extremely grateful for any assistance.

Thanks

<CFQUERY datasource=&quot;AlexanderForbes&quot; name=&quot;list&quot;>
declare @add_column int
declare @column varchar(160)
declare @strSQL varchar(300)
declare @search_arg varchar(40)

select @column = uniquekey from matterparties where uniquekey = '46025'
set @add_column = 0
set @strSQL = 'select ''' + @column + '''as '+ '''' + cast(@add_column as varchar(4)) + ''''
select @column
print @strSQL

select @column = rtrim(title+' '+left(contactfirst,1)+' '+contactlast) from matterparties where uniquekey = '46025'
set @add_column = case when @column = '' or @column is Null then @add_column else @add_column + 1 end
set @strSQL = case when @column = '' or @column is Null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

select @column = rtrim(jobtitle) from matterparties where uniquekey = '46025'
set @add_column = case when @column = '' or @column is Null then @add_column else @add_column + 1 end
set @strSQL = case when @column = '' or @column is Null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

exec(@strSQL)
</CFQUERY>

<CFOUTPUT QUERY=&quot;list&quot;>
<HTML>
<BODY bgcolor=&quot;white&quot;>
<table border=&quot;1&quot; width=&quot;99%&quot;>
<TR>
<td width=&quot;30%&quot;>#1#</td><td width=&quot;15%&quot;>#2#</td
</tr>
</table>
</body>
</html>
</cfoutput>
 
Sort by date Sort by votes
It's ODBC (assuming that you're using it) that must understand the print variables, and I'm would not expect it to understand any vendor-specific syntax.
 
Upvote 0 Downvote
I believe ODBC is just a connection syntax, and should &quot;pass through&quot; any SQL regardless of whether it is vendor-specific. The problem is that <CFQUERY> isn't a full-fledged SQL development environment. It expects a single result set, and doesn't support any debugging output (which is more or less the purpose of the print statement in SQL Server batch programming).

Instead of
Code: 
print @strSQL
, you could use
Code: 
SELECT @strSQL
and that will send the value of the variable as a result set and not as debugging output, but you still have the problem of multiple result sets.

If you need to see the output each time, I would recommend formatting this as a stored procedure and using <cfstoredproc> which can handle multiple result sets. Also, be sure you include the statment &quot;set nocount on&quot; at the top of your procedure to prevent row counts from the select statments that do not output any data from messing up your query results.

-pcorreia
Hope this was a Helpful post!
 
Upvote 0 Downvote
pcorreia,

Yes, it was helpful. But, I thought that ODBC was more than just a connection syntax. My understanding is that when one uses an ODBC macro, the ODBC driver rewrites it (if necessary) to suit the particular database. For example, for the ODBC query

SELECT *
FROM table1 t1 {oj LEFT OUTER JOIN table2 t2 ON t1.field1 = t2.field1}

the MS Access driver would send

SELECT *
FROM table1 AS t1 LEFT OUTER JOIN table2 AS t2 ON t1.field1 = t2.field1

while the Oracle driver would send

SELECT *
FROM table AS t1, table AS t2
WHERE t1.field1 = t2.table2 (+)
 
Upvote 0 Downvote
Admittedly, I know very little about ODBC. What I meant was, when I put SQL Server-specific syntax in a <CFQUERY>, it is executed properly on the SQL Server even though it is going through ODBC, so clearly you can use SQL commands that are not part of the ANSI spec. I think DBAssam should focus less on understanding ODBC (after all, the point of ODBC is to be as transparent as possible) and should primarily consider the difference between SQL statments (regardless of the SQL dialect) and batch processing commands. DBAssam, I hope my earlier post has given you some points to consider that you can apply to your problem.

I think it's important to keep the focus on solving DBAssam's problem and not get off on a tangent about how ODBC works. If you can suggest a different solution for the problem at hand, please do.

-pcorreia
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cohan123
  • Locked
  • Question
page layout-&gt;page size, optimal for web
Replies
0
Views
113
cohan123
cohan123
mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
104
mdvp
mdvp
lwfg
  • Locked
  • Question
session variable timeout problem
Replies
2
Views
49
lwfg
lwfg
ibeenjammin
  • Locked
  • Question
FrameMaker 2015 Conditional Text Problems
Replies
0
Views
106
ibeenjammin
ibeenjammin
lwfg
  • Locked
  • Question
client variables disappearing
Replies
0
Views
33
lwfg
lwfg

Part and Inventory Search

Sponsor

Back
Top