• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Coldfusion converting varchar to nvarchar

New Here ,
Dec 02, 2022 Dec 02, 2022

Copy link to clipboard

Copied

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 @p1 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?

Views

911

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 03, 2022 Dec 03, 2022

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 05, 2022 Dec 05, 2022

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

 

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.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

This is happening to MANY (if not all) varchar fields that will NEVER allow NVARCHAR values.  I only posted a single, simple example.   If my database does not allow nvarchar values and I set the query param to the followiung, I would prefer it to honor those settings:

 

<cfqueryparam value="ONLYVARCHARTEXT" cfsqltype="cf_sql_varchar">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

The way I understand ColdFusion's warning, your code perhaps contains something like

<cfqueryparam value="N'SOMETEXT'" cfsqltype="cf_sql_varchar">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

I wish that were true.   I have many of these warnings that the data is what is in the quotes.  I am not sure why the sql monitor puts the "N" in front of it. 

 

The variable @P3 has a data type of nvarchar which caused implicit conversion on the column [sy].[LocationID] with the value N'31'.

The variable @P4 has a data type of nvarchar which caused implicit conversion on the column [scg].[CourseID] with the value N'08103'.

The variable @Deleted User has a data type of nvarchar which caused implicit conversion on the column [scg].[SectionId] with the value N'03'

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

The data being sent from the previous example is:

@P3 = 31

@P4 = 08103

@Deleted User = 03

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

  I am not sure why the sql monitor puts the "N" in front of it. 

 


By @asiciliano

 

I'm not sure why either. Strange.

 

For the time being, a possible workaround is to use script syntax for the queries. For example, replace

<cfquery name="UnreadUserMessages" datasource="theDSN">
	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
</cfquery>

 

with

 

<cfscript>
sqlString="select top 1 as hasunreadmessages
	from usermessages um with (nolock)
	left outer join iepreferral on iepreferral.iepcaseid = um.iepcaseid
	where um.tousername = :clientUserName
	and um.isread = 0
	and um.isdeleted = 0
	and isnull(iepreferral.isDeleted, 0) = 0;";
sqlParams.clientUserName={value: client.userName, cfsqltype: "CF_SQL_VARCHAR"};
options.datasource="theDSN";

UnreadUserMessages=queryExecute(sqlString, sqlParams, options);
<cfscript>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

Found the cause of the problem - and the solution: you need to add the parameter sendStringParametersAsUnicode=false to the JDBC URL

BKBK_0-1670339887250.png

 

For more on this, see "cfqueryparam converting varchar to nvarchar in sql azure".

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

That is unfortunately not an option for Microssoft SQL Server datasources.   CF claims checking the box in the screenshot will send varchar as nvarchar, but we do NOT have it checked.

 

Screen Shot 2022-12-06 at 11.00.46 AM.pngScreen Shot 2022-12-06 at 10.58.56 AM.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 06, 2022 Dec 06, 2022

Copy link to clipboard

Copied

You've probably misunderstood what I mean. You should add the flag sendStringParametersAsUnicode=false to the JDBC URL. I used MySQL only as example. You can do the same for SQL Server, for example, a JDBC URL field consisting of

 

jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=high_performance_sql;sendStringParametersAsUnicode=false;

 

See 

https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-se... 

https://vladmihalcea.com/sql-server-jdbc-sendstringparametersasunicode/ 

 

Or, if you are using the default SQL Server settings in the ColdFusion Administrator,

BKBK_0-1670360711144.png

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 07, 2022 Dec 07, 2022

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 08, 2022 Dec 08, 2022

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 08, 2022 Dec 08, 2022

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 08, 2022 Dec 08, 2022

Copy link to clipboard

Copied

That tells me that Adobe's ColdFusion Team might have added the setting STRINGINPUTPARAMETERTYPE to ColdFusion. Let's hope a team member will respond.

 

In any case, when you say,

 

"For my test the parameter SENDSTRINGPARAMETERSASUNICODE was already in the connection string and set to false since "String Format" for the datasource was unchecked."

 

it is unclear to me whether you have done the required test, namely:

  1.  to actually append the parameter sendStringParametersAsUnicode=false to the JDBC URL (if you're using a custom JDBC URL) or to actually add it to the Connection String field (if you're using the default MS SQL Server settings in the Administrator);
  2.  to LEAVE OUT the setting STRINGINPUTPARAMETERTYPE;
  3.  to leave unchecked the checkbox "String Format";
  4.  to restart ColdFusion.

Following these steps should have solved the problem.

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 20, 2022 Dec 20, 2022

Copy link to clipboard

Copied

Hi @asiciliano  and @D Tripler 

Did the setting 

sendStringParametersAsUnicode=false

solve the problem?

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 20, 2022 Dec 20, 2022

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 21, 2022 Dec 21, 2022

Copy link to clipboard

Copied

@D Tripler ,

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 22, 2022 Dec 22, 2022

Copy link to clipboard

Copied

The diagnostic log I was referring to is available when the "Log Activity" setting in the Advanced Settings for a datasource is enabled. The log will contain details like the full jdbc url, connection options, driver details, queries executed including the parameters, and the data type for the columns in the query result set.

 

jdbc_logging.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 22, 2022 Dec 22, 2022

Copy link to clipboard

Copied

Hi @D Tripler ,

Thanks for your explanation. Understood.

 

I suspect that the reporting of the "Log Activity" contains an error. For a start, take the setting you mentioned earler, "STRINGINPUTPARAMETERTYPE". As far as I know, there is no such setting in relation to MS SQL Server.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 03, 2023 Sep 03, 2023

Copy link to clipboard

Copied

LATEST

Did anyone found any solution to this? This keeps happening even when sendStringParametersAsUnicode=false and String Format is unchecked. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation