>
> 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?
It might actually work for DBD::ODBC...
>
> >
> >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
>
>
