DataDirect 3.5 JDBC Driver / Problem with CFQUERYPARAMand SQL Server

[mr snoopy]

Is anyone else experiencing this? I just reported it as a bug, but want to be
sure that I'm not the only one, and that it doesn't disappear into the ether...

Prior to the DataDirect JDBC driver 3.5, when a CFQUERY using the CFQUERYPARAM
tag was executed in a ColdFusion template against a SQL Server 2000 database,
the SQL was executed with sp_execute, and results returned. With the 3.5
version of the driver, using a CFQUERYPARAM tag now causes sp_preparesql to be
executed (ie the SQL is parsed before execution), which returns an error if a
table that did not exist prior to the CFQUERY call is created and accessed
within the SQL statement.
Removing the CFQUERYPARAM tags and embedding the variable directly in the SQL
eliminates the parse prior to execution.

An example:

<cfset myVar = 1 />

<!--- this CFQUERY will not throw an error using the 3.5 version of the
drivers --->
<cfquery datasource="test">
select #myVar# as theColumn into #testTable

select * from #testTable

drop table #testTable
</cfquery>

<!--- this CFQUERY will throw an error even though it's technically valid
using the 3.5 version of the drivers --->
<cfquery datasource="test">
select <cfqueryparam value="#myVar#" cfsqltype="cf_sql_integer" /> as
theColumn into #testTable

select * from #testTable

drop table #testTable
</cfquery>

[BKBK]

I suspect that both your examples have the wrong syntax. I don't believe -- but
I have to check the literature -- that it is correct to put a primitive data
value such as #myVar# or <cfqueryparam value="#myVar#"
cfsqltype="cf_sql_integer" /> right after the word "select".

You might not get an error in the first example because the following just
happens to be correct, if uninspiring, SQL:

select 5 as myColumn
from myTable

This will simply return the value 5 thirty-seven times, if myTable has 37
rows. The error thrown during the binding with cfqueryparam is perhaps a signal
that a you should put a column-name after "select", not a value. Just my
immediate reaction.

[mr snoopy]

In my original example, I forgot to escape the # signs. I'm using the # sign to
create a temp table in SQL Server. The syntax (except for that) is actually
correct. It would be like saying "select name, 5 as someColumn from
sysobjects", which if you try in SQL Server, will work. I was only using a
simple query to illustrate the problem. A more realistic example is below. No
variables, just straight SQL in CF:

<!--- this will work --->
<cfquery datasource="test">
select name into ##myTable from sysfiles where name = 'master'
select * from ##myTable
drop ##myTable
</cfquery>

<!--- this will not work --->
<cfquery datasource="test">
select name into ##myTable from sysfiles where name = <cfqueryparam
value="master" cfsqltype="cf_sql_varchar" />
select * from ##myTable
drop ##myTable
</cfquery>

[BKBK]

I now understand. It's indeed worth a look.

[BKBK]

<!--- this will not work --->
Returning to it, what error did you get?