Darin McBride
and 1 contributors


DB2::Table - Framework wrapper around tables using DBD::DB2


    package myTable;
    use DB2::Table;
    our @ISA = qw( DB2::Table );
    use myDB;
    use myTable;
    my $db = myDB->new;
    my $tbl = $db->get_table('myTable');
    my $row = $tbl->find($id);



Do not call this - you should get your table through your database object.


the key sub to override! The data must be a reference to an array of hashes. Each element (hash) in the array must contain certain keys, others are optional.


Column Name (must be upper case)


SQL type or one of:


This will be represented by a NOT NULL CHAR that is limited to 'Y' or 'N'. In perl, this will be auto-converted to perlish true/false values. An undef will be treated as expected in perl: as false.


As above, but NULLs will be permitted. In this case, an 'N' in the database will become a false, but defined, value. Only a NULL in the database will translate to undef in perl.


for CHAR, VARCHAR, etc.


optional stuff - NOT NULL, PRIMARY KEY, etc. (Should use primary rather than opts => 'PRIMARY KEY'.)


default value


true for the primary key


stuff that is placed in the table create independantly


For this column, will create a FOREIGN KEY statement. The value here is used during creation of the table, and should begin with the foreign table name and include any "ON DELETE", "ON UPDATE", etc., portions. This may change in the future where foreignkey will be itself another hashref with all these fields.


For this column, will create as a generated identity. If this is undef or the word 'default', the option will be (START WITH 0, INCREMENT BY 1, NO CACHE), otherwise it will use whatever you provide here.

This is somewhat based on a single column for a primary key, which is not necessarily the "right" thing to do in relational design, but sure as heck simplifies coding! NOTE: Other columns may be present, but would only be used by the subclass.


When allowing the framework to create your row type object because there is no backing module, we need to know what to derive it from. If you have a generic row type that is derived from DB2::Row that you want all your rows to be derived from, you can override this.

If all your empty Row types are derived from a single type that is not DB2::Row, you should create a single Table type and have all your tables derived from that. That is, to create a derivation tree for your row such as:

    DB2::Row -> My::Row -> My::UserR

your derivation tree for your tables should look like:

    DB2::Table -> My::Table -> My::User

And then My::Table can override get_base_row_type to return q(My::Row)


Gets the DB2::db object that contains this table


You need to override this. Must return the DB2 Schema to use for this table. Generally, you may want to derive a single "schema" class from DB2::Table which only overrides this method, and then derive each table in that schema from that class.


Creates a new DB2::Row object for this table. Called instead of the constructor for the DB2::Row object. Sets up defaults, etc. NOTE: this will not generate any identity column! We leave that up to the database, so we will retrieve that during the save before committing.


Should be obvious - a full count of all the rows in this table


Similar to count, except that the first parameter will be the SQL WHERE condition while the rest of the parameters will be the bind values for that WHERE condition.


Finds all rows with the primary column matching any of the parameters. For example, $tbl->find_id(1, 2, 10) will return an array of DB2::Row derived objects with all the data from 0-3 rows from this table, if the primary column for that row is either 1, 2, or 10.


Similar to find_id, the first parameter is the SQL WHERE condition while the rest of the parameters are the bind values for the WHERE condition.

In array context, will return the array of DB2::Row derived objects returned, whether empty or not.

In scalar context, will return undef if no rows are found, will return the single Row object if only one row is found, or an array ref if more than one row is found.


Similar to find_where, the first parameter is the tables to join and how they are joined (any '!!!' found will be replaced with the current table's full name), the second parameter is the where condition, if any, and the rest are bind values.


Internally used to set any prepare attributes. Parameter says what type of prepare this is, although the list is not finalised yet.


Internally used to cache statements. This may change to prepare if it is found to be useful.


Shortcuts to get the DBI err, errstr, and state's, respectively.


The table is what saves a row. If you've made changes to a row, this function will save it. Not really needed since the Row's destructor will save, but doesn't hurt.


Commits all current actions


Deletes the given row from the database.


Deletes a row based on its ID. To delete multiple IDs simultaneously, simply pass in an array ref.


Deletes rows based on the given WHERE clause. Further parameters are then bound to the DELETE statement.


Wrapper around performing an SQL SELECT statement.


  • Optional: Hashref of options. Options may include:


    This is the WITH clause tacked on to the front of the SELECT statement, if any. (!!! replacement as per SELECT_join is done on this.)

    Or, this can be a hashref:

        with => {
            temp2 => {
                fields => [ qw/empno firstnme/ ],
                as => q[SELECT EMPNO, FIRSTNAME FROM !!!,!XYZ! WHERE ...],
            temp1 => {
                as => q[...],

    This will create a WITH clause like this:

        WITH temp1 AS (...), temp2 (empno,firstname) AS (SELECT EMPNO, FIRSTNAME
        FROM !!!,!XYZ! WHERE ...

    (except that !!! and !XYZ! will be expanded in the context of the current table) which will then go in the front of the rest of the SELECT statement.


    If true, the DISTINCT keyword will be added prior to the column names resulting in a return set where each row is unique. Somewhat useless if the columns are all columns or include UNIQUE columns.


    Set the query up as a "FOR READ ONLY" statement (potential performance enhancement).


    This is either a string with the table names, or an array ref of table names. Used in joins.


    This is used in the prepare statement as extra options - see DBD::DB2 under the heading Statement Attributes. The value here must be a hashref ready to be passed in to the prepare function.

  • Arrayref of columns or string of columns, seperated by commas. For example:

        [ qw(col1 col2 col3) ]

    or 'col1,col2,col3'

  • Optional: Where-clause for SQL query.

  • Optional: Bind values for the where-clause - this is not an arrayref but the actual elements.

For example:

    $table-E<gt>SELECT({distinct=>1},[qw(col1 col2)],
                       'col3 in (?,?,?)', 'blah', 'burg', 'frob');

This will result in an SQL statement of:

    SELECT DISTINCT col1, col2 FROM myschema.mytable WHERE col3 in (?,?,?)

And ('blah', 'burg', 'frob') will be bound to the ?'s.


Wrapper around performing an SQL SELECT statement with distinct rows only returned. Otherwise, it's exactly the same as SELECT above


Wrapper around performing an SQL SELECT statement where you may be joining with other tables. The first argument is the columns you want, the second is the tables, and how they are to be joined, while the third is the WHERE condition. Further parameters are bind values. Any text matching '!!!' in the columns text will be replaced with this table's full table name. Any text matching '!(\S+?)!' will be replaced with $1's full table name.


The name of this table, excluding schema. This will default to the part of the current package after the last double-colon. For example, if your table is in package "myDB2::foo", then the table name will be "foo".


Shortcut to schema.table_name


Returns an array of all the column names, in order


Returns a hash ref which is all the data from data_order, but in no particular order (it's a hash, right?).


Gets information about a column or its data. First parameter is the column. Second parameter is the key (NAME, type, etc.). If the key is not given, a hash ref is returned with all the data for this column. If the key is given, only that scalar is returned.


Find the primary column. First time it is called, it will determine the primary column, and then it will cache this for later calls. If you want a table with no primary column, you must override this method to return undef.

If no column has the primary attribute, then the last column is defaulted to be the primary column.


Determine the generated identity column, if any. This is determined by looking for the string 'GENERATED ALWAYS AS IDENTITY' in the opts of the column. Again, this is cached on first use.


Check if the table already exists. Normally only called by create_table.


Creates the current table. Normally only called by DB2::db::create_table.


A hook that will allow you to initialise the table immediately after its creation. If the table is newly created, the only parameter will be 'CREATE'. If the table is being altered, the first parameter will be 'ALTER' while the rest of the parameters will be the list of columns added.

The default action is mildly dangerous. It grants full select, insert, update, and delete authority to the user 'nobody'. This is the user that many daemons, including the default Apache http daemon, run under. If you override this, you can do whatever you want, including nothing. This default was put in primarily because many perl DBI scripts are expected to also be CGI scripts, so this may make certain things easier. This does not change the fact that when this grant is executed you will need some admin authority on the database.