<CFQUERYPARAM> Tag - ColdFusion


>> Return to ColdFusion Tag Index

The <CFQUERYPARAM> tag allows you ...



  <CFQUERYPARAM> Comments from Macromedia LiveDocsLiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
To gregorC's comment on cfqueryparam LIKE clause workaround... that's a bit overkill.

You can just do:

SELECT ...
WHERE sr.DriverName LIKE '%' + <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.DriverName#" maxlength="100" /> + '%'

CF just appends another variable declaration to yours.. so it's a bit redundant.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I've also recently experienced something in addition to the wildcard (select *) bug. Even if you have explicitly defined your column list in your SQL statement (stored procedure or otherwise), and append a column to that list, the use of <cfqueryparam will still retrieve the old columnlist. I'm going to guess that it's really the way that <cfqueryparam uses the bind variables in certain DB's which retrieve an existing execution plan for the given query. In this case the use of explicitly named columns still causes and error when adding a column for use in a CFQUERY resultset.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
MS SQl Server 2000 mappings of data type to CFSQLTYPE:

cf_sql_bigint bigint
cf_sql_bit bit
cf_sql_char char, nchar
cf_sql_date [smalldatetime]
cf_sql_decimal numeric, decimal
cf_sql_double double, float
cf_sql_float double, float
cf_sql_idstamp timestamp
cf_sql_integer int
cf_sql_longvarchar text
cf_sql_money money
cf_sql_money4 smallmoney
cf_sql_numeric numeric, decimal
cf_sql_real real
cf_sql_refcursor cursor
cf_sql_smallint smallint
cf_sql_time
cf_sql_timestamp datetime
cf_sql_tinyint tinyint
cf_sql_varchar varchar, nvarchar,uniqueidentifier


This can be useful for people who are confused with DATETIME and etc...

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
LIKE Workaround for CFQueryParam


DECLARE @tempDriverName varchar(100);
SET @tempDriverName = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.DriverName#" maxlength="100">;

SELECT ...
WHERE sr.DriverName LIKE '%' + @tempDriverName + '%'

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
the cfqueryparam tag causes an 'Invalid parameter binding(s)' error, when used on an update query to set fields. The update query works fine if i just pass in the variables normally, but the error occurs when I use the cfqueryparam tag. Also, I can use the cfqueryparam tag on the 'wehere' clause in the query with no problems. It is just on the 'set' clauses that the error occurs.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
In response to dorange;
You might try enabling Unicode for the data source.
To do so:
1. Open the ColdFusion Administrator.
2. Go to the data sources page.
3. Click the name of the data source.
4. Click Show Advanced Settings.
5. Check the "Enable Unicode for data sources configured for non-Latin characters" check box.
6. Click Submit.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I didn't try other databases but this tag doesn't support double-byte characters on MS SQL 2000. MSSQL is peculiar in that it requires prefixing double byte text with an N, for example:
...
SET dbtxt= N'double-foo-text'
....
When i put this in cfqueryparam, it inserts N and quotes and text. Well, documents should say something about usage with double-byte chars. If tag doesn't support them, docs should say so.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I found that the bug is caused by using a return immediately after a star. So instead of the following:

SELECT Note.*,
Name.FirstName
FROM Note
INNER JOIN Note ON Name.ID = Note.CreatedByUserID
WHERE Name.ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">

I used the following to workaround the problem:

SELECT Note.*, Name.FirstName
FROM Note
INNER JOIN Note ON Name.ID = Note.CreatedByUserID
WHERE Name.ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">

It seemed to have worked for the one instance I had the problem. I'm not sure if the problem will return.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Great idea! I entered enhancement request 60636 into our system. FYI, you can enter enhancement requests yourself through http://www.macromedia.com/go/wish/.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
It would be really nice if there could be a function version of this tag.

Consider:
---
INSERT
INTO member
(title, forename, surname)
VALUES
(
<cfqueryparam value="#Arguments.title#"
cfsqltype="CF_SQL_CHAR" maxlength="15"/>,
<cfqueryparam value="#Arguments.forename#"
cfsqltype="CF_SQL_CHAR" maxlength="30"/>,
<cfqueryparam value="#Arguments.surname#"
cfsqltype="CF_SQL_CHAR" maxlength="30"/>
)
---

compared to:

---
INSERT
INTO member
(title, forename, surname)
VALUES
(
#QueryParam(Arguments.title, 'CHAR', 15)#,
#QueryParam(Arguments.forename,'CHAR', 30)#,
#QueryParam(Arguments.surname, 'CHAR', 30)#
)
---

Much much nicer, easier to read and understand, and doesn't have such a significant impact on the width/height of large queries...

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I suppose you might call it a bug. However, it's easy to imagine the cfqueryparam might have a different processing path depending on whether you specify * or name columns specifically.

If you feel it's a bug, you can report it at http://www.macromedia.com/support/email/wishform/main.cgi

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I've had a problem using query param... it's a bit wierd.

I have a query
select float_colum from table where id = cfqueryparam cfsqltype="CF_SQL_INTEGER" value="45" null="No"

which works OK

if I change the query:
select * from table where id = cfqueryparam cfsqltype="CF_SQL_INTEGER" value="45" null="No"

the float value get converted to an integer.

is this a bug?

I'm using sql2000 on win2k3 with mx6.1

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I forgot to mention in my above post that if I do the exact same situation but without the '%' entered in the value area like...

<cfset variables.string = "%">

<cfqueryparam value="#variables.string#">

that this seems to search for... \% and not %

This is not true with my above comment.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I am under the impression that the cfqueryparam will escape the sql characters that are passed in through a variable but will not do so for strings you enter in.

For instance:

<cfset variables.string = "%">

<cfqueryparam value="%#variables.string#%">

This would search for... %\%%

but....

if it seems to seach for %%%.

Is this intended?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I checked with one of our technical people and this is what he said:
*****
Most database (including SQLServer) presume that the database structure does not change between re-uses of parameterized queries.

Here are three possible solutions. Try #1 first - it may be enough to fix the problem.
With #2 and #3 efficiency is reduced because the SQL statement will be compiled more often.

1. Avoid using wildcard SELECT * expressions.

The expansion of the "*" is the part of the SQL statement most likely to change when the schema is altered, making the compiled SQL statement become invalid. If the specific fields are listed (and presuming they are still valid fields after the schema change) the compiled statement might still be useable.

2. Change [Max Pooled Statements] in Datasource Advanced Settings to zero.

3. Use inline arguments instead of <cfqueryparam> to cause the SQL statement be re-compiled by SQLServer for every request.
*****

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Does anyone know how to use the CFQUERYPARAM tag when you want to use one of the db functions? I want to insert a date using Oracle's to_date function & use CFQUERYPARAM but I get different Oracle errors each way I try.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Does anyone have answers on the issue with CFQUERYPARAM originally posted by WillRocks? We've had the same problem with our servers. The problem appears to originate when we change table structure on a large table in our database (We're running MSSQL Server 2000 Enterprise). We tested this out and as soon as we added a column to a large table (2+ million records), we immediately got CFQUERYPARAM errors (unable to convert data type). This is quite frustrating because we have 7 servers on which we have to cycle ColdFusion when this happens (we have ColdFusion MX 6.1 on each of them). I hope someone has an answer for this...

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Something's messed up with cfquerparam when used within the order by SQL statement.
It was like it was ignoring the order by entirely.

<cfif isdefined("arguments.sortby") and arguments.sortby neq "">
order by <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sortby#">
</cfif>

is what I had at the bottom of my query in the CFC. There was no CF Error Message or anything to assist me in finding out where the problem was.

I took the SQL code generated by ColdFusion (in the debug section),
dumped that into Oracle and BAM ordered by correctly.
I thought something was up with my <cfoutput>
I'm using a nested <cfoutput> with the group= attribute.
I tried everything I could think of.. finally asked a friend who
fortunately for me said that he ran into problems using the
<cfqueryparam> tag in the ORDER BY statement..
I took it out and replaced with
<cfif isdefined("arguments.sortby") and arguments.sortby neq "">
order by #arguments.sortby#
</cfif>
and it worked.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
? using LIKE in cfqueryparam. Is it possible to have a like statement with percent signs? Works fine without %, but pt of like-stmt is to match patterns? Ex:
<cfquery name="myQry" datasource="myDS">
SELECT * FROM job WHERE title LIKE
<cfqueryparam value="%#myTitle#%" cfsqltype="cf_sql_varchar">
</cfquery>

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I find this a bit short on insert queries. You speak to select queries but I can find no exeplars for an Insert query, notably how cfqueryparam is used in the values line of the query statement.

thanks!

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
I did a quick inquiry and a quick test and it does not appear that this will work.
However, you might be able to get more information from the ColdFusion Forums, http://webforums.macromedia.com/coldfusion/.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
If you use coldfusion variables to specify field and table names, should you use cfqueryparam for that too? Will it make a difference?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
The maxlength attribute ensures that the length check is done by ColdFusion before the string is sent to the DBMS.
It can help ensure security by preventing the submission of malicious strings to the DBMS.
For more information on using the maxlength attribute see http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/queryd14.htm.
For more information on using cfqueryparam, see http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/queryd11.htm and the pages that follow it. (There is also a link to the information page in the Description section of this page.)

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Can anyone explain to me the use of the maxLength attribute? I really do not see the need to use it.

It does not automatically truncate a string! so all I can work out is...

An error is thrown in coldfusion if a string exceeds the maxlength, if you omit maxLength then the error is thrown from the database if the string exceeds the maxlength of a column. Is this all it does?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
You shouldn't use select * in queries using the cfqueryparam tag. It can cause unexpected errors when you add, remove or modify the table's columns as explained here: http://www.1pixelout.net/index.cfm/blog/entry/cfqueryparam.htm

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
<cfqueryparam> doesn't support double byte character. I think it will be a great barrier to the usage on it.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
No, it doesn't support the Oracle xmltype. This has been entered as an enhancement request for consideration for a future release.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Does this tag support the Oracle xmltype?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
You are correct, rev_robert. I've entered a doc bug (#55016) on this issue. Thanks for your feedback.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm
Based on the documentation and the recommendation contained within the same, I modified many of my queries to use the <cfqueryparam>. Most of these queries also used the cached-within parameter. I guess I should have tested one before changing all, but on testing I got the following error:

Using "cachedWithin" or "cachedAfter" in CFQUERY with CFQUERYPARAM is not allowed.

It would have been nice if this was stated in the documentation for <cfqueryparam>. Now I have to undo all those changes.

We trust the documentation to identify all the attributes, properties, behaviors, and limitations for tags. Failure to do so is frustrating and is a breech of trust.

Thank you.

Robert



[Top]


Recommended Books

[Top]

Web Sites

[Top]

Copyright © 2003- 2008, Orville Paul Chomer, All Rights Reserved