[Zope-dev] ZSybaseDA: multiple sql-statements

9 Aug 1999

      Hi,

the new ZSybaseDA 1.0.1 seems to be very restrictive regarding
multiple statements in one ZSQLMethod.

Even simple cases as this one (inspired by the "auto commit hint"
at the end of the README) don't work:

ZSQLMethod:

  commit transaction
  <!--#var sql_delimiter-->

  SELECT * FROM bar

An error is signaled if there are multiple statements (queries) and
at least one has a "select" in it:

Zope-2.0.0b3-linux2-x86/lib/python/Products/ZSybaseDA/db.py:

            if len(queries) > 1:
                result=[]
                for qs in queries:
                    if select_m(qs) >= 0: raise 'Query Error', (
                        'select in multiple sql-statement query'
                        )

I'am using temporary tables for some of my queries.
With  1.0.1 it is impossible to do this:

  COMMIT TRANSACTION
  <!--#var sql_delimiter-->

  SELECT * INTO #foo from something
  <!--#var sql_delimiter-->

  <!--#comment-->
  other SELECTs with empty result set...
  <!--#/comment-->

  <!--#comment--> Only the result of this query should be displayed <!--#/comment-->
  SELECT * FROM #foo
  <!--#var sql_delimiter-->

  DROP TABLE #foo

The solution would be to take the result of exactly one select query
if there are more than one. If all other selects have an empty result
set, nothing is lost -- Even more: my queries start working again!

To be concrete -- 
I would suggest to change the query() method in Products/ZSybaseDA/db.py like this:

    def query(self,query_string,
              max_rows=9999999,
              select_m=regex.compile('[\0- ]*select[\0- ]+',
                                     regex.casefold).match,
              try_again=1
              ):
        global failures, calls
        calls=calls+1
        db=self.db
        try:
            c=self.cursor
            self._register()
            queries=filter(None, map(strip,split(query_string, '\0')))
            if not queries: raise 'Query Error', 'empty query'

            if len(queries) > 1:
                result=[]
                lastQueryWithSelect=None
                for qs in queries:
                    if select_m(qs) >= 0: lastQueryWithSelect=qs
                if lastQueryWithSelect is None:
                    # non-select queries only: execute them all
                    for qs in queries:
                        r=db.execute(qs)
                        result.append([qs, str(`r`), calls])
                    desc=nonselect_desc
                else: # use cursor on last select, but execute the other queries
                    for qs in queries:
                        if qs is lastQueryWithSelect:
                            r=c.execute(qs)
                            result=c.fetchmany(max_rows)
                            desc=c.description[0]
                            c.close()
                        else:
                            r=db.execute(qs)
                            if r and r[0]:
                                raise 'Query Error', (
                                    # explain...
                                    'multiple selects with non-empty results in multiple sql-statement query'
                                    )
            else:  # one query only
                query_string=queries[0]
                if select_m(query_string) >= 0:
                    r=c.execute(query_string)
                    result=c.fetchmany(max_rows)
                    desc=c.description[0]
                    c.close()
                else:
                    r=db.execute(query_string)
                    result=[[query_string, str(`r`), calls]]
                    desc=nonselect_desc
            failures=0
            #c.close()
        except self.Database_Error, v:
     [no change below]

-- 
Frank Buhr