Count(*) throwing unexpected result inside QueryOfQuery in ColdFusion

Of late, I came across an issue while using QoQ in ColdFusion. Here the issue I had faced -

<cfquery name="parentQuery" datasource="myDSN" >
      select id,name,department
      from employee
</cfquery>

Let say, the above query gives three records:
ID                           NAME                         DEPARTMENT
1                             SONU                          MANAGEMENT
2                             MIKE                           SALES
3                             JOHN                           HR


Now query the parent query using QoQ and with department value "ENGINEERING"

<cfquery name="childQuery" dbtype="query" >
      count (*) AS myCount
      from parentQuery where department = <cfqueryparam value= 'ENGINEERING'  cfsqltype='varchar' >
</cfquery>

Since no records matches with the  above filter parameter, we would expect myCount value as 0. But it will give us [empty string] . So if you are using count(column_name) inside QoQ always used Val(childQuery.myCount) or childQuery.RECORDCOUNT to get the number of records.

Here Val(childQuery.myCount)  will be 0 if childQuery.myCount is [empty string].

Creating a database conection object in Coldfusion

In my last project ,I came across a situation where I had to create a database conection object in coldfusion and passed it to the java class which further make a database query using that conection object.

Here is the code to implement it..

//Creating an object of servicefactory class

theServiceFactory = createObject('java','coldfusion.server.ServiceFactory');

//Creating the connection object simply by passing the DSN name

con = theServiceFactory.getDataSourceService().getDataSource('DataSource Name').getConnection();

//Now passing the connection object as argument to the test.java class

testConnection = createObject('java', 'namespace to code').init(con );

Hope this will help you..Keep coding :)