MySQL::Handler - Builds upon DBD::mysql for advanced CGI web apps
MySQL::Handler builds upon the foundation set by DBI and DBD::mysql to create a superset of methods for tying together some of the basic interface concepts of DB management when used in a web server environment. MySQL::Handler is meant to build upon the strengths of DBD::mysql and DBI and add common usability features for a variety of Internet applications.
MySQL::Handler encapsulates error message handling, information message handling, simple caching of requests through a complete iteration of a server CGI request. You will also find some key elements that hook the CGI class to the DBI class to simplify data IO to & from web forms and dynamic pages.
# Instantiate Object
#
use MySQL::Handler;
my $DB = MySQL::Handler->new(dbname=>'products',dbuser=>'MySQL',dbpass=>'msqlpassword');
# 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
);
CGI::Carp CGI::Util Class::Struct DBD::mysql DBI
None by default.
Create a new MySQL::Handler object.
dbname => name of the database to connect to dbuser => MySQL user dbpass => password for that user
Get/set the data hash - this is where data fields are stored for the active record.
Returns the database handle for the DB connection.
Get/set MySQL user's password.
Get/set database name. Simple string name of the database.
Get/set MySQL username.
Returns the statement handle for the active record selection.
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 serial primary key to auto-increment our keys when adding new records.
--
-- Table: xyz
--
CREATE TABLE xyz (
xyz_pkid serial primary key,
xyz_update timestamp default now(),
xyz_ipadd char(32)
);
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 MySQL 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 = '.'.
CGI => a CGI object from the CGI:: module
DBKEY => the name of the key field within the table
defaults to MySQL::Handler Object Property <table>!PGHkeyfld
must be provided
- or -
the <table>!PGHkeyfld option must have
been setup when creating a new MySQL::Handler object
TABLE => the name of the table to play with
CGISTART or hrCGIMAP must be set (see below)
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 MySQL::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 MySQL::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
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.
1 for success, get message with lastinfo() 0 for failure, get message with lasterror()
Do DBH Command and log any errors to the log file.
[0] = SQL command
[1] = Die on error
[2] = return error on 0 records affected
[3] = quiet mode (don't log via carp)
Set the object 'errortype' data element to 'simple' for short error messages.
i.e.
$self->data('errortype') = 'simple';
1 for success 0 for failure, get message with lasterror
Retreive a field from the specified table.
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 MySQL::Handler object.
WHERE => Use this where clause to select the record instead of the key FORCE => Force Reload
The value of the field.
Returns 0 and lasterror() is set to a value if an error occurs
lasterror() is blank if there was no error
my $objPGDATA = new MySQL::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");
Retrieves the record in a hash reference with uppercase field names.
rtype not set or set to 'HASHREF',
Calls fetchrow_hashref('NAME_uc') from the specified SQL statement.
rtype not set or set to 'ARRAY',
Calls fetchrow_array() from the specified SQL statement.
rtype not set or set to 'ITEM',
Calls fetchrow() from the specified SQL statement.
[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' or 'ITEM' - type of structure to return data in
[2] or -finish set to '1' to close the named statement handle after returning the data
the hashref or array or scaler on success 0 for failure, get message with lasterror
Retrieve the latest error produced by a MySQL::Handler object.
The error message
Retrieve the latest info message produced by a MySQL::Handler object.
The info message
Retrieve a named statement handle
The handle, as requested.
Prepare an SQL statement and returns the statement handle, log errors if any.
[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
1 for success
Same as PrepLE but also executes the SQL statement
[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
1 for success
Quote a parameter for SQL processing via the DBI::quote() function Sets the data handle if necessary.
Using these methods without understanding the implications of playing with their values can wreak havoc on the code. Use with caution...
Internal function to set data handles Returns Data Handle If you don't want the MySQL username and password littering your perl code, create a subclass that overrides SetDH with DB specific connection info.
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.
[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
Populates the hash refered to in the first param with keys & values
An object of type class, newly instantiated if necessary.
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};
}
Prepare a hash reference for mapping CGI parms to DB fields typically used with AddUpdate() from MySQL::Handler.
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
@boolist = qw(form_field1 form_field2); $item->CGIMap(CGI => $objCGI, hrCGIMAP=>\%cgimap, CGISTART=>'cont_', CGIKEY=>'cont_id', BOOLEANS=>\@boolist);
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 MySQL::Handler;
my $DB = MySQL::Handler->new(dbname=>'products',dbuser=>'MySQL',dbpass=>'pgpassword');
$DB->PrepLEX('SELECT * FROM products');
# Named Example
#
use MySQL::Handler;
my $DB = MySQL::Handler->new(dbname=>'products',dbuser=>'MySQL',dbpass=>'pgpassword');
$DB->PrepLEX( -cmd => 'SELECT * FROM products' );
# Instantiate Object
#
use MySQL::Handler;
my $DB = MySQL::Handler->new(dbname=>'products',dbuser=>'MySQL',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
);
Lance Cleveland, Advanced Internet Technology Consultant Contact info@charlestonsw.com for more info.
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
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.
(c) 2006, 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
v2.1 - Apr 2006
Fixed problem with SetDH - database handle management
v2.0 - Feb 2006
Moved CGI::Carp outside of the package to prevent perl -w warnings
v1.9 - Feb 2006 (yup, already)
Update Field() to prevent SIGV error when WHERE clause causes error on statement
Field() now returns 0 + lasterror() set to value if failed execute
returns fldval + lasterror() is blank if execution OK
v1.8 - Feb 2006
MySQL::Handler created from Postgres::Handler module
