Re: JDBC keygen select
От | Mike Clements |
---|---|
Тема | Re: JDBC keygen select |
Дата | |
Msg-id | 06B12D1D68BCCA4CB1F60BB4EA509768245289@pq-exch01.actional.com обсуждение исходный текст |
Ответ на | JDBC keygen select ("Mike Clements" <mclements@actional.com>) |
Список | pgsql-jdbc |
Dave, Thanks for the info. Right now I'm issuing a select currval('foo') after the insert to get the generated PK. This works butthe extra SQL round trip slows down insert performance quite a bit. Mike > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: Monday, October 24, 2005 11:20 AM > To: Mike Clements > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JDBC keygen select > > Mike, > > Well, until we get insert returning implemented (allegedly > soon), the > protocol doesn't support returning any values without another round > trip to the db > > So you have two choices here. > > 1) get the ID before and insert it ie select > nextval('sequence_name') > and insert the value explicitly. If you cache sequences you > can cache > them on the connection too ( more work ) > 2) get the ID after using currval('sequence_name') > > P.S. Using PostGRE is frowned upon the name is either Postgres, or > PostgreSQL > > Dave > On 22-Oct-05, at 4:57 PM, Mike Clements wrote: > > > Hello, > > > > I'm a newbie on PostGRE but have experience using SQL > Server & Oracle > > via JDBC. Something that works fine on these does not work in > > PostGRE so > > I'm looking for advice. > > > > In my schema all primary keys are integers and the DB automatically > > assigns values (using sequences or identities). When I insert into > > these > > tables via JDBC I do not specify any value for the primary > key, and I > > use the RETURN_GENERATED_KEYS flag so the generated key is > provided in > > the RecordSet returned from the insert command - something > like this: > > > > pStmt = itsDbConn.prepareStatement( > > "insert into tbl (col1) values (?)", > > Statement.RETURN_GENERATED_KEYS); > > pStmt.setString(1, "foo"); > > count = pStmt.executeUpdate(); > > if(count > 0) > > { > > rs = pStmt.getGeneratedKeys(); > > rs.next(); > > pk = rs.getLong(1); > > } > > > > This works on SQL Server & Oracle but *not* on PostGRE SQL. In the > > latter, it throws an exception in prepareStatement() saying "this > > method > > is not yet implemented". > > > > So my question is, how does one do this? This keygen approach is > > important for performance, scalability and robustness. Launching a > > separate SQL command to fetch the generated key has performance > > problems. Self-generating the keys has problems with concurrency > > across > > multiple clients. > > > > Thanks > > > > Michael R. Clements > > Principal Architect, Actional Corp. > > mclements@actional.com > > FREE! Actional SOAPstation Developer Version > > Web services routing, security, transformation and versioning > > http://www.actional.com/sstdownload > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > >
В списке pgsql-jdbc по дате отправления: