Tim Bunce wrote:
>
> On Mon, Mar 18, 2002 at 12:05:28PM +0100, Steffen Goeldner wrote:
> A more realistic example would be for drivers that return empty
> strings or NULL for the catalog portion.
Currently, DBD::Oracle's table_info() method returns '' -> NULL ->
undef for the PUBLIC pseudo-schema:
, decode( t.OWNER, 'PUBLIC', '', t.OWNER ) TABLE_SCHEM
Oracle's weird '' -> NULL conversion has one advantage: DBI's
tables() method does the right thing (it omits the schema part).
BTW: Should tables() really return something like '""."DBA_TABLES"'
for empty schemas?
Unfortunately, an empty schema argument (currently) does not match
the PUBLIC schema.
Should I generate another predicate for that case?
> > + However, an empty string ('') is treated as a regular search criteria
> > + and will only match an empty value.
>
> Does that apply equally ato both ordinary values and search patterns?
I think yes. Maybe I'm wrong, but see again
<http:[EMAIL PROTECTED]/msg01045.html>
for *my* interpretation of the ODBC spec. (IMO, the spec gives a very
nebulous impression about that topic.)
> > + escape character which can be retrieved with
> > +
> > + $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
>
> + $search_pattern =~ s/([_%])/$esc$1/g;
O.k.
> > + The ODBC and SQL/CLI specifications define a way to change the default
> > + behavior described above: All arguments (except list value arguments) are
> > + treated as identifier if the C<SQL_ATTR_METADATA_ID> attribute is set to
> > + C<SQL_TRUE>.
>
> If "identifier" is not the same as "ordinary value" then the difference ought to
> be explained here.
O.k.
> > + The DBI (currently) does not support the C<SQL_ATTR_METADATA_ID> attribute,
> > + i.e. it behaves like an ODBC driver where C<SQL_ATTR_METADATA_ID> is set to
> > + C<SQL_FALSE>.
>
> ... which is the default setting for most (all?) ODBC drivers?
The default according to ODBC (3.0).
Steffen
*** DBI-1.21-orig/DBI.pm Thu Feb 07 04:15:50 2002
--- DBI.pm Mon Mar 18 23:27:01 2002
***************
*** 3120,3126 ****
Note that C<table_info> might not return records for all tables.
Applications can use any valid table regardless of whether it's
returned by C<table_info>.
! See also L</tables> and L</"Standards Reference Information">.
=item C<column_info> I<NEW>
--- 3120,3128 ----
Note that C<table_info> might not return records for all tables.
Applications can use any valid table regardless of whether it's
returned by C<table_info>.
!
! See also L</tables>, L</"Catalog Methods"> and
! L</"Standards Reference Information">.
=item C<column_info> I<NEW>
***************
*** 3234,3240 ****
Note: There is some overlap with statement attributes (in perl) and
SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
! See also L</"Standards Reference Information">.
=item C<primary_key_info> I<NEW>
--- 3236,3242 ----
Note: There is some overlap with statement attributes (in perl) and
SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
! See also L</"Catalog Methods"> and L</"Standards Reference Information">.
=item C<primary_key_info> I<NEW>
***************
*** 3277,3283 ****
B<PK_NAME>: The primary key constraint identifier.
This field is NULL (C<undef>) if not applicable to the data source.
! See also L</"Standards Reference Information">.
=item C<primary_key> I<NEW>
--- 3279,3285 ----
B<PK_NAME>: The primary key constraint identifier.
This field is NULL (C<undef>) if not applicable to the data source.
! See also L</"Catalog Methods"> and L</"Standards Reference Information">.
=item C<primary_key> I<NEW>
***************
*** 3404,3410 ****
key and PRIMARY if the foreign key references a primary key, or it
may be undefined if the driver doesn't have access to the information.
! See also L</"Standards Reference Information">.
=item C<tables> I<NEW>
--- 3406,3412 ----
key and PRIMARY if the foreign key references a primary key, or it
may be undefined if the driver doesn't have access to the information.
! See also L</"Catalog Methods"> and L</"Standards Reference Information">.
=item C<tables> I<NEW>
***************
*** 4461,4466 ****
--- 4463,4521 ----
=head1 FURTHER INFORMATION
+
+ =head2 Catalog Methods
+
+ An application can retrieve metadata information from the DBMS by issuing
+ appropriate queries on the views of the Information Schema. Unfortunately,
+ C<INFORMATION_SCHEMA> views are seldom supported by the DBMS.
+ Special methods (catalog methods) are available to return result sets
+ for a small but important portion of that metadata:
+
+ column_info
+ foreign_key_info
+ primary_key_info
+ table_info
+
+ All catalog methods accept arguments in order to restrict the result sets.
+ Passing C<undef> to an optional argument does not constrain the search for
+ that argument.
+ However, an empty string ('') is treated as a regular search criteria
+ and will only match an empty value.
+
+ B<Note>: SQL/CLI and ODBC differ in the handling of empty strings. An
+ empty string will not restrict the result set in SQL/CLI.
+
+ Most arguments in the catalog methods accept only I<ordinary values>, e.g.
+ the arguments of C<primary_key_info()>.
+ Such arguments are treated as a literal string, i.e. the case is significant
+ and quote characters are taken literally.
+
+ Some arguments in the catalog methods accept I<search patterns> (strings
+ containing '_' and/or '%'), e.g. the C<$table> argument of C<column_info()>.
+ Passing '%' is equivalent to leaving the argument C<undef>.
+
+ B<Caveat>: The underscore ('_') is valid and often used in SQL identifiers.
+ Passing such a value to a search pattern argument may return more rows than
+ expected!
+ To include pattern characters as literals, they must be preceded by an
+ escape character which can be achieved with
+
+ $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
+ $search_pattern =~ s/([_%])/$esc$1/g;
+
+ The ODBC and SQL/CLI specifications define a way to change the default
+ behavior described above: All arguments (except I<list value arguments>)
+ are treated as I<identifier> if the C<SQL_ATTR_METADATA_ID> attribute is
+ set to C<SQL_TRUE>.
+ I<Quoted identifiers> are very similar to I<ordinary values>, i.e. their
+ body (the string within the quotes) is interpreted literally.
+ I<Unquoted identifiers> are compared in UPPERCASE.
+
+ The DBI (currently) does not support the C<SQL_ATTR_METADATA_ID> attribute,
+ i.e. it behaves like an ODBC driver where C<SQL_ATTR_METADATA_ID> is set to
+ C<SQL_FALSE>.
+
=head2 Transactions
