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?
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.
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.
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.
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">
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">
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'
Copy link to clipboard
Copied
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>
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
For more on this, see "cfqueryparam converting varchar to nvarchar in sql azure".
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.
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://vladmihalcea.com/sql-server-jdbc-sendstringparametersasunicode/
Or, if you are using the default SQL Server settings in the ColdFusion Administrator,
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.
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
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.
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:
Following these steps should have solved the problem.
Copy link to clipboard
Copied
Hi @asiciliano and @D Tripler
Did the setting
sendStringParametersAsUnicode=false
solve the problem?
Copy link to clipboard
Copied
sendStringParametersAsUnicode=false did not solve the problem based on the diagnostic logs for the datasource.
Copy link to clipboard
Copied
I don't understand what you mean by "based on the diagnostic logs for the datasource". Could you please elaborate?
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.
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.
Copy link to clipboard
Copied
Did anyone found any solution to this? This keeps happening even when sendStringParametersAsUnicode=false and String Format is unchecked.