Fw: How to Retrieve Table Name from Statement Handle

To answer you question, for an Oracle environment I would like  $sth->{TABLE
NAME}  to contain a list.

my $tablename = $sth->{TABLENAME} ->[0] = First table
$tablename  $sth->{TABLENAME} ->[1] = Second table

The $tablename value will  be schema.tablename format.
For example:
schema.narf
schema.zord

Joseph Lamb


----- Forwarded Message ----
From: Alexander Foken <alexander@foken.de>
To: Lamb Joseph <joseph_lamb@yahoo.com>
Cc: dbi-users@perl.org
Sent: Wednesday, May 7, 2008 11:18:53 AM
Subject: Re: How to Retrieve Table Name from Statement Handle

Hmmm, and what do you think $sth->{TABLENAME} should contain after
executing the following SQL?

SELECT t1.foo,t2.bar FROM narf t1, zord t2 WHERE t1.ikes=t2.blurb

Alexander

On 07.05.2008 19:51, Lamb Joseph wrote:
> I am creating a simple tool that will query one table and retrieve the dat
a. Then this tool will turn the data into insert statements.
>
> I was wondering if there was a way to retrieve the table name from the sta
tement handle?
>
> Similar to print "SQL statement contains $sth->{NUM_OF_FIELDS} columns\n";
>
> but like this
>
> print "SQL statement table name is $sth->{TABLENAME} \n";
>
>
>  Joseph Lamb
>
>
>
>        ________________________________________
____________________________
________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu0<...tDypao8Wcj9tAcJ
>


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


 ________________________________________
____________________________________
________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06...tDypao8Wcj9tAcJ

On Wed, May 7, 2008 at 12:37 PM, Lamb Joseph <joseph_lamb@yahoo.com> wrote:

> To answer you question, for an Oracle environment I would like
>  $sth->{TABLENAME}  to contain a list.
>
> my $tablename = $sth->{TABLENAME} ->[0] = First table
> $tablename  $sth->{TABLENAME} ->[1] = Second table
>
> The $tablename value will  be schema.tablename format.
> For example:
> schema.narf
> schema.zord
>


So, what do you expect from a more complex statement, such as one which
includes multiply nested sub-queries in the FROM clause, with renaming
operations?

SELECT * FROM table1, (SELECT * FROM (SELECT * FROM ...) AS renaming) AS
renaming2 OUTER JOIN (...) ...

And who do you expect to do the parsing of your SQL?  On average, the DBD
driver shouldn't have to, and the average database server won't tell you,
so...  Also, don't forget that any of the names could be a reference to an
arbitrarily complex view -- what should be returned then?

I'm sorry, but I don't think you are going to get the information, unless
some DB server is willing to give that information to the driver.

I've seen your response to my other post:
I will have to break apart the SQL statement with a regex and store it
that way.
Question: how do you know that it is query using a single table - as your
original question posited?


I won't lambast this any further.  Suffice to say, what you are asking for
is incredibly non-trivial in the general case, and the general case has to
work as well as the trivial.



> ----- Forwarded Message ----
> From: Alexander Foken <alexander@foken.de>
> To: Lamb Joseph <joseph_lamb@yahoo.com>
> Cc: dbi-users@perl.org
> Sent: Wednesday, May 7, 2008 11:18:53 AM
> Subject: Re: How to Retrieve Table Name from Statement Handle
>
> Hmmm, and what do you think $sth->{TABLENAME} should contain after
> executing the following SQL?
>
> SELECT t1.foo,t2.bar FROM narf t1, zord t2 WHERE t1.ikes=t2.blurb
>
> Alexander
>
> On 07.05.2008 19:51, Lamb Joseph wrote: 
> data. Then this tool will turn the data into insert statements. 
> statement handle? 
> columns\n"; 
>  ________________________________________
__________________________________
__________ 
> http://mobile.yahoo.com/;_ylt=Ahu06...tDypao8Wcj9tAcJ 
>
>
> --
> Alexander Foken
> mailto:alexander@foken.de  http://www.foken.de/alexander/
>
>
>
>   ________________________________________
_________________________________
___________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile.  Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06...tDypao8Wcj9tAcJ
>



--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

You may be able to use the explain plan functionality in Oracle to
obtain the tables participating in a SELECT statement.

Steve

On Wed, 2008-05-07 at 12:48 -0700, Jonathan Leffler wrote:
> On Wed, May 7, 2008 at 12:37 PM, Lamb Joseph <joseph_lamb@yahoo.com> wrote
:
> 
>
>
> So, what do you expect from a more complex statement, such as one which
> includes multiply nested sub-queries in the FROM clause, with renaming
> operations?
>
> SELECT * FROM table1, (SELECT * FROM (SELECT * FROM ...) AS renaming) AS
> renaming2 OUTER JOIN (...) ...
>
> And who do you expect to do the parsing of your SQL?  On average, the DBD
> driver shouldn't have to, and the average database server won't tell you,
> so...  Also, don't forget that any of the names could be a reference to an
> arbitrarily complex view -- what should be returned then?
>
> I'm sorry, but I don't think you are going to get the information, unless
> some DB server is willing to give that information to the driver.
>
> I've seen your response to my other post:
>     I will have to break apart the SQL statement with a regex and store it
> that way.
> Question: how do you know that it is query using a single table - as your
> original question posited?
>
>
> I won't lambast this any further.  Suffice to say, what you are asking for
> is incredibly non-trivial in the general case, and the general case has to
> work as well as the trivial.
>
>
> 
>
>
>



 ****************************************
**************************
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.

If you receive this email in error please immediately notify the
sender and delete the email.
 ****************************************
**************************