[Zope] [Zope]re: Help regarding databse & variable management

[Richard Moon]

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