I'm having a little trouble using the DBI functions table_info(),
column_info(), and primary_key_info() functions which I've never tried
using before now. I'm not certain what sort of values I should specify
as arguments. For table_info(), it returns a valid statement handle
from which I can 'fetch' the records, but it fetches records for every
table in the database REGARDLESS of what my arguments are. I guess
DBD::Informix doesn't support the filters (as suggested in the DBI
documentation). I can live with that. But for column_info() and
primary_key_info(), I have NEVER been successful at getting a valid
(defined) statement handle back from the function call REGARDLESS of
what my arguments are.

Can someone provide sample code or otherwise explain what I am doing
wrong? Here is the pertinent code I have...


use DBI;
use strict;

my $dbh ->connect( ... );

my $sth = $dbh->primary_key_info('%','oper','crest'); # this returns
undef regardless of what my arguments are...
my $data = $sth->fetchall_arrayref; # error: $sth is null (not
defined)...


Any ideas?

Mark

Ah. I found in this document
http://search.cpan.org/~johnl/DBD-In...mix/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...)

Mark

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-In...mix/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