I believe this is a problem in your SQL syntax, not your Perl. In short, your SQL statement should be:

INSERT INTO Questions(Question,Answer_A,
                               Answer_B,
                               Answer_C,
                               Answer_D,
                               Answer_E,
                               Correct_Answer)
VALUES(?,?,?,?,?,?,?)
[download]

I claim no particular in-depth knowledge of MySQL, however, so I may be mistaken.

Update: http://www.sqlcourse.com may be useful in improving one's SQL-fu.

perl -pe '"I lo*`+$^X$\"$]!$/"=~m%(.*)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'

Chmrr is correct. In order for SQL to know what columns your are inserting into, you have to name them.  INSERT INTO Table VALUES (?,?) isn't sufficient. You need to specify the fields, via   INSERT INTO Table (a, b) VALUES (?,?) Think of it as a list of associations, but with an array of keys on the left, and an array of values (or placeholders) on the right.


Update: According the the MySQL doc for INSERT, the column names are indeed optional. This is a bad practice, however, since it will break your code whenever you change a table. My recommendation is to always explicitly name columns.

You don't actually have to specify the columns on an insert. If you don't you need to provide a value for each column in the order that they were created. However this shortcut syntax is error prone, any change to the database will probably break your code and it also stops you from using auto_increment/default columns.

mysql> create table test (a int, b char(1));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values (1, 'A');
Query OK, 1 row affected (0.06 sec)

mysql> insert into test (a, b) values (2, 'B');
Query OK, 1 row affected (0.06 sec)

mysql> select * from test;
+------+------+
| a    | b    |
+------+------+
|    1 | A    |
|    2 | B    |
+------+------+
2 rows in set (0.05 sec)
[download]

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.

The problem is this line:

my($TN,$Question,$AnswerA,$AnswerB,$AnswerC,$AnswerD,$Correct)=...
[download]

What you end up with is trying to insert only 7 variables where DBI is expecting 8.

In the long run though, follow the advice of Chmrr, dws, and Ovid. It will keep you out of trouble.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

P.S. Brownie points to adamsj for spotting this error too.

P.P.S Or i could be completely wrong about Answer E. I now see that you are indeed assigning the variable $AnswerE to "NULL", which is not the same as NULL. Use undef instead, but you really should specify $AnswerE in the split, i think. How about this instead:

foreach my $element(@Array){
   chomp $element;
   # define $STH as you did
   $STH->execute(split/\|/,$element,8);
}
[download]

Good point. I make sure I specify the columns all the time because I have been caught out by this before.

I had a quick check through the MySQL docs and I noticed for versions > 3.22.10 you can use SQL like:

INSERT INTO table SET x = 1, y = 2, z = 3;
[download]

Which is interesting, but I'm not sure if it is better, and I'm pretty sure it isn't portable. There is a couple of other things that are cool (I never noticed this stuff before!) such as inserting multiple records in 1 statement:

INSERT INTO table (x,y,z) VALUES (1,2,3), (4,5,6), (7,8,9);
[download]

Of course this is only really handy if you know in advance how many records you want to insert.

What I forgot to mention is that you should really be using:

INSERT DELAYED INTO table (x,y,z) VALUES (1,2,3);
[download]

if you are doing multiple inserts as this can give you quite a bit of a performance boost.

gav^

just a note that i've found the "insert into table set" syntax more reliable than the standard forms for mysql. not portable, of course, but you can use placeholders in the statement. one of the reasons i started moving away from mysql.