coldfusion/6.1/htmldocs/tags-b20.htm

timschot — 2007-09-04T13:36:20

To gregorC's comment on cfqueryparam LIKE clause workaround... that's a bit overkill.

You can just do:

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

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

coldfusion/6.1/htmldocs/tags-b20.htm

heashon2000 — 2006-06-07T09:39:08

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

coldfusion/6.1/htmldocs/tags-b20.htm

2006-04-26T08:38:29

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...

coldfusion/6.1/htmldocs/tags-b20.htm

GregorC — 2006-04-04T19:38:18

LIKE Workaround for CFQueryParam

DECLARE @tempDriverName varchar(100);
SET @tempDriverName = ;

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

coldfusion/6.1/htmldocs/tags-b20.htm

2005-09-02T06:37:13

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.

coldfusion/6.1/htmldocs/tags-b20.htm

ASandstrom — 2005-08-25T07:58:09

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.

coldfusion/6.1/htmldocs/tags-b20.htm

dorange — 2005-08-24T13:27:53

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.

coldfusion/6.1/htmldocs/tags-b20.htm

2005-07-27T11:32:20

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 =

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 =

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

coldfusion/6.1/htmldocs/tags-b20.htm

jrunrandy — 2005-07-18T14:06:27

Great idea! I entered enhancement request 60636 into our system. FYI, you can enter enhancement requests yourself through http://www.macromedia.com/go/wish/.

coldfusion/6.1/htmldocs/tags-b20.htm

boughtonp — 2005-06-09T08:38:55

It would be really nice if there could be a function version of this tag.

Consider:
---
INSERT
INTO member
(title, forename, surname)
VALUES
(
cfsqltype="CF_SQL_CHAR" maxlength="15"/>,
cfsqltype="CF_SQL_CHAR" maxlength="30"/>,
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...

coldfusion/6.1/htmldocs/tags-b20.htm

jrunrandy — 2005-03-09T11:17:32

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

coldfusion/6.1/htmldocs/tags-b20.htm

topol_sheap — 2005-03-04T03:25:43

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

coldfusion/6.1/htmldocs/tags-b20.htm

FuseDesign — 2004-11-28T22:24:50

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...

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

This is not true with my above comment.

coldfusion/6.1/htmldocs/tags-b20.htm

FuseDesign — 2004-11-28T22:21:35

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:

This would search for... %\%%

but....

if it seems to seach for %%%.

Is this intended?

coldfusion/6.1/htmldocs/tags-b20.htm

jrunrandy — 2004-11-15T11:39:58

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 to cause the SQL statement be re-compiled by SQLServer for every request.
*****

coldfusion/6.1/htmldocs/tags-b20.htm

mitchelj — 2004-11-15T08:15:58

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.

coldfusion/6.1/htmldocs/tags-b20.htm

2004-11-15T06:37:50

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...

coldfusion/6.1/htmldocs/tags-b20.htm

gmorphis — 2004-10-13T12:38:33

Something's messed up with cfquerparam when used within the order by SQL statement.
It was like it was ignoring the order by entirely.

order by

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
I'm using a nested 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
tag in the ORDER BY statement..
I took it out and replaced with

order by #arguments.sortby#

and it worked.

coldfusion/6.1/htmldocs/tags-b20.htm

-ian — 2004-10-04T15:21:26

? 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:

SELECT * FROM job WHERE title LIKE

coldfusion/6.1/htmldocs/tags-b20.htm

Daveb457 — 2004-09-22T07:33:47

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!

coldfusion/6.1/htmldocs/tags-b20.htm

halL — 2004-08-17T13:22:59

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/.

coldfusion/6.1/htmldocs/tags-b20.htm

2004-08-11T07:32:03

If you use coldfusion variables to specify field and table names, should you use cfqueryparam for that too? Will it make a difference?

coldfusion/6.1/htmldocs/tags-b20.htm

halL — 2004-06-22T06:20:27

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.)

coldfusion/6.1/htmldocs/tags-b20.htm

rippo123456 — 2004-06-22T02:35:22

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?

coldfusion/6.1/htmldocs/tags-b20.htm

Doryphores — 2004-06-16T13:01:20

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

coldfusion/6.1/htmldocs/tags-b20.htm

2004-06-02T00:08:06

doesn't support double byte character. I think it will be a great barrier to the usage on it.

coldfusion/6.1/htmldocs/tags-b20.htm

ASandstrom — 2004-05-25T09:15:08

No, it doesn't support the Oracle xmltype. This has been entered as an enhancement request for consideration for a future release.

coldfusion/6.1/htmldocs/tags-b20.htm

comcd — 2004-05-24T14:16:49

Does this tag support the Oracle xmltype?

coldfusion/6.1/htmldocs/tags-b20.htm

ASandstrom — 2004-04-13T07:04:22

You are correct, rev_robert. I've entered a doc bug (#55016) on this issue. Thanks for your feedback.

coldfusion/6.1/htmldocs/tags-b20.htm

rev_robert — 2004-04-10T05:49:03

Based on the documentation and the recommendation contained within the same, I modified many of my queries to use the . 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 . 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

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm

coldfusion/6.1/htmldocs/tags-b20.htm