Hello,
I am using Zope with Postgre with Psycopg. Now I have 4 related tables, and
would like to write a query which will input data from one html form.
So here are my tree sql statements, is there a way to make them into one:
1) INSERT INTO business_name (business_name, business_url)
values ('<dtml-var business_name>', '<dtml-var business_url>');
2) select last_value from business_name_business_name_seq
3) INSERT INTO business_address (street_name, town, county, postcode,
county_id, business_name_id)
values (<dtml-sqlvar street_name type="string">, <dtml-sqlvar town
type="string">, <dtml-sqlvar county type="string">, <dtml-sqlvar postcode
type="string">, <dtml-sqlvar county_id type="int">, <dtml-var last>);
This works for zope, where the last SQL Method calls a DTML Method "last"
which basically calls the 2nd SQL statement which returns the last value
from the business name sequence table.
Obvoiusly we come to the race condition realm here which may cause a foreign
key being allocated to the wrong business_address, should two users add a
record but the first user's line slows down for a fraction so that his
second sql method returns the value from the 2nd user -- you see my
problem;^)
What was suggested was to write a subselect query, which would first insert
the business_name, then selects the last_value from
business_name_business_name_seq and then insert this value into the
business_address table ..... an so on. I would like to do this for all 4
tables!!!
Is this at all possible if so can you provide me with links to example code
or be good enough to reply to me.
many thanks
Norm