Thread: The old Insert and retrieving your Serial problem in VB
I am once again dealing with that age old problem of retrieving the value of your inserted serial field in VB. I am fully aware that I can manually pull the currval or nextval from my sequence, but I consider having to manually deal with an auto-generated database object an *extremely* inelegant solution. In every language I have used with PostgreSQL so far (VB, PHP, C++) I have attempted to create an abstracted way of retrieving the generated serial. I have accomplished this in PHP and C++ with the following solution: I have an abstracted SQL Query class with a method that looks roughly like this: //Will return the value of the inserted record at $sColumnName $obDB->RunInsert( $sSQL, $sTableName, $sColumnName ); Inside the method, after the insert succeeds, it calls a method like so: function AutonumberWorkaround( $sTableName, $sColumnName ) { //Need to do a workaround here... if( $this->m_nAutonumber && ($this->m_sDriver == "postgres" || (!$this->m_sDriver && DATABASEDRIVER == "postgres" ) ) ) { $conn = $this->GetConnection(); $rs = $conn->Execute("SELECT " . $sColumnName . " FROM " . $sTableName . " WHERE OID = " . $this->m_nAutonumber); if( $rs ) { $this->m_nAutonumber = $rs->fields[$sColumnName]; } else { echo "<FONT COLOR=RED>Error retrieving autonumber for " . $sTableName . "(OID " . $this->m_nAutonumber . ")<BR>"; return(-1); } } return( $this->m_nAutonumber ); } //AutonumberWorkaround (m_nAutonumber is set in the RunInsert as the OID returned from the statement) I am trying to implement something (ANYTHING!) in VB that will allow me to retrieve my serials **without** having to manually pass in the sequence name. Unfortunately, I can't seem to figure out how to retrieve the inserted OID when using ADO. I have tried using the ado recordset object and use rs.Open on a ado command object, but the resulting recordset is in the closed state and seems to have no data. I have using the ado command execute method, but it doesn't seem to return anything. Is there any permutation of ADO commands that will return the OID of an inserted record? I am even willing to use the crappy writeable recordset syntax: rs.Insert rs("foo") = bar rs.Update But, once again, my recordset's serial column is not being updated (And bear in mind that I am using my ADO class which worked fine in MS SQL Server, so I'm pretty sure that my cursor settings etc. are good...for the record I'm using adOpenDynamic, adLockOptimistic for the recordset, and adUseClient for the server) John
On Tue, Nov 14, 2006 at 03:54:42PM -0600, John McCawley wrote: > I am once again dealing with that age old problem of retrieving the > value of your inserted serial field in VB. I am fully aware that I can > manually pull the currval or nextval from my sequence, but I consider > having to manually deal with an auto-generated database object an > *extremely* inelegant solution. Maybe you're looking for lastval()? http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
That looks like the solution to my problem, thanks!...I tried running it on my 8.0.8 server, but it wasn't found, I assume that's an 8.1 only function? Martijn van Oosterhout wrote: >On Tue, Nov 14, 2006 at 03:54:42PM -0600, John McCawley wrote: > > >>I am once again dealing with that age old problem of retrieving the >>value of your inserted serial field in VB. I am fully aware that I can >>manually pull the currval or nextval from my sequence, but I consider >>having to manually deal with an auto-generated database object an >>*extremely* inelegant solution. >> >> > >Maybe you're looking for lastval()? > >http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html > >Have a nice day, > >
> That looks like the solution to my problem, thanks!...I tried running it > on my 8.0.8 server, but it wasn't found, I assume that's an 8.1 only > function? Note that the upcoming 8.2 release has a handy "returning" clause for insert: http://developer.postgresql.org/pgdocs/postgres/sql-insert.html Bye, Chris.
I think it is. There are also functions that will tell you what the sequence for a given field in a given table is (might also be new in 8.1, but you could probably recreate them yourself). Also, in 8.2 you'll be able to do INSERT ... RETURNING, which can return the id directly back to you. On Nov 14, 2006, at 3:47 PM, John McCawley wrote: > That looks like the solution to my problem, thanks!...I tried > running it on my 8.0.8 server, but it wasn't found, I assume that's > an 8.1 only function? > > Martijn van Oosterhout wrote: > >> On Tue, Nov 14, 2006 at 03:54:42PM -0600, John McCawley wrote: >> >>> I am once again dealing with that age old problem of retrieving >>> the value of your inserted serial field in VB. I am fully aware >>> that I can manually pull the currval or nextval from my sequence, >>> but I consider having to manually deal with an auto-generated >>> database object an *extremely* inelegant solution. >>> >> >> Maybe you're looking for lastval()? >> >> http://www.postgresql.org/docs/8.1/interactive/functions- >> sequence.html -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)