Error with PreserveSingleQuotes in query involving cfqueryparam

[Tom Lenz]

This code:

<cfset fields = 'work_package'>
<cfset values = '<cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="64" value="">'>

<cfquery datasource="dsn" result="qFB">
INSERT INTO timesheet_entries
(#fields#)
VALUES
(#PreserveSingleQuotes(values)#)
</cfquery>

reports errors about the SQL syntax. (You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '<cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="64"
value="">)' at line 4 SQL )

But if I take the resulting code, the code that the debug output tells me has a syntax error in it,

INSERT INTO timesheet_entries (WORK_PACKAGE) VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="64" value="">)

and run it, it works fine.

I'm using PreserveSingleQuotes because sometimes, though not in this example of course, values has a timestamp value in it.

Two issues occur to me.
1. Shouldn't this code work?
2. How can the error output say there's a syntax problem with code that actually works?

[Peter Boughton]

No, it shouldn't work; you are trying to insert <cfqueryparam...> into
the SQL code.

<cfqueryparam...> is CFML code, and must be evaluated as such before
the query gets to the SQL side.

[Peter Boughton]

Also, I'm assuming this is a simplified version in an attempt to
isolate the issue, otherwise you'd just do:

> <cfquery  datasource="dsn" result="qFB">
>        INSERT INTO timesheet_entries

>       (work_package)

>        VALUES
>        (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="64"
> value="">)

> </cfquery>

Which will work.

Can you elaborate on the problem you're trying to solve, and we can
perhaps offer a suitable alternative?

[Tjarko Rikkerink]

You are using the tag <cfquerparam /> as a string value.

[Tom Lenz]

Peter,

Yes, a simplified version. Originally I was just going to save only values that had been entered from a form. Something
like:

<cfloop collection="#stCreate#" item="key">

<cfset fields &= "#key#,">
<cfswitch expression="#key#">
<cfcase value= 'description'>
<cfset values &= "<cfqueryparam cfsqltype='CF_SQL_VARCHAR' maxlength='256' value='#stCreate[key]#'>,">
</cfcase>
<cfcase value= 'timesheet_id'>
<cfset values &= "<cfqueryparam cfsqltype='CF_SQL_INTEGER' value='#stCreate[key]#'>,">
</cfcase>
<cfcase value= 'entry_date'>
<cfset values &= "<cfqueryparam cfsqltype='CF_SQL_DATE' value=""#stCreate[key]#"">,">
</cfcase>
<cfcase value= 'work_package'>
<cfset values &= "<cfqueryparam cfsqltype=""CF_SQL_VARCHAR"" maxlength=""64"" value=""#stCreate[key]#"">,">
</cfcase>
<cfcase value= 'hours'>
<cfset values &= "<cfqueryparam cfsqltype='CF_SQL_DECIMAL' value='#stCreate[key]#'>,">
</cfcase>

<cfdefaultcase></cfdefaultcase>
</cfswitch>

</cfloop>

[Tom Lenz]

On 2/2/2011 1:33 PM, Tjarko Rikkerink wrote:
> You are using the tag <cfquerparam /> as a string value.

Yes. I thought it should work thinking that the coldfusion inside the query would produce sql that gets sent to the
database. And, judging from the alleged syntax errored SQL from the debug info, seems like it almost works.

[Peter Boughton]

Ok, well give something like this a try...

<cfset FieldParams =
{ description : { cfsqltype : 'CF_SQL_VARCHAR' , value :
stCreate[key] , maxlength : 256 }
, timesheet_id : { cfsqltype : 'CF_SQL_INTEGER' , value : stCreate[key] }
, entry_date : { cfsqltype : 'CF_SQL_DATE' , value : stCreate[key] }
} />

<cfquery ...>
INSERT INTO timesheet_entries
(#ArrayToList(Fields)#)
VALUES
(
<cfloop index="Key" array="#Fields#">
<cfif NOT FirstRow>,</cfif>
<cfqueryparam attributecollection=#FieldParams[Key]# />
</cfloop>
)
</cfquery>

In your actual code, build the FieldParams structure dynamically based
on what is provided by the form (or remove ones that aren't provided,
whatever makes sense).

I've not actually tested attributecollection with cfqueryparam yet,
but it should work. In theory. :)

[Michael Offner]

you cannot do something like that, "values" is not interpreted and even you use a evaluate it will still fail because the interpreter can only interpret cfscript expressions no tags.

use attribute "attributecollection" for tag cfqueryparam instead.

example (not tested):

<cfset fields = 'work_package,work_package2'>

<cfset attrs = [

{cfsqltype="CF_SQL_VARCHAR", maxlength="64", value=""},
{cfsqltype="CF_SQL_VARCHAR", maxlength="32", value=""}

]>  
<cfquery  datasource="dsn" result="qFB">
       INSERT INTO timesheet_entries
      (#fields#)
       VALUES

       (<cfloop from="1" to="#listLen(fields)#" index="i"><cfqueryparam attributeCollection="#attrs[i]#"></cfloop>)
</cfquery>

/micha

2011/2/2 Tom Lenz <astonis...@gmail.com>

[Tom Lenz]

Thanks Peter,

That's similar to what Michael suggested. It gave me errors:

Message	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7 '2011-02-02' )' at line 10
SQL	INSERT INTO timesheet_entries (description,timesheet_id,entry_date) VALUES ( 'busier than a one armed paper hanger' 7 {d '2011-02-02'} )

Not seeing commas in the values portion, I tried this for the loop:

    <cfset firstRow = true>

    <cfloop index="Key" array="#Fields#">

        <cfif NOT FirstRow><cfoutput>,</cfoutput> <cfset firstRow = false></cfif>

        <cfqueryparam attributecollection=#FieldParams[Key]# />
    </cfloop>

with the same results.

[Peter Boughton]

Odd - the lack of comma is the cause of the first error, so putting
the comma in should at least give a different error.

Can you test it again with your simple example - i.e. only one field
so comma is not relevant - and see if that errors?

[Tom Lenz]

Well what I already did try this:

INSERT INTO timesheet_entries
(#Fields#)
VALUES
(

<cfqueryparam attributecollection=#FieldParams['description']# />,
<cfqueryparam attributecollection=#FieldParams['timesheet_id']# />,
<cfqueryparam attributecollection=#FieldParams['entry_date']# />
)

And that works fine.

[Tom Lenz]

I mean:

INSERT INTO timesheet_entries
(#ArrayToList(Fields)#)
VALUES
(

<cfqueryparam attributecollection=#FieldParams['description']# />,

<cfqueryparam attributecollection=#FieldParams['timesheet_id']# />,
<cfqueryparam attributecollection=#FieldParams['entry_date']# />
)

And that works fine.

On 2/2/2011 3:46 PM, Peter Boughton wrote: