You can do this in one ZSQL Method - like this example ... The table tune_name has a unique key on tune_name_id, this value is generated when a row is inserted because there is a sequence defined as follows CREATE SEQUENCE "tune_name_tune_name_id_seq" start 15 increment 1 maxvalue 21474 83647 minvalue 1 cache 1 and tune_name.tune_name_id is defined as "tune_name_id" int4 DEFAULT nextval('tune_name_tune_name_id_seq'::text) NOT NULL, So the ZSQL Method looks like this INSERT INTO tune_name (tune_name, search_name, user_id) VALUES ( <dtml-sqlvar "cap_names(tune_name)" type=string>, <dtml-sqlvar search_name type=string>, <dtml-sqlvar user_id type=int>) <dtml-var sql_delimiter> INSERT INTO tune_x_name (tune_id, tune_name_id, user_id) SELECT <dtml-sqlvar tune_id type=int>, currval('tune_name_tune_name_id_seq'), <dtml-sqlvar user_id type=int> FROM users WHERE <dtml-sqltest user_id type=int> <dtml-var sql_delimiter> The second insert (INTO tune_x_name) gets the current value tune_name_tune_name_id_seq as that is the value of tune_name_id which has been written to the table tune_name. The only way I know to get at this value though is within a SELECT statement so I have made a dummy SELECT on the table 'users' which I am certain will return one, and only one row. When I ported this to MySQL there was no currval function so I coded the same thing as follows (again tune_name.tune_name_id is a unique key value generated when a row is inserted into tune_name - it is defined as tune_name_id int4 NOT NULL AUTO_INCREMENT PRIMARY KEY,) LOCK TABLES tune_name WRITE, tune_x_name WRITE <dtml-var sql_delimiter> INSERT INTO tune_name (tune_name, search_name, user_id) VALUES ( <dtml-sqlvar "cap_names(tune_name)" type=string>, <dtml-sqlvar search_name type=string>, <dtml-sqlvar user_id type=int>) <dtml-var sql_delimiter> INSERT INTO tune_x_name (tune_id, tune_name_id, user_id) SELECT <dtml-sqlvar tune_id type=int>, MAX(tune_name_id), <dtml-sqlvar user_id type=int> FROM tune_name <dtml-var sql_delimiter> UNLOCK TABLES Which I guess would work just as well with PostgreSQL but I haven't tried it. HTH At 17:01 08/04/01, you wrote:
To: zope@zope.org Cc: zope-dev@zope.org, zope-checkins@zope.org Subject: [Zope] Help regarding databse & variable management
Hello, I have a problem in database connectivity in Zope with Postgres. If anybody can help me in this regard please reply asap.
I want to insert a row into a tabel, get a unique id from the table and make another entry into another table.I am able to insert into the forst table and retrieve the unique ID. But while trying to insert into the next table, it says no value for the unique id retireved.
Expecting a solution.
Reagrds, George
Richard Moon richard@dcs.co.uk