NAME

DBD::Informix::Metadata - Metadata Methods for DBD::Informix

SYNOPSIS

  use DBI; # This is the usual method.

  # If you need direct access to the functions.
  use DBD::Informix::Metadata;

DESCRIPTION

This document describes the metadata methods for DBD::Informix (Informix Database Driver for Perl DBI Version 2015.1101 (2015-11-01)).

Note that you would seldom actually use this package directly (despite the synopsis above); the methods you would use are defined in the DBD::Informix::db package (in the Informix.pm file).

The ix_tables function

You can call two methods using the DBI func() to get at some basic Informix metadata relatively conveniently.

    @list = $dbh->func('_tables');
    @list = $dbh->func('user', '_tables');
    @list = $dbh->func('base', '_tables');
    @list = $dbh->func('user', 'base', '_tables');
    @list = $dbh->func('system', '_tables');
    @list = $dbh->func('view', '_tables');
    @list = $dbh->func('synonym', '_tables');

Alternatively, the direct calling mechanism is:

    @list = DBD::Informix::Metadata::ix_tables($dbh, @attrs);

The lists of tables are all qualified as "owner".tablename (with metacharacter mapping done by ix_map_tablename described below), and you can use them in SQL statements without fear that the table is not present in the database (unless someone deletes it behind your back). The leading arguments qualify the list of names returned. Private synonyms are reported for just the current user.

Note that the names are returned in the format suitable for use in SQL statements; this is distinct from the format the values are stored in the database.

The ix_columns function

The normal mechanism for calling this function is:

    @list = $dbh->func('_columns');
    @list = $dbh->func(@tables, '_columns');

Alternatively, the direct calling mechanism is:

    @list = DBD::Informix::Metadata::ix_columns($dbh, @tables);

The lists are each references to an array of values corresponding to the owner name, table name, column number, column name, basic data type (ix_ColType value--see below), and data length (ix_ColLength--see below). If no tables are listed, all columns in the database are listed. This can be quite slow because handling synonyms properly requires a UNION operation.

Further, although the '_tables' method reports the names of remote synonyms, the '_columns' method does not expand them (mainly because it is very hard to do properly). See the examples in t/t55mdata.t for how to use these methods. Exercise for the reader: Extend '_columns' to get reports on the columns in remote synonyms, including relocated remote synonyms where the original referenced site now forwards the name to a third site!

Note that the return values from this are in the same format as found in the system catalogues and are not necessary suitable for directly embedding in an SQL statement.

The ix_map_tablename method

This method is used internally to map the owner, table (and optionally column) names from the format found in the system catalog to a format that can be used in an SQL statement. The difference is important - and can be substantial.

    $sql = ix_map_tablename($owner, $table [, $column]);

The owner name will be enclosed in double quotes; if it contains double quotes, those will be doubled up as required by SQL. The table name will only be enclosed in double quotes if it is not a valid C identifier (meaning, it starts with an alphabetic character or underscore, and continues with alphanumeric characters or underscores). If it is enclosed in double quotes, any embedded double quotes are doubled up. If provided, the column name is given the same treatment as the table name.

The ix_table_info method

    $sth = ix_table_info($dbh);

The ix_table_info method returns a statement handle for the given database handle that will return a description of all the tables in the database. The description of the data complies with a very old version the requirements of the DBI table_info method.

Expect this function to change!

The ix_delimit_identifier method

    $id = ix_delimit_identifier($id);

The ix_delimit_identifier encloses the given argument in double quotes, and doubles up any embedded double quotes, and returns the delimited identifier. This converts a value from the system catalog into a form that can be used in an SQL statement provided $ENV{DELIMIDENT} is set.

The ix_cond_delimit_identifier method

    $id = ix_cond_delimit_identifier($id);

The ix_cond_delimit_identifier calls ix_delimit_identifier on the argument if the argument is not a valid C identifier. This converts a value from the system catalog into a form that can be used in an SQL statement if $ENV{DELIMIDENT} is set, but avoids converting values that do not have to be delimited to maximize the chance of it working when $ENV{DELIMIDENT{ is not set.

The ix_undelimit_identifier method

    $id = ix_undelimit_identifier($id);

The ix_undelimit_identifier converts a delimited identifier into the form that would be found in the system catalog, and returns other identifiers unchanged. A delimited identifier consists of a double quote, a sequence of either not duoble quotes or pairs of double quotes and a trailing double quote. Note that neither "owner"."table" nor "owner".table qualifies as a delimited identifier.

The resulting value is suitable for use with a placeholder in a query on the system catalog. If the value is to be embedded in a string literal as part of a query, then it needs to be escaped with single quotes using the DBI-standard $h->quote method so that any embedded single quotes are doubled up correctly.

AUTHOR

Jonathan Leffler (jleffler@google.com)

SEE ALSO

perl(1)

Using 'perldoc', read the pages on:

• DBI - main documentation on Perl DBI

• DBI::FAQ - Separately installable module of Frequently Asked Questions

• DBD::Informix - The Perl DBI Driver for Informix

cpanm DBD::Informix

perl -MCPAN -e shell
install DBD::Informix