PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY

New to this mailing list - sorry if this has already been covered...


COLUMN_NAME

When doing a multi-table query, it's useful to be able to differentiate the
table that the resulting column comes from - particularly if there are name
conflicts.

For instance: "SELECT table1.ID, table2.ID" will result in 2 columns named
"ID".

Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
instead of "ID" and "ID", but it's a bit late to change its behavior now...

So I propose a couple of options:

1. Add a TABLE statement handle attribute that returns an array of table
names for each resulting column.  This is equivalent to MySQL's mysql_table
attribute.

or

2. Add a COLUMN_NAME attribute that returns an array of column names
formatted in the standard SQL format: "table.column".  This would eventually
obsolete the NAME attribute.

My preference is the latter option.

For DBs that do not provide native support, this feature can be supported by
parsing the prepare statement.


KEY

In order to retrieve/update/delete a record resulting from a query, a column
needs to be uniquely identified within a table - however, there is no
portable attribute for determining which columns are unique.

Again, I propose a couple of options:

1. Add a UNIQUE attribute that returns an array of boolean flags indicating
whether the column is known to be unique or not.  This is similar to MySQL's
mysql_is_pri_key attribute.

or

2. Add a KEY statement handle attribute that would return an array of key
types for each resulting column.  "0" would indicate unknown key status, "1"
would indicate that the column was known to be unique and "-1" would
indicate that it was known to be a primary key.

While #1 might be "cleaner", #2 is more informative and extensible.  For my
purposes, I could live with either.

Platforms without native support would report 0.


If I can be of assistance in spec'g, promoting or implementing these
enhancements, please let me know.  Likewise, if I'm covering old ground,
please give me a nudge.

Thanks - Bob

0
bfree (4)
2/21/2001 8:39:09 AM
perl.dbi.dev 1960 articles. 0 followers. Follow

16 Replies
491 Views

Similar Articles

[PageSpeed] 10

Why not just specify it when selecting the columns:

SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...

--
   Simon Oliver

Robert M. Free wrote:

> New to this mailing list - sorry if this has already been covered...
> 
> 
> COLUMN_NAME
> 
> When doing a multi-table query, it's useful to be able to differentiate the
> table that the resulting column comes from - particularly if there are name
> conflicts.
> 
> For instance: "SELECT table1.ID, table2.ID" will result in 2 columns named
> "ID".
> 
> Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
> instead of "ID" and "ID", but it's a bit late to change its behavior now...
> 
> So I propose a couple of options:
> 
> 1. Add a TABLE statement handle attribute that returns an array of table
> names for each resulting column.  This is equivalent to MySQL's mysql_table
> attribute.
> 
> or
> 
> 2. Add a COLUMN_NAME attribute that returns an array of column names
> formatted in the standard SQL format: "table.column".  This would eventually
> obsolete the NAME attribute.
> 
> My preference is the latter option.
> 
> For DBs that do not provide native support, this feature can be supported by
> parsing the prepare statement.
> 
> 
> KEY
> 
> In order to retrieve/update/delete a record resulting from a query, a column
> needs to be uniquely identified within a table - however, there is no
> portable attribute for determining which columns are unique.
> 
> Again, I propose a couple of options:
> 
> 1. Add a UNIQUE attribute that returns an array of boolean flags indicating
> whether the column is known to be unique or not.  This is similar to MySQL's
> mysql_is_pri_key attribute.
> 
> or
> 
> 2. Add a KEY statement handle attribute that would return an array of key
> types for each resulting column.  "0" would indicate unknown key status, "1"
> would indicate that the column was known to be unique and "-1" would
> indicate that it was known to be a primary key.
> 
> While #1 might be "cleaner", #2 is more informative and extensible.  For my
> purposes, I could live with either.
> 
> Platforms without native support would report 0.
> 
> 
> If I can be of assistance in spec'g, promoting or implementing these
> enhancements, please let me know.  Likewise, if I'm covering old ground,
> please give me a nudge.
> 
> Thanks - Bob

0
simon
2/21/2001 11:45:14 AM
Hi Simon - good suggestion!

However, "AS" doesn't work when the select is "*" or "table.*".


I've written an abstracted class that supports arbitrary queries - and it
works fine via DBD::mysql, using driver specific attributes... adding these
two attributes to DBI would allow my class to be completely
driver-independent.

In any case, DBI knows (or can find out more efficiently than the developer)
what table each column came from - and the resulting info is useful,
portable, simplifies the query, and is in line with the other statement
handle attributes.

Again, thanks for the reply and suggestion! - Bob


----- Original Message -----
From: "Simon Oliver" <simon.oliver@umist.ac.uk>
To: "Robert M. Free" <bfree@graphcomp.com>
Cc: <dbi-dev@perl.org>
Sent: Wednesday, February 21, 2001 3:45 AM
Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY


> Why not just specify it when selecting the columns:
>
> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
>
> --
>    Simon Oliver
>
> Robert M. Free wrote:
>
> > New to this mailing list - sorry if this has already been covered...
> >
> >
> > COLUMN_NAME
> >
> > When doing a multi-table query, it's useful to be able to differentiate
the
> > table that the resulting column comes from - particularly if there are
name
> > conflicts.
> >
> > For instance: "SELECT table1.ID, table2.ID" will result in 2 columns
named
> > "ID".
> >
> > Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
> > instead of "ID" and "ID", but it's a bit late to change its behavior
now...
> >
> > So I propose a couple of options:
> >
> > 1. Add a TABLE statement handle attribute that returns an array of table
> > names for each resulting column.  This is equivalent to MySQL's
mysql_table
> > attribute.
> >
> > or
> >
> > 2. Add a COLUMN_NAME attribute that returns an array of column names
> > formatted in the standard SQL format: "table.column".  This would
eventually
> > obsolete the NAME attribute.
> >
> > My preference is the latter option.
> >
> > For DBs that do not provide native support, this feature can be
supported by
> > parsing the prepare statement.
> >
> >
> > KEY
> >
> > In order to retrieve/update/delete a record resulting from a query, a
column
> > needs to be uniquely identified within a table - however, there is no
> > portable attribute for determining which columns are unique.
> >
> > Again, I propose a couple of options:
> >
> > 1. Add a UNIQUE attribute that returns an array of boolean flags
indicating
> > whether the column is known to be unique or not.  This is similar to
MySQL's
> > mysql_is_pri_key attribute.
> >
> > or
> >
> > 2. Add a KEY statement handle attribute that would return an array of
key
> > types for each resulting column.  "0" would indicate unknown key status,
"1"
> > would indicate that the column was known to be unique and "-1" would
> > indicate that it was known to be a primary key.
> >
> > While #1 might be "cleaner", #2 is more informative and extensible.  For
my
> > purposes, I could live with either.
> >
> > Platforms without native support would report 0.
> >
> >
> > If I can be of assistance in spec'g, promoting or implementing these
> > enhancements, please let me know.  Likewise, if I'm covering old ground,
> > please give me a nudge.
> >
> > Thanks - Bob
>

0
bfree
2/21/2001 4:32:34 PM
Robert M. Free wrote:

> Hi Simon - good suggestion!
> 
> However, "AS" doesn't work when the select is "*" or "table.*".
> 
But if you did "SELECT * FROM table1, table2" how would the DBI driver 
work out which column came from which table?

I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT * 
FROM table2 WHERE 1=0" to get the two sets of column names and then 
guess that the first group of columns of the original query come from 
table 1 and the second group of columns come from table 2.

But things could get tricky in the parsing - even a simple join would 
require a sophisticated SQL parser:
  "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE 
table1.id = table2.id"
  "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *= table2.id"

And this doesn't involve subqueries!

Or the derived columns, say where a column is the product of 2 columns 
from two seperate tables - how would you classify it?

And what about views or stored proceedures?  How would column names be 
handled?  Would DBI be expected to determine the composition of a view 
(query the schema)?

I think its a nice idea but could quickly run into trouble!

For a SQL parser you could look at SQL::Statement.

--
   Simon Oliver

> 
> I've written an abstracted class that supports arbitrary queries - and it
> works fine via DBD::mysql, using driver specific attributes... adding these
> two attributes to DBI would allow my class to be completely
> driver-independent.
> 
> In any case, DBI knows (or can find out more efficiently than the developer)
> what table each column came from - and the resulting info is useful,
> portable, simplifies the query, and is in line with the other statement
> handle attributes.
> 
> Again, thanks for the reply and suggestion! - Bob
> 
> 
> ----- Original Message -----
> From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> To: "Robert M. Free" <bfree@graphcomp.com>
> Cc: <dbi-dev@perl.org>
> Sent: Wednesday, February 21, 2001 3:45 AM
> Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY
> 
> 
> 
>> Why not just specify it when selecting the columns:
>> 
>> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
>> 
>> --
>>    Simon Oliver
>> 
>> Robert M. Free wrote:
>> 
>> 
>>> New to this mailing list - sorry if this has already been covered...
>>> 
>>> 
>>> COLUMN_NAME
>>> 
>>> When doing a multi-table query, it's useful to be able to differentiate
>> 
> the
> 
>>> table that the resulting column comes from - particularly if there are
>> 
> name
> 
>>> conflicts.
>>> 
>>> For instance: "SELECT table1.ID, table2.ID" will result in 2 columns
>> 
> named
> 
>>> "ID".
>>> 
>>> Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
>>> instead of "ID" and "ID", but it's a bit late to change its behavior
>> 
> now...
> 
>>> So I propose a couple of options:
>>> 
>>> 1. Add a TABLE statement handle attribute that returns an array of table
>>> names for each resulting column.  This is equivalent to MySQL's
>> 
> mysql_table
> 
>>> attribute.
>>> 
>>> or
>>> 
>>> 2. Add a COLUMN_NAME attribute that returns an array of column names
>>> formatted in the standard SQL format: "table.column".  This would
>> 
> eventually
> 
>>> obsolete the NAME attribute.
>>> 
>>> My preference is the latter option.
>>> 
>>> For DBs that do not provide native support, this feature can be
>> 
> supported by
> 
>>> parsing the prepare statement.
>>> 
>>> 
>>> KEY
>>> 
>>> In order to retrieve/update/delete a record resulting from a query, a
>> 
> column
> 
>>> needs to be uniquely identified within a table - however, there is no
>>> portable attribute for determining which columns are unique.
>>> 
>>> Again, I propose a couple of options:
>>> 
>>> 1. Add a UNIQUE attribute that returns an array of boolean flags
>> 
> indicating
> 
>>> whether the column is known to be unique or not.  This is similar to
>> 
> MySQL's
> 
>>> mysql_is_pri_key attribute.
>>> 
>>> or
>>> 
>>> 2. Add a KEY statement handle attribute that would return an array of
>> 
> key
> 
>>> types for each resulting column.  "0" would indicate unknown key status,
>> 
> "1"
> 
>>> would indicate that the column was known to be unique and "-1" would
>>> indicate that it was known to be a primary key.
>>> 
>>> While #1 might be "cleaner", #2 is more informative and extensible.  For
>> 
> my
> 
>>> purposes, I could live with either.
>>> 
>>> Platforms without native support would report 0.
>>> 
>>> 
>>> If I can be of assistance in spec'g, promoting or implementing these
>>> enhancements, please let me know.  Likewise, if I'm covering old ground,
>>> please give me a nudge.
>>> 
>>> Thanks - Bob
>> 

0
simon
2/21/2001 4:58:54 PM
Simon Oliver writes:
 > Robert M. Free wrote:
 > 
 > > Hi Simon - good suggestion!
 > > 
 > > However, "AS" doesn't work when the select is "*" or "table.*".
 > > 
 > But if you did "SELECT * FROM table1, table2" how would the DBI driver 
 > work out which column came from which table?

Or simply:

select convert(char, datefield) from table

or

select case 
       when a.f = 1 then 1
       when b.f = 2 then 3
       else 4
  from tablea a, tableb b
 where ...

(silly example, but you get the idea...)

This can get *very* hairy...

(btw - in both cases Sybase does not report a column name)

Michael
-- 
Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
http://www.mbay.net/~mpeppler - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com
0
mpeppler
2/21/2001 5:09:47 PM
Great responses Simon and Michael!

Agreed - the parser approach is gnarly and would add a lot of overhead.
Bummer about the lack of native support!

Much thanks on the sanity-check for this proposal - Bob


----- Original Message -----
From: "Simon Oliver" <simon.oliver@umist.ac.uk>
To: "Robert M. Free" <bfree@graphcomp.com>
Cc: <dbi-dev@perl.org>
Sent: Wednesday, February 21, 2001 8:58 AM
Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY


> Robert M. Free wrote:
>
> > Hi Simon - good suggestion!
> >
> > However, "AS" doesn't work when the select is "*" or "table.*".
> >
> But if you did "SELECT * FROM table1, table2" how would the DBI driver
> work out which column came from which table?
>
> I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT *
> FROM table2 WHERE 1=0" to get the two sets of column names and then
> guess that the first group of columns of the original query come from
> table 1 and the second group of columns come from table 2.
>
> But things could get tricky in the parsing - even a simple join would
> require a sophisticated SQL parser:
>   "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE
> table1.id = table2.id"
>   "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *=
table2.id"
>
> And this doesn't involve subqueries!
>
> Or the derived columns, say where a column is the product of 2 columns
> from two seperate tables - how would you classify it?
>
> And what about views or stored proceedures?  How would column names be
> handled?  Would DBI be expected to determine the composition of a view
> (query the schema)?
>
> I think its a nice idea but could quickly run into trouble!
>
> For a SQL parser you could look at SQL::Statement.
>
> --
>    Simon Oliver
>
> >
> > I've written an abstracted class that supports arbitrary queries - and
it
> > works fine via DBD::mysql, using driver specific attributes... adding
these
> > two attributes to DBI would allow my class to be completely
> > driver-independent.
> >
> > In any case, DBI knows (or can find out more efficiently than the
developer)
> > what table each column came from - and the resulting info is useful,
> > portable, simplifies the query, and is in line with the other statement
> > handle attributes.
> >
> > Again, thanks for the reply and suggestion! - Bob
> >
> >
> > ----- Original Message -----
> > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> > To: "Robert M. Free" <bfree@graphcomp.com>
> > Cc: <dbi-dev@perl.org>
> > Sent: Wednesday, February 21, 2001 3:45 AM
> > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and
KEY
> >
> >
> >
> >> Why not just specify it when selecting the columns:
> >>
> >> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
> >>
> >> --
> >>    Simon Oliver
> >>
> >> Robert M. Free wrote:
> >>
> >>
> >>> New to this mailing list - sorry if this has already been covered...
> >>>
> >>>
> >>> COLUMN_NAME
> >>>
> >>> When doing a multi-table query, it's useful to be able to
differentiate
> >>
> > the
> >
> >>> table that the resulting column comes from - particularly if there are
> >>
> > name
> >
> >>> conflicts.
> >>>
> >>> For instance: "SELECT table1.ID, table2.ID" will result in 2 columns
> >>
> > named
> >
> >>> "ID".
> >>>
> >>> Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
> >>> instead of "ID" and "ID", but it's a bit late to change its behavior
> >>
> > now...
> >
> >>> So I propose a couple of options:
> >>>
> >>> 1. Add a TABLE statement handle attribute that returns an array of
table
> >>> names for each resulting column.  This is equivalent to MySQL's
> >>
> > mysql_table
> >
> >>> attribute.
> >>>
> >>> or
> >>>
> >>> 2. Add a COLUMN_NAME attribute that returns an array of column names
> >>> formatted in the standard SQL format: "table.column".  This would
> >>
> > eventually
> >
> >>> obsolete the NAME attribute.
> >>>
> >>> My preference is the latter option.
> >>>
> >>> For DBs that do not provide native support, this feature can be
> >>
> > supported by
> >
> >>> parsing the prepare statement.
> >>>
> >>>
> >>> KEY
> >>>
> >>> In order to retrieve/update/delete a record resulting from a query, a
> >>
> > column
> >
> >>> needs to be uniquely identified within a table - however, there is no
> >>> portable attribute for determining which columns are unique.
> >>>
> >>> Again, I propose a couple of options:
> >>>
> >>> 1. Add a UNIQUE attribute that returns an array of boolean flags
> >>
> > indicating
> >
> >>> whether the column is known to be unique or not.  This is similar to
> >>
> > MySQL's
> >
> >>> mysql_is_pri_key attribute.
> >>>
> >>> or
> >>>
> >>> 2. Add a KEY statement handle attribute that would return an array of
> >>
> > key
> >
> >>> types for each resulting column.  "0" would indicate unknown key
status,
> >>
> > "1"
> >
> >>> would indicate that the column was known to be unique and "-1" would
> >>> indicate that it was known to be a primary key.
> >>>
> >>> While #1 might be "cleaner", #2 is more informative and extensible.
For
> >>
> > my
> >
> >>> purposes, I could live with either.
> >>>
> >>> Platforms without native support would report 0.
> >>>
> >>>
> >>> If I can be of assistance in spec'g, promoting or implementing these
> >>> enhancements, please let me know.  Likewise, if I'm covering old
ground,
> >>> please give me a nudge.
> >>>
> >>> Thanks - Bob
> >>
>

0
bfree
2/21/2001 6:44:51 PM
"Robert M. Free" wrote:
> 
> Great responses Simon and Michael!
> 
> Agreed - the parser approach is gnarly and would add a lot of overhead.
> Bummer about the lack of native support!
> 
> Much thanks on the sanity-check for this proposal - Bob

IMHO, select * in production code is EVIL. Why? 

(Using Sybase semantics)

Well in a language like 'C' it's evil because you have to then look
further down the page to the bind() statements to get an idea of what
you're selecting. Hopefully the 'C' var names reflect the actual columns
used. Even worse, suppose you are selecting * from foo, bar and someone
adds a column to foo? All the binds for the bar columns are now HOSED.
And the only error you'll get, if you're lucky, is a core dump. If
you're not that lucky, you've silently shifted all your data by one
variable. Try debugging THAT at 2am!

In Perl, the bind() statement problem manifests itself differently: if
you use a fetchrow_hashref, there is NO local code that tells you all
the columns you've retrieved; you'd have to examin the hash keys and you
can't do that unless you run the code. And if you do a
fetchrow_array/arrayref, then you can have the "shifted vars" problem. 

Net-net, five minutes spent typing at 2pm will save you sleep at 2am,
and maybe even your job.

Spend the five minutes. :-)
> From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> To: "Robert M. Free" <bfree@graphcomp.com>
> Cc: <dbi-dev@perl.org>
> Sent: Wednesday, February 21, 2001 8:58 AM
> Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY
> 
> > Robert M. Free wrote:
> >
> > > Hi Simon - good suggestion!
> > >
> > > However, "AS" doesn't work when the select is "*" or "table.*".
> > >
> > But if you did "SELECT * FROM table1, table2" how would the DBI driver
> > work out which column came from which table?
> >
> > I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT *
> > FROM table2 WHERE 1=0" to get the two sets of column names and then
> > guess that the first group of columns of the original query come from
> > table 1 and the second group of columns come from table 2.
> >
> > But things could get tricky in the parsing - even a simple join would
> > require a sophisticated SQL parser:
> >   "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE
> > table1.id = table2.id"
> >   "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *=
> table2.id"
> >
> > And this doesn't involve subqueries!
> >
> > Or the derived columns, say where a column is the product of 2 columns
> > from two seperate tables - how would you classify it?
> >
> > And what about views or stored proceedures?  How would column names be
> > handled?  Would DBI be expected to determine the composition of a view
> > (query the schema)?
> >
> > I think its a nice idea but could quickly run into trouble!
> >
> > For a SQL parser you could look at SQL::Statement.
> >
> > --
> >    Simon Oliver
> >
> > >
> > > I've written an abstracted class that supports arbitrary queries - and
> it
> > > works fine via DBD::mysql, using driver specific attributes... adding
> these
> > > two attributes to DBI would allow my class to be completely
> > > driver-independent.
> > >
> > > In any case, DBI knows (or can find out more efficiently than the
> developer)
> > > what table each column came from - and the resulting info is useful,
> > > portable, simplifies the query, and is in line with the other statement
> > > handle attributes.
> > >
> > > Again, thanks for the reply and suggestion! - Bob
> > >
> > >
> > > ----- Original Message -----
> > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> > > To: "Robert M. Free" <bfree@graphcomp.com>
> > > Cc: <dbi-dev@perl.org>
> > > Sent: Wednesday, February 21, 2001 3:45 AM
> > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and
> KEY
> > >
> > >
> > >
> > >> Why not just specify it when selecting the columns:
> > >>
> > >> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
> > >>
> > >> --
> > >>    Simon Oliver
> > >>
> > >> Robert M. Free wrote:
> > >>
> > >>
> > >>> New to this mailing list - sorry if this has already been covered...
> > >>>
> > >>>
> > >>> COLUMN_NAME
> > >>>
> > >>> When doing a multi-table query, it's useful to be able to
> differentiate
> > >>
> > > the
> > >
> > >>> table that the resulting column comes from - particularly if there are
> > >>
> > > name
> > >
> > >>> conflicts.
> > >>>
> > >>> For instance: "SELECT table1.ID, table2.ID" will result in 2 columns
> > >>
> > > named
> > >
> > >>> "ID".
> > >>>
> > >>> Ideally, the NAME attribute would report "table1.ID" and "table2.ID",
> > >>> instead of "ID" and "ID", but it's a bit late to change its behavior
> > >>
> > > now...
> > >
> > >>> So I propose a couple of options:
> > >>>
> > >>> 1. Add a TABLE statement handle attribute that returns an array of
> table
> > >>> names for each resulting column.  This is equivalent to MySQL's
> > >>
> > > mysql_table
> > >
> > >>> attribute.
> > >>>
> > >>> or
> > >>>
> > >>> 2. Add a COLUMN_NAME attribute that returns an array of column names
> > >>> formatted in the standard SQL format: "table.column".  This would
> > >>
> > > eventually
> > >
> > >>> obsolete the NAME attribute.
> > >>>
> > >>> My preference is the latter option.
> > >>>
> > >>> For DBs that do not provide native support, this feature can be
> > >>
> > > supported by
> > >
> > >>> parsing the prepare statement.
> > >>>
> > >>>
> > >>> KEY
> > >>>
> > >>> In order to retrieve/update/delete a record resulting from a query, a
> > >>
> > > column
> > >
> > >>> needs to be uniquely identified within a table - however, there is no
> > >>> portable attribute for determining which columns are unique.
> > >>>
> > >>> Again, I propose a couple of options:
> > >>>
> > >>> 1. Add a UNIQUE attribute that returns an array of boolean flags
> > >>
> > > indicating
> > >
> > >>> whether the column is known to be unique or not.  This is similar to
> > >>
> > > MySQL's
> > >
> > >>> mysql_is_pri_key attribute.
> > >>>
> > >>> or
> > >>>
> > >>> 2. Add a KEY statement handle attribute that would return an array of
> > >>
> > > key
> > >
> > >>> types for each resulting column.  "0" would indicate unknown key
> status,
> > >>
> > > "1"
> > >
> > >>> would indicate that the column was known to be unique and "-1" would
> > >>> indicate that it was known to be a primary key.
> > >>>
> > >>> While #1 might be "cleaner", #2 is more informative and extensible.
> For
> > >>
> > > my
> > >
> > >>> purposes, I could live with either.
> > >>>
> > >>> Platforms without native support would report 0.
> > >>>
> > >>>
> > >>> If I can be of assistance in spec'g, promoting or implementing these
> > >>> enhancements, please let me know.  Likewise, if I'm covering old
> ground,
> > >>> please give me a nudge.
> > >>>
> > >>> Thanks - Bob
> > >>
> >


-- 
Matthew O. Persico
    
"If you were supposed to understand it,
we wouldn't call it code." - FedEx

Shop online without a credit card
http://www.rocketcash.com
RocketCash, a NetZero subsidiary
0
persicom
2/22/2001 2:43:47 AM
I totally agree with Matthew.  I never let programmers use select * from a
table.  From a query/view, however, I let it slide. Views are great and
should be used anyway.

----- Original Message -----
From: "Matthew O. Persico" <persicom@acedsl.com>
To: <dbi-dev@perl.org>
Sent: Wednesday, February 21, 2001 9:43 PM
Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY


> "Robert M. Free" wrote:
> >
> > Great responses Simon and Michael!
> >
> > Agreed - the parser approach is gnarly and would add a lot of overhead.
> > Bummer about the lack of native support!
> >
> > Much thanks on the sanity-check for this proposal - Bob
>
> IMHO, select * in production code is EVIL. Why?
>
> (Using Sybase semantics)
>
> Well in a language like 'C' it's evil because you have to then look
> further down the page to the bind() statements to get an idea of what
> you're selecting. Hopefully the 'C' var names reflect the actual columns
> used. Even worse, suppose you are selecting * from foo, bar and someone
> adds a column to foo? All the binds for the bar columns are now HOSED.
> And the only error you'll get, if you're lucky, is a core dump. If
> you're not that lucky, you've silently shifted all your data by one
> variable. Try debugging THAT at 2am!
>
> In Perl, the bind() statement problem manifests itself differently: if
> you use a fetchrow_hashref, there is NO local code that tells you all
> the columns you've retrieved; you'd have to examin the hash keys and you
> can't do that unless you run the code. And if you do a
> fetchrow_array/arrayref, then you can have the "shifted vars" problem.
>
> Net-net, five minutes spent typing at 2pm will save you sleep at 2am,
> and maybe even your job.
>
> Spend the five minutes. :-)
> > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> > To: "Robert M. Free" <bfree@graphcomp.com>
> > Cc: <dbi-dev@perl.org>
> > Sent: Wednesday, February 21, 2001 8:58 AM
> > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and
KEY
> >
> > > Robert M. Free wrote:
> > >
> > > > Hi Simon - good suggestion!
> > > >
> > > > However, "AS" doesn't work when the select is "*" or "table.*".
> > > >
> > > But if you did "SELECT * FROM table1, table2" how would the DBI driver
> > > work out which column came from which table?
> > >
> > > I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT
*
> > > FROM table2 WHERE 1=0" to get the two sets of column names and then
> > > guess that the first group of columns of the original query come from
> > > table 1 and the second group of columns come from table 2.
> > >
> > > But things could get tricky in the parsing - even a simple join would
> > > require a sophisticated SQL parser:
> > >   "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE
> > > table1.id = table2.id"
> > >   "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *=
> > table2.id"
> > >
> > > And this doesn't involve subqueries!
> > >
> > > Or the derived columns, say where a column is the product of 2 columns
> > > from two seperate tables - how would you classify it?
> > >
> > > And what about views or stored proceedures?  How would column names be
> > > handled?  Would DBI be expected to determine the composition of a view
> > > (query the schema)?
> > >
> > > I think its a nice idea but could quickly run into trouble!
> > >
> > > For a SQL parser you could look at SQL::Statement.
> > >
> > > --
> > >    Simon Oliver
> > >
> > > >
> > > > I've written an abstracted class that supports arbitrary queries -
and
> > it
> > > > works fine via DBD::mysql, using driver specific attributes...
adding
> > these
> > > > two attributes to DBI would allow my class to be completely
> > > > driver-independent.
> > > >
> > > > In any case, DBI knows (or can find out more efficiently than the
> > developer)
> > > > what table each column came from - and the resulting info is useful,
> > > > portable, simplifies the query, and is in line with the other
statement
> > > > handle attributes.
> > > >
> > > > Again, thanks for the reply and suggestion! - Bob
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
> > > > To: "Robert M. Free" <bfree@graphcomp.com>
> > > > Cc: <dbi-dev@perl.org>
> > > > Sent: Wednesday, February 21, 2001 3:45 AM
> > > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME
and
> > KEY
> > > >
> > > >
> > > >
> > > >> Why not just specify it when selecting the columns:
> > > >>
> > > >> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
> > > >>
> > > >> --
> > > >>    Simon Oliver
> > > >>
> > > >> Robert M. Free wrote:
> > > >>
> > > >>
> > > >>> New to this mailing list - sorry if this has already been
covered...
> > > >>>
> > > >>>
> > > >>> COLUMN_NAME
> > > >>>
> > > >>> When doing a multi-table query, it's useful to be able to
> > differentiate
> > > >>
> > > > the
> > > >
> > > >>> table that the resulting column comes from - particularly if there
are
> > > >>
> > > > name
> > > >
> > > >>> conflicts.
> > > >>>
> > > >>> For instance: "SELECT table1.ID, table2.ID" will result in 2
columns
> > > >>
> > > > named
> > > >
> > > >>> "ID".
> > > >>>
> > > >>> Ideally, the NAME attribute would report "table1.ID" and
"table2.ID",
> > > >>> instead of "ID" and "ID", but it's a bit late to change its
behavior
> > > >>
> > > > now...
> > > >
> > > >>> So I propose a couple of options:
> > > >>>
> > > >>> 1. Add a TABLE statement handle attribute that returns an array of
> > table
> > > >>> names for each resulting column.  This is equivalent to MySQL's
> > > >>
> > > > mysql_table
> > > >
> > > >>> attribute.
> > > >>>
> > > >>> or
> > > >>>
> > > >>> 2. Add a COLUMN_NAME attribute that returns an array of column
names
> > > >>> formatted in the standard SQL format: "table.column".  This would
> > > >>
> > > > eventually
> > > >
> > > >>> obsolete the NAME attribute.
> > > >>>
> > > >>> My preference is the latter option.
> > > >>>
> > > >>> For DBs that do not provide native support, this feature can be
> > > >>
> > > > supported by
> > > >
> > > >>> parsing the prepare statement.
> > > >>>
> > > >>>
> > > >>> KEY
> > > >>>
> > > >>> In order to retrieve/update/delete a record resulting from a
query, a
> > > >>
> > > > column
> > > >
> > > >>> needs to be uniquely identified within a table - however, there is
no
> > > >>> portable attribute for determining which columns are unique.
> > > >>>
> > > >>> Again, I propose a couple of options:
> > > >>>
> > > >>> 1. Add a UNIQUE attribute that returns an array of boolean flags
> > > >>
> > > > indicating
> > > >
> > > >>> whether the column is known to be unique or not.  This is similar
to
> > > >>
> > > > MySQL's
> > > >
> > > >>> mysql_is_pri_key attribute.
> > > >>>
> > > >>> or
> > > >>>
> > > >>> 2. Add a KEY statement handle attribute that would return an array
of
> > > >>
> > > > key
> > > >
> > > >>> types for each resulting column.  "0" would indicate unknown key
> > status,
> > > >>
> > > > "1"
> > > >
> > > >>> would indicate that the column was known to be unique and "-1"
would
> > > >>> indicate that it was known to be a primary key.
> > > >>>
> > > >>> While #1 might be "cleaner", #2 is more informative and
extensible.
> > For
> > > >>
> > > > my
> > > >
> > > >>> purposes, I could live with either.
> > > >>>
> > > >>> Platforms without native support would report 0.
> > > >>>
> > > >>>
> > > >>> If I can be of assistance in spec'g, promoting or implementing
these
> > > >>> enhancements, please let me know.  Likewise, if I'm covering old
> > ground,
> > > >>> please give me a nudge.
> > > >>>
> > > >>> Thanks - Bob
> > > >>
> > >
>
>
> --
> Matthew O. Persico
>
> "If you were supposed to understand it,
> we wouldn't call it code." - FedEx
>
> Shop online without a credit card
> http://www.rocketcash.com
> RocketCash, a NetZero subsidiary

0
rhoobler
2/22/2001 2:56:27 AM
On Wed, 21 Feb 2001 21:43:47 -0500, "Matthew O. Persico" <persicom@acedsl.com> wrote:
> Well in a language like 'C' it's evil because you have to then look
> further down the page to the bind() statements to get an idea of what
> you're selecting.

my %fld;
my $sth = $dbh->prepare ("select * from foo");
$sth->execute;
$sth->bind_columns (\@fld{@{$sth->{NAME}}});
while ($sth->fetch) {
    $fld{key} < 0 and next;
    ...

I've just done some serious testing of the four different fetching methods
(fetchrow_hashref, fetchrow_array, fetchrow_arrayref and fetch with
bind_columns). I will post the results early next month. (Not sooner, cause I
don't want to spoil my talk at Amsterdam.pm). It's worth looking at.

-- 
H.Merijn Brand           Amsterdam Perl Mongers (http://www.amsterdam.pm.org/)
using perl-5.005.03, 5.6.0, 5.6.1, 5.7.1 & 623 on HP-UX 10.20 & 11.00, AIX 4.2
   AIX 4.3, WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.022 &/| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/

0
h
2/22/2001 9:19:40 AM
Roy Hoobler writes:
 > I totally agree with Matthew.  I never let programmers use select * from a
 > table.  From a query/view, however, I let it slide. Views are great and
 > should be used anyway.

I never let programmers use direct SELECT statements, be it from views
or tables...

All database access is done via stored procedures, where the SQL can
be controlled and tuned, and where the query plans get pre-compiled.

(yes I know this isn't always possible, but on a large web site for
example this should be a must when using a Sybase or MS-SQL back-end)

Michael

 > ----- Original Message -----
 > From: "Matthew O. Persico" <persicom@acedsl.com>
 > To: <dbi-dev@perl.org>
 > Sent: Wednesday, February 21, 2001 9:43 PM
 > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY
 > 
 > 
 > > "Robert M. Free" wrote:
 > > >
 > > > Great responses Simon and Michael!
 > > >
 > > > Agreed - the parser approach is gnarly and would add a lot of overhead.
 > > > Bummer about the lack of native support!
 > > >
 > > > Much thanks on the sanity-check for this proposal - Bob
 > >
 > > IMHO, select * in production code is EVIL. Why?
 > >
 > > (Using Sybase semantics)
 > >
 > > Well in a language like 'C' it's evil because you have to then look
 > > further down the page to the bind() statements to get an idea of what
 > > you're selecting. Hopefully the 'C' var names reflect the actual columns
 > > used. Even worse, suppose you are selecting * from foo, bar and someone
 > > adds a column to foo? All the binds for the bar columns are now HOSED.
 > > And the only error you'll get, if you're lucky, is a core dump. If
 > > you're not that lucky, you've silently shifted all your data by one
 > > variable. Try debugging THAT at 2am!
 > >
 > > In Perl, the bind() statement problem manifests itself differently: if
 > > you use a fetchrow_hashref, there is NO local code that tells you all
 > > the columns you've retrieved; you'd have to examin the hash keys and you
 > > can't do that unless you run the code. And if you do a
 > > fetchrow_array/arrayref, then you can have the "shifted vars" problem.
 > >
 > > Net-net, five minutes spent typing at 2pm will save you sleep at 2am,
 > > and maybe even your job.
 > >
 > > Spend the five minutes. :-)
 > > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
 > > > To: "Robert M. Free" <bfree@graphcomp.com>
 > > > Cc: <dbi-dev@perl.org>
 > > > Sent: Wednesday, February 21, 2001 8:58 AM
 > > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and
 > KEY
 > > >
 > > > > Robert M. Free wrote:
 > > > >
 > > > > > Hi Simon - good suggestion!
 > > > > >
 > > > > > However, "AS" doesn't work when the select is "*" or "table.*".
 > > > > >
 > > > > But if you did "SELECT * FROM table1, table2" how would the DBI driver
 > > > > work out which column came from which table?
 > > > >
 > > > > I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT
 > *
 > > > > FROM table2 WHERE 1=0" to get the two sets of column names and then
 > > > > guess that the first group of columns of the original query come from
 > > > > table 1 and the second group of columns come from table 2.
 > > > >
 > > > > But things could get tricky in the parsing - even a simple join would
 > > > > require a sophisticated SQL parser:
 > > > >   "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE
 > > > > table1.id = table2.id"
 > > > >   "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *=
 > > > table2.id"
 > > > >
 > > > > And this doesn't involve subqueries!
 > > > >
 > > > > Or the derived columns, say where a column is the product of 2 columns
 > > > > from two seperate tables - how would you classify it?
 > > > >
 > > > > And what about views or stored proceedures?  How would column names be
 > > > > handled?  Would DBI be expected to determine the composition of a view
 > > > > (query the schema)?
 > > > >
 > > > > I think its a nice idea but could quickly run into trouble!
 > > > >
 > > > > For a SQL parser you could look at SQL::Statement.
 > > > >
 > > > > --
 > > > >    Simon Oliver
 > > > >
 > > > > >
 > > > > > I've written an abstracted class that supports arbitrary queries -
 > and
 > > > it
 > > > > > works fine via DBD::mysql, using driver specific attributes...
 > adding
 > > > these
 > > > > > two attributes to DBI would allow my class to be completely
 > > > > > driver-independent.
 > > > > >
 > > > > > In any case, DBI knows (or can find out more efficiently than the
 > > > developer)
 > > > > > what table each column came from - and the resulting info is useful,
 > > > > > portable, simplifies the query, and is in line with the other
 > statement
 > > > > > handle attributes.
 > > > > >
 > > > > > Again, thanks for the reply and suggestion! - Bob
 > > > > >
 > > > > >
 > > > > > ----- Original Message -----
 > > > > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
 > > > > > To: "Robert M. Free" <bfree@graphcomp.com>
 > > > > > Cc: <dbi-dev@perl.org>
 > > > > > Sent: Wednesday, February 21, 2001 3:45 AM
 > > > > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME
 > and
 > > > KEY
 > > > > >
 > > > > >
 > > > > >
 > > > > >> Why not just specify it when selecting the columns:
 > > > > >>
 > > > > >> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
 > > > > >>
 > > > > >> --
 > > > > >>    Simon Oliver
 > > > > >>
 > > > > >> Robert M. Free wrote:
 > > > > >>
 > > > > >>
 > > > > >>> New to this mailing list - sorry if this has already been
 > covered...
 > > > > >>>
 > > > > >>>
 > > > > >>> COLUMN_NAME
 > > > > >>>
 > > > > >>> When doing a multi-table query, it's useful to be able to
 > > > differentiate
 > > > > >>
 > > > > > the
 > > > > >
 > > > > >>> table that the resulting column comes from - particularly if there
 > are
 > > > > >>
 > > > > > name
 > > > > >
 > > > > >>> conflicts.
 > > > > >>>
 > > > > >>> For instance: "SELECT table1.ID, table2.ID" will result in 2
 > columns
 > > > > >>
 > > > > > named
 > > > > >
 > > > > >>> "ID".
 > > > > >>>
 > > > > >>> Ideally, the NAME attribute would report "table1.ID" and
 > "table2.ID",
 > > > > >>> instead of "ID" and "ID", but it's a bit late to change its
 > behavior
 > > > > >>
 > > > > > now...
 > > > > >
 > > > > >>> So I propose a couple of options:
 > > > > >>>
 > > > > >>> 1. Add a TABLE statement handle attribute that returns an array of
 > > > table
 > > > > >>> names for each resulting column.  This is equivalent to MySQL's
 > > > > >>
 > > > > > mysql_table
 > > > > >
 > > > > >>> attribute.
 > > > > >>>
 > > > > >>> or
 > > > > >>>
 > > > > >>> 2. Add a COLUMN_NAME attribute that returns an array of column
 > names
 > > > > >>> formatted in the standard SQL format: "table.column".  This would
 > > > > >>
 > > > > > eventually
 > > > > >
 > > > > >>> obsolete the NAME attribute.
 > > > > >>>
 > > > > >>> My preference is the latter option.
 > > > > >>>
 > > > > >>> For DBs that do not provide native support, this feature can be
 > > > > >>
 > > > > > supported by
 > > > > >
 > > > > >>> parsing the prepare statement.
 > > > > >>>
 > > > > >>>
 > > > > >>> KEY
 > > > > >>>
 > > > > >>> In order to retrieve/update/delete a record resulting from a
 > query, a
 > > > > >>
 > > > > > column
 > > > > >
 > > > > >>> needs to be uniquely identified within a table - however, there is
 > no
 > > > > >>> portable attribute for determining which columns are unique.
 > > > > >>>
 > > > > >>> Again, I propose a couple of options:
 > > > > >>>
 > > > > >>> 1. Add a UNIQUE attribute that returns an array of boolean flags
 > > > > >>
 > > > > > indicating
 > > > > >
 > > > > >>> whether the column is known to be unique or not.  This is similar
 > to
 > > > > >>
 > > > > > MySQL's
 > > > > >
 > > > > >>> mysql_is_pri_key attribute.
 > > > > >>>
 > > > > >>> or
 > > > > >>>
 > > > > >>> 2. Add a KEY statement handle attribute that would return an array
 > of
 > > > > >>
 > > > > > key
 > > > > >
 > > > > >>> types for each resulting column.  "0" would indicate unknown key
 > > > status,
 > > > > >>
 > > > > > "1"
 > > > > >
 > > > > >>> would indicate that the column was known to be unique and "-1"
 > would
 > > > > >>> indicate that it was known to be a primary key.
 > > > > >>>
 > > > > >>> While #1 might be "cleaner", #2 is more informative and
 > extensible.
 > > > For
 > > > > >>
 > > > > > my
 > > > > >
 > > > > >>> purposes, I could live with either.
 > > > > >>>
 > > > > >>> Platforms without native support would report 0.
 > > > > >>>
 > > > > >>>
 > > > > >>> If I can be of assistance in spec'g, promoting or implementing
 > these
 > > > > >>> enhancements, please let me know.  Likewise, if I'm covering old
 > > > ground,
 > > > > >>> please give me a nudge.
 > > > > >>>
 > > > > >>> Thanks - Bob
 > > > > >>
 > > > >
 > >
 > >
 > > --
 > > Matthew O. Persico
 > >
 > > "If you were supposed to understand it,
 > > we wouldn't call it code." - FedEx
 > >
 > > Shop online without a credit card
 > > http://www.rocketcash.com
 > > RocketCash, a NetZero subsidiary
 > 

-- 
Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
http://www.mbay.net/~mpeppler - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com
0
mpeppler
2/22/2001 3:07:37 PM
On Thu, 22 Feb 2001, Michael Peppler wrote:

> Roy Hoobler writes:
>  > I totally agree with Matthew.  I never let programmers use select * from a
>  > table.  From a query/view, however, I let it slide. Views are great and
>  > should be used anyway.
> 
> I never let programmers use direct SELECT statements, be it from views
> or tables...
> 
> All database access is done via stored procedures, where the SQL can
> be controlled and tuned, and where the query plans get pre-compiled.
> 
> (yes I know this isn't always possible, but on a large web site for
> example this should be a must when using a Sybase or MS-SQL back-end)

This is surprising. Why is it a "must"? How do you deal with the lack of
separation of the code between multiple developers, the possible
bottleneck of the DBA, and my own experience that suggests DBA's aren't
such great programmers?


>  > ----- Original Message -----
>  > From: "Matthew O. Persico" <persicom@acedsl.com>
>  > To: <dbi-dev@perl.org>
>  > Sent: Wednesday, February 21, 2001 9:43 PM
>  > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY
>  > 
>  > 
>  > > "Robert M. Free" wrote:
>  > > >
>  > > > Great responses Simon and Michael!
>  > > >
>  > > > Agreed - the parser approach is gnarly and would add a lot of overhead.
>  > > > Bummer about the lack of native support!
>  > > >
>  > > > Much thanks on the sanity-check for this proposal - Bob
>  > >
>  > > IMHO, select * in production code is EVIL. Why?
>  > >
>  > > (Using Sybase semantics)
>  > >
>  > > Well in a language like 'C' it's evil because you have to then look
>  > > further down the page to the bind() statements to get an idea of what
>  > > you're selecting. Hopefully the 'C' var names reflect the actual columns
>  > > used. Even worse, suppose you are selecting * from foo, bar and someone
>  > > adds a column to foo? All the binds for the bar columns are now HOSED.
>  > > And the only error you'll get, if you're lucky, is a core dump. If
>  > > you're not that lucky, you've silently shifted all your data by one
>  > > variable. Try debugging THAT at 2am!
>  > >
>  > > In Perl, the bind() statement problem manifests itself differently: if
>  > > you use a fetchrow_hashref, there is NO local code that tells you all
>  > > the columns you've retrieved; you'd have to examin the hash keys and you
>  > > can't do that unless you run the code. And if you do a
>  > > fetchrow_array/arrayref, then you can have the "shifted vars" problem.
>  > >
>  > > Net-net, five minutes spent typing at 2pm will save you sleep at 2am,
>  > > and maybe even your job.
>  > >
>  > > Spend the five minutes. :-)
>  > > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
>  > > > To: "Robert M. Free" <bfree@graphcomp.com>
>  > > > Cc: <dbi-dev@perl.org>
>  > > > Sent: Wednesday, February 21, 2001 8:58 AM
>  > > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and
>  > KEY
>  > > >
>  > > > > Robert M. Free wrote:
>  > > > >
>  > > > > > Hi Simon - good suggestion!
>  > > > > >
>  > > > > > However, "AS" doesn't work when the select is "*" or "table.*".
>  > > > > >
>  > > > > But if you did "SELECT * FROM table1, table2" how would the DBI driver
>  > > > > work out which column came from which table?
>  > > > >
>  > > > > I suppose it could issue "SELECT * FROM table1 WHERE 1=0" and "SELECT
>  > *
>  > > > > FROM table2 WHERE 1=0" to get the two sets of column names and then
>  > > > > guess that the first group of columns of the original query come from
>  > > > > table 1 and the second group of columns come from table 2.
>  > > > >
>  > > > > But things could get tricky in the parsing - even a simple join would
>  > > > > require a sophisticated SQL parser:
>  > > > >   "SELECT table1.id, name FROM table1 LEFT OUTER JOIN table 2 WHERE
>  > > > > table1.id = table2.id"
>  > > > >   "SELECT table1.id, name FROM table1, table 2 WHERE table1.id *=
>  > > > table2.id"
>  > > > >
>  > > > > And this doesn't involve subqueries!
>  > > > >
>  > > > > Or the derived columns, say where a column is the product of 2 columns
>  > > > > from two seperate tables - how would you classify it?
>  > > > >
>  > > > > And what about views or stored proceedures?  How would column names be
>  > > > > handled?  Would DBI be expected to determine the composition of a view
>  > > > > (query the schema)?
>  > > > >
>  > > > > I think its a nice idea but could quickly run into trouble!
>  > > > >
>  > > > > For a SQL parser you could look at SQL::Statement.
>  > > > >
>  > > > > --
>  > > > >    Simon Oliver
>  > > > >
>  > > > > >
>  > > > > > I've written an abstracted class that supports arbitrary queries -
>  > and
>  > > > it
>  > > > > > works fine via DBD::mysql, using driver specific attributes...
>  > adding
>  > > > these
>  > > > > > two attributes to DBI would allow my class to be completely
>  > > > > > driver-independent.
>  > > > > >
>  > > > > > In any case, DBI knows (or can find out more efficiently than the
>  > > > developer)
>  > > > > > what table each column came from - and the resulting info is useful,
>  > > > > > portable, simplifies the query, and is in line with the other
>  > statement
>  > > > > > handle attributes.
>  > > > > >
>  > > > > > Again, thanks for the reply and suggestion! - Bob
>  > > > > >
>  > > > > >
>  > > > > > ----- Original Message -----
>  > > > > > From: "Simon Oliver" <simon.oliver@umist.ac.uk>
>  > > > > > To: "Robert M. Free" <bfree@graphcomp.com>
>  > > > > > Cc: <dbi-dev@perl.org>
>  > > > > > Sent: Wednesday, February 21, 2001 3:45 AM
>  > > > > > Subject: Re: PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME
>  > and
>  > > > KEY
>  > > > > >
>  > > > > >
>  > > > > >
>  > > > > >> Why not just specify it when selecting the columns:
>  > > > > >>
>  > > > > >> SELECT table1.ID as "table1.ID", table2.ID as "table2.ID" FROM...
>  > > > > >>
>  > > > > >> --
>  > > > > >>    Simon Oliver
>  > > > > >>
>  > > > > >> Robert M. Free wrote:
>  > > > > >>
>  > > > > >>
>  > > > > >>> New to this mailing list - sorry if this has already been
>  > covered...
>  > > > > >>>
>  > > > > >>>
>  > > > > >>> COLUMN_NAME
>  > > > > >>>
>  > > > > >>> When doing a multi-table query, it's useful to be able to
>  > > > differentiate
>  > > > > >>
>  > > > > > the
>  > > > > >
>  > > > > >>> table that the resulting column comes from - particularly if there
>  > are
>  > > > > >>
>  > > > > > name
>  > > > > >
>  > > > > >>> conflicts.
>  > > > > >>>
>  > > > > >>> For instance: "SELECT table1.ID, table2.ID" will result in 2
>  > columns
>  > > > > >>
>  > > > > > named
>  > > > > >
>  > > > > >>> "ID".
>  > > > > >>>
>  > > > > >>> Ideally, the NAME attribute would report "table1.ID" and
>  > "table2.ID",
>  > > > > >>> instead of "ID" and "ID", but it's a bit late to change its
>  > behavior
>  > > > > >>
>  > > > > > now...
>  > > > > >
>  > > > > >>> So I propose a couple of options:
>  > > > > >>>
>  > > > > >>> 1. Add a TABLE statement handle attribute that returns an array of
>  > > > table
>  > > > > >>> names for each resulting column.  This is equivalent to MySQL's
>  > > > > >>
>  > > > > > mysql_table
>  > > > > >
>  > > > > >>> attribute.
>  > > > > >>>
>  > > > > >>> or
>  > > > > >>>
>  > > > > >>> 2. Add a COLUMN_NAME attribute that returns an array of column
>  > names
>  > > > > >>> formatted in the standard SQL format: "table.column".  This would
>  > > > > >>
>  > > > > > eventually
>  > > > > >
>  > > > > >>> obsolete the NAME attribute.
>  > > > > >>>
>  > > > > >>> My preference is the latter option.
>  > > > > >>>
>  > > > > >>> For DBs that do not provide native support, this feature can be
>  > > > > >>
>  > > > > > supported by
>  > > > > >
>  > > > > >>> parsing the prepare statement.
>  > > > > >>>
>  > > > > >>>
>  > > > > >>> KEY
>  > > > > >>>
>  > > > > >>> In order to retrieve/update/delete a record resulting from a
>  > query, a
>  > > > > >>
>  > > > > > column
>  > > > > >
>  > > > > >>> needs to be uniquely identified within a table - however, there is
>  > no
>  > > > > >>> portable attribute for determining which columns are unique.
>  > > > > >>>
>  > > > > >>> Again, I propose a couple of options:
>  > > > > >>>
>  > > > > >>> 1. Add a UNIQUE attribute that returns an array of boolean flags
>  > > > > >>
>  > > > > > indicating
>  > > > > >
>  > > > > >>> whether the column is known to be unique or not.  This is similar
>  > to
>  > > > > >>
>  > > > > > MySQL's
>  > > > > >
>  > > > > >>> mysql_is_pri_key attribute.
>  > > > > >>>
>  > > > > >>> or
>  > > > > >>>
>  > > > > >>> 2. Add a KEY statement handle attribute that would return an array
>  > of
>  > > > > >>
>  > > > > > key
>  > > > > >
>  > > > > >>> types for each resulting column.  "0" would indicate unknown key
>  > > > status,
>  > > > > >>
>  > > > > > "1"
>  > > > > >
>  > > > > >>> would indicate that the column was known to be unique and "-1"
>  > would
>  > > > > >>> indicate that it was known to be a primary key.
>  > > > > >>>
>  > > > > >>> While #1 might be "cleaner", #2 is more informative and
>  > extensible.
>  > > > For
>  > > > > >>
>  > > > > > my
>  > > > > >
>  > > > > >>> purposes, I could live with either.
>  > > > > >>>
>  > > > > >>> Platforms without native support would report 0.
>  > > > > >>>
>  > > > > >>>
>  > > > > >>> If I can be of assistance in spec'g, promoting or implementing
>  > these
>  > > > > >>> enhancements, please let me know.  Likewise, if I'm covering old
>  > > > ground,
>  > > > > >>> please give me a nudge.
>  > > > > >>>
>  > > > > >>> Thanks - Bob
>  > > > > >>
>  > > > >
>  > >
>  > >
>  > > --
>  > > Matthew O. Persico
>  > >
>  > > "If you were supposed to understand it,
>  > > we wouldn't call it code." - FedEx
>  > >
>  > > Shop online without a credit card
>  > > http://www.rocketcash.com
>  > > RocketCash, a NetZero subsidiary
>  > 
> 
> -- 
> Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
> http://www.mbay.net/~mpeppler - mpeppler@mbay.net
> International Sybase User Group - http://www.isug.com
> Sybase on Linux mailing list: ase-linux-list@isug.com
> 
> 

0
willer
2/22/2001 3:55:01 PM
Hmmm...

Merijn - That's very similar to what my abstracted class does, except that I
create an object and hand it out in a callback statement.  On mysql, I
prepend the TABLE attribute to the NAME key.  Perhaps you'd be interested in
reviewing the modules I've created...

Michael - the reason I need to support "*" is that non-programmers are
generating the queries - marketing and sales types that know just enough to
be dangerous.  They have access to a mirrored DB, and are free to bang away
at it to generate reports.

We have specialists that do all the serious reporting, but the
sales/marketing folks like to play at it, as well.  This lets them do their
own research without impacting engineering resources.

- Bob


> my %fld;
> my $sth = $dbh->prepare ("select * from foo");
> $sth->execute;
> $sth->bind_columns (\@fld{@{$sth->{NAME}}});
> while ($sth->fetch) {
>     $fld{key} < 0 and next;
>     ...
>
> I've just done some serious testing of the four different fetching methods
> (fetchrow_hashref, fetchrow_array, fetchrow_arrayref and fetch with
> bind_columns). I will post the results early next month. (Not sooner,
cause I
> don't want to spoil my talk at Amsterdam.pm). It's worth looking at.
>
> --
> H.Merijn Brand           Amsterdam Perl Mongers
(http://www.amsterdam.pm.org/)
> using perl-5.005.03, 5.6.0, 5.6.1, 5.7.1 & 623 on HP-UX 10.20 & 11.00, AIX
4.2
>    AIX 4.3, WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.022 &/|
DBD-Unify
> ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
>

0
bfree
2/22/2001 4:12:15 PM
On Thu, Feb 22, 2001 at 10:19:40AM +0100, H.Merijn Brand wrote:
> On Wed, 21 Feb 2001 21:43:47 -0500, "Matthew O. Persico" <persicom@acedsl.com> wrote:
> > Well in a language like 'C' it's evil because you have to then look
> > further down the page to the bind() statements to get an idea of what
> > you're selecting.
> 
> my %fld;
> my $sth = $dbh->prepare ("select * from foo");
> $sth->execute;
> $sth->bind_columns (\@fld{@{$sth->{NAME}}});

For portability that should be {NAME_lc} in this case.

Tim.

> while ($sth->fetch) {
>     $fld{key} < 0 and next;
>     ...
> 
> I've just done some serious testing of the four different fetching methods
> (fetchrow_hashref, fetchrow_array, fetchrow_arrayref and fetch with
> bind_columns). I will post the results early next month. (Not sooner, cause I
> don't want to spoil my talk at Amsterdam.pm). It's worth looking at.
> 
> -- 
> H.Merijn Brand           Amsterdam Perl Mongers (http://www.amsterdam.pm.org/)
> using perl-5.005.03, 5.6.0, 5.6.1, 5.7.1 & 623 on HP-UX 10.20 & 11.00, AIX 4.2
>    AIX 4.3, WinNT 4, Win2K pro & WinCE 2.11 often with Tk800.022 &/| DBD-Unify
> ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
0
Tim
2/22/2001 7:44:13 PM
<rant>

I hate getting the same messages multiple times - once from the list,
once 'cause I'm on the CC.

One of these days, I'm going to write a Perl mail client that allows me
to create MAILLIST rules that say, "If one of the addresses is
dbi-dev@perl.org, blow away the others."

Until then, I will Reply-To-All and carefully delete the non-list
addresses.

</rant>

-- 
Matthew O. Persico
    
"If you were supposed to understand it,
we wouldn't call it code." - FedEx

Shop online without a credit card
http://www.rocketcash.com
RocketCash, a NetZero subsidiary
0
persicom
2/23/2001 3:07:12 AM
On Thu, 22 Feb 2001, Matthew O. Persico wrote:

> I hate getting the same messages multiple times - once from the list,
> once 'cause I'm on the CC.

I just use the key 'd' in Pine if I feel the urge to purge my screen of
the offending duplicate.

One keystroke.

0
willer
2/23/2001 3:56:53 AM
Steve Willer wrote:
> 
> On Thu, 22 Feb 2001, Matthew O. Persico wrote:
> 
> > I hate getting the same messages multiple times - once from the list,
> > once 'cause I'm on the CC.
> 
> I just use the key 'd' in Pine if I feel the urge to purge my screen of
> the offending duplicate.
> 
> One keystroke.

If I were lucky enough to be on a 'NIX box, yes. Unfortunately, this is
a Win2000 machine using Netscape mailer. Why don't I convert? I should.
Maybe someday.

-- 
Matthew O. Persico
    
"If you were supposed to understand it,
we wouldn't call it code." - FedEx

Shop online without a credit card
http://www.rocketcash.com
RocketCash, a NetZero subsidiary
0
persicom
2/24/2001 1:00:10 AM
"Matthew O. Persico" <persicom@acedsl.com> writes:

> In Perl, the bind() statement problem manifests itself differently: if
> you use a fetchrow_hashref, there is NO local code that tells you all
> the columns you've retrieved; 

This is a good thing. It means your code is independent of the database
structure. The fewer pieces of code that need hard coded column names the more
abstract and flexible the code is. 

Consider an object that represents a database record, should you have to
adjust the code every time you add a column to your database record? Consider
a generic "view table" web page that formats a database table as SQL, should
it have to be customized for each table?

However as you describe Oracle handles this very poorly. Any open cursors
become hosed when the database structure changes. This is unfortunate because
it forces people to hard code lists of columns in many places where they have
no business being. 

-- 
greg

0
gsstark
2/25/2001 5:23:35 PM
Reply:

Similar Artilces:

DBI handling of attribute hash in DBI->connect
I am encountering this warning message when working with the sample database provided with "MySQL 3rd ed., DuBois, Sams Publishing": "Can't set DBI::db=HASH(0x81cfbdc)->{Autocommit}: unrecognised attribute or invalid value at /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi/DBI.pm line 648." The test script used, the database create & table create sql strings and table inserts are shown below. A sample of calling the test script and its output is included. Changing the attribute values seems to have no effect. Removing the attribute hash from...

[perl5-dbi/dbi] 2f21dd: handle aliasing of STORE'd attributes for looking ...
----==_mimepart_518cb70daa3c2_12f7a9bdd4642e8 Date: Fri, 10 May 2013 01:59:57 -0700 Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-ID: <518cb70dab132_12f7a9bdd4643c@hookshot-fe5-pe1-prd.aws.github.net.mail> Branch: refs/heads/master Home: https://github.com/perl5-dbi/dbi Commit: 2f21ddff0707ac6d236ce54af91931f4e863ecae https://github.com/perl5-dbi/dbi/commit/2f21ddff0707ac6d236ce54af91931f4e863ecae Author: Jens Rehsack <sno@netbsd.org> Date: 2013-05-10 (Fri, 10 May 2013) Changed paths: M...

[perl5-dbi/dbi] 2f21dd: handle aliasing of STORE'd attributes for looking ... #2
----==_mimepart_51faace334a3f_29aa1083d50971b3 Date: Thu, 01 Aug 2013 11:45:55 -0700 Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-ID: <51faace3365c5_29aa1083d509727@hookshot-fe1-pe1-prd.aws.github.net.mail> Branch: refs/heads/dbi-test Home: https://github.com/perl5-dbi/dbi Commit: 2f21ddff0707ac6d236ce54af91931f4e863ecae https://github.com/perl5-dbi/dbi/commit/2f21ddff0707ac6d236ce54af91= 931f4e863ecae Author: Jens Rehsack <sno@netbsd.org> Date: 2013-05-10 (Fri, 10 May 2013) Ch...

DBI::DBD: attribute handling (outer/inner handles, $h->STORE vs. $h->{})
--------------020705030901070303060403 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit "The Perl DBI Database Driver Writer's Guide" introduces attribute handling in the following way: Note the use of the STORE method for setting the dbh attributes. That's because within the driver code, the handle object you have is the 'inner' handle of a tied hash, not the outer handle that the users of your driver have. Because you have the inner handle, tie magic doesn't get invoked when you get or se...

Statement Handle Attribute Precision
Using mariadb 10 series, Perl 5.18 DBI 1.623 DBD-mysql 4.20 I seem to be getting 3 byte 'PRECISION' back on the statement handle for CHAR and VARCHAR field, so if its supposed to be 35 character length, we get 105 back, before some updates to our server it was returning 35 byte; so 1byte per character. Have checked that the server, table and column are all indeed latin1 and not UTF-8, even checked mariadb information_schema.COLUMNS and it says CHARACTER_OCTET_LENGTH=35 for that column. was trying to figure out how DBI gets the information to populate PRECISION? ...

Statement Handle Attribute Precision
Using mariadb 10 series, Perl 5.18 DBI 1.623 DBD-mysql 4.20 I seem to be getting 3 byte 'PRECISION' back on the statement handle for CHAR and VARCHAR field, so if its supposed to be 35 character length, we get 105 back, before some updates to our server it was returning 35 byte; so 1byte per character. Have checked that the server, table and column are all indeed latin1 and not UTF-8, even checked mariadb information_schema.COLUMNS and it says CHARACTER_OCTET_LENGTH=35 for that column. was trying to figure out how DBI gets the information to populate PRECISION? ...

reusing DBI statement handle
In following db functions I am using returned "statement handle " and afterword just ignoring it and overwrite it with the newly returned value. Does this cause memory leak/ or any kind of resouce leak? Can I reuse statement handle in such a way? If not, what is cleaner way to do it? ------------------------- sub getTotalDBTags (){ my $rTableListRef; my $query; my $sth; my $count=0; my $rRowCount=0; db_init(); $query = "select TableName from hourly_tables"; $sth = perform_query($query); $rTableListRef = $sth...

DBD::Pg statement handle attributes?
I'm probably not doing something right, but I'm not finding $sth->{PRECISION} $sth->{SCALE} $sth->{NULLABLE} attributes for a Postgres table. I'm doing a select on the table and then after "executing" the select, I'm looking for those statement handle attributes and they're not there - they *are* each a reference to an array, and the array *does* contain the same number of elements as the number of fields, but all the elements are undefined. Is this a problem with DBD::Pg, or is it me? TIA. -- Hardy Merrill Senior Software En...

Getting statement handle attributes from dbdimp.c
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 DBD::Pg gathers up information for foreign_key_info and then uses DBD::Sponge to turn it into a statement handle which is passed back to the caller. The problem is that the imp_dbh has a user-settable flag (pg_expand_array) which should always be off inside of dbd_st_fetch. What I need is some way to detect, inside of dbdimp.c, that the statement handle (imp_sth at this level) came from DBD::Sponge. Either inside of fetcth itself, or perhaps better, at an earlier stage so that I can set one of the internal imp_sth field...

[dbi-dev] Proposed fetchall_hashref and selectall_hashref extensions
DBI Developers, > Speak before you patch I would like to extend DBI's fetchall_hashref and selectall_hashref methods to handle multiple keys. Being new to DBI, I miss Michael Peppler's Sybperl::Simple's useful HashOfHashOfHash method. The following are my proposed new syntaxes: $sth->fetchall_hashref(@key_field); $dbh->selectall_hashref($statement, \@key_field [, \%attr[, @bind_values]]); For example, let FOO and BAR form a composite key. Then: $sth = $dbh->prepare("SELECT FOO, BAR, NAME, BAZ FROM TABLE"); $sth->execute; $hash_r...

Statement handle creation not seen by subclassed DBI
Hi, Just wondered if anyone else had seen this and if there are any opinions on getting around it. I use DBIx::Log4perl (which I wrote to help debug our DBI appication) and DBD::Oracle. New code added to our application calls an oracle package function which returns a reference cursor and it is bound in Perl like so: my $sth2; my $sth1 = $h->prepare(q{BEGIN ? := pkg.testfunc; END;}); $sth1->bind_param_inout(1, \$sth2, 0, { ora_type => ORA_RSET } ); as per DBD::Oracle pod. After $sth1->execute, $sth2 is a statement handle but DBIx::Log4perl does not know about i...

Keys, keys,keys...
Just installed PGP 6.5.8 and all is running fine, so far :-) Question: When I opened the PGP Keys window for the first time, apart from the *Create Key* wizard, there are about 50 odd keys belonging to various and sundry at NAI. Were these supplied for a purpose or only for illustration? Is there any reason they could not or should no be deleted? Question 2: I sent someone an encrypted test message using that person's public key. It went off fine except, I am left with only the encrypted eMail. What setting should I change in order for my eMail client (Outlook 2000) ...

Assigning private attributes to DBI database handles
--_000_B0011508DF5C184ABEC7A8978D7AB5E80BC3071E78NYKPCMMGMB03I_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I'd like to set some private attributes on my db handles so that later I = can interrogate them to get that information. We are doing this with statement handles, but not DB handles, and it simp= ly doesn't seem to be working in any case. Any thoughts? Eric _______________________________________________ This e-mail may contain information that is confidential, privileged or o= therwise protected from d...

[perl5-dbi/dbi] cd6755: Changed delete $h->{$key} to work for keys with 'p...
----==_mimepart_541ec97c77659_4b7c3f94e16f72b8101861 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Branch: refs/heads/master Home: https://github.com/perl5-dbi/dbi Commit: cd6755ff7c440ed95b172dac4caa688feaa9f56d https://github.com/perl5-dbi/dbi/commit/cd6755ff7c440ed95b172dac4caa688feaa9f56d Author: Tim Bunce <Tim.Bunce@pobox.com> Date: 2014-09-21 (Sun, 21 Sep 2014) Changed paths: M Changes M DBI.pm M DBI.xs M lib/DBI/PurePerl.pm M t/06attrs.t Log Message: ----------- Changed delete $h->...

values of DBI statement handle not getting written into text file
------=_NextPart_000_0015_01C84D78.D80D8C80 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0016_01C84D78.D80FD670" ------=_NextPart_001_0016_01C84D78.D80FD670 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi gurus, I have a problem printing values of DBI statement handle into a text file. The detail problem is as bellows: I am sending a query to one of the subroutines of a package and getting the statement handle with return statement If I print the values in statem...

Web resources about - PROPOSAL - Add Statement Handle Attributes: COLUMN_NAME and KEY - perl.dbi.dev

Resources last updated: 12/8/2015 8:26:55 PM