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 }
>
I do care about all errors except for the "no select statement
executing" one.
>
>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.
>
Impossible (stored procedures might do anything)
>
>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?
>
I don't parse 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?
>
No, actually it's XML and I was trying to simplify. It would look like this:
<step>
<statement type="Text" database="source">
<text>select ID from table1</text>
</statement>
<step>
<statement type="Text" database="source">
<text>select Name from table2 where PersonID = ?</text>
<parameter direction="In" name="ID" />
</statement>
</step>
<!-- ... insert skipped -->
</step>
>
>
>
>>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?
>
It's in the configuration file. Named connections.
>
>
>
>>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/
>
Hmm, I've got this great invention, it's called the wheel :-)
>
>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
>
>
