[Zope] Dynamic SQL problem

[FR Chalaoux]

Hi Zopepists,

The first statement of this SQL method get 
sqlvars from a form. It's working fine :)
But as you can see 'idsoftware' is unknown in 
the second sql statement before the first 
statement is achieved.
Thus, I tried to get 'idsoftware' from [select 
idsoftware from software where name = <!--
#sqlvar name type=string-->] but obtained the 
error message (see below)

Is there a solution, do I need a dynamic SQL 
methodology!?

FR

SQL method
----------
insert into software 
(name,idcompany,sversion,url,howto)
select max(idsoftware)+1,
<!--#sqlvar name type=string-->,
<!--#sqlvar idcompany type=int-->,
<!--#sqlvar sversion type=string-->,
<!--#sqlvar url type=string-->,
<!--#sqlvar howto type=string-->
 from software

<!--#var sql_delimiter-->

insert into softdomain (idsoftware,iddomain) 
values( (select idsoftware from software where 
name = <!--#sqlvar name type=string-->),
<!--#sqlvar iddomain type=string-->)

Error message
-------------

<strong>Error, 
<em>exceptions.SyntaxError</em>:</strong> 
unexpected token sequence.near ::
'domain) values('*'(select idsoftw'
*******************************
current state = 168
expects: 
'numeric_literal', '?', 
'character_string_literal', 
('nomatch1',)
current token = ((-6, '('), '(')

<hr><strong>SQL used:</strong><br>
<pre>
insert into software 
(idsoftware,name,idcompany,sversion,url,howto)
select max(idsoftware)+1,
'cerius',
1,
'3.1',
'dd',
'dd'
 from software


-------------------------------------------------
-----------


insert into softdomain (idsoftware,iddomain) 
values((select idsoftware from software where 
name = 'cerius'),
'1')
</pre>
<hr>
</body></html>
</body>
</html>
<!--
Traceback (innermost last):
  File C:\Program 
Files\zopesite\lib\python\ZPublisher\Publish.py, 
line 877, in publish_module
  File C:\Program 
Files\zopesite\lib\python\ZPublisher\Publish.py, 
line 590, in publish
    (Info: 
/GadSoft/DBALS/software/insert_software/manage_te
st)
  File C:\Program 
Files\zopesite\lib\python\Shared\DC\ZRDB\DA.py, 
line 305, in manage_test
    (Object: CopySource)
  File C:\Program 
Files\zopesite\lib\python\Shared\DC\ZRDB\DA.py, 
line 286, in manage_test
    (Object: CopySource)
  File C:\Program 
Files\zopesite\lib\python\Shared\DC\ZRDB\DA.py, 
line 390, in __call__
    (Object: CopySource)
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\db.p
y, line 175, in query
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\gadfly.py, line 312, in execute
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\kjParser.py, line 1175, in DoParse1
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\kjParser.py, line 1167, in DoParse
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\kjParser.py, line 832, in GO
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\kjParser.py, line 760, in DoOneReduction
  File C:\Program 
Files\zopesite\lib\python\Products\ZGadflyDA\gadf
ly\kjParser.py, line 816, in ParseError
SyntaxError: (see above)

-->

François-Régis Chalaoux
Bioinformatics Group
Synthélabo Biomoléculaire
67080 Strasbourg Cedex
FRANCE
Tél : 03 88 60 87 14
Fax : 03 88 45 90 70