[JDBC] JDBC changes for 7.2... some questions...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Folks- I'm looking into some modifications for the Castor project on how we get identies from inserted objects into the database. Specifically, if we have used the SERIAL type to link in a sequence number during row insertion, it would be great to get that sequence number when we do the insert. Both Oracle and mssql have something to provide this. Getting the OID would be second-best solution for us. Now, I understand that in the Statement class, we have getInsertedOID() in the table. However, the problem we run into is that this isn't accessiable if we use something like poolman to provide database pooling of connections. (You get the poolMan Statement object which is wraps the Statement classes of the driver.) So, what I'm looking for is some of the following ways to get the primary key back, or the OID if nothing else... 1) Being able to use the RETURNING clause in prepared statements, like this INSERT INTO tableName (key1,...) VALUES (value1,...) RETURNING primKeyName INTO ? Which is what Oracle provides. 2) Working like Sybase and call select @@OID on the next call, which would be trapped by the statment object to return a resultset with the oid of the last inserted oid. 3) or, someother way that where one doesn't need direct access to method calls to get 'getInsertedOID()', but indirect ones. Thoughts? Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gr83iysnOdCML0URAj+QAJ91RZXOoEA+xnu68YhIA4euNfIWOgCfcG1/ 5L5ATkdL/wPwTnbQy0NI2jI= =l/7B -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] JDBC changes for 7.2... some questions...
Ned Wolpert [EMAIL PROTECTED] writes: 1) Being able to use the RETURNING clause in prepared statements, like this INSERT INTO tableName (key1,...) VALUES (value1,...) RETURNING primKeyName INTO ? Which is what Oracle provides. INSERT ... RETURNING was discussed recently, and I think people agreed it's a good idea, but it got hung up on some unresolved issues about how it should interact with ON INSERT rules for views. Search the pghackers mailing list archives for details. At this point I think it's probably too late to consider it for 7.2, but I'm still open to doing it in 7.3 if we can come up with a bulletproof spec. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] JDBC changes for 7.2... some questions...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 What about the 'select @@last_oid' to make the getInsertedOID() call available even when the driver is wrapped by a pooling manager? How do people feel about this? (I would be happy to contribute to the codebase for this one.) I understand the RETURNING clause is more work... and I would like to help out on that for 7.3. On 21-Aug-2001 Tom Lane wrote: Ned Wolpert [EMAIL PROTECTED] writes: 1) Being able to use the RETURNING clause in prepared statements, like this INSERT INTO tableName (key1,...) VALUES (value1,...) RETURNING primKeyName INTO ? Which is what Oracle provides. INSERT ... RETURNING was discussed recently, and I think people agreed it's a good idea, but it got hung up on some unresolved issues about how it should interact with ON INSERT rules for views. Search the pghackers mailing list archives for details. At this point I think it's probably too late to consider it for 7.2, but I'm still open to doing it in 7.3 if we can come up with a bulletproof spec. regards, tom lane Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gtIoiysnOdCML0URAmgIAJwJVf2BGhFq88bXHY3yni9qzGohegCdHmPf 7Xnb57gfiP2xoMC8x5mIhWU= =p5Bx -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] JDBC changes for 7.2... some questions...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 it should interact with ON INSERT rules for views. Search the pghackers mailing list archives for details. At this point I think it's probably Ugh... a quick search shows me asking this same thing in May, when the 'big TODO' list was being commented on. Now that I remember, I thought that the RETURNING clause was being implemented. I didn't realize there wasn't agreement on it. I need to pay more attention to what I write. :-) Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gtSBiysnOdCML0URAtXNAJ4+W3AgpEk5QZM5IKCFFMQ2tGP5UQCeM1Lx TXQw4pL4ew65B1iH+EfmZhk= =Ho0k -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] JDBC changes for 7.2... some questions...
Ned Wolpert [EMAIL PROTECTED] writes: What about the 'select @@last_oid' to make the getInsertedOID() call available even when the driver is wrapped by a pooling manager? How do people feel about this? Yech. At least, not with *that* syntax. @@ is a valid operator name in Postgres. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] JDBC changes for 7.2... some questions...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ok, so you're not opposed to the idea then, just the syntax. Does anyone oppose having this concept in the JDBC driver? And what syntax is acceptable? Could we just do 'select getInsertedOID()' which would break people who have functions called getInsertedOID() of course... On 21-Aug-2001 Tom Lane wrote: Ned Wolpert [EMAIL PROTECTED] writes: What about the 'select @@last_oid' to make the getInsertedOID() call available even when the driver is wrapped by a pooling manager? How do people feel about this? Yech. At least, not with *that* syntax. @@ is a valid operator name in Postgres. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V ymYTw8l3f9WU4V5idFsibAE= =UQ2M -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
