.com Solutions Inc. - logo  
Products - Header Buttons
.
Repgen bullet item 7b Installgen bullet item 7b FmPro Migratorbullet item 7b CGIScripter
.
.
.

.
.

CGIScripter - MySQL Files - Title Graphic

CGIScripter Features and Benefits

CGIScripter Demo Available for download...

Bookmark This Page

email a friend

Flowchart

.

#!/usr/bin/perl
# Script: example_update_record_validate1
# Features: This Perl program accepts web submitted
# form data from the update script, provides
# data validation then updates the specified
# record in the database.
#
# Requirements:
# Perl DBI module
# Perl DBD::mysql module
# MySQL client software must be installed on the
# webserver running this program.
#
# Database Connection:
# MySQL Connection: database=mysql1:host=mysqlhost1:port=3306
#
# External Files:
# Update Script: example_update_record1
# HTML Success Page: http://www.dotcomsolutionsinc.net/success.html
#
#
#
# Usage: Update Web Form Submits To: http://www.dotcomsolutionsinc.net/cgi-bin/example_update_record_validate1
#
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 7-6-2003 dsimpson Initial Release
#
# This output file was created by CGIScripter version 1.48 on Sat Jul 12 10:14:55 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
#
use strict;
use DBI qw(:sql_types);
use CGI qw(:standard);

$CGI::POST_MAX=10000; # max post size - to avoid denial of service attacks
$CGI::DISABLE_UPLOADS = 0; # 1 = no uploads of files allowed via CGI.pm - for security
my $db_connect_string = 'database=mysql1:host=mysqlhost1:port=3306';
my $database_tablename = 'asset_management2';
my $schema_name = 'mysqluser1';
my $schema_password = 'mysqluser1pwd';
my $debug = 0; # DBI tracing enable/disable
my $long_readlength = 10000; # maximum number of bytes for character and binary large object types data inserted into database - increase this value as needed
my $global_validation_error = 0; # 0 = no error, 1 = error occurred with data validation
my $global_validation_error_text = ''; # The accumulated error text gets stored here
my $primary_key_column = 'item';
my @list_of_update_fields = (); # Dynamically built list of update fields
my $update_fields_text = ''; # List of update fields with bind variable placeholders
my $number_of_update_fields = 0; # The number of update fields which contain data
my $dbi_bind_param_text = ''; # list of DBI bind parameter statements
my $bind_param_count = 1; # Bind parameter counter starts at 1
my @global_error_fields_list = (0); # The list of fields for which errors have been found
my $static_success_html_page = 1; # 1 = use static success page, 0 = use dynamic success page
my $static_success_html_page_url = 'http://www.dotcomsolutionsinc.net/success.html'; # URL to static HTML submit success page - if used
my $global_database_error = 0;
my $dynamic_success_page_html =<<"EOF"; # HTML for the body of the dynamic html page
Thank you for your order.
EOF
# ---------- Alternate HTML Header/Footer -----------
# This HTML page Header/Footer info is used by default
my $html_header =<<"EOF"; # default HTML header
<html>
<head>
<title>example update validation</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000" >
EOF
my $html_footer =<<"EOF"; # default HTML footer
</body>
</html>
EOF
my $use_external_html = 0; # 1 = read HTML from external files, 0 = use default HTML
my $external_html_header_filename = 'example_header1.html';
my $external_html_footer_filename = 'example_footer1.html';
if ($use_external_html == 1)
{
{
# set input line separator to undef to read whole file at once
local $/ = undef;

# read HTML header from external file
open (FILE1,"$external_html_header_filename") || warn ("Could not open input file $external_html_header_filename for reading. Using default header HTML instead of external file.");
$html_header = <FILE1>;
# close the input file
close (FILE1);

# read HTML footer from external file
open (FILE1,"$external_html_footer_filename") || warn ("Could not open input file $external_html_footer_filename for reading. Using default footer HTML instead of external file.");
$html_footer = <FILE1>;
# close the input file
close (FILE1);
}
}

# ---------- Displayed Field Names -----------
my $item_display_as = 'Item to Enter';
my $category_display_as = 'Category of Item';
my $picture_display_as = 'Picture';
my $model_display_as = 'Model';
my $serial_number_display_as = 'Serial Number';
my $information_display_as = 'Information';
my $date_purchased_display_as = 'Date Purchased';
my $depreciation_display_as = 'depreciation';
my $assigned_display_display_as = 'assigned_display';
my $depreciation_life_display_as = 'depreciation_life';
my $remaining_life_display_as = 'remaining_life';
my $cost_display_as = 'cost';
my $book_value_display_as = 'book_value';

# ---------- Custom Data Validation Error Messages -----------
my $item_custom_error = '';
my $category_custom_error = '';
my $picture_custom_error = '';
my $model_custom_error = '';
my $serial_number_custom_error = '';
my $information_custom_error = '';
my $date_purchased_custom_error = '';
my $depreciation_custom_error = '';
my $assigned_display_custom_error = '';
my $depreciation_life_custom_error = '';
my $remaining_life_custom_error = '';
my $cost_custom_error = '';
my $book_value_custom_error = 'Book Value must contain a number.';

# ---------- validate_data_not_empty -----------
# This sub validates field data - Not Empty validation
# Parameters: field data, displayed field name, custom validation error text/html
sub validate_data_not_empty($$$)
{
my ($field_data, $field_name_display, $error_text) = @_;

if ($field_data eq '')
{
# no data supplied - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display must not be empty.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_char_only -----------
# This sub validates field data - Characters Only validation
# Parameters: field data, displayed field name, custom validation error text/html
sub validate_data_char_only($$$)
{
my ($field_data, $field_name_display, $error_text) = @_;
my $temp_local_error = 0;

if ($field_data =~ /\W/)
{
# found characters beyond the range A-Za-z0-9
$temp_local_error = 1;
$global_validation_error = 1;
}
if ($field_data =~ /\d/)
{
# found numeric characters - not allowed
$temp_local_error = 1;
$global_validation_error = 1;
}

if ($temp_local_error == 1)
{
# error occurred - set global flag and append error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display field must only contain characters A-Za-z.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_numbers_only -----------
# This sub validates field data - Numbers Only validation
# Parameters: field data, displayed field name, custom validation error text/html
sub validate_data_numbers_only($$$)
{
my ($field_data, $field_name_display, $error_text) = @_;

if ($field_data =~ /\D/)
{
# non numeric data supplied - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display field must only contain numbers.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_alphanumeric -----------
# This sub validates field data - Letters and Numbers Only validation
# Parameters: field data, displayed field name, custom validation error text/html
sub validate_data_alphanumeric($$$)
{
my ($field_data, $field_name_display, $error_text) = @_;

if ($field_data =~ /\W/)
{
# non alpha-numeric data supplied - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display field must only contain the characters a-zA-Z0-9.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_email -----------
# This sub validates field data - Email Address validation
# Parameters: field data, displayed field name, custom validation error text/html
sub validate_data_email($$$)
{
my ($field_data, $field_name_display, $error_text) = @_;

if ($field_data !~ /.+\@.+\..+/)
{
# non email address data supplied - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display field must contain an email address.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_numeric_range_low -----------
# This sub validates field data - Numeric Range Low validation
# Parameters: field data, displayed field name, custom validation error text/html, range limit
sub validate_data_numeric_range_low($$$$)
{
my ($field_data, $field_name_display, $error_text, $range_limit) = @_;

if ($field_data < $range_limit)
{
# numeric value lower than specified range - set error flag
$global_validation_error = 1;
# add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The contents of the $field_name_display field must not be less than $range_limit.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_numeric_range_high -----------
# This sub validates field data - Numeric Range High validation
# Parameters: field data, displayed field name, custom validation error text/html, range limit
sub validate_data_numeric_range_high($$$$)
{
my ($field_data, $field_name_display, $error_text, $range_limit) = @_;

if ($field_data > $range_limit)
{
# numeric value higher than specified range - set error flag
$global_validation_error = 1;
# add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The contents of the $field_name_display field must not be more than $range_limit.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_length_min -----------
# This sub validates field data - Minimum Length validation
# Parameters: field data, displayed field name, custom validation error text/html, range limit
sub validate_data_length_min($$$$)
{
my ($field_data, $field_name_display, $error_text, $range_limit) = @_;
my $plural_error_text = 's';

if (length($field_data) < $range_limit)
{
# number of characters less than specified range - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$plural_error_text = '' if $range_limit == 1; # use proper English - if only 1 character
$global_validation_error_text .= "The $field_name_display field must contain at least $range_limit character$plural_error_text.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- validate_data_length_max -----------
# This sub validates field data - Maximum Length validation
# Parameters: field data, displayed field name, custom validation error text/html, range limit
sub validate_data_length_max($$$$)
{
my ($field_data, $field_name_display, $error_text, $range_limit) = @_;

if (length($field_data) > $range_limit)
{
# number of characters greater than specified range - set error flag
$global_validation_error = 1;
#add to error text
if (length($error_text) < 2)
{
# if no meaningfull error text provided - use default
$global_validation_error_text .= "The $field_name_display field must not contain more than $range_limit characters.<BR>";
}
else
{
# use the error text which was passed in
# Note: It is the user's responsibility to add the <BR> at the end
# of the error message text.
$global_validation_error_text .= $error_text;
}
}
}
# ---------- auto_enter_date_iso -----------
# This sub returns the current date in YYYY-MM-DD format
# Parameters: none
sub auto_enter_date_iso()
{
my $date_string = '';
my ($day,$month,$year) = (localtime) [3,4,5];
$month += 1;
$year += 1900;
$date_string = $year . "-" . $month . "-" . $day;
return $date_string;
}
# ---------- auto_enter_date -----------
# This sub returns the current date in MM-DD-YYYY format
# Parameters: none
sub auto_enter_date()
{
my $date_string = '';
my ($day,$month,$year) = (localtime) [3,4,5];
$month += 1;
$year += 1900;
$date_string = $month . "-" . $day . "-" . $year;
return $date_string;
}
# ---------- auto_enter_time -----------
# This sub returns the current local time in HH24:MM:SS format
# Parameters: none
sub auto_enter_time()
{
my $time_string = '';
my ($hours,$minutes,$seconds) = (localtime) [2,1,0];
$time_string = $hours . ":" . $minutes . ":" . $seconds;
return $time_string;
}
# ---------- Get CGI Form Data -----------
# get parameters passed in via POST by using CGI.pm
my $cgi_category = param("category");
my $cgi_picture = param("picture");
my $cgi_picture_menu = param("picture_menu");
if ($cgi_picture)
{
if ($cgi_picture_menu eq 'Replace')
{
# get the new image from the browser
# set input record separator to undefined - locally
local $/ = undef;
# read the entire file
$cgi_picture = <$cgi_picture>;
}
if ($cgi_picture_menu eq 'Keep')
{
# set image variable to undef to prevent any update
undef $cgi_picture;
}
if (!defined $cgi_picture_menu)
{
# get the new image from the browser
# set input record separator to undefined - locally
local $/ = undef;
# read the entire file
$cgi_picture = <$cgi_picture>;
}
}
if ($cgi_picture_menu eq 'Remove')
{
# replace value of image variable with 1 byte of data
# A BLOB object is never expected to be equal to
# exactly one byte in length under normal conditions
# therefore this means to set the data column to empty
# after the bind variable statements are created.
$cgi_picture = ' ';
}
my $cgi_model = param("model");
my $cgi_information = param("information");
my $cgi_depreciation = param("depreciation");
my $cgi_assigned_display = param("assigned_display");
my $cgi_depreciation_life = param("depreciation_life");
my $cgi_remaining_life = param("remaining_life");
my $cgi_cost = param("cost");
my $cgi_book_value = param("book_value");

# ---------- Auto-Enter Field Values -----------


my $cgi_serial_number =<<'EOF';
Example Data
EOF
chop($cgi_serial_number);


# ---------- Define Bind Variable Types -----------
my $cgi_bind_vartype_category = '';
my $cgi_bind_vartype_picture = '';
my $cgi_bind_vartype_model = '';
my $cgi_bind_vartype_serial_number = '';
my $cgi_bind_vartype_information = '';
my $cgi_bind_vartype_depreciation = '';
my $cgi_bind_vartype_assigned_display = '';
my $cgi_bind_vartype_depreciation_life = '';
my $cgi_bind_vartype_remaining_life = '';
my $cgi_bind_vartype_cost = '';
my $cgi_bind_vartype_book_value = '';

# find fields containing data
if ($cgi_category)
{
push (@list_of_update_fields,"category");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_category$cgi_bind_vartype_category);\n";
$bind_param_count++;
}
if ($cgi_picture)
{
push (@list_of_update_fields,"picture");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_picture$cgi_bind_vartype_picture);\n";
$bind_param_count++;
}
if ($cgi_model)
{
push (@list_of_update_fields,"model");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_model$cgi_bind_vartype_model);\n";
$bind_param_count++;
}
if ($cgi_serial_number)
{
push (@list_of_update_fields,"serial_number");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_serial_number$cgi_bind_vartype_serial_number);\n";
$bind_param_count++;
}
if ($cgi_information)
{
push (@list_of_update_fields,"information");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_information$cgi_bind_vartype_information);\n";
$bind_param_count++;
}
if ($cgi_depreciation)
{
push (@list_of_update_fields,"depreciation");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_depreciation$cgi_bind_vartype_depreciation);\n";
$bind_param_count++;
}
if ($cgi_assigned_display)
{
push (@list_of_update_fields,"assigned_display");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_assigned_display$cgi_bind_vartype_assigned_display);\n";
$bind_param_count++;
}
if ($cgi_depreciation_life)
{
push (@list_of_update_fields,"depreciation_life");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_depreciation_life$cgi_bind_vartype_depreciation_life);\n";
$bind_param_count++;
}
if ($cgi_remaining_life)
{
push (@list_of_update_fields,"remaining_life");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_remaining_life$cgi_bind_vartype_remaining_life);\n";
$bind_param_count++;
}
if ($cgi_cost)
{
push (@list_of_update_fields,"cost");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_cost$cgi_bind_vartype_cost);\n";
$bind_param_count++;
}
if ($cgi_book_value)
{
push (@list_of_update_fields,"book_value");
$dbi_bind_param_text .= "\$db_sth->bind_param($bind_param_count,\$cgi_book_value$cgi_bind_vartype_book_value);\n";
$bind_param_count++;
}

$number_of_update_fields = @list_of_update_fields;
if ($number_of_update_fields > 0)
{
for (my $update_loop=0;$update_loop < $number_of_update_fields; $update_loop++)
{
# build list of query fields
$update_fields_text .= $list_of_update_fields[$update_loop] . '=?, ';
}
}

# remove trailing space and comma
chop($update_fields_text);
chop($update_fields_text);

# bind_param_count still contains next bind_param value after end of loop.
# Create one more bind param statement for the primary key within the where clause
$dbi_bind_param_text .= '$db_sth->bind_param(' . $bind_param_count . ',$cgi_' . $primary_key_column . "); ";
my $cgi_item = param("item");

if (length($cgi_picture) == 1)
{
# Any BLOB field with a size equal to 1 byte means that
# the column data will be set to empty because the Remove menu
# option was selected in the web browser.
$cgi_picture = '';
}

# ---------- Perform All Validation -----------
# format: field name, displayed field name, custom validation error


&validate_data_numbers_only($cgi_depreciation, $depreciation_display_as, $depreciation_custom_error);
&validate_data_numbers_only($cgi_assigned_display, $assigned_display_display_as, $assigned_display_custom_error);
&validate_data_numbers_only($cgi_depreciation_life, $depreciation_life_display_as, $depreciation_life_custom_error);
&validate_data_numbers_only($cgi_remaining_life, $remaining_life_display_as, $remaining_life_custom_error);
&validate_data_numbers_only($cgi_cost, $cost_display_as, $cost_custom_error);
&validate_data_numbers_only($cgi_book_value, $book_value_display_as, $book_value_custom_error);

&validate_data_length_min($cgi_depreciation, $depreciation_display_as, $depreciation_custom_error, 1);
&validate_data_length_min($cgi_assigned_display, $assigned_display_display_as, $assigned_display_custom_error, 1);
&validate_data_length_min($cgi_depreciation_life, $depreciation_life_display_as, $depreciation_life_custom_error, 1);
&validate_data_length_min($cgi_remaining_life, $remaining_life_display_as, $remaining_life_custom_error, 1);
&validate_data_length_min($cgi_cost, $cost_display_as, $cost_custom_error, 1);
&validate_data_length_min($cgi_book_value, $book_value_display_as, $book_value_custom_error, 1);

# if errors found - print error text
if ($global_validation_error == 1)
{
# print header
print header();
print $html_header;
print "The following errors were found with your submission:<BR>";
# print error text
print $global_validation_error_text;

# print footer
print $html_footer;
exit;
}

# ---------- Update Data in Database -----------
print header() if ($static_success_html_page == 0);
print $html_header if ($static_success_html_page == 0);

if ($debug == 1)
{
# turn on DBI tracing
unlink 'dbitrace.log' if -e 'dbitrace.log';
DBI->trace(2, 'dbitrace.log');
}

my $db_dbh = DBI->connect ("dbi:mysql:$db_connect_string", "$schema_name", "$schema_password", {RaiseError => 0, PrintError => 1, AutoCommit => 1 })
or die "Can't connect to the MySQL $db_connect_string database: $DBI::errstr\n";
$db_dbh->{LongReadLen} = $long_readlength;
$db_dbh->{LongTruncOk} = 0;

$db_dbh->do("SET OPTION SQL_BIG_TABLES = 1");
my $db_sth = '';

$db_sth = $db_dbh->prepare("update $database_tablename set $update_fields_text where $primary_key_column = ?") or die " Database error:",$db_dbh->errstr(), "\n";

eval($dbi_bind_param_text);

$db_sth->execute() or warn $db_sth->errstr(); # check for error
if ($db_sth->errstr())
{
# set error status
$global_database_error = 1;
print $db_sth->errstr(); # print the error info
}
$db_sth->finish();
# disconnect from database
$db_dbh->disconnect or warn "Can't disconnect from the database $db_connect_string database: $DBI::errstr\n";

# ---------- Display Success Page -----------

if (($static_success_html_page == 0) and ($global_database_error == 0) )
{
# header was not already output
print header();
print $html_header;
print $dynamic_success_page_html;
print $html_footer;
}
elsif (($static_success_html_page == 1) and ($global_database_error == 0))
{
# redirect browser to static HTML page
print "Location: $static_success_html_page_url\n\n";
}

.

hline

. .

.

. .
 

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices

.
.   .
.
Home Products Services Downloads Order Support Contact