Z Sql method newbie question
Hallo, Newbie. Zope 2.3.3 on Win98. Interbase 6.0 through gvibDA. I have a database table with three fields : id, name, surname. I want to create a simple Z Sql method that adds a new record to the table and automatically assigns an increased id value to the new record . I tried something like : insert into table(name, surname) values ( max (id) + 1, <dtml-sqlvar name type="string">, <dtml-sqlvar surname type="string"> ) (Method arguments are : name, surname). But It does not work as I expect. Any help will be appreciated. Thanks in advance. Fabrizio C.
max(id) won't work unless you run a separate call to fetch the current id number, and then multithreading will probably do you in. Or, you'll need to implement some locking technique to make sure you dont double assign numbers. A time based ID number might be a good way to do it. Best bet is to let the DB make the call on this. Look here: http://groups.google.com/groups?hl=en&safe=off&ic=1&th=aaedeb0dcc1aba07,5&se ekm=398C8149.9C4F3B3B%40twcny.rr.com#p
-----Original Message----- From: Fabrizio [mailto:facelle@libero.it] Sent: Saturday, July 14, 2001 3:55 PM To: zope@zope.org Subject: [Zope] Z Sql method newbie question
Hallo,
Newbie. Zope 2.3.3 on Win98. Interbase 6.0 through gvibDA.
I have a database table with three fields : id, name, surname. I want to create a simple Z Sql method that adds a new record to the table and automatically assigns an increased id value to the new record .
I tried something like :
insert into table(name, surname) values ( max (id) + 1, <dtml-sqlvar name type="string">, <dtml-sqlvar surname type="string"> )
(Method arguments are : name, surname).
But It does not work as I expect.
Any help will be appreciated. Thanks in advance.
Fabrizio C.
_______________________________________________ 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 )
Hello, I don't know about Interbase; something like this should work in Oracle, although I haven't used multiple SQL statements in a Z SQL Method yet, I just know it's documented as being possible. On your database, this may even interlock correctly with itself without explicit locking. lock table <table> in exclusive mode; insert into <table> (id, name, surname) select nvl(max(id),0)+1, <sqlvar ...>, <sqlvar ...> from <table> The lock table ... is about coping when several of these are run simultaneously. The nvl(...) is about replacing the NULL value when there are no rows with a sensible value. Regards, J. Cone At 22:55 14/07/01 +0200, Fabrizio wrote:
Hallo,
Newbie. Zope 2.3.3 on Win98. Interbase 6.0 through gvibDA.
I have a database table with three fields : id, name, surname. I want to create a simple Z Sql method that adds a new record to the table and automatically assigns an increased id value to the new record .
<snip>
I am not familiar with Interbase but most database have support for atomic autoincrement ids (Oracle calls them sequences, MySQL has an autoincrement flags for columns). ========================================================================== Andreas Jung andreas@digicool.com Digital Creations "Zope Dealers" http://www.zope.org ----- Original Message ----- From: "Fabrizio" <facelle@libero.it> To: <zope@zope.org> Sent: Samstag, 14. Juli 2001 15:55 Subject: [Zope] Z Sql method newbie question
Hallo,
Newbie. Zope 2.3.3 on Win98. Interbase 6.0 through gvibDA.
I have a database table with three fields : id, name, surname. I want to create a simple Z Sql method that adds a new record to the table and automatically assigns an increased id value to the new record .
I tried something like :
insert into table(name, surname) values ( max (id) + 1, <dtml-sqlvar name type="string">, <dtml-sqlvar surname type="string"> )
(Method arguments are : name, surname).
But It does not work as I expect.
Any help will be appreciated. Thanks in advance.
Fabrizio C.
_______________________________________________ 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 )
participants (4)
-
Alan Capesius, MCSE -
Andreas Jung -
Fabrizio -
J. Cone