[ColdBox-3.5]HQL

Probably it’s me, but I cannot create a working HQL. My code:

/**

  • A ColdBox Enabled virtual entity service
    */
    component extends=“coldbox.system.orm.hibernate.VirtualEntityService” accessors=“true” singleton{

property name=“userService” inject=“entityService:Localita”;

/**

  • Constructor
    */
    public GetterService function init(){

// init super class
super.init(entityName=“Localita”);

// Use Query Caching
setUseQueryCaching( false );
// Query Cache Region
setQueryCacheRegion( ‘ORMService.defaultCache’ );
// EventHandling
setEventHandling( true );

return this;
}

function getProvince(){
var c = userService.executeQuery(“select id from Localita”);
return c;
}

}

I got error message: Can not convert the given object to query

There are a couple of things to note here, when creating a service that extends any of the BaseORM, VirtaulEntity etc.

You don’t need to inject your service in like you are doing, so you need to remove the property for your DI and you need to modify the following from

function getProvince(){
var c = userService.executeQuery(“select id from Localita”);
return c;
}

to

function getProvince(){
var c = executeQuery(“select id from Localita”);
return c;
}

As you are defining the name of the entity in the init function, and there is no need to call an external service for a function that is in the parent component.

Now the other thing is you don’t give where the problem is occurring so we are left to try and guess for you, but try these changes first then come back to us with what line is actually causing the issue.

My guess is that you are trying to return something as a query that is not capable of returning as a query, not everything in HQL can be converted to a query.

I have made the change you suggested, but got same error.

I have an orm entity called Localita and I need a query to extract only id and I have thought that using HQL could be a good solution.

The error occurred in:

ID: CF_CFPAGE
LINE: 204
Template: C:\ColdFusion9\wwwroot\ColdBox\system\orm\hibernate\BaseORMService.cfc
ID: CF_SUPERSCOPE
LINE: 88
Template: C:\ColdFusion9\wwwroot\ColdBox\system\orm\hibernate\VirtualEntityService.cfc
ID: CF_UDFMETHOD
LINE: 27
Template: C:\ColdFusion9\wwwroot\AgriturismoInCampania\Coldbox\model\GetterService.cfc

Line 27 is: var c = executeQuery(“select id from Localita”);

Remember that HQL is not SQL.

Here is what I would do, as executeQuery is a wrapper for ORMExecuteQuery, maybe create a normal page that is not ColdBox related so you can have a
play with the query. But I would also read up here on HQL as well, and get familiar with HQL first

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

btw if you look at the error in the BaseORM at libe 204 it is when converting the results to a query, like I said NOT ALL HQL can be converted to a query.

So what you need to do is before the execute query is do this

setDefaultAsQuery(false);

What this will do is run your query, but convert the results to a query.

This works!!! Man, I really appreciate your help

hehe, well considering I meant to say will not convert your query, I am glad you understood.

So there is no option to make a query that give as results only one or two column from database and returns a query object?

The only option is to have an array as results?

Not sure, like I said not all HQL can be converted to a query. Your best bet is to read the previous link I gave you on HQL.

I read the link. And in that document about select clause http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-select it seems I could select just a column and return a query object.

I don’t know if this i s a ColdBox bug, probably Luis could know better.

Hibernate Query result set is not anyway the same as ColdFusion, when ColdFusion tries to convert the object to a query it looks for a specific object format. If you dump out your object what do you see?

Could you show us what your dump looks like?

Like I have said and I will say it again, not all HQL can be converted to a ColdFusion query, knowing how HQL works is a step in understanding that.

And it is not a ColdBox issue, nor is it a ColdFusion issue. Sometimes your HQL can return an Associate array, once that happens there is no way to convert this to a ColdFusion query.

I could not dump my object, because if I set setDefaultAsQuery(true); it gives me error, the same as before. If I try setDefaultAsQuery(false); I see my array.

If I try another HQL for example executeQuery(“from Localita”); it returns an query object with all records and columns.

This make me think that is the select clause generator error.

So if you write

setDefaultAsQuery(false);
var c = userService.executeQuery(“select id from Localita”);
writeDump©; abort;

What does the dump look like?

It dumps an array of id.

Which is what I would have expected, so executeQuery being a wrapper for ORMExecuteQuery and entityToQuery() can’t convert that sort of array to a query, as the entityToQuery requires an array of objects.

Which is why I was pointing in the direction of understanding HQL, most of the time the select will not return an array of objects, sometimes it can be an associate array.

So by doing

setDefaultAsQuery(false);

you are saying to ColdBox run the ORMExecuteQuery but do not run the entityToQuery.

So in the end, if I wanna make an select clause with only one or two column, I cannot obtain an query object, but only an array.

In my case this is not a problem, because my code should have convert the query to array object. So in my case this is a pro.

Many thanks for your help, I think this could be useful for many other developers that are discovering HQL.

You are right, it is a misconception that all HQL will be returned as an array of objects.

Remember you can get HQL to return an array of structures rather than an array of arrays when selecting a subset of columns…

http://www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/hql-tip-use-a-hashmap-283

Regards,

Richard

Also you can mix name values with an array of objects as well.