Tim Bunce wrote:
>
> Thanks.
>
> I think the DBI should follow ODBC where it differs from SQL/CLI
> in areas where ODBC is not likely to change. This is probably
> one of those places.
O.k., I dropped the first 'XXX' (and retained that paragraph).
I added a note that ODBC and SQL/CLI differ and dropped the
paragraph with the second 'XXX'.
FYI, let's look how various ODBC driver handle empty strings!
First, I tried to create a (non-standard) table like:
create table cat.sch.tbl ("" integer, ...)
via dbish (with varying cat, sch, tbl).
Next, I used odbcte.exe's SQLColumns with
cat sch tbl ""
as arguments (again with varying cat, sch, tbl).
Here the results:
ORACLE 08.01.0600
-----------------
- illegal zero-length identifier
- Optional feature not implemented.
MS ORACLE 02.573.6019
---------------------
- illegal zero-length identifier
- <Null>, "TST", "T1", "C1", 3, "NUMBER", ...
...
MS ACCESS 04.00.6019
--------------------
- dump
- 0 rows fetched from 19 columns.
OCELOT 03.00.0207
-----------------
- An unexpected token <integer> was found following <(>. ...
- "OCELOT", "OCELOT", "T1", "C1", 4, "INTEGER", ...
...
Steffen
*** DBI-1.21-orig/DBI.pm Thu Feb 07 04:15:50 2002
--- DBI.pm Sun Mar 17 21:13:44 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,4516 ----
=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 retrieved with
+
+ $esc = $dbh->get_info( 14 ); # SQL_SEARCH_PATTERN_ESCAPE
+
+ 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>.
+ 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
