Login or Register to Ask a Question and Join Our Community


Programming

Help with mySQL database by perl script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Help with mySQL database by perl script
# 1  
Old 09-27-2011
Help with mySQL database by perl script
Hello;

I was trying to set up a mysql database using following script, but never went through. The code seems fine without any syntax error as I tested it:
Code:
perl -c Brapa0101-db.pl
Brapa0101-db.pl syntax OK

However, whenever I run it, an error message was tossed out:
Code:
DBD::mysql::st execute failed: called with 9 bind variables when 0  are needed at Brapa0101-db.pl.bk line 45, <FILE> line  41174.

Could any expert help me through this problem? Thanks a lot!
Here is my code:
Code:
#!/usr/bin/perl -w

use strict;
use DBI;

# # # # # # # # # # # # # # # #  # # # # # # # # # # # # # # 
#The database "Brapa0101_db" has been created with mysql
#mysql> CREATE database Brapa0101_db;
# # # # # # # # # # # # # # # #  # # # # # # # # # # # # # # 

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:$DataBaseHost", $MySQLUser, $MySQLUserPass) 
            or die "Cannot connect: " . $DBI::errstr;
   my $sql = qq/INSERT INTO $Table 
                      (run, geneid,            seqtype,     scaffold,  seqstart, seqend, seqstrand,  seqlength, cdsseq)
              VALUES( int, char(20) primary key, char(20), char(100), int,   int, char(10), int,    text(40000))
            /;
##############Example rows of the file /path/to/MySQL_Study/Brapa1.1-database/Brassica_rapa.20100830.cds.tab5"
#    Run    Gene    mRNA    Scaffold    Start    End    Strand    Length    Sequence
#    1    Bra000001    [mRNA]    locus=Scaffold000001    3252        5836        +    2584    ATGGGGAAGATCTTGAAAACTAAGTCTT
#    2    Bra000002    [mRNA]    locus=Scaffold000001    6640        9120        -    2480    ATGGAGGAAGTAAGGAAGATGGGTTGTAT
#    3    Bra000003    [mRNA]    locus=Scaffold000001    21211    22012    +    801    ATGAGCTCTGTTTGTGGTAAGCTGGATTT
#    4    Bra000004    [mRNA]    locus=Scaffold000001    25599    25973    +    374    ATGATTCGCCGTCTATTCTCGTCTCTGACT.
#    5    Bra000005    [mRNA]    locus=Scaffold000001    26822    28864    -    2042    ATGGCGGCAGCTAGACGATT..
#    6    Bra000006    [mRNA]    locus=Scaffold000001    29480    29791    -    311    ATGTC...............
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 $_=~ m/Run\t/;                #This $_ has caused many headaches on me, 
    
     next if $line=~ m/Run\t/;                #This is the right syntax 
     chomp $line;
  my @fields = split(/\t/, $line);
    $sth->execute(@fields);
}

while (my $row = $sql->fetchrow_arrayref) {
        print join("\t", @$row), "\n";
    }
$dbh->disconnect;

# 2  
Old 09-28-2011
You can't pass an array to execute(). A normal way to execute a statement is:

Code:
my $sth = $dbh->prepare($sql);
$sth->execute();

This User Gave Thanks to MacMonster For This Post:
yifangt
# 3  
Old 09-28-2011
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.

Can I ask how I can get the code working ?
# 4  
Old 09-28-2011
Quote:
Originally Posted by yifangt
...
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.

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:

Code:
$
$ cat Brassica_rapa.20100830.cds.tab5
Run     Gene    mRNA    Scaffold        Start   End     Strand  Length  Sequence
1       Bra000001       [mRNA]  locus=Scaffold000001    3252    5836    +       2584    ATGGGGAAGATCTTGAAAACTAAGTCTT
2       Bra000002       [mRNA]  locus=Scaffold000001    6640    9120    -       2480    ATGGAGGAAGTAAGGAAGATGGGTTGTAT
3       Bra000003       [mRNA]  locus=Scaffold000001    21211   22012   +       801     ATGAGCTCTGTTTGTGGTAAGCTGGATTT
4       Bra000004       [mRNA]  locus=Scaffold000001    25599   25973   +       374     ATGATTCGCCGTCTATTCTCGTCTCTGACT.
5       Bra000005       [mRNA]  locus=Scaffold000001    26822   28864   -       2042    ATGGCGGCAGCTAGACGATT..
6       Bra000006       [mRNA]  locus=Scaffold000001    29480   29791   -       311     ATGTC...............
$
$

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:
yifangt
# 5  
Old 09-29-2011
Thank you so much Durden! It works now.

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;

Is this a dump question? Thanks again!

Yifang
# 6  
Old 09-30-2011
No it is not a dumb question.
You could just plug in those statements once you connect to your data source and get a handle for that connection:

Code:
$dbh->do("CREATE DATABASE Brapa0101_db");
$dbh->do("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)"
        );

tyler_durden

This User Gave Thanks to durden_tyler For This Post:
yifangt
# 7  
Old 09-30-2011
This is what I meant, and really great! Thanks a lot again!
Yifang
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help on Insert data to phpMyAdmin mySQL database from Shell Script

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)
Discussion started by: aoiregion
4 Replies

2. Shell Programming and Scripting

Perl script database date convertion

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)
Discussion started by: ajayram_arya
3 Replies

3. Shell Programming and Scripting

Help with perl mysql backup script

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)
Discussion started by: Fireline
4 Replies

4. Shell Programming and Scripting

[Perl] script -database

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)
Discussion started by: qwerty007
4 Replies

5. Shell Programming and Scripting

Create mysql database with bash script - confused

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)
Discussion started by: ktm
1 Replies

6. Shell Programming and Scripting

Automating A Perl Script over a database

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)
Discussion started by: fraizerangus
1 Replies

7. Shell Programming and Scripting

shell script to insert data from gps.txt to mysql database

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)
Discussion started by: gudivada213
1 Replies

8. Shell Programming and Scripting

Connecting MySql throug Perl Script ?

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)
Discussion started by: maheshsri
4 Replies
Login or Register to Ask a Question