[Zope] More Z SQL head bashing...

[Chris Withers]

Hi,

Well, further to my earlier SQL post, here's the code I'm trying to encapsulate, but it doesn't work
;-)
The problem is one of getting a unique, integer, id from MySQL to use as a primary key for an
INSERT. And no, an AUTO_INCREMENT column won't help here 'cos I need to know what the number is
before I do the insert (it generates a reference number, which is used as the primary key, and needs
to be given back to the person doing the submission...)

Anyway, here's the Z SQL Method Code which goes into a MySQL DB connection:

SELECT COUNT(*) as exist FROM sequences WHERE sequence=<dtml-sqlvar name type=string>
<dtml-var sql_delimiter>
<dtml-if "exist==0">
 INSERT INTO sequences SET sequence=<dtml-sqlvar name type=string>, n=0
</dtml-if>
<dtml-var sql_delimiter>
UPDATE sequences SET n=LAST_INSERT_ID(n+1) WHERE sequence=<dtml-sqlvar name type=string>
<dtml-var sql_delimiter>
SELECT LAST_INSERT_ID() as number

When I try to test the method, I get a NameError for exists, which I presume is coming from the
dtml-if. Any idea what I'm doing wrong or other ways of solving this problem?

cheers,

Chris