opps forgot a part from the SQL
SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
rownumber
FROM MyView e
ORDER BY name
Order by must be added
Sako.
Signature IT-Consult Armainak Sarkis
----- Original Message -----
From: "John" <[EMAIL PROTECTED]>
To: <user@cayenne.apache.org>
Sent: Thursday, June 07, 2007 5:38 PM
Subject: Re: Select Query on Table with no Primary Key
Hello Dave,
You said you use a view and you will NOT modify the data, then try this:
Your View name : MyView.
--------------------------------------------------------------------------------------------------------------------------
create another view call MyViewWithPK
-------------------------------------------------
SELECT e.* , (SELECT COUNT(*) FROM MyView e2 WHERE e2.name <= e.name ) AS
rownumber
FROM MyView e
// remember that name must be included in the view MyView
// remember that this technique is slow, but i suppose this is your last
problem :)
---------------------------------------------------------------------------------------------------------------------------
let me know if this work :)
Sako
Signature IT-Consult Armainak Sarkis
----- Original Message -----
From: "Dave Merrin" <[EMAIL PROTECTED]>
To: <user@cayenne.apache.org>
Sent: Thursday, June 07, 2007 3:36 PM
Subject: Re: Select Query on Table with no Primary Key
Hi Andrus,
I was playing about with putting in my own EntityResolver. For now I'm
going to take your approach and see how I get on.
Cheers,
Dave
Andrus Adamchik wrote:
To give an example - if you have an EMPLOYEE table, you can make an
assumption that it is highly unlikely that there are two people with the
same name, born on the same date, and working in the same department. On
that assumption you can mark these 4 columns as PK in the Modeler:
FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DEPARTMENT_ID.
Works well with views or tables (updateable or read-only) on any DB.
Andrus
On Jun 7, 2007, at 1:29 PM, Andrus Adamchik wrote:
Tore is right - for Cayenne to handle an object (whether read-only on
read/write), it needs to know which column or columns uniquely identify
each row. Now... you can fake a PK in your model, even if there's none
in the db - just select a really unique combination of columns, and
mark those columns as the PK in the modeler. I've mapped tables with
such "imaginary PK" a lot.
If it is not possible (i.e. duplicate rows are expected to be fetched),
you will have to use DataRows.
Andrus
On Jun 7, 2007, at 1:04 PM, Tore Halset wrote:
On Jun 6, 2007, at 16:12 , Dave Merrin wrote:
I'm trying to run a SelectQuery on a table with no primary key.
Unfortunately it's not working. Can anybody help? I have no control
over the database so I can't add in primary keys.
As you know the PK are essential not only to update a row, but to make
sure a single row maps to a single DataObject in your context.
Some database engines do have a unique invisible column. If your
database does this, then perhaps you could map that column as your
primary key? What database engine are you using?
Regards,
- Tore.
