[Zope] More Z SQL head bashing...

[Daniel.Weber@SEMATECH.Org]

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