FYI, here's how things have worked out...
=item C<get_info> I<NEW>
B<Warning:> This method is experimental and may change.
$value = $dbh->get_info( $info_type );
Returns information about the implementation, i.e. driver and data
source capabilities, restrictions etc. It returns C<undef> for
unknown or unimplemented information types. For example:
$database_version = $dbh->get_info( 18 ); # SQL_DBMS_VER
$max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT
See L</"Standards Reference Information"> for more detailed information
about the information types and their meanings and possible return values.
The DBI curently doesn't provide a name to number mapping for the
information type codes or the results. Applications are expected to use
the integer values directly, with the name in a comment, or define
their own named values using something like the L<constant> pragma.
Because some DBI methods make use of get_info(), drivers are strongly
encouraged to support I<at least> the following very minimal set
of information types to ensure the DBI itself works properly:
Type Name Example A Example B
---- -------------------------- ------------ ------------
17 SQL_DBMS_NAME 'ACCESS' 'Oracle'
18 SQL_DBMS_VER '03.50.0000' '08.01.0721'
29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"'
41 SQL_CATALOG_NAME_SEPARATOR '.' '@'
114 SQL_CATALOG_LOCATION 1 2
=item C<tables> I<NEW>
B<Warning:> This method is experimental and may change.
@names = $dbh->tables( $catalog, $schema, $table, $type );
@names = $dbh->tables;
Simple interface to table_info(). Returns a list of matching
table names, possibly including catalog and schema names.
See L</table_info> for a description of the parameters.
If C<$dbh->E<gt>C<get_info(29)> returns true (29 is SQL_IDENTIFIER_QUOTE_CHAR)
then the table names are constructed and quoted by L</quote_identifier>
to ensure they are usable even if they contain whitespace or reserved
words etc.
=item C<quote_identifier>
$sql = $dbh->quote_identifier( $name );
$sql = $dbh->quote_identifier( $name1, $name2, $name3, \%attr );
Quote an identifier (table name etc.) for use in an SQL statement,
by escaping any special characters (such as double quotation marks)
it contains and adding the required type of outer quotation marks.
Undefined names are ignored and the remainder are quoted and then
joined together, typically with a dot (C<.>) character. For example:
$id = $dbh->quote_identifier( undef, 'Her schema', 'My table' );
would, for most database types, return C<"Her schema"."My table">
(including all the double quotation marks).
If three names are supplied then the first is assumed to be a catalog
name and special rules may be applied based on what L</get_info>
returns for SQL_CATALOG_NAME_SEPARATOR (41) and SQL_CATALOG_LOCATION (114).
For example, for Oracle:
$id = $dbh->quote_identifier( 'link', 'schema', 'table' );
would return C<"schema"."table"@"link">.
And the code:
sub quote_identifier {
my ($dbh, @id) = @_;
my $attr = (@id > 3) ? pop @id : undef;
my $info = $dbh->{dbi_quote_identifier_cache} ||= [
$dbh->get_info(29) || '"', # SQL_IDENTIFIER_QUOTE_CHAR
$dbh->get_info(41) || '.', # SQL_CATALOG_NAME_SEPARATOR
$dbh->get_info(114) || 1, # SQL_CATALOG_LOCATION
];
my $quote = $info->[0];
foreach (@id) { # quote the elements
next unless defined;
s/$quote/$quote$quote/g; # escape embedded quotes
$_ = qq{$quote$_$quote};
}
# strip out catalog if present for special handling
my $catalog = (@id >= 3) ? shift @id : undef;
# join the dots, ignoring any null/undef elements (ie schema)
my $quoted_id = join '.', grep { defined } @id;
if ($catalog) { # add catalog correctly
$quoted_id = ($info->[2] == 2) # SQL_CL_END
? $quoted_id . $info->[1] . $catalog
: $catalog . $info->[1] . $quoted_id;
}
return $quoted_id;
}
Tim.
