Hi All,

I have a query which calculates averages over sets of records (10 per set, in this example) against MySQL. This requires an embedded colon:

select floor((@x := @x + 1) / 10), avg(column1) from ... where ... group by 1;

but the := causes the 'Not all named parameters have been set' exception.

I cannot use the suggested 'replace the string with a named parameter' solution, as that results in:

select floor((@x ':=' @x + 1) / 10), avg(column1) from ... where ... group by 1;

and MySQL does not like the quotes around the :=.

I'm wondering if there is a way to do one (or more...)

• get a named parameter to expand to its literal value rather than (if the parameter is a string) having the quotes applied. I've tried query.setParameter(key, value, Hibernate.OBJECT); but that fails in org.hibernate.impl.SessionFactoryImpl.getEntityPersister (SessionFactoryImpl.java:514) with org.hibernate.MappingException: Unknown entity: java.lang.String.
• escape the colon
• create an SQL query (including handling my 'real' named parameters myself) that doesn't suffer from the above ':=' issue. I can process the named parameters myself, but not sure how I actually get a 'Query' object from that without going through createSQLQuery - but createSQLQuery seems to be the lowest level of call
• rewrite the query - but I'm not sure how else to group things into sets of a known size

Thanks,
Doug

The assignment operation problem is a known issue, for which apparently there is no workaround.

http://opensource.atlassian.com/project ... e/HHH-2697

Could you provide the table(s) and what you are trying to achieve? I have trouble understanding what you say here:

[/code]

Hi Gonzalo

For my



what I could do is process the query that I get from the higher levels of my application and then call MySQl myself: i.e. get the query and the map of named parameters, run through the named parameters and substitute their values into the query string. At that point I would have a query string without any named parameters in it. I would then use that string to perform my query - except I'm not sure if there is any good way to execute the query besides using createSQLQuery and then calling 'list' - and since that string will still have the ':=' in it, I'll still have my original problem.

After a bit more investigation this morning, I think I could use session.connection() to get a raw JDBC connection object and go from there, but I've seen a few mentions (for example, http://opensource.atlassian.com/projects/hibernate/browse/HHH-2603) of that being deprecated in Hibernate 3.2 (I'm using 3.1.3 right now). Also in HHH-2603 is:



but no explanation of what those dangers are (or even if that statement is factual) - so I've been hesitating to go in that direction.

What I am trying to do is calculate averages over sets of rows. So, if I have the table var_test:

CREATE TABLE `var_test` (
`num` int(10) default NULL
);

and it has 25 rows with 'num' set to 1..25, I want to do something like this to get the averages of sets of 5 items:

set @x = -1; select floor((@x := @x + 1) / 6), avg(num) from var_test group by 1;

and get:

+---------------------------+----------+
| floor((@x := @x + 1) / 6) | avg(num) |
+---------------------------+----------+
| 0 | 3.0000 |
| 1 | 8.0000 |
| 2 | 13.0000 |
| 3 | 18.0000 |
| 4 | 23.0000 |
+---------------------------+----------+
5 rows in set (0.00 sec)

My actual query is more complex than that, with a subquery and where and order clauses - so I cannot use a column containing sequential id numbers rather than my '@x' variable.

NOTE the ' / 6' - for some reason, MySql handles the equation differently if there is an aggregation function (max, min, count, avg, sum, ...) in the list of columns. That is

mysql> set @x = -1; select floor((@x := @x + 1) / 5), num from var_test group by 1;

produces 5 rows

+---------------------------+------+
| floor((@x := @x + 1) / 5) | num |
+---------------------------+------+
| 0 | 1 |
| 1 | 6 |
| 2 | 11 |
| 3 | 16 |
| 4 | 21 |
+---------------------------+------+
5 rows in set (0.00 sec)

while

set @x = -1; select floor((@x := @x + 1) / 5), min(num) from var_test group by 1;

produces 7 rows

+---------------------------+----------+
| floor((@x := @x + 1) / 5) | min(num) |
+---------------------------+----------+
| 0 | 1 |
| 1 | 5 |
| 2 | 9 |
| 3 | 13 |
| 4 | 17 |
| 5 | 21 |
| 6 | 25 |
+---------------------------+----------+
7 rows in set (0.00 sec)

But I think that is an issue for the MySQL forums...

Many thanks for your interest,
Doug.

Pulling a connection from the session is untidy, even if you remember to close it and all, because the objects of the resultset returned are not within the scope of hibernate.
A possible solution to your problem could be:

-Make a view inside MySQL that takes care of that @= excentricity
-Create a "subselect class" that simply does a select * of that view, and whose properties map to the fields returned by that select.
-Then you can create any HQL query, selecting from that bean, with all parameters you like, named or not.

The big issue with this one is we're using MySQL 4.1 - which doesn't have views.

I'm not an expert on views, but I'm not sure that would work in any case. From the documentation, it looks like the view is created with a fixed 'select' clause, which means I would need to have a view for every potential query I want to run. As I mentioned above, my actual query is fairly complex, with some joins and such in it. It also has some dynamic elements to it, including inside of the joins - so I'm thinking (but correct me if I'm wrong...) I would need a view for each variation of that query.

Also, how would the syntax for setting the counter variable back to its initial value work with that?

Thanks,
Doug.

Yes, as you say, setting the initial variable value would be a problem, even if you had the ability to create views.
I also believe that HQL has nothing alike to setting a variable, then selecting.
So you will likely have to come up with some dynamic query construction.

Hi Gonzalo,

Many thanks for your hints and suggestions. I've decided to patch hibernate so '::' is an escaped ':'. The other complication was setting the counter variable back to -1 each time, so my SQL looks like:

select floor((@x ::= @x + 1) / 6), count(*) from var_test, (select @x ::= -1) as resetQuery where ...

and that seems to do it.

Now just need to test and see if my :: broke anything else...

Thanks again,
Doug.