Thomas A. Lowery wrote: > > > BTW: I plan support for the ugly (but standard) feature to enumerate > > catalogs and schemas by table_info(). Objections? > > Explain more ... I'm not sure if I follow. >
A patch says more then 1000 words! The 'diff -c' shows many changes,
because most lines are in the else{} block now. That's why I added
a 'diff -y'.
The extended table_info() interface was introduced around DBI 1.18
(see http://search.cpan.org/doc/TIMB/DBI-1.20/Changes) and is
compatible with ODBC and SQL/CLI. The essential part from the DBI
documentation (http://search.cpan.org/doc/TIMB/DBI-1.20/DBI.pm) is:
If the value of $catalog is '%' and $schema and $table name are empty
strings, the result set contains a list of catalog names.
If the value of $schema is '%' and $catalog and $table are empty
strings, the result set contains a list of schema names.
If the value of $type is '%' and $catalog, $schema, and $table are all
empty strings, the result set contains a list of table types.
Steffen
*** DBD-ADO-2.4/lib/DBD/ADO.pm Wed Oct 24 04:41:30 2001
--- ADO.pm Thu Oct 25 18:54:34 2001
***************
*** 638,680 ****
sub table_info {
my($dbh, $attribs) = @_;
my @tp;
-
- my @criteria = (undef); # ADO needs at least one element in the
criteria array!
- for (my $i=0; $i<@$ado_dbi_schematables; $i++) {
- my $field = $ado_dbi_schematables->[$i];
- if (exists $attribs->{$field}) {
- $criteria[$i] = $attribs->{$field};
- }
- }
-
my $field_names = $attribs->{ADO_Columns} ?
$ado_schematables : $ado_dbi_schematables;
! my $oRec = $dbh->{ado_conn}->OpenSchema($ado_consts->{adSchemaTables},
\@criteria);
! if (exists $attribs->{Filter}) {
! $oRec->{Filter} = $attribs->{Filter};
}
! require Win32::OLE::Variant;
! while(! $oRec->{EOF}) {
! my @out = map { $oRec->Fields($_)->{Value} }
! map { $sch_dbi_to_ado->{$_} } @$field_names;
! # Jan Dubois [EMAIL PROTECTED] addition to handle changes
! # in Win32::OLE return of Variant types of data.
! foreach ( @out ) {
! $_ = $_->As(Win32::OLE::Variant::VT_BSTR())
! if (defined $_) && (UNIVERSAL::isa($_,
'Win32::OLE::Variant'));
! }
! if ($attribs->{Trim_Catalog}) {
! $out[0] =~ s/^(.*\\)//; # removes leading
! $out[0] =~ s/(\..*)$//; # removes file extension
}
- push( @tp, \@out );
- $oRec->MoveNext;
}
! $oRec->Close;
$oRec = undef;
my $statement = "adSchemaTables";
--- 638,726 ----
sub table_info {
my($dbh, $attribs) = @_;
+ $attribs = {
+ TABLE_CAT => $_[1],
+ TABLE_SCHEM => $_[2],
+ TABLE_NAME => $_[3],
+ TABLE_TYPE => $_[4],
+ } unless ref $attribs eq 'HASH';
my @tp;
my $field_names = $attribs->{ADO_Columns} ?
$ado_schematables : $ado_dbi_schematables;
+ my $oRec;
! if ( $attribs->{TABLE_CAT} eq '%'
! && $attribs->{TABLE_SCHEM} eq ''
! && $attribs->{TABLE_NAME} eq '') { # Rule 19a
! $oRec =
$dbh->{ado_conn}->OpenSchema($ado_consts->{adSchemaCatalogs});
! if ( $oRec ) {
! while(! $oRec->{EOF}) {
! push @tp, [ $oRec->Fields(0)->{Value}, undef,
undef, undef, undef ];
! $oRec->MoveNext;
! }
! }
! else {
! push @tp, [ undef, undef, undef, undef, undef ];
! }
! }
! elsif ( $attribs->{TABLE_CAT} eq ''
! && $attribs->{TABLE_SCHEM} eq '%'
! && $attribs->{TABLE_NAME} eq '') { # Rule 19b
! $oRec =
$dbh->{ado_conn}->OpenSchema($ado_consts->{adSchemaSchemata});
! if ( $oRec ) {
! while(! $oRec->{EOF}) {
! push @tp, [ $oRec->Fields(0)->{Value},
$oRec->Fields(1)->{Value}, undef, undef, undef ];
! $oRec->MoveNext;
! }
! }
! else {
! push @tp, [ undef, undef, undef, undef, undef ];
! }
}
+ elsif ( $attribs->{TABLE_CAT} eq ''
+ && $attribs->{TABLE_SCHEM} eq ''
+ && $attribs->{TABLE_NAME} eq ''
+ && $attribs->{TABLE_TYPE} eq '%') { # Rule 19c
+ my @TableTypes = ('ALIAS','TABLE','SYNONYM','SYSTEM
+TABLE','VIEW','GLOBAL TEMPORARY','LOCAL TEMPORARY','SYSTEM VIEW'); # XXX
+ for ( sort @TableTypes ) {
+ push @tp, [ undef, undef, undef, $_, undef ];
+ }
+ }
+ else {
+
+ my @criteria = (undef); # ADO needs at least one element in
+the criteria array!
+ for (my $i=0; $i<@$ado_dbi_schematables; $i++) {
+ my $field = $ado_dbi_schematables->[$i];
+ if (exists $attribs->{$field}) {
+ $criteria[$i] = $attribs->{$field};
+ }
+ }
+
+ $oRec =
+$dbh->{ado_conn}->OpenSchema($ado_consts->{adSchemaTables}, \@criteria);
+ if (exists $attribs->{Filter}) {
+ $oRec->{Filter} = $attribs->{Filter};
+ }
! require Win32::OLE::Variant;
! while(! $oRec->{EOF}) {
! my @out = map { $oRec->Fields($_)->{Value} }
! map { $sch_dbi_to_ado->{$_} } @$field_names;
! # Jan Dubois [EMAIL PROTECTED] addition to handle
changes
! # in Win32::OLE return of Variant types of data.
! foreach ( @out ) {
! $_ = $_->As(Win32::OLE::Variant::VT_BSTR())
! if (defined $_) && (UNIVERSAL::isa($_,
'Win32::OLE::Variant'));
! }
! if ($attribs->{Trim_Catalog}) {
! $out[0] =~ s/^(.*\\)//; # removes leading
! $out[0] =~ s/(\..*)$//; # removes file
extension
! }
! push( @tp, \@out );
! $oRec->MoveNext;
}
}
! $oRec->Close if $oRec;
$oRec = undef;
my $statement = "adSchemaTables";
sub table_info { sub table_info
{
my($dbh, $attribs) = @_;
my($dbh, $attribs) = @_;
>
$attribs = {
>
TABLE_CAT => $_[1],
>
TABLE_SCHEM => $_[2],
>
TABLE_NAME => $_[3],
>
TABLE_TYPE => $_[4],
> }
unless ref $attribs eq 'HASH';
my @tp; my @tp;
> my
$field_names = $attribs->{ADO_Columns} ?
>
$ado_schematables : $ado_dbi_schemata
> my
$oRec;
>
> if (
$attribs->{TABLE_CAT} eq '%'
> &&
$attribs->{TABLE_SCHEM} eq ''
> &&
$attribs->{TABLE_NAME} eq '') { # Rule
>
$oRec = $dbh->{ado_conn}->OpenSchema(
>
if ( $oRec ) {
>
while(! $oRec->{EOF}) {
>
push @tp, [ $oRec->Fi
>
$oRec->MoveNext;
>
}
>
}
>
else {
>
push @tp, [ undef, undef, und
>
}
> }
> elsif
( $attribs->{TABLE_CAT} eq ''
>
&& $attribs->{TABLE_SCHEM} eq '%'
>
&& $attribs->{TABLE_NAME} eq '') { # Ru
>
$oRec = $dbh->{ado_conn}->OpenSchema(
>
if ( $oRec ) {
>
while(! $oRec->{EOF}) {
>
push @tp, [ $oRec->Fi
>
$oRec->MoveNext;
>
}
>
}
>
else {
>
push @tp, [ undef, undef, und
>
}
> }
> elsif
( $attribs->{TABLE_CAT} eq ''
>
&& $attribs->{TABLE_SCHEM} eq ''
>
&& $attribs->{TABLE_NAME} eq ''
>
&& $attribs->{TABLE_TYPE} eq '%') { # R
>
my @TableTypes = ('ALIAS','TABLE','SY
>
for ( sort @TableTypes ) {
>
push @tp, [ undef, undef, und
>
}
> }
> else {
my @criteria = (undef); # ADO needs at least
my @criteria = (undef); # ADO needs a
for (my $i=0; $i<@$ado_dbi_schematables; $i++
for (my $i=0; $i<@$ado_dbi_schematabl
my $field = $ado_dbi_schematables->[$
my $field = $ado_dbi_schemata
if (exists $attribs->{$field}) {
if (exists $attribs->{$field}
$criteria[$i] = $attribs->{$f
$criteria[$i] = $attr
}
}
}
}
my $field_names = $attribs->{ADO_Columns} ? |
$oRec = $dbh->{ado_conn}->OpenSchema(
$ado_schematables : $ado_dbi_schemata <
<
my $oRec = $dbh->{ado_conn}->OpenSchema($ado_ <
if (exists $attribs->{Filter}) {
if (exists $attribs->{Filter}) {
$oRec->{Filter} = $attribs->{Filter};
$oRec->{Filter} = $attribs->{
}
}
require Win32::OLE::Variant;
require Win32::OLE::Variant;
while(! $oRec->{EOF}) {
while(! $oRec->{EOF}) {
my @out = map { $oRec->Fields($_)->{V
my @out = map { $oRec->Fields
map { $sch_dbi_to_ado->{$_} }
map { $sch_dbi_to_ado
# Jan Dubois [EMAIL PROTECTED] add
# Jan Dubois jand@activestate
# in Win32::OLE return of Variant typ
# in Win32::OLE return of Var
foreach ( @out ) {
foreach ( @out ) {
$_ = $_->As(Win32::OLE::Varia
$_ = $_->As(Win32::OL
if (defined $_) && (U
if (defined $
}
}
if ($attribs->{Trim_Catalog}) {
if ($attribs->{Trim_Catalog})
$out[0] =~ s/^(.*\\)//; # re
$out[0] =~ s/^(.*\\)/
$out[0] =~ s/(\..*)$//; # re
$out[0] =~ s/(\..*)$/
}
}
push( @tp, \@out );
push( @tp, \@out );
$oRec->MoveNext;
$oRec->MoveNext;
}
}
$oRec->Close; | }
>
$oRec->Close if $oRec;
$oRec = undef; $oRec
= undef;
my $statement = "adSchemaTables"; my
$statement = "adSchemaTables";
my $sponge = DBI->connect("dbi:Sponge:","","" my
$sponge = DBI->connect("dbi:Sponge:","",""
my $sth = $sponge->prepare($statement, my
$sth = $sponge->prepare($statement,
{ rows=> \@tp, NAME=> $field_names })
{ rows=> \@tp, NAME=> $field_names })
$sth; $sth;
} }
