Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of
 gcjcu-cayenne-user-moved1@m.gmane.org designates 80.91.229.2 as permitted
 sender)
To: user@cayenne.apache.org
From: Joseph Schmidt <joseph_schmidt71@yahoo.com>
Subject: Re: "Can't get primary key from temporary id" :(
Date: Mon, 20 Apr 2009 11:59:30 +0200
Lines: 44
Message-ID: <gshh29$u16$1@ger.gmane.org>
References: <315763.11044.qm@web111104.mail.gq1.yahoo.com>
 <FBC15EDC-A107-4F7D-8F3C-630D010BDCF4@ish.com.au>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
In-Reply-To: <FBC15EDC-A107-4F7D-8F3C-630D010BDCF4@ish.com.au>
Sender: news <news@ger.gmane.org>


>>> * write the record to database, fetch it back again and
>>> then you'll have the primary key
>> Than this is not the same transaction :(.
> 
> Correct. This is a limitation of how databases work, not Cayenne. You 
> can't have a primary key until you write the record, unless you do 
> something tricky like using a nanosecond timestamp hashed with the MAC 
> address of your machine and the number you first thought of.
Sorry but this is complete *bullshit*.
There's no such database limitation. I can write in the same transaction 
(i.e. before I do commit), the values received from the previous inserts 
- even those generated by the DB.
E.g. for Mysql, for the following tables:

t_1(id integer autoincrement, name varchar40)
t_2(id integer autoincrement, name varchar40, t_1_id integer)
(see that t_1_id field is not a foreign key here)

No if one is doing with DB directly or with JDBC, the following will work:
-- start transaction
INSERT INTO t_1 (name) VALUES ('name1');
INSERT INTO t_1 (name) VALUES ('name2');
INSERT INTO t_2 (name,t_1_id) select 'name1' , t_1.id from t_1 where 
t_1.name='name1';
commit; -- end transaction

so the correct primary key value (generated by the DB for t_1) will be 
insert at the correct position of t_2.t_1_id, and this inside the same 
transaction - so just like using FK would.

Now, my trivial question again: How to do it with Cayenne, how to get 
that ID to write again inside the same transaction?
(I mentioned in the previous posts that I don't have for t_2.t_1_id a 
relationship, nor FK, because there are too many tables like that).

This is an incredible common scenario in most DBs, so I suppose that it 
should be possible with an ORM like Cayenne too(since with SQL or JDBC 
is dead easy).
I red the entire documentation several times but couldn't find anything 
how to do it in Cayenne :(.

thanks in advance,
Joseph.