Re: catalog method arguments, empty strings ("")

[Tim Bunce]

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"
> --- -  - -
>   "PUBLIC"  "DBA_TABLES" "SYNONYM" 
>   "SYS" "DBA_TABLES" "SYSTEM VIEW" 
> 
> 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 ("")

[Steffen Goeldner]

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"

?

> > 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"
--- -  - -
  "PUBLIC"  "DBA_TABLES" "SYNONYM" 
  "SYS" "DBA_TABLES" "SYSTEM VIEW" 

2 rows fetched from 5 columns.

Thus, I'd better change:

  , decode( t.OWNER, 'PUBLIC', '', t.OWNER ) TABLE_SCHEM

to 

 , t.OWNER  TABLE_SCHEM

?


Steffen

Re: catalog method arguments, empty strings ("")

[Tim Bunce]

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.

> 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?

> > > + 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
> 
>   
> 
> for *my* interpretation of the ODBC spec. (IMO, the spec gives a very
> nebulous impression about that topic.)

[Sigh]

> Steffen
> *** DBI-1.21-orig/DBI.pm  Thu Feb 07 04:15:50 2002
> --- DBI.pmMon Mar 18 23:27:01 2002

Thanks again Steffen.

Tim.

Re: catalog method arguments, empty strings ("")

[Steffen Goeldner]

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

  

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 attribute is set to
> > + C.
> 
> 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 attribute,
> > + i.e. it behaves like an ODBC driver where C is set to
> > + C.
> 
>  ... 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 C might not return records for all tables.
  Applications can use any valid table regardless of whether it's
  returned by C.
! See also L and L.
  
  =item C I
  
--- 3120,3128 
  Note that C might not return records for all tables.
  Applications can use any valid table regardless of whether it's
  returned by C.
! 
! See also L, L and
! L.
  
  =item C I
  
***
*** 3234,3240 
  Note: There is some overlap with statement attributes (in perl) and
  SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
  
! See also L.
  
  =item C I
  
--- 3236,3242 
  Note: There is some overlap with statement attributes (in perl) and
  SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
  
! See also L and L.
  
  =item C I
  
***
*** 3277,3283 
  B: The primary key constraint identifier.
  This field is NULL (C) if not applicable to the data source.
  
! See also L.
  
  =item C I
  
--- 3279,3285 
  B: The primary key constraint identifier.
  This field is NULL (C) if not applicable to the data source.
  
! See also L and L.
  
  =item C I
  
***
*** 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.
  
  
  =item C I
--- 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 and L.
  
  
  =item C I
***
*** 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 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 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: 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, e.g.
+ the arguments of C.
+ 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 (strings
+ containing '_' and/or '%'), e.g. the C<$table> argument of C.
+ Passing '%' is equivalent to leaving the argument C.
+ 
+ B: The underscore ('_') is valid and often used in SQL identifiers.
+ Passing such a value to a search pattern argument m

Re: catalog method arguments, empty strings ("")

[Tim Bunce]

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 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 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: 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, e.g.
> + the arguments of C.
> + 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 (strings
> + containing '_' and/or '%'), e.g. the C<$table> argument of C.
> + Passing '%' is equivalent to leaving the argument C.
> + 
> + B: 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 attribute is set to
> + C.

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 attribute,
> + i.e. it behaves like an ODBC driver where C is set to
> + C.

 ... which is the default setting for most (all?) ODBC drivers?

Tim.

Re: catalog method arguments, empty strings ("")

[Steffen Goeldner]

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
- , "TST", "T1", "C1", 3, "NUMBER", ...
  ...

MS ACCESS 04.00.6019

- dump
- 0 rows fetched from 19 columns.

OCELOT 03.00.0207
-
- An unexpected token  was found following <(>. ...
- "OCELOT", "OCELOT", "T1", "C1", 4, "INTEGER", ...
  ...


Steffen

*** DBI-1.21-orig/DBI.pmThu Feb 07 04:15:50 2002
--- DBI.pm  Sun Mar 17 21:13:44 2002
***
*** 3120,3126 
  Note that C might not return records for all tables.
  Applications can use any valid table regardless of whether it's
  returned by C.
! See also L and L.
  
  =item C I
  
--- 3120,3128 
  Note that C might not return records for all tables.
  Applications can use any valid table regardless of whether it's
  returned by C.
! 
! See also L, L and
! L.
  
  =item C I
  
***
*** 3234,3240 
  Note: There is some overlap with statement attributes (in perl) and
  SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
  
! See also L.
  
  =item C I
  
--- 3236,3242 
  Note: There is some overlap with statement attributes (in perl) and
  SQLDescribeCol (in ODBC). However, SQLColumns provides more metadata.
  
! See also L and L.
  
  =item C I
  
***
*** 3277,3283 
  B: The primary key constraint identifier.
  This field is NULL (C) if not applicable to the data source.
  
! See also L.
  
  =item C I
  
--- 3279,3285 
  B: The primary key constraint identifier.
  This field is NULL (C) if not applicable to the data source.
  
! See also L and L.
  
  =item C I
  
***
*** 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.
  
  
  =item C I
--- 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 and L.
  
  
  =item C I
***
*** 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 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 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: 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, e.g.
+ the arguments of C.
+ 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 (strings
+ containing '_' and/or '%'), e.g. the C<$table> argument of C.
+ Passing '%' is equivalent to leaving the argument C.
+ 
+ B: 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 attribute is set to
+ C.
+ The DBI (currently) does not support the C attribute,
+ i.e. it behaves like an ODBC driver where C is set to
+ C.
+ 
  
  =head2 Transactions
  

Re: catalog method arguments, empty strings ("")

[Tim Bunce]

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:
> 
>  
> 
>  | 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

catalog method arguments, empty strings ("")

[Steffen Goeldner]

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:

 

 | 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


SQLColumns.pl
Description: Perl program