tracker issue : CF-4199809

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

Nulls go to undefined when getQueryRow() is used

| View in Tracker

Status/Resolution/Reason: To Fix//Investigate

Reporter/Name(from Bugbase): David Mitchell / David Mitchell ()

Created: 09/23/2017

Components: Language, Null Support

Versions: 11.0

Failure Type: Crash

Found In Build/Fixed In Build: ColdFusion 11 Update 13 /

Priority/Frequency: Normal / All users will encounter

Locale/System: / Windows 10 64 bit

Vote Count: 0

Problem Description:
When getting a row from a query via getQueryRow(), column values become 'undefined'.  The keys are present in the struct but attempting to get the value results in an exception.

Steps to Reproduce:

{code:java}
<cfscript>
	q = queryExecute("select null as ncol, '' as ecol", {  }, { datasource = "myfrst" } );

	writeDump(q);
	r = queryGetRow(q, 1);
	writeDump(r);
	writeDump(structKeyList(r));
	writeDump(structKeyExists(r, 'ncol'));
	for(k in r){
		writeDump(r[k]);
	}
</cfscript>
{code}


Actual Result:

An exception is raised in the for loop.

Expected Result:

I would expect that the values would continue to be represented as a empty string.

Any Workarounds:

Attachments:

Comments:

We were unable to repro this issue. Please provide the datasource mentioned, either as a SQL file,or a structure definition and sample data.
Comment by Immanuel N.
306 | September 29, 2017 09:33:11 AM GMT
Can use any datasource as this query doesn't query specific tables. This datasource uses "Microsoft Corporation JDBC Driver for SQL Server 4.2" and the database is a "SQL Server 2016".
Comment by Andreas A.
307 | October 01, 2017 06:34:32 PM GMT
Ashudeep, the DB driver is throwing the error, not CF. So whether you see the error or not depends on the underlying DB you are hitting in your query. For example, with MS SQL I see the output noted in you comment, while Oracle, DB2 and derby results in the respective errors below: (verified w/ CF2018 hf7) Caused by: java.sql.SQLSyntaxErrorException: [Macromedia][DB2 JDBC Driver][DB2]ILLEGAL SYMBOL END-OF-STATEMENT; VALID SYMBOLS ARE ncol, 'beep' as ecol at macromedia.jdbc.db2base.ddcp.b(Unknown Source) at macromedia.jdbc.db2base.ddcp.a(Unknown Source) at macromedia.jdbc.db2base.ddco.b(Unknown Source) at macromedia.jdbc.db2base.ddco.a(Unknown Source) at macromedia.jdbc.db2.drda.ddp.c(Unknown Source) at macromedia.jdbc.db2.drda.ddp.a(Unknown Source) at macromedia.jdbc.db2.drda.ddr.a(Unknown Source) at macromedia.jdbc.db2.drda.ddo.a(Unknown Source) at macromedia.jdbc.db2.drda.ddp.b(Unknown Source) at macromedia.jdbc.db2.ddh.o(Unknown Source) at macromedia.jdbc.db2base.dde0.x(Unknown Source) at macromedia.jdbc.db2base.dde0.t(Unknown Source) at macromedia.jdbc.db2base.dde0.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359) at coldfusion.sql.Executive.executeQuery(Executive.java:1567) at coldfusion.sql.Executive.executeQuery(Executive.java:1317) at coldfusion.sql.Executive.executeQuery(Executive.java:1247) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211) at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841) ... 51 more ] with root cause javax.servlet.ServletException: ROOT CAUSE: coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query. at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:894) at coldfusion.tagext.sql.QueryUtils.executeQuery(QueryUtils.java:72) at coldfusion.runtime.CFPage.QueryExecute(CFPage.java:12450) at cfnullvar2ecfm1761850137.runPage(E:\ColdFusion2018\cfusion\wwwroot\misc\null\nullvar.cfm:6) Caused by: ERROR 42X01: Syntax error: Encountered "null" at line 1, column 8. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(Unknown Source) at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 59 more ] with root cause javax.servlet.ServletException: ROOT CAUSE: coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query. at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:894) at coldfusion.tagext.sql.QueryUtils.executeQuery(QueryUtils.java:72) at coldfusion.runtime.CFPage.QueryExecute(CFPage.java:12450) at cfnullvar2ecfm1765513814.runPage(C:\ColdFusion2018\cfusion\wwwroot\misc\lang\null\nullvar.cfm:6) Caused by: java.sql.SQLException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00923: FROM keyword not found where expected at macromedia.jdbc.oraclebase.ddcr.b(Unknown Source) at macromedia.jdbc.oraclebase.ddcr.a(Unknown Source) at macromedia.jdbc.oraclebase.ddcq.b(Unknown Source) at macromedia.jdbc.oraclebase.ddcq.a(Unknown Source) at macromedia.jdbc.oracle.ddal.a(Unknown Source) at macromedia.jdbc.oracle.ddal.a(Unknown Source) at macromedia.jdbc.oracle.ddam.t(Unknown Source) at macromedia.jdbc.oraclebase.dde3.y(Unknown Source) at macromedia.jdbc.oraclebase.dde3.u(Unknown Source) at macromedia.jdbc.oraclebase.dde3.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359) at coldfusion.sql.Executive.executeQuery(Executive.java:1567) at coldfusion.sql.Executive.executeQuery(Executive.java:1317) at coldfusion.sql.Executive.executeQuery(Executive.java:1247) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211) at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841) ... 51 more ] with root cause javax.servlet.ServletException: ROOT CAUSE: coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query. at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:894) at coldfusion.tagext.sql.QueryUtils.executeQuery(QueryUtils.java:72) at coldfusion.runtime.CFPage.QueryExecute(CFPage.java:12450) at cfnullvar2ecfm1761850137.runPage(E:\ColdFusion2018\cfusion\wwwroot\misc\null\nullvar.cfm:6) test code: // enable null support in App.cfc q = queryExecute("select null as ncol, 'beep' as ecol", { }, { datasource = "oracle12c" } ); writeDump(q);
Comment by Piyush K.
33277 | March 07, 2020 08:14:02 AM GMT
David, helps if you share the exception stack trace.
Comment by Piyush K.
33278 | March 07, 2020 08:16:56 AM GMT