Skip to main content
J
Jofi
Participant
August 19, 2008
Answered

cfqueryparam behaving really strange

  • August 19, 2008
  • 4 replies
  • 520 views
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
    This topic has been closed for replies.
    Correct answer Newsgroup_User
    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/

    4 replies

    N
    Newsgroup_User
    Inspiring
    August 19, 2008
    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
    J
    JofiAuthor
    Participant
    August 20, 2008
    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
    N
    Newsgroup_UserCorrect answer
    Inspiring
    August 19, 2008
    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/
    J
    JofiAuthor
    Participant
    August 19, 2008
    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
    N
    Newsgroup_User
    Inspiring
    August 19, 2008
    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/
    N
    Newsgroup_User
    Inspiring
    August 19, 2008
    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/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices