SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-15-2008, 11:54 PM
| ||||
| ||||
 Re: JDBC keygen select When Postgres eventually does support keygen as you mentioned, which form is more likely to be supported? ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"}); OR ps = itsDBConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); In the first, the caller declares which columns he wants back so there is no possibility of any ambiguity. In the second, the caller relies on the database to decide which column will be returned, and we hope the DB knows which one the caller wants (most likely the primary key column). > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto  gsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements> Sent: Monday, October 24, 2005 12:51 PM > To: Dave Cramer > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] JDBC keygen select > > Dave, > > Thanks for the info. Right now I'm issuing a select > currval('foo') after the insert to get the generated PK. This > works but the extra SQL round trip slows down insert > performance quite a bit. > > Mike > > > -----Original Message----- > > From: Dave Cramer [mailto g@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 > > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org      |
|
04-15-2008, 11:54 PM
| |||
| |||
| Re: JDBC keygen select Mike, Apparently there is a patch floating around that will implement INSERT ... RETURNING So I'd say that it is more likely that number 1 gets implemented as it would seem to me easier to do if we know exactly which column to return. Dave On 2-Feb-06, at 9:07 AM, Mike Clements wrote: > When Postgres eventually does support keygen as you mentioned, > which form is more likely to be supported? > > ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"}); > > OR > > ps = itsDBConnection.prepareStatement(sql, > Statement.RETURN_GENERATED_KEYS); > > In the first, the caller declares which columns he wants back so > there is no possibility of any ambiguity. In the second, the caller > relies on the database to decide which column will be returned, and > we hope the DB knows which one the caller wants (most likely the > primary key column). > >> -----Original Message----- >> From: pgsql-jdbc-owner@postgresql.org >> [mailto gsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements>> Sent: Monday, October 24, 2005 12:51 PM >> To: Dave Cramer >> Cc: pgsql-jdbc@postgresql.org >> Subject: Re: [JDBC] JDBC keygen select >> >> Dave, >> >> Thanks for the info. Right now I'm issuing a select >> currval('foo') after the insert to get the generated PK. This >> works but the extra SQL round trip slows down insert >> performance quite a bit. >> >> Mike >> >>> -----Original Message----- >>> From: Dave Cramer [mailto g@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 >>>> >>>> >>> >>> >>> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
|
04-15-2008, 11:54 PM
| ||||
| ||||
| Re: JDBC keygen select Hi, Dave, Dave Cramer wrote: > Mike, > > Apparently there is a patch floating around that will implement INSERT > ... RETURNING > > So I'd say that it is more likely that number 1 gets implemented as it > would seem to me easier to do if we know exactly which column to return. I'd also prefer that second way, as it additionally allows returing more than one column (as a resultset), especially together with the multi-row insert extension that's also discussed. >> ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"}); ps = itsDBConnection.prepareStatement(sql+" RETURNING myKeyColumn"); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match |
Linear Mode
