I had a similar problem I just puzzled through, though I'm pretty new and don't know the right ways yet :-) What I did was create another DSQl query (sql_newID) that queried for the new ID (select max(ID+1) as newID from table) and another sql_exist( select count *....) then I used this result, but it had to be within a <dtml-in > tag... <dtml-in sql_exist> <dtml-if "exist > 0"> <dtml-in sql_newID> insert into table (ID, name) values (<dtml-var newID>, <dtml-sqlvar name...> </dtml-in> </dtml-if> </dtml-in> This worked because I knew the sql_newID and sql_exist will only return a single value, so the loop only iterates once. I've never seen the <dtml-var sqldelimiter>, though, so I don't know what it does.... Maybe this will help.....

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

_______________________________________________ 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 )