Category Archive: SQL

8月 24th, 2012

post by yamamoto | | 0 | 885 views

perl memo DBD::CSV csv file name 2

ファイル名を大文字と小文字の両方を使った場合、テーブルのエリアスが使えなくなる。例えば、 select a.* from herbRecordNo a　＝＞　エラー select a.* from herbrecordno a　＝＞　エラーにならない原因は不明です。＞＜

Perl, SQL

5月 25th, 2012

post by yamamoto | | 0 | 1,703 views

perl memo DBD::CSV JOIN

#!/usr/bin/perl

    use DBI;
    $dbh = DBI->connect("DBI:CSV:f_dir=/Volumes/RamDisk/csvdb")
        or die "Cannot connect: " . $DBI::errstr;
        
        $sth = $dbh->prepare("DROP TABLE a ")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
    $sth = $dbh->prepare("CREATE TABLE a (id INTEGER not null, name CHAR(10))")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
        $sth = $dbh->prepare(" insert into a (id,name) values (1,'カンマ　太郎')")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
    
        $sth = $dbh->prepare(" insert into a (id,name) values (2,'カンマ　次郎')")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
    
            $sth = $dbh->prepare(" update a set name='カンマ　花子' where id=1")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();

    
            $sth = $dbh->prepare("DROP TABLE b ")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
    $sth = $dbh->prepare("CREATE TABLE b (id INTEGER not null, age INTEGER)")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
            $sth = $dbh->prepare(" insert into b (id,age) values (2,30)")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
                $sth = $dbh->prepare(" insert into b (id,age) values (1,26)")
        or die "Cannot prepare: " . $dbh->errstr();
    $sth->execute() or die "Cannot execute: " . $sth->errstr();
    
    
        my($query) = "SELECT a.id id , a.name name, b.age age FROM a a, b b  WHERE a.id = b.id";
    my($sth) = $dbh->prepare($query);
    $sth->execute();
    while (my $row = $sth->fetchrow_hashref) {
        print("Found result row: id = ", $row->{'id'},
              ", name = ", $row->{'name'},
              ", age = ", $row->{'age'}, "\n");
    }
    
        $sth->finish();
    
    $dbh->disconnect();

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect("DBI:CSV:f_dir=/Volumes/RamDisk/csvdb")

or die "Cannot connect: " . $DBI::errstr;

$sth = $dbh->prepare("DROP TABLE a ")

or die "Cannot prepare: " . $dbh->errstr();