problem with ColdFusion cfqueryparam and a complicated query

0
votes

I have a query where I am inserting ~300 records using one insert query. For each record, I have to do replaces and operations on the data. I just found out I have to use cfqueryparam now because one of the records is a CLOB and is reaching a large size. I was using a string to form the query and placing the string in the cfquery tag. Now I can't do that because you have to use PreserveSingleQuotes to put a string var in a cfquery tag and you can't use PreserveSingleQuotes with cfqueryparam. So is my best option to do all the operations and looping inside the cfquery tag? Will it be able to handle that? Or will I have to do 1000 separate insert statements? Is there a better idea? thanks.

2
stringcoldfusioncfqueryparam
Can you show us some of the code in question? – duncan

2 Answers

2
votes

If you're only doing PreserveSingleQuotes, then the above answer works.

If you're doing complex manipulation, then do your operations on your data outside of cfquery and store your good values into an array.

Inside the cfquery, loop through the array and put the values into cfqueryparam.

0
votes
<cfqueryparam value="#PreserveSingleQuotes(whatever)#" CFSQLType="CF_SQL_CLOB" />

Should work in CFQUERY.