ZopeLABS - Sequences and ZSQL

Anonymous User

Join
Login

Features

Lab Wear
Advanced Search

gadfly
mysql
ODBC
Oracle
Solid
PostgreSQL PostgreSQL

Z O P E

Get It
Achieve Zen
Newbies
The Book
Building Websites
Hosting
EU Hosting

Information

Help!
PyReference
PyCookbook
FAQTs
Daily-URL!
Contact

Sequences and ZSQL

Submitted by: runyaga
Last Edited: 2001-09-17

Category: ZSQL and SQL DA

Average rating is: 0.0 out of 5

(0 ratings)

Description:
if you need to use a sequence from Oracle in ZOPE you may be stumped. its kinda obvious. but here is what I needed to do: create a table thats primary key was a sequence (very common).

I have different levels each level can call levelN-1 as a parent_id

Source (Text):

#create a ZSQLMethod, getLevelSequence 
#argument, lvl_num
select level<dtml-var lvl_num>_sequence.nextval from dual

#create a ZSQLMethod, createLevel1
#arguements:
#lvl1_name:string="blankName"
#display_name:string=""

insert into level1 (id, name, parent_id, display_name)
values ( <dtml-var "getLevelSequence(lvl_num=1)[0].NEXTVAL">,
         <dtml-sqlvar lvl1_name type="string">,
         0,
         <dtml-sqlvar display_name type="string"> )

Explanation:
you will notice that I'm referencing the first resultant from the ZSQLMethod
getLvlSeq()[0] and then to get the value, I must specifiy the column .NEXTVAL

Comments:

by stschmid - 2001-10-18

I did the same for MySQL: # Z SQL Method next_objectid UPDATE objectid SET oid = LAST_INSERT_ID(oid+1) <dtml-var sql_delimiter> SELECT LAST_INSERT_ID() AS oid FROM objectid This works as expected when called from a DTML Document but calls from a second Z SQL Method generate a sequence of numbers that increase by 2 each time. # Z SQL Method insert_test INSERT INTO test(oid, descr) VALUES (<dtml-var expr="next_objectid()[0].oid">, "Test") Is anybody noticing anything similar, or if you have any opinion on what might be going on, please reply.