Richard,
> Hello,
>
> I'm brand new to this group and I have a question (If this isn't the
> group for asking questions about using the Perl, MySQL, and DBI can
> someone point me in the right direction.)
>
> I have an existing script that writes information to a text based
> database and sends an email based on a form submission. I am now
> trying to get it to write to a mySQL database as well. I've tested
> the sub for writing to mysql numerous times - it works fine. However,
> when I integrate it into the existing script one of two things happens
> (I've spent several days banging my head on this one -- to no avail --
> aghh!).
>
> 1. If I call the formtoMySQL sub first it writes to the db perfectly
> but then I get a 500 error and it doesn't send an email or write to
> the text db. 2. If I call the formtoMySQL sub after I call the
> formtoDB sub it works but I end up with empty fields in mySQL other
> then downloaded. (downloaded is the only variable that is being set
> with a string rather then a variable.) It does write to the text db
> but it doesn't send the email.
>
> What I think is happening is this:
>
> In #2 After the script goes through the first few subs the form info
> is no longer in a format that I can grab using CGI.PM, this is why I
> get the empy strings in the db. In #1 vice versa... Can anybody
> point out the proper way to set the variables that are inserted into
> mySQL?
>
> Also, if anyone can shed light on why the email isn't sending that
> would also be very helpful.
Your program crashes somewhere and you don't know where, because you
don't implement proper error handling.
> Best Regards and Thanks in Advance,
>
> Richard
This is merely a CGI/general Perl topic.
These are the spots I found at first glance:
- comment in use strict ( I know I'm boring );
- you never check the return code of system calls (especially the
open()s). Let your script die() on failed open()s, so CGI::Carp can
tell you what happened.
- you are using some self-pasted/hacked &readParse and GGI.pm in
parallel. Switch to CGI only . If you want all the parameters in a
hash, use CGI's Vars() method.
- you are unnecessarily double-quoting scalars
- You do not run your script under -w or use warnings, otherwise
there should be an error pointing to double my - declaration of $dbh.
- this script contains too many irrelevant code concerning the
problem. Shrink it and repost.
For more DBI-specific comments see formtoMySQL()
HTH
Bodo
> ======================================================================
> ==================================================
>
> Here is my script:
>
> #!/usr/bin/perl5
>
> use DBI;
> use CGI qw(:standard escapeHTML);
> use CGI::Carp qw(fatalsToBrowser);
> # use strict;
>
> chdir ('../cgi-data/'); #NOTE: Line 114 = mail functions
>
>
> &readParse;
> &Validate;
> &Mail;
> &formToDB;
> &formtoMySQL;
> &redirect;
>
> #print "Location: $in{'redirect'}\n\n";
> #----------- Subroutines ---------------
>
> sub formtoMySQL {
>
> my $dbh;
>
> ### dsn, user, password variables removed for security reasons
> ### ###########
>
> my $title = $in{'12.E-mail'}
> my $first_name = param ("02.First Name");
> my $last_name = param ("03.Last Name");
> my $address = param ("04.Address");
> my $city = param ("05.City");
> my $state = param ("06.State");
> my $province = param ("07.Province");
> my $zip = param ("08.Zip");
> my $country = param ("09.Country");
> my $contact = param ("10.What is the best way to contact you\?"); my
> $telephone = param ("11.Telephone"); my $email = param
> (escapeHTML("12.E-mail")); my $additional_info = param
> ("14.Comments"); my $user_pass = param ("password"); my @newsletters =
> param ("13.Newsletters"); my $downloaded = "no";
>
>
> #Create DataBase Handle
> my $dbh = DBI->connect($dsn, $user, $password);
double usage of my on $dbh.
Add error capture:
$dbh = DBI->connect($dsn, $user, $password) or die DBI::errstr();
Switch RaiseError on:
$dbh->{RaiseError} = 1;
> my $stmt = qq{INSERT INTO newsletter_test SET
> title = "$title",
> first_name = "$first_name",
> last_name = "$last_name",
> address = "$address",
> city = "$city",
> state = "$state",
> province = "$province",
> zip = "$zip",
> country = "$country",
> contact = "$contact",
> telephone = "$telephone",
> email = "$email",
> newsletters = "@newsletters",
> additional_info = "$additional_info",
> user_pass = "$user_pass",
> downloaded = "$downloaded"};
You should map your parameters into an array @values and use
placeholders here with the "INSERT INTO table (field1, field2, ..)
VALUES (?, ...)" syntax. If the form values contain double quotes for
some reason, your program will crash here.
Place the insertion into an eval, so CGI::Carp can promt errors if
there are any:
eval {
my $sth = $dbh->prepare($stmt); # is the SQL OK ?
$sth->execute(@values); # are the values OK ?
$dbh->disconnect;
};
die $@ if $@;
>
>
> $dbh->do($stmt);
>
>
> $dbh->disconnect();
> }
>
>
> sub redirect {
>
---snip---
