package SQL;
# 
# SQL.pm 
#     Copyright (c) 1998-2001 by Tomonori NOMOTO
# 

BEGIN {
    ($sql_id,$sql_password) = ($main::sql_id,$main::sql_password);
    use DBI;
# For MySQL その１
    $dbh = DBI -> connect('DatabaseName',$sql_id,$sql_password,'mysql')||&error;;
# For MySQL その２
#  $dbh = DBI->connect("DBI:mysql:DatabaseName:localhost", $sql_id, )||&error;
# For MySQL その３
#    $drh = DBI -> install_driver('mysql');
#    $dbh = $drh->connect('localhost','DatabaseName',$sql_id,$sql_password)||&error;
# For Oracle
#    $drh = DBI->install_driver('Oracle')||&error;
#    $dbh = $drh->connect('DatabaseName',$sql_id,$sql_password)||die "An error occured in SQL.pm\n";
    &error unless $dbh;
}

END {
  $dbh->disconnect;
}

# &SQL::select($select)
#   渡されたselect文を実行し、結果をタブ区切りのテキストの配列として返す。
sub select {
    $select = @_[0];
    undef @line;
    undef @field;
#    print $select;    # Only for debug.
    $cursor = $dbh->prepare("$select")||&error;
    $cursor->execute();
    $n = 0;
    while(@line = $cursor->fetchrow){
        $field[$n] = "";
        foreach $value (@line){
            $field[$n] = $field[$n] . $value . "\t";
        }
        $field[$n] =~ s/\t$//;
        $n++;
    }
    $cursor->finish;
    return @field;
}

# &SQL::delete($tbl,$mst_name,$mst_cd [,$mst_name1,$mst_cd1,...] )
#   テーブル $tbl の $mst_nameN の値が $mst_cdN であるレコードが消される。
sub delete {
    $tbl = shift(@_);
    $mst_name = shift(@_);
    $mst_cd = shift(@_);
    $plus = "";
    if(defined $_[0] && defined $_[1]){
        while(defined $_[0] && defined $_[1]){
            $plus = "$plus and $_[0]='$_[1]'";
            shift @_;
            shift @_;
        }
    }
    $delete="delete from $tbl where $mst_name='$mst_cd' $plus";
#    print $delete;    # Only for debug
    $delete_cursor = $dbh->prepare("$delete")||&error;
    $delete_cursor->execute();
    $delete_cursor->finish;
}

# &SQL::update($tbl,$mst_name,$mst_cd [,$mst_name,$mst_cd2,...])
#   テーブル $tbl の $mst_name の値が $mst_cd であるレコードが
#                                      %呼出元Package::inputの値に更新される
sub update {
    $tbl = shift(@_);
    $mst_name = shift(@_);
    $mst_cd = shift(@_);
    $plus = "";
    if(defined $_[0] && defined $_[1]){
    while(defined $_[0] && defined $_[1]){
            $plus = "$plus and $_[0]='$_[1]'";
            shift @_;
            shift @_;
        }
    }
    ($package,$pp,$ll) = caller;
    $input = $package ."::input";
    %input = %$input;
    @keys = keys(%input);
    $sets = "";
    foreach $key (@keys){
        $sets = "$sets$key='$input{$key}',";
    }
    $sets =~ s/,$//;
    $update="update $tbl set $sets where $mst_name='$mst_cd' $plus";
#    print $update;    # Only for debug
    $update_cursor = $dbh->prepare("$update")||&error;
    $update_cursor->execute();
    $update_cursor->finish;
}

# &SQL::insert($tbl [,$cd1,$value1,$cd2,$value2,...] )
# $tbl にinsert
sub insert {
    undef %insert;
    $tbl = shift(@_);
    if(defined $_[0] && defined $_[1]){
        while(defined $_[0] && defined $_[1]){
            $insert{$_[0]} = $_[1];
            shift @_;
            shift @_;
        }
    }
    $vars = "";
    $value = "";
    @keys = keys(%insert);
    foreach(@keys){
        $vars = "$vars$_,";
        $value = "$value'$insert{$_}',"
    }
    $vars =~ s/,$//;
    $value =~ s/,$//;
    $insert = "insert into $tbl($vars) values($value)";
#    print "$insert";    # Only for debug
    $insert_cursor = $dbh->prepare("$insert")||&error; 
    $insert_cursor->execute();
    $insert_cursor->finish;
}

sub error {
    print "An Error occured in SQL.pm";
    die;
}

1;