Second replacement of a named parameter fails in HQL

Question

I have an HQL statement of this sort:

update versioned MyTable t 
set t.myCol = case 
   when ((t.myCol + :myColChange) < 0) then 0 
   else (t.myCol + :myColChange) end

I see that this is not working due to the second occurrence of :myColChange. The following exception is thrown:

org.hibernate.QueryException: Not all named parameters have been set: [myColChange] [ update versioned MyTable t set t.myCol = case when ((t.myCol + :myColChange) < 0) then 0 else (t.myCol + :myColChange) end]

Would appreciate any ideas to resolve this problem. Thanks!

What about posting the code that creates the Query and sets the param values? ;) — AndrewBourgeois, May 02 '11 at 09:44

score 3 · Answer 1 · answered May 02 '11 at 09:45

3

How about

.... set t.myCol = max(0, t.myCol + :myColChange)

answered May 02 '11 at 09:45

Ingo

36,037
5
53
100

Thanks, that was a good idea. But its rather set t.myCol = greatest(0, t.myCol + :myColChange) However, this too throws the same exception. So the problem could not be due to the second occurrence of the parameter. Any idea why this is happening? – pall May 02 '11 at 12:24

score 0 · Answer 2 · answered May 02 '11 at 09:45

0

A work around would be to use two different names/parameter.

update versioned MyTable t 
set t.myCol = case 
   when ((t.myCol + :myColChange1) < 0) then 0 
   else (t.myCol + :myColChange2) end

answered May 02 '11 at 09:45

Ralph

118,862
56
287
383

score 0 · Answer 3 · answered May 02 '11 at 13:50

0

Did you actually

setXXXXX("myColChange", myColChangeValue);

on the Query object before executing it?

I'm just asking because you didn't post your code.

answered May 02 '11 at 13:50

AndrewBourgeois

2,634
7
41
58

Yes, I did. And I could see the value while debugging. I din't post the code because we are calling our company specific architecture code to acheive it and it wouldn't make sense to post it here. – pall May 02 '11 at 14:00

score 0 · Answer 4 · answered Dec 20 '11 at 09:09

I have this problem too. My current workaround is NOT to use named parameters, but used positions instead.

For me this is not a big problem because I store the queryparameters in my own object in a list.

    public void apply( final CategoryFilter f )
    {
        final String jsonFilter = f.getFilter();
        final QueryParameters queryParams = QueryParameters.createFromJsonString( jsonFilter );

        final StringBuilder queryStringBuilder =
            new StringBuilder( "UPDATE Transaction t SET t.category = 'myCatergory' WHERE " );

        boolean isFirst = true;
        for ( final QueryParameter queryParam : queryParams.getQueryParams() )
        {
            if (!isFirst)
            {
                queryStringBuilder.append( " AND " );
            }
            else
            {
                isFirst = false;
            }
            queryStringBuilder.append( "t." );
            queryStringBuilder.append( queryParam.getField() );
            queryStringBuilder.append( " " );
            queryStringBuilder.append( queryParam.getOp() );
            queryStringBuilder.append( " ? " );
        }
        final Query q = getSession().createQuery( queryStringBuilder.toString() );

        int index = 0;
        for ( final QueryParameter queryParam : queryParams.getQueryParams() )
        {
            q.setParameter( index++, queryParam.getValue() );
        }
        q.executeUpdate();

    }

Second replacement of a named parameter fails in HQL

4 Answers4