value in CFQUERYPARAM

[BYJ_wntrsnt]

I'm cleaning up some codes and start using <cfqueryparam> tags in all the queries.

There is one query that I'm not 100% sure how to convert to cfqueryparam :

INSERT INTO sometable (Column_1,Column_2, .......)

VALUES ( '#Value_1#', '#Value_2# #Value_3#', ......)

The value inserted into Column_2 shows ' #value_1# #value_3# '

Should I use it this way in cfqueryparam:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#value_1# #value_2#"> ? will this be a proper way to do it?

[Owainnorth · Accepted solution]

That is correct.

[Adam Cameron.]

One value per CFQUERYPARAM tag, surely?!

--

Adam

[Dave Watts]

No, not necessarily. There's nothing wrong with concatenating values to build a single field, which is what the OP is doing.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

[Adam Cameron.]

Wow.  I never took the values in an insert statement to be a list of values; like in a WHERE IN() sort of thing, so the whole lot can be parameterised). I more thought they were individual values separated by a comma; wherein the comma is part of the SQL statement, so could not be paramterised, if you see what I mean.

That's my thing for the day learned.

--

Adam

[-__cfSearching__-]

I could be wrong, but I thought Dave was just referring to normal concatenation ie fullName = "#firstName# #middelInitial# #lastName#"

But I was surprised too that something like this works. At least with mySQL. Though I cannot say I have ever used it outside of test code

INSERT INTO TestTable ( ColumnA, ColumnB, ColumnC )

VALUES (

[ cfqueryparam value="#A#,#B#,#C#" cfsqltype="cf_sql_varchar" list="true" ]

)

[LyndonPatton]

I think the original question is being misread by some... It's not a list value, it's a concatenation

.

This will work:

<cfquery name="putSomeTable" datasource="MySource">
  INSERT INTO sometable (Column_1,Column_2)
  VALUES (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Value_1#">,
  <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Value_2# #Value_3#">)
</cfquery>

[Adam Cameron.]

Ah: good catch. Man: it was a long day. Glad I'm in the pub now.

(in the pub & reading the Adobe forums... hmmm... I hope my mate hurrys up with that pint...)

Cheers.

--

Adam

[-__cfSearching__-]

It's not a list value, it's a concatenation

Agreed it is normal concatenation. The typo in variable names might have caused some confusion. ie ' #value_1# #value_3# ' versus value="#value_1# #value_2#"

[Owainnorth]

I think it varies between databases. Not saying it's something I'd do myself, one param per value as Adam says.

[-__cfSearching__-]

I think it varies between databases. Not saying it's

something I'd do myself, one param per value as Adam says.

Yes, that is definitely the sort of feature that would vary. I probably would not use it either. Even if my insert columns were miraculously all the same data type