Web Development

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.

#!/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;

#!/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;

1. call $SysDatabase (mysql) first, connect to the system, 
2. then create the new  $DataBaseName, 
3. then use $DataBaseName and insert the $Table.

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;

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)

mysql> create table  t(x int);
ERROR 1050 (42S01): Table 't' already exists

$dsn = "DBI:mysql:database=$SysDataBase;host=$DataBaseHost;port=$DataBasePort";

$dsn = "DBI:mysql:database=$SysDataBase;host=$DataBaseHost;port=$DataBasePort";