Hi. I am having some issues trying to get my query (which contains named parameters) to work successfully. Consider the query;

SELECT * FROM A WHERE A.COL = :myParam

In my code I am setting the named parameter depending on some user input, in one case it is set such as;

Query sqlQuery = session.getNamedQuery("QueryName");
sqlQuery.setString("myParam", "M");

and that works fine. But when I want to retrieve all rows where the COL value is equal to '' then I can't get it to work. I have tried (individually);

sqlQuery.setString("myParam", null);
sqlQuery.setString("myParam", ""); // 2 double quotes
sqlQuery.setString("myParam", "''"); // double quote, 2 single quotes, double quote

None of those work, the query runs fine but it is returning 0 rows. Is there a way to set the named parameter to be an empty string?

Hibernate version: 3.1.3

Name and version of the database you are using: SQLServer 7

can i suggest an alternative?

if you are retrieving all the entries, you can probably just use criteria:

Criteria criteria = sess.createCriteria(Table_Name.class);
criteria.setMaxResults(50); //maximum of 50 entries
List list = crit.list();

sess is the hibernate session.

HQL is good when the query you are trying to do is too complicated. Best leave hibernate to optimize etc.


hth,

pali

I should have explained in my first post. The front end needs to see the data in 3 ways:

1. All rows
2. Rows where COL = 'M'
3. Rows where COL = '' (or not 'M')

In the database, COL only contains the values '' or 'M'.

Finally figured it out although I don't understand exactly why it works this way. Need to set the parameter to be (" ") i.e. have a space character in between the double quotes.