[Zope] [SQL] Retrieving info from the previous SQL insert

[Chris Kratz]

If you need to know the inserted value, you would normally do a

select currval('some_sequence');

where some_sequence is the name of the sequence attaced to the autoincrement 
column.  

So, you should be able to do the two inserts like this:

insert into table1(col1, col2, etc) values(val1, val2, etc);
insert into table2(col1, col2, etc) values(val1, currval('some_sequence'), 
etc);

As long as you are in a transaction, I believe this should work.  We do the 
same thing, but a little differently.

I don't know your problem domain, but one of the really nice things about 
zope is that it can handle your transactions for you.  So, anything that you 
do within one request is either all commited or all rolled back (ie 
everything in each transaction can be atomic).  And this is of course also 
rippled to the database.  This only works if you do not send your own commits 
and rollbacks of course.  So most of the time, you don't really need to do 
the above.  You can split the two inserts into separate statements if you 
want to and they are still treated as being part of the same transaction and 
are all either commited or rolled back at the end of the transaction.  

For example, I often want to pull the autoincremented value back into zope to 
do other stuff with it.  So, I just created a ZSQL statement with select 
currval('some_sequence') and call that after the insert to get the inserted 
key value.  Postgres keeps track of the last inserted value for each 
transaction and thread, so you always get the last incremented value that 
this particular thread inserted for a sequence.  So, on a heavily updated 
database, you don't have to worry that you are getting the correct value.   
Kind of cool!!

Hope that helps.

-Chris

On Tuesday 11 December 2001 03:57 am, Stephane Bortzmeyer wrote:
> I have a SQL method which performs an INSERT in one table, followed by
> an INSERT into another table. The two INSERTs have to be in the same
> method because they need to be atomic.
>
> In the first table, there is an auto-incrementing field that the
> PostgreSQL DBMS assigns itself. I need the value of that field for the
> second INSERT and I don't see how to retrieve it inside the SQL
> method.
>
> Yes, I'll read more deeply PostgreSQL documentation and Zope
> documentation and <dtml-var my_postgresql_zope_adapter> documentation
> but, in the mean time, if someone has a ready-to-use solution?
>
>
>
>
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )

-- 
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com