Hi, I had a chance to create a database refreshing the previous post. An error was tossed out with the following message:
DBI connect('nr20111117_db','yifangt',...) failed: Unknown database 'nr20111117_db' at create-nrDB-20111214.pl line 17
Cannot connect: Unknown database 'nr20111117_db' at create-nrDB-20111214.pl line 17.
Obviously the problem is line 17 to CREATE DATABASE and TABLE. Appreciate any help. Thanks a lot!
#!/usr/bin/perl -w
use strict;
use DBI;
my $DataBaseName="nr20111117_db";
my $DataBaseHost="localhost";
my $MySQLUser="yifangt";
my $MySQLUserPass="PassWord";
my $dsn="DBI:mysql";
my $Table="table01";
my $dbh = DBI->connect("$dsn:$DataBaseName", $MySQLUser, $MySQLUserPass) or die "Cannot connect: " . $DBI::errstr;
$dbh->do("CREATE DATABASE $DataBaseName");
$dbh->do("CREATE TABLE $Table (
number INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
gi_id VARCHAR(20),
description TEXT)"
);
my $sql = qq{INSERT INTO $Table (number, gi_id, description) VALUES (?, ?, ?)};
my $sth = $dbh->prepare($sql);
my $file_name="/home/yifang/20111114-Yangdou/BLAST-vs-nr/nr-headers+row.tab";
open (FILE, "<", $file_name) or die "Cannot open the file named $file_name to read: $!";
#################### Example rows of the data
#Run_Number gi_ID Description
# 1 >gi|15674171|ref|NP_268346.1| 30S ribosomal protein S18
# 2 >gi|66816243|ref|XP_642131.1| hypothetical protein DDB_G0277827 [Dictyostelium discoideum AX4]
# 3 >gi|66818355|ref|XP_642837.1| hypothetical protein DDB_G0276911 [Dictyostelium discoideum AX4]
############## ### End of Example rows of the file #
foreach my $line (<FILE>) {
chomp $line;
my @fields = split(/\t/, $line);
$sth->execute(@fields);
}
while (my $row = $sql->fetchrow_arrayref) {
print join("\t", @$row), "\n";
}
$dbh->disconnect;
I had thought it to be simple to add the lines by durden_tyler, but not that easy! Thanks again!
You cannot connect to a database that you're about to create ...
This modified part of the script should work (I've removed some parts in order to test the code):
#!/usr/bin/perl -w
use strict;
use DBI;
my $DataBaseName="nr20111117_db";
my $SysDataBase="mysql";
my $DataBaseHost="localhost";
my $DataBasePort="3306";
my $MySQLUser="yifangt";
my $MySQLUserPass="PassWord";
my $dsn = "DBI:mysql:database=$SysDataBase;host=$DataBaseHost;port=$DataBasePort";
my $Table="table01";
my $dbh = DBI->connect($dsn, $MySQLUser, $MySQLUserPass) or die "Cannot connect: " . $DBI::errstr;
$dbh->do("CREATE DATABASE $DataBaseName");
$dbh->do("use $DataBaseName");
$dbh->do("CREATE TABLE $Table (
number INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
gi_id VARCHAR(20),
description TEXT)"
);
$dbh->disconnect;
Thanks!
Do you mean the $SysDatabase and $dbh->do("use $DataBaseName") are needed in order to have the code run?
I am trying to catch the points as a beginner. It seems the way is
1. call $SysDatabase (mysql) first, connect to the system,
2. then create the new $DataBaseName,
3. then use $DataBaseName and insert the $Table.
Not quite sure.
Yes, the code is running now, but the table is empty (Need to figure out the other part of the code and the format of the data too. let me put them aside at this moment).
There is another problem emerging as I can not run this script for a second time because the database and table were already created. They need be deleted before the second run. My colleague suggest not using this method by embedding the CREATE DATABASE or CREATE TABLE in the perl script. His point is these two commands only need once at the beginning to create database. But, my original point is to avoid switching back and forth between mysql console and the terminal console, especially when there are many columns in the table. Now compare the two strategies:
One: Create DATABASE/TABLES under >mysql console, then code the perl script to insert data;
Two: embed everything with perl script, but delete the database/tables before any second try when needed;
Which is the professional way? Thanks again! Yifangt
You need to connect to some database as the syntax of DBD::mysql forces you to do so (it seems so at least, after a quick look at the module's documentation).
The mysql system database will be always there, so that's a good start.
This could be problematic if you don't have the right privileges to use it,
but that doesn't seem a problem in your case.
Well, as an Oracle DBA I agree with your colleague
Run the DDL once (well, unless you're trying to automate software installation or some sort of upgrade, of course).
Anyway, in this case mysql offers the following syntactic sugar that will resolve this issue:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database /*!32312 IF NOT EXISTS*/ test_db;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test_db |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database /*!32312 IF NOT EXISTS*/ test_db;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> use test_db;
Database changed
mysql> create table /*!32312 IF NOT EXISTS*/ t(x int);
Query OK, 0 rows affected (0.06 sec)
mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> create table /*!32312 IF NOT EXISTS*/ t(x int);
Query OK, 0 rows affected, 1 warning (0.00 sec)
