This is one solution. The problem here is not that the SQL is being built dynamically -- somehow the thread got off track with that assumption. The real problem is, at least in CF 5.0 (which is the version Jason Kufner is using), single quotes are not escape in a complex variable for whatever reason (at least in arrays, I haven't tested it otherwise).
Instead of setting a variable equal to the array member for each pass, you can do what Jochem has been saying: use cfqueryparam (it works, I tested it on dummy data; "loopnum" identifies the current array position): <cfquery name="newAuthorBio" datasource="#application.dsn#"> INSERT into authorBio (authorBio_authorId,authorBioDetail) VALUES ( <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#session.authorPkey#">, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#bookArray[loopnum]#"> ) </cfquery> You might have to use a cfsqltype of CF_SQL_LONGVARCHAR depending on what datatype your field is set to. > -----Original Message----- > From: Richard Meredith-Hardy [mailto:rmh@;flymicro.com] > Sent: Monday, November 04, 2002 9:32 AM > To: CF-Talk > Subject: Re: HELP!! sql wierdness ERROR message > > > Hold on a second guys, all Jason said was > > > -This is representative some of the data being inserted. This > was a NASTY > > 550 page file maker pro data base that I'm converting. Some > fields are empty > > some are huge like this one. > > So I assume with these looped queries all he is trying to achieve is > some sort of one shot data transfer / conversion operation (which is > failing for some reason). Nothing to do with production, so speed is > more or less irrelavent. > > I have occasionally done thousands of looped inserts like this with some > sort of quick & dirty coding to achieve some sort of one-off data > conversion and never had to resort to adjusting connection pooling or > any of the other special solutions being suggested here. > > My solution? > > 1) try setting the variable as a simple value before the query so CF has > every opportunity to do its built-in escaping of special characters in > the query, as I suggested before: > > <CFSET st = bookArray[43]> > > <cfquery name="newAuthorBio" datasource="#application.dsn#"> > INSERT into authorBio (authorBio_authorId,authorBioDetail) > VALUES (#session.authorPkey#, '#st#') > </cfquery> > > if still no worky: > > 2) Put the query in a <CFTRY> <CFCATCH> block set up to abort the loop > on the first failure and display the raw uadulterated string causing the > failure. > > Let's have a look at that string.... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
