Can I capture results from insert in Z SQL methods?
In a Z SQL method I want to create a row in a table which has several foreign keys. The method is going to create other rows in subsidiary tables with some of its inputs, then use their primary keys as foreign keys in the final insert. For example, a contact table has foreign keys referring to address and phone tables. The code looks something like begin; # voice phone insert into phone ( ... ); # fax insert into phone ( ... ); insert into address ( ... ); insert into contact ( name, email, address_id, phone, fax ) values ( ... ); commit; In the final insert I need row ids corresponding to rows I inserted in the phone and address tables. Can I capture them as a result returned from the earlier inserts or do I need to do a subselect in the last insert, like so: insert into contact ( name, email, address_id, phone, fax ) values ( <dtml-sqlvar name type="string">, <dtml-sqlvar email type="string">, (select max(id) from address where address1 = <dtml-sqlvar address1 type="string"> and address2 = <dtml-sqlvar address2 type="string"> and city = <dtml-sqlvar city type="string"> and state = <dtml-sqlvar state type="string"> and postal_code = <dtml-sqlvar postal_code type="string">), (select max(id) from phone where phone_area = <dtml-sqlvar phone_area type="string"> and phone_number = <dtml-sqlvar phone_number type="string"> and type = 'work'), (select max(id) from phone where fax_area = <dtml-sqlvar fax_area type="string"> and fax_number = <dtml-sqlvar fax_number type="string"> and type = 'fax') ); or do Z SQL methods support some other mechanism? I didn't see any examples of such stuff in the Zope Book. (I'm using the Psycopg DA, Zope 2.5.1.) Thanks, -- Skip Montanaro - skip@pobox.com http://www.mojam.com/ http://www.musi-cal.com/
This is really a general SQL problem rather than anything to do with Zope/ ZSQL. As you're using Postgres you should really be using sequences to store your id's. Using the serial type is a quick way of doing that. Then do a select nextval('whatever'); to get the next id which can then be used for the inserts. A -- Logical Progression Ltd, 3 Randolph Crescent, Edinburgh, EH3 7TH, UK Tel: +44 (0)131 466 9585 Web: http://www.logicalprogression.net/
Andrew> This is really a general SQL problem rather than anything to do Andrew> with Zope/ZSQL. So ZSQL methods don't provide any special syntactic sugar other than <dtml-sqlvar>? Andrew> Then do a select nextval('whatever'); to get the next id which Andrew> can then be used for the inserts. Thanks, I'll check it out. This seems (naively) like it would be a race to me though. Let's say I do this: insert into phone ( ... ) values ( ... ); The new row in phone got a unique id (I do use the serial type for my primary keys). Now, I need the id that row got to use in another insert. If I do something like: insert into contact ( ... phone, ... ) values ( ... (select currval(phone_id_seq)), ... ); That will get the id of the record I just inserted assuming someone else didn't sneak in and insert another row in the phone table before I queried phone_id_seq. Or were you suggesting I do something like: <some dtml assignment ... select nextval(phone_id_seq); ...> insert into phone ( id, ... ) values ( <value retrieved above> ... ); If so, how do I capture the value of nextval(...) within the ZSQL method? Thx, -- Skip Montanaro - skip@pobox.com http://www.mojam.com/ http://www.musi-cal.com/
Hi Skip, Skip Montanaro wrote:
Andrew> This is really a general SQL problem rather than anything to do Andrew> with Zope/ZSQL.
So ZSQL methods don't provide any special syntactic sugar other than <dtml-sqlvar>?
Andrew> Then do a select nextval('whatever'); to get the next id which Andrew> can then be used for the inserts.
Thanks, I'll check it out. This seems (naively) like it would be a race to me though. Let's say I do this:
insert into phone ( ... ) values ( ... );
The new row in phone got a unique id (I do use the serial type for my primary keys). Now, I need the id that row got to use in another insert. If I do something like:
insert into contact ( ... phone, ... ) values ( ... (select currval(phone_id_seq)), ... );
That will get the id of the record I just inserted assuming someone else didn't sneak in and insert another row in the phone table before I queried phone_id_seq.
Or were you suggesting I do something like:
<some dtml assignment ... select nextval(phone_id_seq); ...> insert into phone ( id, ... ) values ( <value retrieved above> ... );
If so, how do I capture the value of nextval(...) within the ZSQL method?
I've struggled with the same thing, but never got it work satisfactorily. The approach I take is to have a separate ZSQL method for each insert and glue them together with a python script. ZSQL method nextPhoneId: select nextval('phone_id_seq') as phone_id; ZSQL method nextContactId: select nextval('contact_id_seq') as contact_id; ZSQL method insertPhone: insert into phones (phone_id, phone_number ) values (<dtml-sqlvar phone_id type=int>,<dtml-sqlvar phone_number type=string>) Python script: request = container.REQUEST response = request.RESPONSE contact_id = context.nextContactId() request.set(contact_id) phone_id = context.nextPhoneId() request.set(phone_id) # call ZSQL method to insert the contact context.insertContact() # call ZSQL method to insert the phone context.insertPhone() # return the user a page template to view the newly added contact response.redirect('viewContact?contact_id=%s' % (contact_id)) I've found this works well for me, although you do tend to end up with a lot of separate ZSQL methods. Jim
Hi Jim, hi Skip, --On Sonntag, 3. November 2002 12:30 -0500 Jim Gallacher <jg.lists@sympatico.ca> wrote:
Hi Skip,
Skip Montanaro wrote:
Andrew> This is really a general SQL problem rather than anything to do Andrew> with Zope/ZSQL.
So ZSQL methods don't provide any special syntactic sugar other than <dtml-sqlvar>?
Its nothing magic with this sqlvar. It only assembles the query which is then send to the DB - as one block of text.
Andrew> Then do a select nextval('whatever'); to get the next id which Andrew> can then be used for the inserts.
Thanks, I'll check it out. This seems (naively) like it would be a race to me though. Let's say I do this:
insert into phone ( ... ) values ( ... );
The new row in phone got a unique id (I do use the serial type for my primary keys). Now, I need the id that row got to use in another insert. If I do something like:
insert into contact ( ... phone, ... ) values ( ... (select currval(phone_id_seq)), ... );
That will get the id of the record I just inserted assuming someone else didn't sneak in and insert another row in the phone table before I queried phone_id_seq.
You should consider consulting your database manual. This sequcence nextval/currval is an FAQ. currval is only valid after nextval in the same transaction. So currval, used correctly after nextval will under any circumstance give you the same value as your last nextval in your transaction, no matter how many nextval() calls are made in another transactions in the same time. Here is the relevant section from Postgres documentation: --------- cite -------- The available sequence functions are: nextval Advance the sequence object to its next value and return that value. This is done atomically: even if multiple server processes execute nextval concurrently, each will safely receive a distinct sequence value. currval Return the value most recently obtained by nextval for this sequence in the current server process. (An error is reported if nextval has never been called for this sequence in this process.) Notice that because this is returning a process-local value, it gives a predictable answer even if other server processes are executing nextval meanwhile. setval Reset the sequence object's counter value. The two-parameter form sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value. In the three-parameter form, is_called may be set either true or false. If it's set to false, the next nextval will return exactly the specified value, and sequence advancement commences with the following nextval. For example, SELECT setval('foo', 42); Next nextval() will return 43 SELECT setval('foo', 42, true); Same as above SELECT setval('foo', 42, false); Next nextval() will return 42 The result returned by setval is just the value of its second argument. Important: To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions may leave unused "holes" in the sequence of assigned values. setval operations are never rolled back, either. If a sequence object has been created with default parameters, nextval() calls on it will return successive values beginning with one. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command; see its command reference page for more information. ------------------- HTH Tino Wildenhain
Skip Montanaro writes:
... insert into contact ( ... phone, ... ) values ( ... (select currval(phone_id_seq)), ... );
That will get the id of the record I just inserted assuming someone else didn't sneak in and insert another row in the phone table before I queried phone_id_seq. Usually, this is not a problem, because "nextval/currval" are connection local. Thus, a "nextval" issued in a different connection does not affect "currval" in this connection.
Usually, you have a connection per thread. Thus, the race condition does not occur. There are DAs that use a single connection for several threads but hopefully, the sequence values are then protected by the transactions. When you ask your question on "Zope-DB@zope.org", you may get more detailed information. Dieter
participants (5)
-
Andrew Veitch -
Dieter Maurer -
Jim Gallacher -
Skip Montanaro -
Tino Wildenhain