EJBQL doesnt seem to be working

[Peter Matthews]

Hi All,

Figured out the last problem (previous post), but have hit another in the process...as it always seems to be the case!

The EJBQL:

SELECT DISTINCT OBJECT(h)
FROM House h, Location l WHERE l.name = ?1 AND h.bedrooms = ?2
AND h.price BETWEEN ?3 AND ?4

The bedrooms and price conditions work, the problem occurs in the location. For example, I put in Headingley (for location name), 3 (for bedrooms), and 0 (for min price) and 500000 (for max. price). It should display no results, as there are no properties in headingley, however it displays the details of a house located in Harehills which has 3 bedrooms and is between the prices specified. Any ideas why it is ignoring the location condition?

Thanks,

Pete

[Jeanne Boyarsky]

Pete,
In EJBQL (or SQL which it is based on) there is a concept called a join. This means that if you have more than one bean/table involved, you have to tell the query how to match it up.

So you want to include something like l.id = h.locationid in your query.

Otherwise, the query will be a "cartesian product" and give you all the rows that match either part of the query.

[Peter Matthews]

Thanks Jeanne,

However I'm still having difficulties - as I'm very new to EJBQL - and not too hot on SQL either.

The revised EJBQL query is:

EJBQL: SELECT DISTINCT OBJECT(h)
FROM House h, Location l WHERE h.location = l.name AND location.description = ?1 AND h.bedrooms = ?2
AND h.price BETWEEN ?3 AND ?4

But now I am getting the following error when deploying:

EJBQL: SELECT DISTINCT OBJECT(h)
FROM House h, Location l WHERE h.location = l.name AND location.description = ?1 AND h.bedrooms = ?2
AND h.price BETWEEN ?3 AND ?4
Error: line(2) column(43): JDO75311: Invalid argument(s) for '='.
; requested operation cannot be completed
C:\Documents and Settings\Peter Matthews\HBUYERS\nbproject\build-impl.xml:281: Deployment failed.
BUILD FAILED (total time: 1 second)

Any ideas what is going wrong? Do I need to specify any relationships (CMRs)? Or is my syntax incorrect?

Thanks once again for your help,

Pete

[Krithika Srinath]

Peter,

Since you have multiple AND conditions in your criteria, try using braces to make it well formed.

In your query,

EJBQL: SELECT DISTINCT OBJECT(h)
FROM House h, Location l WHERE h.location = l.name AND location.description = ?1 AND h.bedrooms = ?2
AND h.price BETWEEN ?3 AND ?4

can be written as
EJBQL: SELECT DISTINCT OBJECT(h) FROM House h, Location l
WHERE h.location = l.name
AND location.description = ?1
AND h.bedrooms = ?2
AND (h.price BETWEEN ?3 AND ?4)

Regards,
~Krithika

[Peter Matthews]

Thanks for the tip, however the same exception still persists afterwards - any other ideas - maybe its a mismatch of datatypes?

SELECT DISTINCT OBJECT(h) FROM House h, Location l
WHERE h.location = l.name
AND l.description = ?1
AND h.bedrooms = ?2
AND (h.price BETWEEN ?3 AND ?4)

Ive included the database tables below to see if that is where I am going wrong - sorry about all these questions, its just that Ive been at this for a few days, and have reached no where.

create table House(
ref varchar(16),
seller varchar(16),
description varchar(512),
bedrooms integer,
location varchar(16),
price integer,
CONSTRAINT pk_house PRIMARY KEY (ref,seller),
CONSTRAINT fk_seller FOREIGN KEY (seller) REFERENCES Seller(login),
CONSTRAINT fk_location FOREIGN KEY (location) REFERENCES Location(name));

create table Location(
name varchar(16) CONSTRAINT pk_location PRIMARY KEY,
description varchar(64));

Thanks for all the help so far, much appreciated!

Pete

[Jeanne Boyarsky]

Pete,
That should work. I recommend starting with the simplest possible query and then start adding parameters to see where the problem lies. For example, start with:

SELECT DISTINCT OBJECT(h) FROM House h, Location l
WHERE h.location = l.name

If that works, add the first "and" clause. Etc. Then maybe someone here will have an idea about that line of code.

[Peter Matthews]

Hi Jeanne,

I have done as you suggested - and it fails at the first hurdle:

SELECT DISTINCT OBJECT(h) FROM House h, Location l
WHERE h.location = l.name

Error message returned:

Error: line(2) column(18): JDO75311: Invalid argument(s) for '='.
; requested operation cannot be completed

I have no idea what the error message means (and have search google, without luck). Do you have any ideas - the syntax and names seems fine to me!

Thanks,

Pete