NAME

Postgres::Handler - Accessors for PostgreSQL data (1.1)

DESCRIPTION

Accessors for PostgreSQL data. Simplifies data access through a series of standard class methods.

SYNOPSIS

# Instantiate Object
#
use Postgres::Handler;
my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

# Retrieve Data & List Records
#
$DB->PrepLEX('SELECT * FROM products');
while ($item=$DB->GetRecord()) {
    print "$item->{PROD_ID}\t$item->{PROD_TITLE}\t$item->{PROD_QTY}\n";
}

# Add / Update Record based on CGI Form
# assuming objCGI is an instatiated CGI object
# if the CGI param 'prod_id' is set we update
# if it is not set we add
#
my %cgimap;
foreach ('prod_id','prod_title','prod_qty') { $cgimap{$_} = $_; }
$DB->AddUpdate( CGI=>$objCGI     , CGIKEY=>'prod_id', 
                TABLE=>'products', DBKEY=>'prod_id',
                hrCGIMAP=>\%cgimap
               );

REQUIRES

CGI::Carp
CGI::Util
Class::Struct
DBI

EXPORT

None by default.

DATA ACCESS METHODS

new()

Create a new Postgres::Handler object.

Parameters

dbname => name of the database to connect to

dbuser => postgres user

dbpass => password for that user

data()

Get/set the data hash - this is where data fields are stored
for the active record.

dbh()

Returns the database handle for the DB connection.

dbpass()

Get/set postgres user's password.

dbname()

Get/set database name.
Simple string name of the database.

dbuser()

Get/set postgres username.

sth()

Returns the statement handle for the active record selection.

PUBLIC METHODS

#==============================================================================

#--------------------------------------------------------------------

AddUpdate()

Adds a new record or updates an existing record in the database
depending on whether or not a specific CGI parameter has been set.

Useful for processing a posted form that contains form fields
that match data fields.   Pre-populate the form field that contains
the database key field and an update occurs.  Set it to blank and
a new record is added.

Your database key field should have a default value that is unique
and should be set as type 'PRIMARY KEY'.  We always use the default
nextval('myspecialsequence') to auto-increment our keys when adding
new records.

If a key is provided but is doesn't match anything in the existing
data then the update fails, UNLESS... CHECKKEY=> 1 in which case it
will attempt to add the record.

Your CGI->DB key hash reference should look something like this:
%mymap = ( tablefld_name => 'form_name', tablefld_ssn => 'htmlform_ssn' );
And is passed with a simple \%mymap as the hrCGIMAP parameter to this function.
-or-
Even better, name your CGI form fields the same thing as your Postgres DB field
names.  Then you can skip the map altogether and just provide the CGISTART
variable.  All fields that start with the the CGISTART string will be mapped.
Want to map every field?  Set CGISTART = '.'.

Parameters (Required)

 CGI       => a CGI object from the CGI:: module

 DBKEY     => the name of the key field within the table
              defaults to Postgres::Handler Object Property <table>!PGHkeyfld
              must be provided 
				  - or -
			     the <table>!PGHkeyfld option must have
              been setup when creating a new Postgres::Handler object

 TABLE     => the name of the table to play with

 CGISTART or hrCGIMAP must be set (see below)

Parameters (Optional)

 CGISTART  => map all CGI object fields starting with this string
              into equivalently named database fields
				  only used when hrCGIMAP is not set

 CGIKEY    => the CGI parameter name that stores the data key
              defaults to DBKEY


 CHECKKEY  => set to 1 to perform ADD if the DBKEY is not found in the
              database.

 DBSTAMP   => the name of the timestamp field within the table
              defaults to Postgres::Handler Object Property <table>!PGHtimestamp

 DONTSTAMP => set to 1 to stop timestamping
              timestamp field must be set

 hrCGIMAP  => a reference to a hash that contains CGI params as keys and
              DB field names as values

 MD5   	  => the name of the md5 encrypted field within the table
              defaults to Postgres::Handler Object Property <table>!PGHmd5

 REQUIRED  => array reference pointing to array that holds list of CGI
              params that must contain data

 VERBOSE   => set to 1 to set lastinfo() = full command string
              otherwise returns 'INSERT' or 'UPDATE' on succesful execution

 BOOLEANS  => array reference pointing to the array that holds the list
              of database field booleans that we want to force to false
				  if not set by the equivalently named CGI field

Action

Either adds or updates a record in the specified table.

Record is added if CGI data key [1] is blank or if CHECKKEY is set
and the value of the key is not already in the database.

Record is updated if CGI data key [2] contains a value.

Returns

1 for success, get message with lastinfo()
0 for failure, get message with lasterror()

DoLE()

 Do DBH Command and log any errors to the log file.
	[0] = SQL command
	[1] = Die on error

Returns

1 for success
0 for failure, get message with lasterror

Field()

Retreive a field from the specified table.

Parameters (required)

DATA     => Which data item to return, must be of form "table!field"

KEY      => The table key to lookup in the database
              Used to determine if our current record is still valid.
              Also used as default for WHERE, key value is searched for 
              in the PGHkeyfld that has been set for the Postgres::Handler object.

Parameters (optional)

WHERE	 => Use this where clause to select the record instead of the key

Returns

The value of the field.

Example

my $objPGDATA = new Postgres::Handler::HTML ('mytable!PGHkeyfld' => 'id');
my $lookupID = '01123';
my $data = $objPGDATA->Field(DATA=>'mytable!prod_title', KEY=>$lookupID);

my $lookupSKU = 'SKU-MYITEM-LG';
my $data = $objPGDATA->Field(DATA=>'mytable!prod_title', WHERE=>"sku=$lookupSKU");

GetRecord()

Retrieves the record in a hash reference with uppercase field names.
Calls fetchrow_hashref('NAME_uc') from the specified SQL statement.

Parameters

[0] or -name     select from the named statement handle,
                 if not set defaults to the last active statement handle

[1] or -rtype    'HASHREF' (default) or 'ARRAY' - type of structure to return data in

[2] or -finish   set to '1' to close the named statement handle after returning the data

Returns

the hashref or array on success
0 for failure, get message with lasterror

lasterror()

Retrieve the latest error produced by a Postgres::Handler object.

Returns

The error message

lastinfo()

Retrieve the latest info message produced by a Postgres::Handler object.

Returns

The info message

nsth()

Retrieve a named statement handle

Returns

The handle, as requested.

PrepLE()

Prepare an SQL statement and returns the statement handle, log errors if any.

Parameters (positional or named)

[0] or -cmd 	- required -statement
[1] or -exec	- execute flag (PREPLE) or die flag (PREPLEX)
[2] or -die		- die flag     (PREPLE) or null     (PREPLEX)
[3] or -param	- single parameter passed to execute 
[4] or -name	- store the statement handle under this name

Returns

1 for success

PrepLEX()

Same as PrepLE but also executes the SQL statement

Parameters (positional or named)

[0] or -cmd 	- required -statement
[1] or -die		- die flag     (PREPLE) or null     (PREPLEX)
[2] or -param	- single parameter passed to execute 
[3] or -name	- store the statement handle under this name

Returns

1 for success

Quote()

Quote a parameter for SQL processing via
the DBI::quote() function

Sets the data handle if necessary.

SEMI-PUBLIC METHODS

Using these methods without understanding the implications of playing with their
values can wreak havoc on the code.  Use with caution...

SetDH()

Internal function to set data handles
Returns Data Handle

If you don't want the postgres username and password
littering your perl code, create a subclass that
overrides SetDH with DB specific connection info.

SetMethodParms()

Allows for either ordered or positional parameters in
a method call AND allows the method to be called as EITHER
an instantiated object OR as an direct class call.

Parameters

[0] - self, the instantiated object
[1] - the class we are looking to instantiate if necessary
[2] - reference to hash that will get our named parameters
[3] - an array of the names of named parameters 
      IN THE ORDER that the positional parameters are expected to appear
[4] - extra parameters, positional or otherwise

Action

Populates the hash refered to in the first param with keys & values

Returns

An object of type class, newly instantiated if necessary.

Example

 sub MyMethod() {
 	my $self = shift;
	my %options;
		$self = SetMethodParms($self,'MYCLASS::SUBCLASS', \%options, [PARM1,PARM2,PARM3], @_ );
	print $options{PARM1} if ($options{PARM2} ne '');
	print $options{PARM3};
 }

CGIMap()

Prepare a hash reference for mapping CGI parms to DB fields
typically used with AddUpdate() from Postgres::Handler.

Parameters

hrCGIMAP 	- reference to hash that contains the map
CGI 			- the CGI object
CGISTART 	- map all fields starting with this text
CGIKEY 		- the cgi key field
BOOLEANS 	- address to list of boolean fields

Example

@boolist = qw(form_field1 form_field2);
$item->CGIMap(CGI => $objCGI, hrCGIMAP=>\%cgimap, CGISTART=>'cont_', CGIKEY=>'cont_id', BOOLEANS=>\@boolist);

NOTES

Some methods allow for parameters to be passed in via both positional and named formats.
If you decide to use named parameters with these "bi-modal" methods you must prefix the
parameter with a hyphen.

# Positional Example
#
use Postgres::Handler;
my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');
$DB->PrepLEX('SELECT * FROM products');

# Named Example
#
use Postgres::Handler;
my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');
$DB->PrepLEX(	-cmd	=>	'SELECT * FROM products'	);

EXAMPLES

# Instantiate Object
#
use Postgres::Handler;
my $DB = Postgres::Handler->new(dbname=>'products',dbuser=>'postgres',dbpass=>'pgpassword');

# Retrieve Data & List Records
#
$DB->PrepLEX('SELECT * FROM products');
while ($item=$DB->GetRecord()) {
	print $item->{PROD_ID}\t$item->{PROD_TITLE}\t$item->{PROD_QTY}\n";
}

# Add / Update Record based on CGI Form
# assuming objCGI is an instatiated CGI object
# if the CGI param 'prod_id' is set we update
# if it is not set we add
#
my %cgimap;
foreach ('prod_id','prod_title','prod_qty') { $cgimap{$_} = $_; }
$DB->AddUpdate( CGI=>$objCGI     , CGIKEY=>'prod_id', 
                TABLE=>'products', DBKEY=>'prod_id',
                hrCGIMAP=>\%cgimap
              );

AUTHOR

Lance Cleveland, Advanced Internet Technology Consultant
Contact info@charlestonsw.com for more info.

ABOUT CSA

Charleston Software Associates (CSA) is and advanced internet technology
consulting firm based in Charleston South Carolina.   We provide custom
software, database, and consulting services for small to mid-sized
businesses.

For more information, or to schedule a consult, visit our website at
www.CharlestonSW.com

CONTRIBUTIONS

Like the script and want to contribute?  
You can send payments via credit card or bank transfer using
PayPal and sending money to our info@charlestonsw.com PayPal address.

COPYRIGHT

(c) 2005, Charleston Software Associates
This script is covered by the GNU GENERAL PUBLIC LICENSE.
View the license at http://www.charlestonsw.com/community/gpl.txt
or at http://www.gnu.org/copyleft/gpl.html

REVISION HISTORY

 v1.1 - Jun 9 2005
      pod updates
		Field() cache bug fix
		GetRecord() expanded, added finish option
		Moved from root "PGHandler" namespace to better-suited "Postgres::Handler"

 v0.9 - May 2 2005
      pod updates
		AddUpdate() updated, CGIKEY optional - defaults to DBKEY
		AddUpdate() updated, BOOLEANS feature added
		GetRecord() updated, added check for sth active before executing
		Field()	fixed hr cache bug and data bug and trap non-set hr issue

 v0.8 - Apr 26 2005
      Fixed GetRecord() (again) - needed to check $DBI::errstr not $err

 v0.7 - Apr 25 2005
      Added error check on ->Field to ensure hashref returned from getrecord
      Added CGIMAP method
      Invoke CGIMAP from within AddUpdate if missing map
      Fixed GetRecord Return system

 v0.5 - Apr/2005
      Added DBI error trap on DoLE function
      Added named statement handles for multiple/nested PrepLE(X) capability
      Added VERBOSE mode to AddUpdate
      Added NAME to retrieved named statements via GetRecord
      Updated FIELD to use named statement handles 

 v0.4 - Apr/2005
 		Fixed some stuff

 v0.3 - Apr/2005
      Added REQUIRED optional parameter to AddUpdate
      Improved documentation
      Quoted DBKEY on add/update to handle non-numeric keys

 v0.2 - Mar/2005 - 
      Added error messages to object
      Fixed issues with Class:Struct and the object properties
      Updated AddUpdate to use named parameters (hash) for clarity

 v0.1 - Dec/2004
      Initial private release

To install Postgres::Handler, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Postgres::Handler

CPAN shell

perl -MCPAN -e shell
install Postgres::Handler

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

DESCRIPTION

SYNOPSIS

REQUIRES

EXPORT

DATA ACCESS METHODS

new()

data()

dbh()

dbpass()

dbname()

dbuser()

sth()

PUBLIC METHODS

AddUpdate()

DoLE()

Field()

GetRecord()

lasterror()

lastinfo()

nsth()

PrepLE()

PrepLEX()

Quote()

SEMI-PUBLIC METHODS

SetDH()

SetMethodParms()

CGIMap()

NOTES

EXAMPLES

AUTHOR

ABOUT CSA

CONTRIBUTIONS

COPYRIGHT

REVISION HISTORY

Module Install Instructions