Highlighted

Insert into SQL Temp table with cfqueryparam

New Here ,
Apr 21, 2017

Copy link to clipboard

Copied

Has anyone successfully inserted values into SQL temp table using cfqueryparam and able to called from it?

The below codes work just fine and only failed when replaced

values ('myValue')  with  values (<cfqueryparam value="myValue" cfsqltype="cf_sql_varchar" >)

Appreciated if someone can take a look and let me know what is going on.

<cfquery name="myQry" datasource="myDSN" >

  CREATE TABLE ##tmpTable
  (
  column1 nvarchar(50)
  )

  insert into ##tmpTable (column1) values ('myValue')

</cfquery>

<cfquery name="tmpTableQry" datasource="myDSN" >
  select colum1 from ##tmpTable
< /cfquery>

<cfdump var="#tmpTableQry#">

CF11

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

Near as I can figure from Microsoft's documentation, you can't use prepared statements with temporary tables.  From SQL Server 2014 docs:

In SQL Server 2005, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Not entirely on point, but close.

I ran the code sample you provided on ColdFusion 2016 and SQL Server 2012 (that's what I have) without the <cfqueryparam> and as you noted it works.  Then I added the <cfqueryparam> which causes the query to be processed by JDBC as a prepared statement, and it failed.  Then I turned on SQL Profiler and ran both ways again.  When run with the <cfqueryparam>, the whole first <cfquery> statement never even gets passed into SQL Server.  Only the second <cfquery> runs, but throws an error because the temporary table doesn't exist since the first <cfquery> didn't run.  It looks to me like attempting to run the <cfqueryparam> version of the query gets swallowed by the JDBC driver (probably because the driver is smart enough to recognize that the query can't be run as a prepared statement) and never even makes it to SQL Server.

Bottom line - it looks like you cannot use <cfqueryparam> with temporary tables due to SQL Server's restrictions.

-Carl V.

Views

777

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Insert into SQL Temp table with cfqueryparam

New Here ,
Apr 21, 2017

Copy link to clipboard

Copied

Has anyone successfully inserted values into SQL temp table using cfqueryparam and able to called from it?

The below codes work just fine and only failed when replaced

values ('myValue')  with  values (<cfqueryparam value="myValue" cfsqltype="cf_sql_varchar" >)

Appreciated if someone can take a look and let me know what is going on.

<cfquery name="myQry" datasource="myDSN" >

  CREATE TABLE ##tmpTable
  (
  column1 nvarchar(50)
  )

  insert into ##tmpTable (column1) values ('myValue')

</cfquery>

<cfquery name="tmpTableQry" datasource="myDSN" >
  select colum1 from ##tmpTable
< /cfquery>

<cfdump var="#tmpTableQry#">

CF11

Most Valuable Participant
Correct answer by Carl_Von_Stetten | Most Valuable Participant

Near as I can figure from Microsoft's documentation, you can't use prepared statements with temporary tables.  From SQL Server 2014 docs:

In SQL Server 2005, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Not entirely on point, but close.

I ran the code sample you provided on ColdFusion 2016 and SQL Server 2012 (that's what I have) without the <cfqueryparam> and as you noted it works.  Then I added the <cfqueryparam> which causes the query to be processed by JDBC as a prepared statement, and it failed.  Then I turned on SQL Profiler and ran both ways again.  When run with the <cfqueryparam>, the whole first <cfquery> statement never even gets passed into SQL Server.  Only the second <cfquery> runs, but throws an error because the temporary table doesn't exist since the first <cfquery> didn't run.  It looks to me like attempting to run the <cfqueryparam> version of the query gets swallowed by the JDBC driver (probably because the driver is smart enough to recognize that the query can't be run as a prepared statement) and never even makes it to SQL Server.

Bottom line - it looks like you cannot use <cfqueryparam> with temporary tables due to SQL Server's restrictions.

-Carl V.

Views

778

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 21, 2017 0
Most Valuable Participant ,
Apr 21, 2017

Copy link to clipboard

Copied

Near as I can figure from Microsoft's documentation, you can't use prepared statements with temporary tables.  From SQL Server 2014 docs:

In SQL Server 2005, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Not entirely on point, but close.

I ran the code sample you provided on ColdFusion 2016 and SQL Server 2012 (that's what I have) without the <cfqueryparam> and as you noted it works.  Then I added the <cfqueryparam> which causes the query to be processed by JDBC as a prepared statement, and it failed.  Then I turned on SQL Profiler and ran both ways again.  When run with the <cfqueryparam>, the whole first <cfquery> statement never even gets passed into SQL Server.  Only the second <cfquery> runs, but throws an error because the temporary table doesn't exist since the first <cfquery> didn't run.  It looks to me like attempting to run the <cfqueryparam> version of the query gets swallowed by the JDBC driver (probably because the driver is smart enough to recognize that the query can't be run as a prepared statement) and never even makes it to SQL Server.

Bottom line - it looks like you cannot use <cfqueryparam> with temporary tables due to SQL Server's restrictions.

-Carl V.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 21, 2017 1