Renaud Guerin wrote:
Hello,
I have a probably simple question, but got different opinions on the matter, and I'm seriously in need of a quick answer.
I need to make a ZSQL method that inserts data in a table this way:
1) first, generate a new ID with something like SELECT MAX(ID)+1 AS my_id FROM ... (using a sequence is not an option for me for various reasons)
2) then, INSERT with the ID previously generated ( is INSERT INTO ... VALUES (<dtml-var my_id>.... the right way to do this ?)
3) display my_id to the user for future reference.
First, I'm not sure how to do 3) : how do I pass my_id back to the original DTML document (which calls the ZSQL method with dtml-call) ?
Also (main question), is there something particular to do to ensure SELECT+INSERT is done in an atomic way that avoids concurrency problems ?
I know Zope starts and ends transactions with HTTP requests, but I'm not sure this implies the kind of read locking I need.
thanks a lot! PS: please cc any answers.
Well, the "best" way to do this is with a stored procedure, and using an Oracle sequence. e.g. CREATE SEQUENCE MY_SEQUENCE; CREATE OR REPLACE PROCEDURE MY_INSERT ( VAL1 IN INTEGER, VAL2 IN VARCHAR2, ID OUT INTEGER -- the important bit ) AS BEGIN SELECT NEXTVAL(MY_SEQUENCE) INTO ID FROM DUAL; -- I think INSERT INTO MY_TABLE (VAL1, VAL2, ID) VALUES (VAL1, VAL2, ID); END MY_INSERT; / Be warned that I did that off the top of my head without looking at the references -- I may have goofed the syntax. With something like that, you can use a DCOracle2 Stored Procedure to invoke it, the OUT parameter is the id that was created, and so will be returned from the stored procedure. -- Matt Kromer Zope Corporation http://www.zope.com/