tracker issue : CF-3806744

select a category, or use search below
(searches all categories and all time range)
Title:

Query of Queries does not maintain original/parent query datatype for cf_sql_datatype nvarchar

| View in Tracker

Status/Resolution/Reason: To Fix/Fixed/BugVerified

Reporter/Name(from Bugbase): Unknown Unknown / Unknown Unknown (Unknown Unknown)

Created: 08/17/2014

Components: Database

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final /

Priority/Frequency: Major / All users will encounter

Locale/System: English / Win All

Vote Count: 1

Verification notes: verified_not_fixed on August 06, 2017 using build 2016.0.01.298513 
Related Bugs:
CF-4007420 - Similar to


Problem Description: 
When performing a Query of Queries statement, the original datatype from the original/parent query is not maintained. If the original query contains a column with a cf_sql_datatype attribute of nvarchar, then this is not maintained after the recordset is returned. So if you now wish to do a Query of Queries statement that includes a string comparison WHERE statement such as: ".... WHERE Column1 = <cfqueryparam value="SomeValue" cf_sql_datatype="nvarchar"/>" then it returns an error of "PreparedStatement.setNString(int,java.lang.String)" even though "Column1" has a datatype of nvarchar in the database. This is because the recordset returned by ColdFusion has changed it from an nvarchar to normal varchar datatype.

Steps to Reproduce:

Actual Result:

Expected Result:

Any Workarounds:
The only workaround is to specify varchar as the cf_sql_datatype value after the recordset is returned (when executing a Query of Queries statement)

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3806744

External Customer Info:
External Company:  
External Customer Name: solaced
External Customer Email:  
External Test Config: My Hardware and Environment details:

Windows 7 Ultimate 64bit / Windows Server 2008 R2

IIS 7.5

ColdFusion 10 Developer and Enterprise

Attachments:

Comments:

Title should read: Recordsets returned by ColdFusion do not maintain the datatypes of columns from the database in the case of datatype nvarchar
Comment by External U.
11344 | August 17, 2014 08:53:59 AM GMT
DD issue. DD returns wrong typeID but correct typeName. We use the typeID in QoQ. SQLServer driver properly returns the sql type of the column. We will also have to make some changes in our code after DD fixes it.
Comment by Himavanth R.
11345 | November 18, 2014 06:04:20 AM GMT
Adobe, In the following (where mynvarchar is an nvarchar field), getMetadata(q)[1].typeName returns "nvarchar". So, it appears the issue is QoQ-specific. <cfquery name="q"> SELECT mynvarchar FROM mytable </cfquery> <cfdump var="#getMetadata(q)[1].typeName#" /> <cfquery name="qoq" dbtype="query"> SELECT * FROM q WHERE mynvarchar = <cfqueryparam cfsqltype="nvarchar" value="foo" /> </cfquery> <cfdump var="#qoq#" /> Thanks!, -Aaron
Comment by External U.
11346 | November 23, 2014 09:46:06 PM GMT
+1 ......................
Vote by External U.
11352 | November 23, 2014 09:46:20 PM GMT
Was this actually fixed?? There is no comment from Adobe as regards to fixing it yet it is marked as such.
Comment by External U.
11347 | July 30, 2015 07:53:52 PM GMT
Verified this is NOT fixed in CF2016 Update 1 (2016.0.01.298513) and CF2016 Update 4 (build 2016.0.04.302561). QoQ still throws same "PreparedStatement.setNString(int,java.lang.String)" exception for nvarchar. Verified w/ MSSQL 2012 on Windows 2012 R2. Just use the repro code from my 11/23/2014 21:46:06 GMT comment above. This ticket needs re-opened/fixed. Thanks!, -Aaron
Comment by Aaron N.
11348 | August 06, 2017 06:42:01 PM GMT
Also fails for cfsqltype="CF_SQL_NVARCHAR" (which, BTW, should be documented per CF-4199434).
Comment by Aaron N.
11349 | August 06, 2017 08:02:45 PM GMT
*bump* Adobe? Again, this is _not_ fixed. Please see above comments. Please re-open. Thanks!, -Aaron
Comment by Aaron N.
11350 | August 22, 2017 07:50:43 PM GMT
Aaron, Thanks for pointing this out. Can you please confirm which CF version did you try it on? Please try adding this connection setting "jdbcbehavior=0" in the data source definition and see if it works for you. I will verify it once again and re-open it if it does not work. -Nimit
Comment by Nimit S.
11351 | October 01, 2017 02:20:08 PM GMT
Hi Nimit, You're very welcome and sorry I did not see your reply till now. However I'd already answered your "which CF version" earlier in this ticket. Please see my '08/06/2017 18:42:01 GMT' comment: "Verified this is NOT fixed in CF2016 Update 1 (2016.0.01.298513) and CF2016 Update 4 (build 2016.0.04.302561) [....] Verified w/ MSSQL 2012 on Windows 2012 R2". Also, just confirmed this is not fixed in CF2018 Update 4 (build 2018.0.04.314546) on x64 Windows 10 w/ MSSQL 2012. Regarding "jdbcbehavior=0", that connection setting has no effect (in any of the builds I mentioned). Could Adobe please re-open/fix this ticket? Thanks!, -Aaron
Comment by Aaron N.
31323 | September 12, 2019 10:40:09 AM GMT
Please see my '11/23/2014 21:46:06 GMT' comment for repro case. Thanks!, -Aaron
Comment by Aaron N.
31324 | September 12, 2019 10:51:12 AM GMT
Hi Aaron, I will re-evaluate this issue and re-open if it is still an issue. Thanks for reporting this.  -Nimit
Comment by Nimit S.
31357 | September 23, 2019 06:19:20 PM GMT
Hi Nimit, Thank you very much. Could you please confirm if my repro in my 11/23/2014 21:46:06 GMT comment works for you or not? BTW, CF-4053567 isn't fixed either. Thanks!, -Aaron
Comment by Aaron N.
31448 | September 28, 2019 10:32:38 PM GMT