Thanks Monster!
Actually I copied this line from somewhere and thought it would work.
After I removed the array parameters for execute, there was another error:
DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'int, char(20) primary key, char(20), char(100), int, int, char(10), int, te'
at line 3 at Brapa0101-db.pl line 45, <FILE> line 41174.
then your Perl DBI program should be something like this:
#!/usr/bin/perl -w
use strict;
use DBI;
my $DataBaseName="Brapa0101_db";
my $DataBaseHost="localhost";
my $MySQLUser="myql_account";
my $MySQLUserPass="passwrdxyzt";
my $dsn="DBI:mysql";
my $Table="table01" ;
my $dbh = DBI->connect("$dsn:$DataBaseName", $MySQLUser, $MySQLUserPass) or die "Cannot connect: " . $DBI::errstr;
my $sql = qq{INSERT INTO $Table (run, geneid, seqtype, scaffold, seqstart, seqend, seqstrand, seqlength, cdsseq)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)};
my $sth = $dbh->prepare($sql);
my $file_name="/path/to/MySQL_Study/Brapa1.1-database/Brassica_rapa.20100830.cds.tab5";
open (FILE, "<", $file_name) or die "Cannot open the file named $file_name to read: $!";
foreach my $line (<FILE>) {
next if $line=~ m/Run\t/;
chomp $line;
my @fields = split(/\t/, $line);
$sth->execute(@fields);
}
close (FILE) or die "Can't close $file_name: $!";
$sth->finish();
$dbh->disconnect();
I couldn't test it though, as I don't have DBI or MySQL on my system.
By the way, can I ask another question to create both the database and the tables, especially the tables, within perl script? What I meant is to integrate the mysql code(commented in my first post)
mysql> CREATE DATABASE Brapa0101_db;
mysql> CREATE TABLE table01 (
run int NOT NULL AUTO_INCREMENT PRIMARY KEY, geneid VARCHAR(20), seqtype VARCHAR(50), scaffold VARCHAR(50),
seqstart INT(11), seqend INT(11), seqstrand VARCHAR(1), seqlength INT(11), cdsseq TEXT
);
into the perl code. But I am not sure how to realize this idea. The reasons are:
1) It seems it can be done to me as for my learning purpose;
2) If it is done in perl script, then there is no need to go back and forth from mysql (to check the columns and the variable type etc) to my editor while writing the perl code. Here in your code the place holders did the trick, but if all are in perl code, it can be easier to control, especially with the split and joint functions.
3) Through this way, I can have full use of MySQL and do not need to go to the MySQL console;