On Mon, Mar 18, 2002 at 12:05:28PM +0100, Steffen Goeldner wrote:
> 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!
Thanks.
A more realistic example would be for drivers that return empty
strings or NULL for the catalog portion.
> + =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.
Does that apply equally ato both ordinary values and search patterns?
> +
> + 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
+ $search_pattern =~ s/([_%])/$esc$1/g;
(not checked, but I think something like that is worth adding.)a
> + 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.
> + 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?
Tim.
