Smart way to detect SELECT statements?

Hello DBI-Users,

is there a smart way to detect if a prepared statement is a SELECT or a
non-SELECT statement?

Background: My application allows selected users (the Admin account, to
be precise) to enter arbitary SQL statements, passes them to prepare(),
execute(), some fetch()es, and finally finish(). Of course, when the
Admin executes something like UPDATE or DELETE, there is nothing to
fetch(), and fetch() complains loudly. The application is designed to
work with several database servers, so a database-specific solution
won't help me.

I found $sth->{'Active'}, but the DBI documentation is a bit vague about
its meaning. Is there another, portable way than the brute-force
$sth->fetch() if $sql=~/\s*SELECT\b/i ?

Thanks,
Alexander

Re: Smart way to detect SELECT statements?

Alexander Foken wrote:
> Hello DBI-Users,
>
> is there a smart way to detect if a prepared statement is a SELECT or a
> non-SELECT statement?

Examine NUM_OF_FIELDS on the statement which will be 0 for non-select
statements.

From DBI:

Statements that don’t return rows of data, like "DELETE" and
"CREATE" set "NUM_OF_FIELDS" to 0 (though it may be undef in some
drivers).


> Background: My application allows selected users (the Admin account, to
> be precise) to enter arbitary SQL statements, passes them to prepare(),
> execute(), some fetch()es, and finally finish(). Of course, when the
> Admin executes something like UPDATE or DELETE, there is nothing to
> fetch(), and fetch() complains loudly. The application is designed to
> work with several database servers, so a database-specific solution
> won't help me.
>
> I found $sth->{'Active'}, but the DBI documentation is a bit vague about
> its meaning. Is there another, portable way than the brute-force
> $sth->fetch() if $sql=~/\s*SELECT\b/i ?
>
> Thanks,
> Alexander
>
>

Hope this helps.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: Smart way to detect SELECT statements?

Great, exactly what I needed. I did not see the wood for the trees .... ;-)

Thanks,
Alexander

On 09.07.2007 10:29, Martin Evans wrote:
> Alexander Foken wrote:
>> Hello DBI-Users,
>>
>> is there a smart way to detect if a prepared statement is a SELECT or
>> a non-SELECT statement?
>
> Examine NUM_OF_FIELDS on the statement which will be 0 for non-select
> statements.
>
> From DBI:
>
> Statements that don’t return rows of data, like "DELETE" and
> "CREATE" set "NUM_OF_FIELDS" to 0 (though it may be undef in some
> drivers).
>
>
>> Background: My application allows selected users (the Admin account,
>> to be precise) to enter arbitary SQL statements, passes them to
>> prepare(), execute(), some fetch()es, and finally finish(). Of
>> course, when the Admin executes something like UPDATE or DELETE,
>> there is nothing to fetch(), and fetch() complains loudly. The
>> application is designed to work with several database servers, so a
>> database-specific solution won't help me.
>>
>> I found $sth->{'Active'}, but the DBI documentation is a bit vague
>> about its meaning. Is there another, portable way than the
>> brute-force $sth->fetch() if $sql=~/\s*SELECT\b/i ?
>>
>> Thanks,
>> Alexander
>>
>>
>
> Hope this helps.
>
> Martin


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Smart way to detect SELECT statements?

------=_Part_30597_10772039.1184106789007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/10/07, Alexander Foken wrote:
>
> Great, exactly what I needed. I did not see the wood for the trees ....
> ;-)


Remember that procedures might return values, and might therefore be
confused with SELECT statements (eg Informix with EXECUTE PROCEDURE -
sometimes; sometimes Informix procedures don't return values). There again,
maybe that wouldn't matter to you.

And some SELECT statements start with WITH these days (DB2, ISO SQL).

On 09.07.2007 10:29, Martin Evans wrote:
> > Alexander Foken wrote:
> >> is there a smart way to detect if a prepared statement is a SELECT or
> >> a non-SELECT statement?
> >
> > Examine NUM_OF_FIELDS on the statement which will be 0 for non-select
> > statements.
> >
> > From DBI:
> >
> > Statements that don't return rows of data, like "DELETE" and
> > "CREATE" set "NUM_OF_FIELDS" to 0 (though it may be undef in some
> > drivers).
>


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_30597_10772039.1184106789007--