Hm. You're defining a relationship; I've always mapped relationships
aas relationships, and let Cayenne deal with the id specifics. Note
that cayenne won't force the creation foreign key (although you should
create one, if your db supports it).
You say you have "too many tables like that"... what order of
magnitude are you talking about?
I think the upfront work of mapping relationships as relationships
will be worth it to you, long term.
In terms of committing it all in the "same transaction", if you're
using myisam in mysql, it doesn't support true transactions, anyway.
Anyway, if you /really/ want to deal with all of the nastiness of
id's, then I would think about using SQLTemplate.
You can script that using velocity directives and variables, so then
you may just wind up executing a query chain of SQLTemplates, where
the first template inserts into t_1 and the second template in the
chain inserts into t_2 per your requirements. If you really want low-
level jdbc-like functionality, SQLTemplate is the key.
Robert
On Apr 20, 2009, at 4/204:59 AM , Joseph Schmidt wrote:
* 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.
