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.
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
