QueryHelper Type Comparison Exception...

Communities ColdBox HMVC
1

All,

I'm running into a bit of a data type conversion problem with
doLeftOuterJoin() and doInnerJoin(). I'm trying to run a QoQ on a
joined result set using cfqueryparam and Im losing my data type when
using the queryHelper... If I do a QoQ on a standard cfquery, I get
correct results when trying to set a condition on my parentId. (if
that makes sense)

original query:
<cfquery name="userProjects" datasource="myds">
  SELECT projects.projectId, projectName, parentId, userId FROM
projects
  LEFT OUTER JOIN users2projects ON (projects.projectId =
users2projects.projectId)
</cfquery>

however, If I try and use the queryHelper to do my join, and then do
my QoQ on that result set my condition breaks using cfqueryparam....

one liner join query (much nicer):
<cfset userProjects = instance.QueryHelper.doLeftOuterJoin(projects,
users2projects, 'projectId', 'projectId') />

my QoQ looks like so...

<cfquery name="objNav" dbtype="query">
  SELECT * FROM userProjects
  WHERE parentid = <cfqueryparam value="#arguments.parentId#"
cfsqltype="cf_sql_integer">
</cfquery>

Im getting a Unsupported Type Comparison Exception when using the
queryHelper... It seems as though doLeftOuterJoin() is changing the
data type of my parentID

or something screwy is happening...

any thoughts?

2

I have never used the query helper, so I don’t know if it has any configuration abilities on the cf_sql_datatype setting. But from what you’ve posted, it seems to me that your ProjectId field is NOT an integer (e.g. it is ‘varchar’ or something similar), and the query helper defaults to using integer as the ID datatype. This will cause the error that you’re seeing.

3

Hi

Which version of ColdFusion you are using ?

QueryHelper does not convert any datatype of query result.
-------------TRY THIS -------------
my QoQ looks like so...
<cfquery name="objNav" dbtype="query">
        SELECT * FROM userProjects
        WHERE parentid = #arguments.parentId#
</cfquery>