[egenix-users] trouble with intermediary results sets and Zope DA

[M.-A. Lemburg]

Erik Myllymaki wrote:
> I am moving from Zope 2.3.2 on Windows NT 4.0, w ZODBCDA->MSSQL7.0 SP3 
> to Zope 2.7 on Win2k w mxODBCDA->SQL Server 2000 SP3a.
> 
> I am getting the following error from a query that worked well on the 
> there machine:
> 
> Error Type: ValueError
> Error Value: only the last statement in an SQL batch may generate a 
> result set

I think the error message is clear: only the last statement
may generate a result set. In your case the select is embedded
into a series of statements (the drop being the last statement).

> the query looks something  like this(simplified):
> 
> select * into #temp_table1 from some_table
> <dtml-var sql_delimeter>
> select * into #temp_table2 from some_other_table
> <dtml-var sql_delimeter>
> select * from
> #temp_table1 t1
> join
> #temp_table2 t2
> on
> t1.id = t2.id
> <dtml-var sql_delimeter>
> drop table #temp_table1
> <dtml-var sql_delimeter>
> drop table #temp_table2

Why don't you join the tables directly and let the database
optimizer figure out which parts to copy and which not ?

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 22 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2004, Göteborg, Sweden                           15 days left

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::