Here is a ZSQL Method (called insert_author) which inserts into a PostgreSQL table which has a serial column called author_id. Arguments ---------------- surname search_name initials known_name user_id notes Query Template ---------------------- insert into author (surname,search_name,initials, known_name,user_id,notes) values( '<dtml-var surname sql_quote>', '<dtml-var search_name sql_quote>', '<dtml-var "_.string.upper(initials)" sql_quote>', '<dtml-var "_.string.capwords(known_name)" sql_quote>', '<dtml-var user_id>', '<dtml-var notes>') <dtml-var sql_delimiter> select currval('author_author_id_seq') Note that we don't insert anything into the serial column author_id since PostgreSQL puts that in for you. Here is the schema for that table CREATE TABLE author ( author_id serial, surname text, search_name text, initials text, known_name text, user_id int4, notes text And here is a snippet from a dtml method which performs the insert and retrieves the value of author_id for the row just inserted. <dtml-in insert_author> <dtml-call expr="REQUEST.set('author_id',currval)"> </dtml-in> Author_id is <dtml-var author_id> This assumes all the arguments to insert_author (that is surname, user_id etc) have already been set up from a form or in some other way. HTH Richard At 23:22 20/05/00 -0400, you wrote:
This is going to be a long winded questions.
Ok I have been hacking around with Zope and Pgsql for awhile now. I am trying to learn both at the same time, not any easy task, I don't know SQL that well. I learn by doing ,and this is really fun, kinda like a digital puzzle of sorts. So any way I set up a database in pgsql to hold contact information. This was no small task for me, like I said, I am new to SQL. I did discover a neat way to use CREATE VIEW foo AS SELECT that is not in the book though. Anyway the PRIMARY KEY for the table of names is a SERIAL type, all other tables such as phone numbers and addresses use REFERENCES to this KEY. The database works quite well, I think I have it normalize as much as need be (bare with me I am still learning). Now on to Zope
I have Zope all set, and I can run all your standard SELECT, INSERT, UPDATE stuff. But I have one hitch, I can't seem to figure out how to make it so an end user could add a name and email address without knowing about the PKEY number. This is no problem for a DBA but I want to make it easy for anyone to INSERT and UPDATE records. Did I make a bad design choice in use unique numbers to glue all the tables together. Is this an interface problem. Am I missing something. I need some help. Can someone point me in the right direction please?
Richard
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Richard Moon richard@dcs.co.uk