MySql through Perl

Posted on 2003-03-06
Medium Priority
Last Modified: 2013-12-25
Alright, I'm trying to use Perl to draw info from a web form and use it to create a new record in a database, the MySql statement works just fine if I use it manually in a SQL editor, but the CGI script just stops dead at the portion of the script that has the useage of the SQL statement in it, the problematic part is as follows:

$dbh = DBI->connect("dbi:mysqlPP:database=cvdvbe;host=", "fosters_web", "webaccess", {'RaiseError' => 1}) or
     dienice("Can't connect: ",$dbh->errstr);

$sth = $dbh->prepare("insert into net_members values(NULL, 'new', '$b_name', '$b_add_1', '$b_add_2', '$b_city', '$b_state', '$b_zip', '$b_url', '$b_phone_a', '$b_phone_b', '$b_phone_c', '$b_phone_d', '$b_fax_a', '$b_fax_b', '$b_fax_c', '$b_contact', '$b_con_cell_a', '$b_con_cell_b', '$b_con_cell_c', '$b_con_email', '$b_naics', '$b_dvbe_num', '$b_dgs_num', '$b_dgs_exp_m', '$b_dgs_exp_d', '$b_dgs_exp_y', '$b_sb', '$b_sb_exp_m', '$b_sb_exp_d', '$b_sb_exp_y', '$b_mb', '$b_cmas', '$b_cmas_num', '$b_cmas_exp_m', '$b_cmas_exp_d', '$b_cmas_exp_y', '$b_dab_num', '$b_year_est');")


$rv = $sth->execute;

I don't know where the error is at exactly, since it doesn't generate an error, it just stops dead, doesn't go onto the next script, and doesn't create the entry, so can someone help me fix this, or tell me another way to do it?
Question by:InsanityInc
LVL 51

Expert Comment

ID: 8087601

Accepted Solution

Itatsumaki earned 400 total points
ID: 8090952
Nope, it isn't 'NULL'.

DBI needs to take an undef to insert a null.  So you need to write:
my $temp = undef;
and pass that in as your first parameter.

Also, I *strongly* recommend that you go ahead and put all those variables into an array and use place-holders.  This approach is much more efficient for the DB.  Your code would look like:

my $sql = '
INSERT INTO net_members
VALUES ('.('?,' x 38).'?)';
my $sth = $dbh->prepare($sql);

Finally I also recommend that you explicitly specify your column names in the insert SQL:
INSERT INTO net_members(

Author Comment

ID: 8094024
Well, it turns out everything was fine, I just forgot use DBI and use CGI at the top.

Expert Comment

ID: 8185834

It is toooooo easy to do that!

Itatsumaki's advice is still good though...  your script will execute better.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question