Update: I see that the two posts above are telling you that you need to specify the field names in the insert statement. In ANSI sql, this is optional if you are inserting data for all of the fields in the order they exist. See the MySQL documenation.

Offhand, I don't see any immediate problems, but there are a few things I would change.

01  #!perl -w
02  use strict;
03  use DBI;
04  my $DS="dbi:mysql:QuizTaker";
05  my $User="tstanley";
06  my $File="A_Plus_Core.psv";
07  
08  open(FH,"$File")||die"Can't open $File: $!\n";
09  my @Array=<FH>;
10  close FH;
11
12  my $dbh=DBI->connect($DS,$User, { RaiseError => 1 } )
13    ||die"Can't connect: $DBI::errstr\n";
14
15  my $AnswerE="NULL";
16  my $sql = 'INSERT INTO Questions values(?,?,?,?,?,?,?,?)';
17  my $sth=$dbh->prepare( $sql );
18
19  foreach my $element(@Array){
20    chomp( my @data =split /\|/,$element, 8 );
21    splice @data, -1, 0, $AnswerE;
22    $sth->execute( @data );
23  }
24
25  $dbh->disconnect;

[download]

In line 12, I added the "RaiseError" attribute. This means that you no longer need to explicitly check every DBI method for success as DBI will do this for you.

Line 17 has your statement handle being prepared outside of the loop. I dropped prepared_cached as this is not useful here. This essentially creates a cache of the statement handle which is useful primarily if you are likely to be creating many different statement handles that might overlap. See the documentation for this, and for the pitfalls involved. By preparing a statement handle outside of the loop, we achieve what I think you were trying to achieve with the prepare_cached.

In the foreach loop starting at 19, I dropped the variable names as you really don't use them and they merely (IMHO) get in the way. Of course, we still need to deal with $AnswerE, so I splice it in.

I also lower-cased many variable names as upper case typically denotes constants or filehandles.

Without seeing your actual data, I can't tell what your problem is. If the above doesn't solve it, look in the documentation for the $DBI->trace method.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

In reply to (Ovid) Re: Inserting values into a MySQL database by Ovid
in thread Inserting values into a MySQL database by TStanley

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.