[DB-SIG] How can I reliably detect whether an SQL statement is a Query?

[Andy Dustman]

On Mon, Aug 2, 2010 at 5:57 AM, Vernon Cole <vernondcole at gmail.com> wrote:
> Dear Gurus:
>
> Please give your advice up front to help me avoid making a design error. I
> am asking for help because:
> 1) I am not confident in my ability to understand Regular Expression
> strings.
> 2) I do not know much about any dialect of SQL other than Microsoft T-SQL
> (and sometime precious little of that.)
>
> I am ready for the next step in development of adodbapi, which is to use
> real ADO.NET (rather than COM ADO-db) when running on Iron Python.
>
> My research indicates that, when using ADO.NET, one must choose to call
> either  an ExecuteReader() method, or an  ExecuteNonQuery() method.
>
> I am attempting to use a lightweight db-api implementation from FePy for my
> pattern. It includes the following snippets of code:
>
> <code snippet 1>
> import re
> P_IS_QUERY = re.compile('^[ \r\n]*SELECT ',re.IGNORECASE)

A slightly better expression would be ^\w*SELECT

\w matches whitespace.

> <code snippet 2>
> class Cursor(object):
>
> <code snippet 2A>
>     def _is_query(self, operation):
>         '''Identify whether an operation is a query or not'''
>         if P_IS_QUERY.match(operation):
>             return True
>         else:
>             return False
>
> <code snippet 2B>
>         if self._is_query(operation):
>             self.reader = command.ExecuteReader()
>             self._set_description()
>         else:
>             command.ExecuteNonQuery()
>             self.description = None
> </code>
>
> It seems to me that this code could be confused by the substring 'SELECT'
> being included as part of a longer string, or in a string literal. Am
> reading it wrong?

The way your expression is written, it only matches SELECT at the
beginning of the line (after any whitespace).

> It also seems to me that I should be able to detect a query by the fact that
> the first token in the command will be either 'SELECT' or 'WITH, but would
> that still be true for other dialects of SQL?'
>
> I am thinking of using something like:
> <code>
> def is_query(operation):
>     return operation.split(' ')[0].upcase in ['SELECT','WITH']
> </code>
>
> Good idea, or Bad idea?
>
> Any comments appreciated.

What are the consequences of using ExecuteReader() when there is
nothing to read? If none, i.e. you get an empty set of results, then I
would say to use that all the time, and don't bother to examine your
SQL at all.
-- 
Question the answers