-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 22 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
bozidardangubic
 Post subject: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Wed Jan 20, 2010 2:20 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
All,

We are using filters which are defined for instance as:
Code:
<class>
....
<filter name="judgeNoteFilter" condition="JUDGE_ID IN (:judgeNoteAccessIds)" />
</class>

<filter-def name="judgeNoteFilter">
         <filter-param name="judgeNoteAccessIds" type="long" />
  </filter-def>


The list of IDs that is being passed in to the filter, filter parameters can change so between one request to the next the size of the list can change. We are experiencing problems when this happens, if more IDs are being sent we get missing IN/OUT parameter and if less IDs is being sent we are getting invalid column index. This of course could be related to the PreparedStatement caching but it's completely disabled via:

Code:
<prop key="hibernate.statement_cache.size">0</prop>      


Any help in this matter is appreciated!
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 5:02 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
The IN clause should not have empty values in it. If you have a case where the list of values is an empty list you can put a condition not to enable the filter. Also in the case of multiple values in the List, how many values are you trying to send? SQL IN clause has a limitation on how many you can send. I was googling to see issues on things like these and found one that is interesting.
viewtopic.php?p=2175608

-Srilatha.
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 5:12 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
thanks for the reply!
the issue is not empty list. the filter is correctly enabled only when there is actual data. the problem is like this. say there are three IDs, 1,2,3. we run the query and you'll see something like
Code:
SELECT.... FROM.... WHERE.... AND xxxx_ID IN (?,?,?)


then on the next request filter parameter contains only two IDs (through the UI we can enable and disable access to certain things that filter enforces). so next time we are passing in two IDs but the query being generated is still

Code:
SELECT.... FROM.... WHERE.... AND xxxx_ID IN (?,?,?)


so then we get missing IN/OUT parameter. if we add additional access so now we have 4 IDs, say 1,2,3 and 4, the query being generated is still

Code:
SELECT.... FROM.... WHERE.... AND xxxx_ID IN (?,?,?)


so now when ID 4 is being set to this query we get "Invalid Column Index". the only way to recover is to bounce the server.
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 6:40 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
can you post the code. your UI code and the Filter enabling at the DAO level? I'm sure you did a sanity check but still did you put a debugger and have seen the entries in the collection object that you are setting on the Filter?
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 7:36 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
latha1119 wrote:
can you post the code. your UI code and the Filter enabling at the DAO level? I'm sure you did a sanity check but still did you put a debugger and have seen the entries in the collection object that you are setting on the Filter?


filter is enabled via:
Code:
public void enableJudgeNoteFilter(Session session) {
        Assert.notNull(session);

        session.enableFilter(judgeNoteFilterName).setParameterList(judgeNoteFilterParameterName,
                appUserContext.getJudgeNoteAccessIds());
    }


any query that pulls in domain object that utilizes this filter creates a problem, here's one:

Code:
...
String queryString = "Select DAY(judgeAppointment.timeBlock.startDateTime) FROM JudgeAppointmentTimeBlock judgeAppointment"
                + " WHERE judgeAppointment.timeBlock.startDateTime >= :startOfMonth"
                + " AND judgeAppointment.timeBlock.endDateTime < :endOfMonth";

        if (null != judge) {
            queryString = queryString + " AND judgeAppointment.judge = :judge";
        }
        queryString = queryString + " GROUP BY DAY(judgeAppointment.timeBlock.startDateTime)";
        Query query = ThreadSession.getSession().createQuery(queryString);
        query.setTimestamp("startOfMonth", startMonth);
        query.setTimestamp("endOfMonth", endMonth);
        if (null != judge) {
            query.setEntity("judge", judge);
        }
        return query.list();
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 7:44 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
so when you put a log message and iterate over the entries in the appUserContext.getJudgeNoteAccessIds() just before the statement:-

session.enableFilter(judgeNoteFilterName).setParameterList(judgeNoteFilterParameterName,
appUserContext.getJudgeNoteAccessIds());

and the entries are different each time? also what is your underlying database?
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 7:55 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
latha1119 wrote:
so when you put a log message and iterate over the entries in the appUserContext.getJudgeNoteAccessIds() just before the statement:-

session.enableFilter(judgeNoteFilterName).setParameterList(judgeNoteFilterParameterName,
appUserContext.getJudgeNoteAccessIds());

and the entries are different each time? also what is your underlying database?


absolutely, that is when the problem occurs. when we start up the app and say there are three IDs, all is well. As soon as this changes, all hell breaks loose. And I know that IDs are coming in correct because I set the trace to see what hibernate is doing. So if initially there were 3 IDs and on subsequent request there are two, hibernate still generates prepared statement with three placeholders for parameters, it the sets the two correctly and tries to execute the query (missing IN/OUT parameter). If subsequent request has four parameters then we still get three placeholders in the prepared statement but this time when hibernate tries to set 4th parameter we get "invalid column index"
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Thu Jan 21, 2010 8:00 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
it's the equivalent to this:

1st request, three filter parameters (5,6,7), hibernate does something like
Code:
PreparedStatement ps = connection.prepareStatement("SELECT xxx FROM xxx WHERE xxx AND ID IN (?,?,?)");
ps.setLong(1,5);
ps.setLong(2,6);
ps.setLong(3,7);
ps.executeQuery();


now 2nd request, we are 4 filter parameters (5,6,7,8), hibernate does something like
Code:
PreparedStatement ps = connection.prepareStatement("SELECT xxx FROM xxx WHERE xxx AND ID IN (?,?,?)");
ps.setLong(1,5);
ps.setLong(2,6);
ps.setLong(3,7);
ps.setLong(4,8); // this causes invalid column index, no 4th placeholder
ps.executeQuery();


if 2nd request was with 2 filter parameters (5,6) hibernate does something like
Code:
PreparedStatement ps = connection.prepareStatement("SELECT xxx FROM xxx WHERE xxx AND ID IN (?,?,?)");
ps.setLong(1,5);
ps.setLong(2,6);
ps.executeQuery(); // this causes "missing IN/OUT parameter"
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 12:26 am 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
is your underlying database Oracle? and you are using an older version of hibernate?
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 7:37 am 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
latha1119 wrote:
is your underlying database Oracle? and you are using an older version of hibernate?


Underlying database is Oracle running the latest Oracle Driver for 11g 11.2.0.1.0, using Hibernate 3.2.6.
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 12:26 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
i remember vaguely people running into this kind of issues. can you upgrade your hibernate version? to 3.3.1 and above. I looked into my previous posts with similar issues and those were fixed in 3.3.1.
Sorry could not help.
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 12:31 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
latha1119 wrote:
i remember vaguely people running into this kind of issues. can you upgrade your hibernate version? to 3.3.1 and above. I looked into my previous posts with similar issues and those were fixed in 3.3.1.
Sorry could not help.


thanks for the help!!! I upgraded to version=3.3.2.GA, same problems still... well maybe someone from the HIbernate Team can shed a light..
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 1:32 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Sorry did not mean to barge in again! just can't stop thinking about this..
can you do something like this just for fun?

public void enableJudgeNoteFilter(Session session) {
Assert.notNull(session);

session.enableFilter(judgeNoteFilterName).setParameterList(judgeNoteFilterParameterName,
appUserContext.getJudgeNoteAccessIds());
//after getting the results, disable the filter so that enabling the filter might reset everything!

}

also are your subsequent queries running under their own session? How/Why does it matter?- just curious.
Top
 Profile  
 
bozidardangubic
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Fri Jan 22, 2010 1:46 pm 
Beginner
Beginner

Joined: Thu Mar 29, 2007 3:33 pm
Posts: 24
Location: Washington DC
latha1119 wrote:
Sorry did not mean to barge in again! just can't stop thinking about this..
can you do something like this just for fun?

public void enableJudgeNoteFilter(Session session) {
Assert.notNull(session);

session.enableFilter(judgeNoteFilterName).setParameterList(judgeNoteFilterParameterName,
appUserContext.getJudgeNoteAccessIds());
//after getting the results, disable the filter so that enabling the filter might reset everything!

}

also are your subsequent queries running under their own session? How/Why does it matter?- just curious.


thanks for trying to help me out with this. each request is a brand-new session so filter is always enabled just before the query is executed. so we have request->open session in view starts the session->enable filter->run query then next request same thing again, request->open session in view starts the session->enable filter->run query
Top
 Profile  
 
latha1119
 Post subject: Re: Invalid Column Index or Missing IN/OUT parameter with Filter
PostPosted: Mon Jan 25, 2010 3:18 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Ok.. I have re-read your issue. It appears the filter is at the collection level on an entity. Also for the same entity you are querying for different collection entries with in it and that is causing the problem? can you enable the filter at the Query level itself? and not at the Domain object. As you know, the entities once loaded are cached for that session.
Two things to do for curiosity purposes:-
1)Clear the session or evict the instances of the parent entity probably JudgeAppointmentTimeBlock before you execute the query.
2)move the enabling filter thing at the query level and not at the DAO level.

Please update if you were able to solve in any other way too.
Thanks,
Srilatha.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 22 posts ]  Go to page 1, 2  Next

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.