Status/Resolution/Reason: To Fix//BugVerified
Reporter/Name(from Bugbase): Aaron N. / ()
Created: 04/06/2020
Components: Language, Query Functions
Versions: 2018
Failure Type: Incorrectly functioning
Found In Build/Fixed In Build: 2018.0.08.318307 /
Priority/Frequency: Normal / Very few users will encounter
Locale/System: / Windows 10 64 bit
Vote Count: 0
Issue: myQuery.setCell(myBinaryColumn, null) throws "Invalid data '' for CFSQLTYPE CF_SQL_BINARY"
Repro:
Application.cfc:
-----------
component {THIS.name = "MyApp"; THIS.enableNullSupport = true;}
-----------
index.cfm
-----------
<cfscript>
columnNameAndTypeList = ["object","integer","bigint","double","decimal","varchar","binary","bit","time","date","timestamp"];
myQuery = queryNew(columnNameAndTypeList.toList(), columnNameAndTypeList.toList());//Columns w/ specified types are added here
myQuery.addColumn("default", []);//Column w/ unspecified (i.e. 'default') type is added here
myQuery.addRow();
for(columnMetadata in getMetadata(myQuery)) {
try {
if((SERVER.ColdFusion.ProductVersion.listFirst() > 2016) && getApplicationMetadata().enableNullSupport) {
myQuery.setCell(columnMetadata.name, null);
} else {
myQuery.setCell(columnMetadata.name, javaCast("null", ""));
}
findNull = queryExecute("SELECT [#columnMetadata.name#] FROM myQuery WHERE [#columnMetadata.name#] IS NULL", [], {dbtype="query"});
typeName = columnMetadata.keyExists("typeName") ? columnMetadata.typeName : 'NULL';
writeOutput(columnMetadata.name & ' [' & typeName & ']: <span style="color:#findNull.RecordCount?'green':'red'#;">' & yesNoFormat(findNull.RecordCount) & '!</span><br>');
}
catch(any e) {
writeOutput(columnMetadata.name & ': <span style="color:red;">' & e.message & ' - ' & reReplace(e.detail, "<[^>]*>", "", "all") & '</span><br>');
}
}
</cfscript>
-----------
Actual Result:
object [JAVA_OBJECT]: Yes!
integer [INTEGER]: Yes!
bigint [BIGINT]: Yes!
double [DOUBLE]: Yes!
decimal [DECIMAL]: Yes!
varchar [VARCHAR]: Yes!
binary: Invalid data '' for CFSQLTYPE CF_SQL_BINARY. - <= "Invalid data '' for CFSQLTYPE CF_SQL_BINARY. -" should be "[BINARY]: Yes!"
bit [BIT]: Yes!
time [TIME]: Yes!
date [DATE]: Yes!
timestamp [TIMESTAMP]: Yes!
default [NULL]: Yes! <= "NULL" should be "JAVA_OBJECT" (CF-4207855)
Expected Result:
object [JAVA_OBJECT]: Yes!
integer [INTEGER]: Yes!
bigint [BIGINT]: Yes!
double [DOUBLE]: Yes!
decimal [DECIMAL]: Yes!
varchar [VARCHAR]: Yes!
binary: [BINARY]: Yes!
bit [BIT]: Yes!
time [TIME]: Yes!
date [DATE]: Yes!
timestamp [TIMESTAMP]: Yes!
default [JAVA_OBJECT]: Yes!
Verified in CF2016 Update 1 (build 2016.0.01.298513) and CF2018 Update 8 (build 2018.0.08.318307) and later.Attachments:
Comments: