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!

score 2 · Answer 1 · answered Nov 11 '10 at 21:56

2

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

answered Nov 11 '10 at 21:56

Jason Dean

9,585
27
36

score 1 · Answer 2 · answered Nov 11 '10 at 12:11

1

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.

answered Nov 11 '10 at 12:11

scrittler

1,121
6
8

I actually WANT to use `cfquery`. This answers means I am back to ysing `ORMExecuteQuery()` which is not what I want. – Ciaran Archer Nov 11 '10 at 13:43
@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 – Antony Nov 11 '10 at 22:59
1

@Antony: The main reason is that I don't have to do ugly string concatenation and can write multi-line statements that are readable! :) – Ciaran Archer Nov 12 '10 at 20:16
@ciaran - try cfsavecontent to avoid string concatenation – Antony Nov 12 '10 at 23:23

score 1 · Accepted Answer · answered Nov 15 '10 at 14:21

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.

Using cfqueryparam with a ColdFusion HQL query

3 Answers3

Linked