Cannot INSERT query string data into database

stevem · #1 (**permalink**) 02-20-06, 23:20

I have never used Perl before, so any help would be greatly appreciated! My script gets a query string, which I break into 130+ variables, and then I'm trying to INSERT that data into my database.

I blanked out the connection details, below, for this post only.

- Through testing, I verified that my connection connects OK.
- Through testing, I verified that my query string is being broken-up OK.
- I made sure that my database has fields established for each value shown in my INSERT, below.
- I checked my script for typos... couldn't find any.
- I previously made a simple INSERT command that only inserted 3 values into the database, and it worked OK.

1) Since I'm trying to INSERT 130+ values, should my INSERT command be broken into multiple INSERT commands? Is there a better way to submit so many values?
2) Can Perl recognize variable names that contain dashes, such as B01-Price ?
3) Is there any other reason that my INSERT command would fail, given my code?

Thank you for any help possible!

#!/usr/local/bin/perl
use CGI;
use DBI;

$dbh = DBI->connect('DBI:mysql:*******;host=localhost', '*******', '*******',
{ RaiseError => 1 }
);
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}
@nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/_/;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$FORM{$name} = $value;
}
$dbh->do('INSERT INTO sales (ID,Date,Day,Time,OrderNum,Email,First,Last,Compan y,Address,Address2,City,State,Zip,Country,Phone,Sh ipFirst,ShipLast,ShipCompany,ShipAddress,ShipAddre ss2,ShipCity,ShipState,ShipZip,ShipCountry,ShipPho ne,B01Quantity,B01Name,B01SKU,B01Subtotal,B02Quant ity,B02Name,B02SKU,B02Subtotal,B03Quantity,B03Name ,B03SKU,B03Subtotal,B04Quantity,B04Name,B04SKU,B04 Subtotal,B05Quantity,B05Name,B05SKU,B05Subtotal,B0 6Quantity,B06Name,B06SKU,B06Subtotal,B07Quantity,B 07Name,B07SKU,B07Subtotal,B08Quantity,B08Name,B08S KU,B08Subtotal,B09Quantity,B09Name,B09SKU,B09Subto tal,B10Quantity,B10Name,B10SKU,B10Subtotal,Product Total,TaxTotal,ShippingTotal,Coupons,GrandTotal,Or derInfo,EmailList,ProductDiskSentDate,Remarks) VALUES(1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?)', undef, $Date,$Day,$Time,$FORM{'O-OrderNum'},$FORM{'Email'},$FORM{'First'},$FORM{'La st'},$FORM{'Company'},$FORM{'Address'},$FORM{'Addr ess2'},$FORM{'City'},$FORM{'State'},$FORM{'Zip'},$ FORM{'Country'},$FORM{'Phone'},$FORM{'ShipFirst'}, $FORM{'ShipLast'},$FORM{'ShipCompany'},$FORM{'Ship Address'},$FORM{'ShipAddress2'},$FORM{'ShipCity'}, $FORM{'ShipState'},$FORM{'ShipZip'},$FORM{'ShipCou ntry'},$FORM{'ShipPhone'},$FORM{'B01-Quantity'},$FORM{'B01-Name'},$FORM{'B01-SKU'},$FORM{'B01-Sub-total'},$FORM{'B02-Quantity'},$FORM{'B02-Name'},$FORM{'B02-SKU'},$FORM{'B02-Sub-total'},$FORM{'B03-Quantity'},$FORM{'B03-Name'},$FORM{'B03-SKU'},$FORM{'B03-Sub-total'},$FORM{'B04-Quantity'},$FORM{'B04-Name'},$FORM{'B04-SKU'},$FORM{'B05-Sub-total'},$FORM{'B05-Quantity'},$FORM{'B05-Name'},$FORM{'B05-SKU'},$FORM{'B05-Sub-total'},$FORM{'B06-Quantity'},$FORM{'B06-Name'},$FORM{'B06-SKU'},$FORM{'B06-Sub-total'},$FORM{'B07-Quantity'},$FORM{'B07-Name'},$FORM{'B07-SKU'},$FORM{'B07-Sub-total'},$FORM{'B08-Quantity'},$FORM{'B08-Name'},$FORM{'B08-SKU'},$FORM{'B08-Sub-total'},$FORM{'B09-Quantity'},$FORM{'B09-Name'},$FORM{'B09-SKU'},$FORM{'B09-Sub-total'},$FORM{'B10-Quantity'},$FORM{'B10-Name'},$FORM{'B10-SKU'},$FORM{'B10-Sub-total'},$FORM{'O-ProductTotal'},$FORM{'O-TaxTotal'},$FORM{'O-ShippingTotal'},$FORM{'O-Coupons'},$FORM{'O-GrandTotal'},$FORM{'O-OrderInfo'},$FORM{'O-EmailList'},$FORM{'ProductDiskSentDate'},$FORM{'Re marks'});
$dbh->disconnect;

reneeb · #2 (**permalink**) 02-21-06, 00:43

please do not parse the query string yourself. Please use CGI.pm. You should print out the error messages.... Test this:

Code:

#!/usr/local/bin/perl
use CGI;
use DBI;

$dbh = DBI->connect('DBI:mysql:*******;host=localhost', '*******', '*******',);

my %FORM = $cgi->Vars();

my @cols = qw(ID Date Day Time OrderNum Email First Last Company
               Address Address2 City State Zip Country Phone ShipFirst               
               ShipLast ShipCompany ShipAddress ShipAddress2 
               ShipCity ShipState ShipZip ShipCountry ShipPho ne 
               B01Quantity B01Name B01SKU B01Subtotal B02Quantity 
               B02Name B02SKU B02Subtotal B03Quantity B03Name  
               B03SKU B03Subtotal B04Quantity B04Name B04SKU 
               B04Subtotal B05Quantity B05Name B05SKU B05Subtotal 
               B06Quantity B06Name B06SKU B06Subtotal B07Quantity 
               B07Name B07SKU B07Subtotal B08Quantity B08Name B08SKU 
               B08Subtotal B09Quantity B09Name B09SKU B09Subtotal                 
               B10Quantity B10Name B10SKU B10Subtotal ProductTotal  
               TaxTotal ShippingTotal Coupons GrandTotal OrderInfo EmailList  
               ProductDiskSentDate Remarks);

my $sth = $dbh->prepare('INSERT INTO sales () VALUES(1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?)') or die $dbh->errstr();

$sth->execute($Date,$Day,$Time,$FORM{'O-OrderNum'},$FORM{'Email'},$FORM{'First'},$FORM{'La st'},$FORM{'Company'},$FORM{'Address'},$FORM{'Addr ess2'},$FORM{'City'},$FORM{'State'},$FORM{'Zip'},$ FORM{'Country'},$FORM{'Phone'},$FORM{'ShipFirst'}, $FORM{'ShipLast'},$FORM{'ShipCompany'},$FORM{'Ship Address'},$FORM{'ShipAddress2'},$FORM{'ShipCity'}, $FORM{'ShipState'},$FORM{'ShipZip'},$FORM{'ShipCou ntry'},$FORM{'ShipPhone'},$FORM{'B01-Quantity'},$FORM{'B01-Name'},$FORM{'B01-SKU'},$FORM{'B01-Sub-total'},$FORM{'B02-Quantity'},$FORM{'B02-Name'},$FORM{'B02-SKU'},$FORM{'B02-Sub-total'},$FORM{'B03-Quantity'},$FORM{'B03-Name'},$FORM{'B03-SKU'},$FORM{'B03-Sub-total'},$FORM{'B04-Quantity'},$FORM{'B04-Name'},$FORM{'B04-SKU'},$FORM{'B05-Sub-total'},$FORM{'B05-Quantity'},$FORM{'B05-Name'},$FORM{'B05-SKU'},$FORM{'B05-Sub-total'},$FORM{'B06-Quantity'},$FORM{'B06-Name'},$FORM{'B06-SKU'},$FORM{'B06-Sub-total'},$FORM{'B07-Quantity'},$FORM{'B07-Name'},$FORM{'B07-SKU'},$FORM{'B07-Sub-total'},$FORM{'B08-Quantity'},$FORM{'B08-Name'},$FORM{'B08-SKU'},$FORM{'B08-Sub-total'},$FORM{'B09-Quantity'},$FORM{'B09-Name'},$FORM{'B09-SKU'},$FORM{'B09-Sub-total'},$FORM{'B10-Quantity'},$FORM{'B10-Name'},$FORM{'B10-SKU'},$FORM{'B10-Sub-total'},$FORM{'O-ProductTotal'},$FORM{'O-TaxTotal'},$FORM{'O-ShippingTotal'},$FORM{'O-Coupons'},$FORM{'O-GrandTotal'},$FORM{'O-OrderInfo'},$FORM{'O-EmailList'},$FORM{'ProductDiskSentDate'},$FORM{'Re marks'}) or die $dbh->errstr();

$dbh->disconnect;

reneeb · #3 (**permalink**) 02-21-06, 00:44

Please add code-tags to your post.

You have whitespaces in several column names...

KevinADC · #4 (**permalink**) 02-21-06, 02:01

you might need to create a new cgi object before calling the Vars function:

Code:

#!/usr/local/bin/perl
use CGI;
use DBI;

$dbh = DBI->connect('DBI:mysql:*******;host=localhost', '*******', '*******',);
my $cgi = new CGI;
my %FORM = $cgi->Vars();

senza_nome · #5 (**permalink**) 02-21-06, 04:10

Are statements like that still written?

I got so tired of trying to write code that was "sync'd" to a form and a table that I ended up creating an "autoloader" that uses the table meta data.
As long as the field names in the form and the field names in the table were the same, it composed the sql statement/and binds on the fly.
If the insert fails, it emals the form field names, the table field names, the SQL statement that it build, the associated binds and the error.

reneeb · #6 (**permalink**) 02-21-06, 04:26

Quote:

Originally Posted by KevinADC

you might need to create a new cgi object before calling the Vars function:

Code:

#!/usr/local/bin/perl
use CGI;
use DBI;

$dbh = DBI->connect('DBI:mysql:*******;host=localhost', '*******', '*******',);
my $cgi = new CGI;
my %FORM = $cgi->Vars();

Yes, you are right. I was in a hurry and forgot this important line! Thanks for pointing that mistake out...

Internet Services

Web Development

Digital Marketing

Consumer Tech