Tim Bunce wrote:
>
> On Mon, Mar 11, 2002 at 05:46:01PM +0100, Steffen Goeldner wrote:
> > + =head2 Catalog Functions
> > +
> > + All catalog functions accept arguments in order to restrict the result sets.
> > + Passing C<undef> to an optional argument does not constrain the search for
> > + that argument.
>
> Let's spell it out for people:
>
> + (i.e., passing undef is equivalent to passing '%').
Yes, but only for 'pattern value arguments'. That's why I mentioned it only
there.
> > + However, an empty string ('') is treated as a regular search criteria.
>
> + and will only match an empty value.
TODO
> > + B<Caveat>: Some DBMS store empty strings as C<NULL>. Neither C<undef> nor
> > + an empty string ('') may match these records!
>
> While that's true in general, what _exact_ impact does it have on
> catalog function parameters as per the standards? Shouldn't it have
> no impact?
TODO
> > + Most arguments in the catalog functions accept only B<ordinary values>, e.g.
>
> I'd prefer I<> for introducing terminology.
O.k.
> > + 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.
>
> The case being significant raises some er, significant, issues for
> portability.
That's true for the database field in general :-(
The following comes into my mind and may help generic applications:
- the (escaped) output of cat_meth1() should be useful
as input for cat_meth2()
- get_info() => SQL_IC_*
- SQL_ATTR_METADATA_ID
- SQLSpecialColumns
TODO
> > + Some arguments in the catalog functions accept B<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>.
>
> Does the DBI spec for each catalog function make it clear enough
> which params are 'ordinary values' and which are 'search patterns'?
I hope so, but we could include an additional overview similar to
<http://msdn.microsoft.com/library/en-us/odbc/htm/odbcarguments_in_catalog_functions.asp>
> They should also all have a 'See also' link to this 'Catalog
> Functions' section (which should perhaps be called catalog methods :-)
O.k.
> > + B<Caveat>: The pattern '%' has a special meaning for C<table_info()>.
>
> ... for certain uses of ...
>
> but it's probably not worth menting here. It should be made clear enough
> in the table_info docs.
Accepted.
> > + 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!
>
> Umm, I guess the fix is to escape literal underscores with the
> appropriate escape character read from get_info (right?).
> Probably worth mentioning that.
O.k.
> > + One argument in the catalog functions accepts a B<list of values>:
> > + the C<$type> argument of C<table_info()>.
>
> I'd skip that one (as per '%' for table_info() above).
Accepted.
> > + 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>.
>
> So is 'identifier' here the same as 'ordinary values' above?
> Best to use consistent terminology.
No:
1) Quoted identifiers are very similar to 'ordinary values', i.e.
their body (the string within the quotes) is interpreted literally.
TODO: They may differ in their handling of empty strings.
2) Unquoted identifiers are compared in UPPERCASE.
SQL/CLI defines rules like:
UPPER(TABLE_NAME) = UPPER(’TBLVAL’) AND
Note the UPPER() lhs! This is ... surprisingly.
> > + 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>.
>
> Tim [who'snot had time to read the ODBC spec recently to answer some of the
> questions for himself, sorry]
Steffen
P.S.: It seems I'll have TO DO some 'empty string' research.
*** DBI-1.21-orig/DBI.pm Thu Feb 07 04:15:50 2002
--- DBI.pm Tue Mar 12 23:19:58 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,4517 ----
=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.
+ XXX: However, an empty string ('') is treated as a regular search criteria
+ and will only match an empty value.
+
+ XXX:
+ B<Caveat>: Some DBMS store empty strings as C<NULL>. Neither C<undef> nor
+ an empty string ('') may match these records!
+
+ 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
