transactions, serial ids, and JDBC
От | Gregory Seidman |
---|---|
Тема | transactions, serial ids, and JDBC |
Дата | |
Msg-id | 20020807224122.GA10184@cs.brown.edu обсуждение исходный текст |
Ответы |
Re: transactions, serial ids, and JDBC
Re: transactions, serial ids, and JDBC |
Список | pgsql-general |
I've come to the point where I really need to run a transaction. In the past it hasn't been as crucial, so I've been happy with individual queries, but I am now past that point. I am now trying to insert a row into three separate tables, and the rows refer to each other. Two of them have SERIAL ids which need to be used as foreign keys. Here's a trimmed down version of the tables: CREATE TABLE A ( id SERIAL not null, somedata int not null, primary key (id) ); CREATE TABLE B ( id SERIAL not null, moredata int not null, a_id integer not null REFERENCES A(id), primary key (id) ); CREATE TABLE C ( b_id integer not null REFERENCES B(id), yetmoredata int not null, primary key (b_id) ); The transaction needs to look something like this: BEGIN INSERT INTO A (somedata) VALUES (1); INSERT INTO B (moredata, a_id) VALUES (1, <id from last insert>); INSERT INTO C (yetmoredata, b_id) VALUES (1, <id from last insert>); END I don't know how to dependably get the id from the last insert. One possibility, I suppose, is to call nextval myself and use the value explicitly, but if there is a way to do it portably (i.e. not depending on PostgreSQL's specific implementation of a self-incrementing id field) I would prefer it. Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a long text string with all of this, or do I need to send each line (including BEGIN and END) as a separate Statement? Or is there some better way? --Greg
В списке pgsql-general по дате отправления: