Hello Monks,

I'm writing a a bit of code to preform an extraction on an XML document. That bit isn't really relevant as I know the XML is being extracted appropriately and when printed to a text file is exactly what I want it to be. Suffice it to say I know I'm getting the data I need.

Because I'm dealing with a database with multiple tables and a variable number of columns within those tables, I want to be able to insert a variable number of items into each row. To this end, I've written the following code. I've excluded the object/methods that extract the data and compile it into a miniature database structure. This is the code for the extraction from the hash and the insert into the database. 

use strict;
use warnings;
use utf8;
use diagnostics;
use DBI;
use xml::twig;

        our %mirror;
        our %referential;
        our $table;
        our $rows;
        our $columns;

our  $dbh= connect_to_db() || die "Can't connect: $DBI::errstr\n";


         
my  $file ="poorlySTRUCTURED.xml";

my $twig=XML::Twig->new(
     twig_handlers=>
        {lots of handlers that call the elements I need});
        
        $twig->parsefile("$file");
        

        
        
sub connect_to_db
  { my $driver = "mysql";
      my $drh = DBI->install_driver($driver);
         my $dsn = "DBI:$driver:database=united;host=somehost;port=any
+portinastorm";
    my $dbh = DBI->connect($dsn, "root", "password", {AutoCommit=>1};
$dbh->{RaiseError}=1;);
    
    return( $dbh);
  }
        sub insertSKU{
    for my $Itemnumber (keys %mirror){
        for $table (keys %{$mirror{$Itemnumber}}){
            if ($table=~/skugroup/){
    $columns= (join ', ', keys %{$mirror{"$Itemnumber"}{"$table"}});
    $rows= (join ', ', values %{$mirror{"$Itemnumber"}{"$table"}}); 

#Now we sanitize the data for MySQL syntax.
    $columns =~s/\.|;|\/|\\|\|//g;
    $rows =~s/\.|;|\/|\\|\|//g;
    my $insert= $dbh->prepare("INSERT INTO $table ($columns) VALUES ($
+rows)GO");
    $insert->execute() || die "Insert failed \n $columns \n\n $rows";
        
        }                            }
    }
}

[download]
The error I get back is 
"You have an error in your SQL syntax; check the manual that correspon
+ds to your MySQL server version for the right syntax use near 'acetat
+e cover protectsdocument, Distinguished leatherette frame is accented
+ wit'

[download]
Any commas present in the data are removed when the value is extracted and I've verified that. I avoid using the bind_param method because I need to insert into multiple tables with a variable number of values. At this point I'm wondering if when using DBI to insert values into a database the values need to be quoted. I don't think I've missed any escape characters in sanitizing the data, and the MySQL website lists this as appropriate syntax for inserting multiple values with a single insert command. So what am I doing wrong? Do you need more information to help out?

In reply to Inserting to MySQL 4.1 database using Perl DBI by Mr.Churka

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.