[Zope] Can I capture results from insert in Z SQL methods?

[Jim Gallacher]

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