NAME

PeopleSoft - Procedural interface for working with PeopleSoft applications.

SYNOPSIS

 use PeopleSoft;
 my $dbh = get_dbh( $username, $password, $SID );
 my $tbl_name_aref = get_tbl_names( 'table_name_spec', $dbh);
 my $tbl_name_aref = where_from( $view_name, $dbh );
 if ( is_view($name, $dbh) )
 my $count = get_rec_count( $tbl_name, $dbh );
 if ( table_exists($tbl_name, $dbh) ) {...}
 my $metadata_href = get_fld_metadata_href( $tbl_name, $dbh );

DESCRIPTION

This module provides a set of simple table query and manipulation functions.

The following functions are provided (and exported) by this module:

get_dbh($username, $password, $SID)

The get_dbh() function will return a database handle (courtesy of DBI/DBD) for use in accessing the database. It returns undef if it fails.

get_table_names('table_name_spec', $dbh)

Get all the table names which match table_name_spec from the database tied to $dbh. table_name_spec should be in a form appropriate for insertion into a SQL where clause (e.g. 'PS_%').

Returns an empty list if it fails.

get_rec_count( $tbl_name, $dbh );

Returns the number of records in a table.

tbl_exists( $tbl_name, $dbh )

Tests for the existence of $tbl_name. If the table exists 1 is returned otherwise 0 is returned. Typical use would be:

if( tbl_exists($tbl_a, $dbh_a) and tbl_exists($tbl_b, $dbh_b) ) {...}

get_fld_metadata_href( $tbl_name, $dbh )

This function returns a reference to a hash keyed by field name. For every field in $tbl_name the hash contains attributes for: data type, precision and nullable. Typical use would be:

 $metadata = get_fld_metadata_href( $tbl, $dbh );
 foreach my $field ( keys( %{$metadata} ) ) {
   print "Field $field has data type $$metadata{$field}->{TYPE}\n";
   print "Field $field has size $$metadata{$field}->{PRECISION}\n";
   if ( $$metadata{$field}->{NULLABLE} ) { print "Field $field accepts nulls\n";
 }

make_ins_stmt($src_md_href,$dest_md_href,$tbl,$src_data_href)

This function returns a SQL insert statement. It is used to migrate data between tables with "slightly" different table structures. This is accomplished by inserting only data for fields that have the same name and data type. It then creates default values for not-nullable fields in the destinition table that otherwise would not get values. This is particularly useful in migrating data to different versions of PeopleSoft applications.

Typical usage is:

 $src_md_href = get_fld_metadata_href( $tbl, $src_dbh );
 $dest_md_href = get_fld_metadata_href( $tbl, $dest_dbh );

  while( $data_href = $sth->fetchrow_hashref ) {
     $insert_sql_script .= make_ins_stmt($src_md_href, $dest_md_href, $tbl, $data_href);
     $insert_sql_script .= "\n";
  }

where_from($view_name, $dbh)

This function returns a reference to an array of the names of all the tables which are used to create the view. This is useful when deriving table loading sequences.

is_view($name, $dbh)

This function returns true (i.e. 1) if the given name is the name of a view in the $dbh.