quick search:

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)

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

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

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


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>

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.