This is a discussion on cfqueryparam behaving really strange - Cold Fusion ; Hi! I have the following scenario. CFMX 7.0 MS SQL 2005 Col1 and Col3 are nvarchar and Col 2 is an int Working sql: (returns all rows matching clause) SELECT * FROM table WHERE col1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#var1#"> AND ...
cfqueryparam behaving really strange Hi!
I have the following scenario.
CFMX 7.0
MS SQL 2005
Col1 and Col3 are nvarchar and Col 2 is an int
Working sql: (returns all rows matching clause)
SELECT * FROM table WHERE col1 = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#var1#"> AND col2 = <cfqueryparam cfsqltype="cf_sql_integer"
value="#var2#"> AND col3 = <cfqueryparam cfsqltype="cf_sql_varchar"
value="staticvalue">
Also working sql (returns all rows matching clause)
SELECT * FROM table WHERE 1=1 AND col1 = <cfqueryparam
cfsqltype="cf_sql_varchar" value="#var1#"> AND col2 = <cfqueryparam
cfsqltype="cf_sql_integer" value="#var2#"> AND col3 = 'staticvalue'
Not working sql (returns only one row)
SELECT * FROM table WHERE col1 = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#var1#"> AND col2 = <cfqueryparam cfsqltype="cf_sql_integer"
value="#var2#"> AND col3 = 'staticvalue'
As you can see, using cfqueryparam with the static value makes query #1 work
and adding 1=1 in the where clause makes query #2 work. But I find it very,
very strange that query #3 shouldn't work - and whats even more strange is that
it somehow at least returns one row...
Has anyone experienced any of this behaviour?
Regards,
Johan
Re: cfqueryparam behaving really strange how many rows does this return:
SELECT *
FROM table
WHERE
col1 = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#var1#">
AND col2 = <cfqueryparam cfsqltype="cf_sql_integer"
value="#var2#">
AND col3 = <cfqueryparam cfsqltype="cf_sql_varchar" value="staticvalue">
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Re: cfqueryparam behaving really strange oops... disregard my post - you already have tried that query. sorry...
no, i haven't come across your issue before, but then i do not use mssql
much...
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Re: cfqueryparam behaving really strange oh, don't you have to add N to the value of nvarchar columns?
iirc, something like AND col3 = N'staticvalue'
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Re: cfqueryparam behaving really strange Yes, of course! Thanks for pointing that out!
Though, it seems like the query becomes more sensitive when using
cfqueryparam. Before I added the cfqueryparam-tag the query looked like this...
SELECT * FROM table WHERE col1 = '#var1#' AND col2 = #var2# AND col3 =
'staticvalue'
...which returned all desired rows without using N for nvarchar.
But anyhow, thanks for your effort!
/Johan
Re: cfqueryparam behaving really strange i came across this in my quick search:
http://kiribao.blogspot.com/2007/11/...ueryparam.html
guess it is a known bug/limitation?
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Re: cfqueryparam behaving really strange Interesting. I've never had this problem. CF_SQL_VARCHAR has always
worked fine with NVARCHAR columns for me. No special settings @ either
end. Odd.
--
Adam
Re: cfqueryparam behaving really strange It seems this issue is sorted for you now.
Just a tip for the future, it's handy if you quantify what you mean by "it
doesn't work"; ie: does it error, does it return something other than what
you expect (if so: what), etc. It makes working out what your problem is a
bit easier.
--
Adam
Re: cfqueryparam behaving really strange Yes, I guess I'll have to accept the fact that I must remember using N in front
of the static variable in the future (which isn't that much of a problem).
And for the explanation of the problem, I did mention in my first post that
query #3 did return at least one row, which makes this problem even stranger as
there are no error code what so ever.
Regards,
Johan
LinkBack URL
About LinkBacks