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 - [EMAIL PROTECTED]
>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
[EMAIL PROTECTED]
_______________________________________________
Zope maillist - [EMAIL PROTECTED]
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 )
