Re: catalog method arguments, empty strings ()
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 CSQL_ATTR_METADATA_ID attribute is set to
+ CSQL_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 CSQL_ATTR_METADATA_ID attribute,
+ i.e. it behaves like an ODBC driver where CSQL_ATTR_METADATA_ID is set to
+ CSQL_FALSE.
... which is the default setting for most (all?) ODBC drivers?
The default according to ODBC (3.0).
Steffen
*** DBI-1.21-orig/DBI.pmThu Feb 07 04:15:50 2002
--- DBI.pm Mon Mar 18 23:27:01 2002
***
*** 3120,3126
Note that Ctable_info might not return records for all tables.
Applications can use any valid table regardless of whether it's
returned by Ctable_info.
! See also L/tables and L/Standards Reference Information.
=item Ccolumn_info INEW
--- 3120,3128
Note that Ctable_info might not return records for all tables.
Applications can use any valid table regardless of whether it's
returned by Ctable_info.
!
! See also L/tables, L/Catalog Methods and
! L/Standards Reference Information.
=item Ccolumn_info INEW
***
*** 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 Cprimary_key_info INEW
--- 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 Cprimary_key_info INEW
***
*** 3277,3283
BPK_NAME: The primary key constraint identifier.
This field is NULL (Cundef) if not applicable to the data source.
! See also L/Standards Reference Information.
=item Cprimary_key INEW
--- 3279,3285
BPK_NAME: The primary key constraint identifier.
This field is NULL (Cundef) if not applicable to the data source.
! See also L/Catalog Methods and L/Standards Reference Information.
=item Cprimary_key INEW
***
*** 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 Ctables INEW
--- 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 Ctables INEW
***
*** 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,
+ CINFORMATION_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 Cundef 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.
+
+ BNote: SQL/CLI and ODBC differ in the handling of empty strings. An
+ empty
Re: catalog method arguments, empty strings ()
On Tue, Mar 19, 2002 at 04:41:33PM +0100, Steffen Goeldner wrote:
Tim Bunce wrote:
On Tue, Mar 19, 2002 at 12:20:43PM +0100, Steffen Goeldner wrote:
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?
I'm not sure what you mean by empty schema.
A defined but empty schema name ('') returned by table_info().
From the ODBC spec:
| If a driver supports schemas for some tables but not for others,
| such as when the driver retrieves data from different DBMSs, it
| returns an empty string () for those tables that do not have
| schemas.
If some 'tables [...] do not have schemas', will the driver accept
select * from .MyTable
?
No idea. Guess we need some real examples to test it on.
Unfortunately, an empty schema argument (currently) does not match
the PUBLIC schema.
Should I generate another predicate for that case?
What does Oracle's ODBC driver do?
SQLTables:
In: StatementHandle = 0x008A1860
CatalogName = SQL_NULL_HANDLE, NameLength1 = 0
SchemaName = , NameLength2 = 0
TableName = DBA_TABLES , NameLength3 = 10
TableType = SQL_NULL_HANDLE, NameLength4 = 0
Return: SQL_SUCCESS=0
Get Data All:
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
--- - - -
Null PUBLIC DBA_TABLES SYNONYM Null
Null SYS DBA_TABLES SYSTEM VIEW Null
2 rows fetched from 5 columns.
Thus, I'd better change:
, decode( t.OWNER, 'PUBLIC', '', t.OWNER ) TABLE_SCHEM
to
, t.OWNER TABLE_SCHEM
?
Yesp, looks like it.
Tim.
Re: catalog method arguments, empty strings ()
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,
+ CINFORMATION_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 Cundef 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?
+
+ BNote: 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 Iordinary values, e.g.
+ the arguments of Cprimary_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 Isearch patterns (strings
+ containing '_' and/or '%'), e.g. the C$table argument of Ccolumn_info().
+ Passing '%' is equivalent to leaving the argument Cundef.
+
+ BCaveat: 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 CSQL_ATTR_METADATA_ID attribute is set to
+ CSQL_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 CSQL_ATTR_METADATA_ID attribute,
+ i.e. it behaves like an ODBC driver where CSQL_ATTR_METADATA_ID is set to
+ CSQL_FALSE.
... which is the default setting for most (all?) ODBC drivers?
Tim.
Re: catalog method arguments, empty strings ()
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.
Tim.
On Fri, Mar 15, 2002 at 12:46:29PM +0100, Steffen Goeldner wrote:
Steffen Goeldner wrote:
P.S.: It seems I'll have TO DO some 'empty string' research.
This is yet another case where ODBC and SQL/CLI differ :-(
ODBC is quite clear about pattern value arguments:
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcpattern_value_arguments.asp
| Passing a null pointer to a search pattern argument does not
| constrain the search for that argument; that is, a null pointer
| and the search pattern % (any characters) are equivalent.
| However, a zero-length search pattern - that is, a valid pointer
| to a string of length zero - matches only the empty string ().
ODBC says not much about empty strings in ordinary arguments in
general. However, the doc's for special catalog functions say
(e.g. for SQLColumns):
| If a driver supports catalogs for some tables but not for others,
| such as when the driver retrieves data from different DBMSs, an
| empty string () denotes those tables that do not have catalogs.
ODBC is quite silent about empty strings in identifier arguments.
In my opinion, ODBC treats empty strings always as restriction
criteria, i.e. they match the empty string.
SQL/CLI gives very precise rules how arguments must be handled.
Attached is a perlified version of these rules for SQLColumns().
As you can see, arguments of length zero are ignored when building
the predicate. Thus, empty strings will not restrict the result
set.
Matching an empty string is only possible if METADATA_ID is TRUE
and the argument is an empty quoted identifier ('').
Steffen
