Participating Frequently

Question

Coldfusion converting varchar to nvarchar

Forum|Forum|3 years ago
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.

D

D Tripler

New Participant

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

Community Expert

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

D

D Tripler

New Participant

For my test the parameter SENDSTRINGPARAMETERSASUNICODE was already in the connection string and set to false since "String Format" for the datasource was unchecked. Just to be as through as possible I reviewed the connection string in neo-datasource.xml and in the datasource logging and confirmed SENDSTRINGPARAMETERSASUNICODE was set to false.

Unless there is a bug in ColdFusion's datasource logging, sadly the solution is not as simple as setting the parameter SENDSTRINGPARAMETERSASUNICODE to false.

BKBK

Community Expert

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

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

Community Expert

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded