Way to insert SQL comments into HQL generated SQL?

Matt2000 · **Posted:** Thu Apr 15, 2004 1:15 pm

Hey all,

I need to be able to insert the SQL comment /*+RULE*/ for stinking Oracle to figure out what indexes it should use. However, the SQL I need to insert it into is generated from a fairly complex HQL query.

Is there a way that anyone knows of via the Query object or whatever, to instruct the SQL generator to put a SQL comment in it's final result? So that it would look something like:

SELECT /*+RULE*/ name from user ....

Thanks for any advice.

Matt

michael · **Posted:** Thu Apr 15, 2004 1:21 pm

This will be a new feature in the next version, you can take a look at the CVS, there is Query.setComment()

Currently it is not possible I think.

greg_barton · **Posted:** Thu Apr 15, 2004 2:09 pm

p6spy (http://www.p6spy.com/index.html) might be useful for this, at least until Hibernate supports SQL comments.

Matt2000 · **Posted:** Thu Apr 15, 2004 4:09 pm

Thanks for the replys.

Michael: I've checked latest CVS for the setComment() method and it doesn't seem to be there? That is basically exactly what I need though, and what I was going to write if I couldn't find an existing way.

Here's the version of the Query interface I'm checking: http://cvs.sourceforge.net/viewcvs.py/h ... &view=auto

Let me know if I'm in the wrong place.

Thanks.

max · **Posted:** Thu Apr 15, 2004 4:31 pm

it's in the v22branch...

michael · **Posted:** Thu Apr 15, 2004 5:49 pm

Look here

Matt2000 · **Posted:** Thu Apr 15, 2004 6:01 pm

Thanks, I see it now. Is there an estimated release time for v2.2? I checked the roadmap but it doesn't have any dates. I'm just wondering if it's worth it for me to port the setComment() changes back to the version of hibernate I'm using or wait for v2.2.

Thanks again.

david · **Posted:** Thu Apr 15, 2004 6:22 pm

It will be sometime before v3.0 is the recommended version.

Matt2000 · **Posted:** Tue May 25, 2004 4:05 pm

If anyone is interested (or found this thread via a search or something) I've written a patch against the older branches of hibernate that allows you to insert SQL comments that Oracle will use as optimizer hints. It's probably not appropriate to be included against the development version of Hibernate since v2.2 will support a version of this already.

I have a patch file against v2.03 ready and am working on one versus v2.1. I think they are too big to include in the forum so send me a message if you're interested.

Matt