Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Unanswered: Cannot INSERT query string data into database

    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;

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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;
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    Please add code-tags to your post.

    You have whitespaces in several column names...
    board.perl-community.de - The German Perl-Community

  4. #4
    Join Date
    Feb 2006
    Posts
    56
    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();

  5. #5
    Join Date
    Jun 2004
    Location
    Nowhere Near You
    Posts
    89
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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...
    board.perl-community.de - The German Perl-Community

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •