Skip to main content
A
asiciliano
Participating Frequently
December 2, 2022
Question

Coldfusion converting varchar to nvarchar

  • December 2, 2022
  • 2 replies
  • 2241 views

I have a simple query that pulls one record from a table based on a varchar(50) uesrname:

 

select top 1 as hasunreadmessages
from usermessages um with (nolock)
left outer join iepreferral on iepreferral.iepcaseid = um.iepcaseid
where um.tousername = <cfqueryparam value="#client.userName#" CFSQLType="CF_SQL_VARCHAR">
and um.isread = 0
and um.isdeleted = 0
and isnull(iepreferral.isDeleted, 0) = 0

 

My database monitor reports the folowing every time this query is run:

 

This ad hoc statement had the following implicit conversions:
The variable @7246612 has a data type of nvarchar which caused implicit conversion on the column [um].[ToUserName] with the value N'testusername'.?>

 

This option in CF Administrator is NOT checked:String Format -- Enable High ASCII characters and Unicode for data sources configured for non-Latin characters

 

Why does CF send the parameter as NVARCHAR?

    This topic has been closed for replies.

    2 replies

    D
    D Tripler
    New Participant
    December 7, 2022

    After enabling logging for a datasource, I noticed the JDBC url had this property. 

    STRINGINPUTPARAMETERTYPE=nvarchar

     Out of curiousity I changed this property to varchar which then sent the parameters as varchar instead of nvarchar. I am a concerned that this property appears to conflict/override the behavior for SENDSTRINGPARAMETERSASUNICODE.

     

    In my quick google search I did not find any significant resources for the STRINGINPUTPARAMETERTYPE property. Without documentation to confirm what the intention of this property, I would be worried that changing it would cause issues with unicode support if there are nvarchar fields in the database.

    BKBK
    BKBK
    Community Expert
    December 8, 2022

    I have never heard of STRINGINPUTPARAMETERTYPE. As Google doesn't know it either, that tells us.

     

    I would suggest you replace it with

    sendStringParametersAsUnicode=false

    as I described in my previous posts

    BKBK
    BKBK
    Community Expert
    December 21, 2022

    sendStringParametersAsUnicode=false did not solve the problem based on the diagnostic logs for the datasource.


    @D Tripler ,

    I don't understand what you mean by "based on the diagnostic logs for the datasource". Could you please elaborate?

    BKBK
    BKBK
    Community Expert
    December 3, 2022

    What is your ColdFusion version? I ask because cf_sql_nvarchar is supported only from ColdFusion 10 onwards.

     

    In any case, ColdFusion seems to be doing you a favour. If the database column has NVARCHAR datatype, then you should check that checkbox.

    A
    asicilianoAuthor
    Participating Frequently
    December 5, 2022

    I am using CF2021   The database does NOT allow for NVARCHAR data types, and the cfqueryparam is set to varchar, not nvarchar.

     

    The fact that this conversion is causing a data type mismatch which then taxes my sql server indicates that this would NOT be desired functionality.

    BKBK
    BKBK
    Community Expert
    December 6, 2022

     

    The fact that this conversion is causing a data type mismatch which then taxes my sql server indicates that this would NOT be desired functionality.


    By @asiciliano

     

    Quite right, @asiciliano .

    Unless, of course, when you try to assign an nvarchar value to um.tousername. The warning from ColdFusion suggests that you're doing that.  

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices