At 9:50 -0500 9/4/02, David Dooling wrote:
>For security reasons, wouldn't you want to know what statements are
>non-select _before_ you execute?
>
>If you only care about after, how about something like this:
>
> $sth->execute;
> my @row = $sth->fetchrow_array;
> if (@row) { # results }
> elsif (!$sth->errstr) { # now rows }
> else { warn $sth->errstr }
>
>You can't distinguish between selects that return no data and
>non-selects. But for your example below, it really wouldn't matter.
>It seems you need to parse this stuff before.
In MySQL, you can distinguish select from non-select statements after
$sth->execute by checking $sth->{NUM_OF_FIELDS}. If it's zero, it's
a non-select, if it's non-zero, it's a select. This works even if the
select returns zero rows, because the "width" of the result set is
greater than zero. No parsing of the statement or any other messing
around with it is necessary.
Does this work for other database engines as well?
>
>On Wed, Sep 04, 2002 at 04:16:14PM +0200, Roger Perttu wrote:
>> I store the result for later use in another query.
>
>How do you parse the SQL to know what to do with it?
>
>> If this is the (pseudo) input to my program:
>>
>> select ID from table1
>> select Name from table2 where PersonID = ID
>> insert into table3 values(ID, Name)
>
>Do you scan the SQL for the values() tag and then look back at the
>previous statements? Is the indentation important?
>
>> Then ID from query one and Name from query two will be inserted into
>> table3. Queries might be spread across different databases and nest to
>> any depth (until I run out of connections).
>
>How do you determine which database to query?
>
>> Does anyone know if such a tool already exists?
>
>The XSQL extensions to XML::Generator::DBI begin to address these
>issues. XSQL provides a means to intelligently store SQL queries and
>their results as structured, XML documents. Some people think XML is
>a little heavy-handed, and it can be at times. But you seems to need
>a lot of power and flexibility, and writing your own parser would be a
>big pain. Of course, to use XSQL, you would have to eventually
>rewrite your queries to get all the power it provides. On the other
>hand, it is easy to start using, and you can change queries to XSQL as
>needed.
>
> http://xsql.sourceforge.net/
>
>What would really be nice is a parser that can read SQL and convert it
>to XSQL. This would ease the transition considerably. XSQL is still
>under development (by me), so that and other features are in the
>works. Help is welcome.
>
>There are still difficulties with PL/SQL anbd PgPL/SQL code, but you
>could still label the code with an attribute that defines it as
>having one effect or the other:
>
> <query type="insert">
> BEGIN ...
> </query>
>
>dd
>--
>David Dooling
