Re: SQL-question: returning the id of an insert querry

On Monday 10 November 2003 08:23, David Green wrote:
> Are X & Y two different connections?
> If you execute 2 statements on the same connection and then get
> currval() it will give the last generated id.
>
> Ex.
> On 1 connection:
> INSERT INTO A (fld) VALUES (val); -- id generated = 1
> INSERT INTO A (fld) VALUES (val2); -- id generated = 2
> SELECT currval('SA');
> 2

Thanks for the clarification.  With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back.  This is what I thought.

I talked with the author or SQLObject about this recently and I thnk
he's implementing this correctly, by querying the cursor for the last
OID?:

    def _queryInsertID(self, conn, table, idName, names, values):
        c = conn.cursor()
        q = self._insertSQL(table, names, values)
        if self.debug:
            print 'QueryIns: %s' % q
        c.execute(q)
        c.execute('SELECT %s FROM %s WHERE oid = %s'
                  % (idName, table, c.lastoid()))
        return c.fetchone()[0]

The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence).  I don't know which way is
best (for performance, for instance).

It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes.  That would solve this
problem in one action and completely remove the need for the second
query.  I expect it would have to be user-togglable so it didn't break
with existing code?

Scott