Attached is a patch for DBI which defines the foreign_key_info
interface.
Some updated links to the ODBC specs are included.
Steffen
*** DBI-1.20/DBI.pm Sat Aug 25 01:33:52 2001
--- DBI.pm Tue Sep 25 19:32:41 2001
***************
*** 298,303 ****
--- 298,304 ----
table_info => { U =>[1,6,'$catalog, $schema, $table, $type [, \%attr ]' ]
},
primary_key_info=> { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ] },
primary_key => { U =>[4,5,'$catalog, $schema, $table [, \%attr ]' ] },
+ foreign_key_info=> { U =>[1,7,'$pk_catalog, $pk_schema, $pk_table,
+$fk_catalog, $fk_schema, $fk_table' ] },
type_info_all => { U =>[1,1] },
type_info => { U =>[1,2] },
get_info => { U =>[2,2] },
***************
*** 1082,1087 ****
--- 1083,1092 ----
return @col;
}
+ sub foreign_key_info {
+ shift->_not_impl('foreign_key_info');
+ }
+
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args);
***************
*** 2708,2714 ****
For more detailed information about the fields and their meanings,
you can refer to:
! http://msdn.microsoft.com/library/psdk/dasdk/odch6wqb.htm
If that URL ceases to work then use the MSDN search facility at:
--- 2713,2719 ----
For more detailed information about the fields and their meanings,
you can refer to:
! http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqltables.asp
If that URL ceases to work then use the MSDN search facility at:
***************
*** 2767,2773 ****
For more detailed information about the fields and their meanings,
you can refer to:
! http://msdn.microsoft.com/library/psdk/dasdk/odch6fn7.htm
If that URL ceases to work then use the MSDN search facility at:
--- 2772,2778 ----
For more detailed information about the fields and their meanings,
you can refer to:
! http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlprimarykeys.asp
If that URL ceases to work then use the MSDN search facility at:
***************
*** 2793,2798 ****
--- 2798,2935 ----
The list is in primary key column sequence order.
+ =item C<foreign_key_info> I<NEW>
+
+ B<Warning:> This method is experimental and may change.
+
+ $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table
+ , $fk_catalog, $fk_schema, $fk_table );
+
+
+ Returns an active statement handle that can be used to fetch information
+ about foreign keys in or (and) referencing the specified table(s).
+ The arguments don't accept search patterns (unlike table_info()).
+
+ C<$pk_catalog>, C<$pk_schema>, C<$pk_table>
+ identify the primary (unique) key table (B<PKT>).
+
+ C<$fk_catalog>, C<$fk_schema>, C<$fk_table>
+ identify the foreign key table (B<FKT>).
+
+ If both B<PKT> and B<FKT> are given, the function returns the foreign key (if
+ any) in table B<FKT> that refers to the primary (unique) key of table B<PKT>.
+ (Note: In SQL/CLI, the result is implementation-defined.)
+
+ If only B<PKT> is given, then the result set contains the primary key
+ of that table and all foreign keys that refer to it.
+
+ If only B<FKT> is given, then the result set contains all foreign keys
+ in that table and the primary keys to which they refer.
+ (Note: In SQL/CLI, the result includes unique keys too.)
+
+ For example:
+
+ $sth = $dbh->foreign_key_info( undef, $user, 'master');
+ $sth = $dbh->foreign_key_info( undef, undef, undef , undef, $user, 'detail');
+ $sth = $dbh->foreign_key_info( undef, $user, 'master', undef, $user, 'detail');
+
+ Note: The support for the selection criteria, such as C<$catalog>, is
+ driver specific. If the driver doesn't support catalogs and/or
+ schemas, it may ignore these criteria.
+
+ The statement handle returned has the following fields in the order shown below.
+ Because ODBC never includes unique keys, they define different columns in the
+ result set than SQL/CLI. SQL/CLI column names are shown in parentheses.
+
+ B<PKTABLE_CAT ( UK_TABLE_CAT )>:
+ The primary (unique) key table catalog identifier.
+ This field is NULL (C<undef>) if not applicable to the data source,
+ which is often the case. This field is empty if not applicable to the
+ table.
+
+ B<PKTABLE_SCHEM ( UK_TABLE_SCHEM )>:
+ The primary (unique) key table schema identifier.
+ This field is NULL (C<undef>) if not applicable to the data source,
+ and empty if not applicable to the table.
+
+ B<PKTABLE_NAME ( UK_TABLE_NAME )>:
+ The primary (unique) key table identifier.
+
+ B<PKCOLUMN_NAME (UK_COLUMN_NAME )>:
+ The primary (unique) key column identifier.
+
+ B<FKTABLE_CAT ( FK_TABLE_CAT )>:
+ The foreign key table catalog identifier.
+ This field is NULL (C<undef>) if not applicable to the data source,
+ which is often the case. This field is empty if not applicable to the
+ table.
+
+ B<FKTABLE_SCHEM ( FK_TABLE_SCHEM )>:
+ The foreign key table schema identifier.
+ This field is NULL (C<undef>) if not applicable to the data source,
+ and empty if not applicable to the table.
+
+ B<FKTABLE_NAME ( FK_TABLE_NAME )>:
+ The foreign key table identifier.
+
+ B<FKCOLUMN_NAME ( FK_COLUMN_NAME )>:
+ The foreign key column identifier.
+
+ B<KEY_SEQ ( ORDINAL_POSITION )>:
+ The column sequence number (starting with 1).
+
+ B<UPDATE_RULE ( UPDATE_RULE )>:
+ The referential action for the UPDATE rule.
+ The following codes are defined:
+
+ CASCADE 0
+ RESTRICT 1
+ SET NULL 2
+ NO ACTION 3
+ SET DEFAULT 4
+
+ B<DELETE_RULE ( DELETE_RULE )>:
+ The referential action for the DELETE rule.
+ The codes are the same as for UPDATE_RULE.
+
+ B<FK_NAME ( FK_NAME )>:
+ The foreign key name.
+
+ B<PK_NAME ( UK_NAME )>:
+ The primary (unique) key name.
+
+ B<DEFERRABILITY ( DEFERABILITY )>:
+ The deferrability of the foreign key constraint.
+ The following codes are defined:
+
+ INITIALLY DEFERRED 5
+ INITIALLY IMMEDIATE 6
+ NOT DEFERRABLE 7
+
+ B< ( UNIQUE_OR_PRIMARY )>:
+ This column is necessary if a driver includes all candidate (i.e. primary and
+ alternate) keys in the result set (as specified by SQL/CLI).
+ The value of this column is �UNIQUE� if the foreign key references an alternate
+ key and �PRIMARY� if the foreign key references a primary key.
+
+ For more detailed information about the fields and their meanings,
+ you can refer to:
+
+ http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlforeignkeys.asp
+
+ If that URL ceases to work then use the MSDN search facility at:
+
+ http://search.microsoft.com/us/dev/
+
+ and search for C<SQLForeignKeys returns> using the exact phrase option.
+ The link you want will probably just be called C<SQLForeignKeys> and will
+ be part of the Data Access SDK.
+
+ See also page 174 of the current SQL/CLI Working Draft:
+
+
+http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF
+
+
=item C<tables> I<NEW>
B<Warning:> This method is experimental and may change.
***************
*** 3049,3067 ****
For more detailed information about these fields and their meanings, you
can refer to:
! http://msdn.microsoft.com/library/psdk/dasdk/odch6yy7.htm
If that URL ceases to work then use the MSDN search facility at
!
! http://search.microsoft.com/us/dev/
!
and search the MSDN Library for C<SQLGetTypeInfo returns> using the exact phrase
option.
The link you want will probably just be called C<SQLGetTypeInfo> (there
may be more than one).
The individual data types are currently described here:
! http://msdn.microsoft.com/library/psdk/dasdk/odap8fcj.htm
If that URL ceases to work, or to get more general information, use the
MSDN search facility as described above and search for C<SQL Data Types>.
--- 3186,3204 ----
For more detailed information about these fields and their meanings, you
can refer to:
! http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlgettypeinfo.asp
If that URL ceases to work then use the MSDN search facility at
!
! http://search.microsoft.com/us/dev/
!
and search the MSDN Library for C<SQLGetTypeInfo returns> using the exact phrase
option.
The link you want will probably just be called C<SQLGetTypeInfo> (there
may be more than one).
The individual data types are currently described here:
! http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsql_data_types.asp
If that URL ceases to work, or to get more general information, use the
MSDN search facility as described above and search for C<SQL Data Types>.
