Re: DBD::Informix

On Wed, 29 Sep 2004 16:01:01 -0400, Mark J Fenbers wrote: > Ah. I found in this document > http://search.cpan.org/~johnl/DBD-Informix-2003.04/lib/DBD/Informix/Summary.pm > where these functions are not implemented. :(( > > This is unforntunate because our agency uses 3 different databases, and > making this app portable among all three will be very difficult at best. > > But it does say that "The keys/indexes of a table be fetched by querying on > the system catalog.", but it gives no further details. What is the logical > way to determine the primary keys of a given table using a system catalog? > (I'm not sure I even understand what a system catalog is...) Every Relational Database has a system catalog. It's how the server knows the location of data and how to understand a table's record structure. Read the Informix Administrator's Guide and Administrator's Reference for more info. Tables are defined in systables, column types and names in syscolumns, indexes (important as primary keys are maintained by a unique index on the key) are defined in sysindexes (in 7.xx, in 9.xx it's sysindices and sysindexes is a VIEW), constraints (like primary key constraints) are defined in sysconstraints. Joining these tables will give you what you need. However, it's not simple: - Find the tabname in systables and use it's tabid to join to sysconstraints - Find the sysconstraints record with constrtype = 'P' and use the idxname column to join to sysindexes - The colno's stored in sysindexes.part1 -> part16 (or partN * -1 if negative) are used with tabid to join to syscolumns to determine the column names of the primary key. To do this in a single query is messy (you have to join to syscolumns 16 times as outer joins, except for the first), but it can be done. Jonathan Leffler posts the query for printing key column lists about once a year. Search the CDI archives on the IIUG web site. Art S. Kagel