Hibernate version:
2

I am trying to do a time zone converted query. In MySQL, I would write:
select time from auditevents where CONVERT_TZ(time, @@session.time_zone, '-08:00') >= '1970-01-02 00:00:00';

This query properly returns me one record.

I would like to do the same kind of query from hibernate. So, I try this:
results = session.createSQLQuery("select {event.*} from auditevents event where CONVERT_TZ(time, @@session.time_zone, '-08:00') >= '1970-01-02 00:00:00'", "event", AuditEvent.class).list();

Unfortunately, hibernate seems to be creating named parameters for me at the colons:
Hibernate: select event.id as id0_, event.type as type0_, event.time as time0_,
event.username as username0_, event.src_IP_high as src_IP_h5_0_, event.src_IP_low as src_IP_low0_ from auditevents event where CONVERT_TZ(event.time, @@session.time_zone, '-08?') >= '1970-1-2 00?'

Hibernate returns to me no results.

Is there any way to escape the colons, or some other solution to this problem?

To clarify the version, I'm using Hibernate 2.1.7c to connect to MySQL 4.1.7 using Connector/J 3.1.7.

I've tried escaping as \\: and as ::.

I've also tried using different kinds of quotes around the times, double quotes, double single quotes.

None has worked.

I've also tried actually using a named parameter:


Hibernate says it's running the following query:
Hibernate: select event.id as id0_, event.type as type0_, event.time as time0_,
event.username as username0_, event.src_IP_high as src_IP_h5_0_, event.src_IP_low as src_IP_low0_ from auditevents event where ?

But no results are returned.

Surely someone has tried doing queries with times in them before. Any help or suggestions would be greatly appreciated.

does this fail in the last released h2 and h3 ?

i think we should be covered with respect to not picking up false : parameters.

otherwise pleae provide a simple failing testcase (hopefully something that can run without some specific function called ,)

/max

Still fails in Hibernate 2.1.8.

A simpler test case is:


Which generates the following query:
Hibernate: select event.id as id0_, event.type as type0_, event.time as time0_,
event.username as username0_, event.src_IP_high as src_IP_h5_0_, event.src_IP_low as src_IP_low0_ from auditevents as event where event.time >= '1970-1-2 00?'

But due to some strangeness in MySQL, that query actually returns the proper number of results. I'm guessing that it can handle the slightly misshapen date (1970-1-2 00?) better than it can the misshapen timezone offset (-08?).

I will try Hibernate 3 next.

Also fails in Hibernate 3.

Simple case:
Code:


Output:
Hibernate: select event.id as id0_, event.type as type24_0_, event.time as time24_0_, event.username as username24_0_, event.src_IP_high as src5_24_0_, event.src_IP_low as src6_24_0_ from auditevents as event where event.time >= '1970-1-2 00?'

Complex case:


Output:
Hibernate: select event.id as id0_, event.type as type24_0_, event.time as time24_0_, event.username as username24_0_, event.src_IP_high as src5_24_0_, event.src_IP_low as src6_24_0_ from auditevents event where CONVERT_TZ(event.time, @@session.time_zone, '-08?') >= '1970-1-2 00?'

Well, I've discovered a workaround to my problem (send the timezone string, rather than the numeric offset). However, I strongly urge the Hibernate team to add some way to escape colons in SQL and HQL.

any reason why you dont just put it in as a parameter ?

Much safer btw.

-max

Have you tried to escape field names ?
results = runSQLQuery("select {event.*} from auditevents event where CONVERT_TZ( {event.time}, @@session.time_zone, '-08:00') >= '1970-1-2 00:00:00'", "event", AuditEvent.class);[/code]