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:
Code:
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.
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.
Can I ask how I can get the code working ?
The problem with the code is that the INSERT statement does not have placeholders. It has the column datatypes instead.
If you put placeholder characters ("?"), then Perl DBI will bind your array "@fields" to those and execute the statement.
So, if your tab-delimited data file looks like this:
then your Perl DBI program should be something like this:
Code:
#!/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.
tyler_durden
This User Gave Thanks to durden_tyler For This Post:
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)
Code:
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;
Sorry to disturb you, I would like to seek help on inserting data whenever the switch is on or off to my phpMyAdmin mySQL database from my Shell Script. I'm using Raspberry PI as my hardware and I have follow this LINK: instructables.com/id/Web-Control-of-Raspberry-Pi-GPIO/?ALLSTEPS to create my... (4 Replies)
Need assistance
Below script get the output correctly
I want to convert the date format .Below is the output . Any idea ?
#!/usr/bin/perl -w
use DBI;
# Get a database handle by connecting to the database
my $db = DBI->connect(... (3 Replies)
Hi, im trying to make a script that backups mysql databases but apparently I am having trouble with the variables, or simply something I am missing.
Would appreciate any help, here is the script
#!/usr/bin/perl -w
use strict;
require File::Spec;
#VARIABLES
my $databasename =... (4 Replies)
Welcome. I am writing a perl script. I have to design a database consisting of a single table (any subject) saved in a text file. (I make it vi command name and I am giving permission chmod u + x?) The table should contain at least four columns, including a column containing the ID (serial number )... (4 Replies)
Hi, i have the following:
db="create database xxx;GRANT ALL PRIVILEGES ON xxx.* TO user@localhost IDENTIFIED BY 'password';FLUSH PRIVILEGES;quit;"
mysql -u root -p$mysql_pass -e "$db"
I don't understand why this is failing, it works fine when run from cmd but when is run in a bash script,... (1 Reply)
Dear Scripting Gods
I've never done shell scripting before and have only recently got to grips with Perl, so apologies for my naivity. I've written a perl program which takes in two files as arguments (these are text documents which take in the information I need) The perl program spits out a... (1 Reply)
Hi,
I have gps receiver, by using gpsd data i can read gps log data to my database(my sql).
Steps:
1. telenet localhost 2947 > gps.txt (press enter)
2. r (press enter) //then i will get the data like below in gps.txt file
Trying 127.0.0.1...
Connected to localhost.... (1 Reply)
Dear Friends,
I am tryin to connect to the myql through perl scrip. I have already installed mysql and DBI modules to my Perl.
There versions are as follows,
DBD-mysql MySQL driver for the Perl5 Database
DBI Database independent interface for
It gives... (4 Replies)