Using cfqueryparam with a ColdFusion HQL query

Question

I am using a HQL query to get a bunch of state objects like so:

<cfquery name="LOCAL.qStates" dbtype="hql">
    from States where countryID = #ARGUMENTS.countryID#
    order by name asc
</cfquery>

This works fine. However, I was brought up well and I want to use cfqueryparam, ideally like so:

<cfquery name="LOCAL.qStates" dbtype="hql">
    from States 
    where countryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.countryID#" />
    order by name asc
</cfquery>

But this throws an error:

[empty string] java.lang.NullPointerException at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:353) at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:323) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:98) at coldfusion.orm.hibernate.HibernatePersistenceManager._executeHQL(HibernatePersistenceManager.java:822) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeHQL(HibernatePersistenceManager.java:751) at ....

Anyone know how to get around this and use cfqueryparam with cfquery HQL queries?

Thanks in advance!

Kiehl

Answer 1

I got to the bottom of this.

My States object was setup like so:

  <cfcomponent output="false" persistent="true">

      <cfproperty name="stateID" type="numeric" fieldType="id" generator="identity" />
      <cfproperty name="name" type="string" />
      <cfproperty name="alphaCode" type="string" />


      <!--- Relationships --->
      <cfproperty name="country" type="array" fieldtype="many-to-one" cfc="Countries" fkcolumn="countryID" lazy="true" />



  </cfcomponent>

When using the <cfqueryparam> tag Hibernate was perhaps trying to map the number I was passing in as an array and failing thus throwing an error.

If I remove the relationship from the property like so:

<cfproperty name="countryID" type="numeric" />

...then it works.

Kiehl

Answer 2

Take off the Datatype, it's not required and hibernate probably does not understand them.

Wink

Answer 3

Indirect answer: use bound parameters instead.

<cfset orderDetail = ORMExecuteQuery("from Orders where OrderID=:orderid and ProductID=:productid", {orderid=1, productid=901}, true)>

You'll have to still roll your own validation on the variables though.

Knightly

I actually WANT to use cfquery. This answers means I am back to ysing ORMExecuteQuery() which is not what I want. – Kiehl

@ciaran - why do you want to use cfquery? are you treating orm as a sql abstraction layer? if so you are missing some/most of the power – Hyehyena

@Antony: The main reason is that I don't have to do ugly string concatenation and can write multi-line statements that are readable! :) – Kiehl

@ciaran - try cfsavecontent to avoid string concatenation – Hyehyena