perl读取数据到本地数据库

时间：2008-07-02 来源：hoo7

    最近要从sqlserver上取数据，到本地数据库（sqlite3）首先创建本地数据库的表： [linuxtest test]$ sqlite3 test.db
SQLite version 3.3.9
Enter ".help" for instructions
sqlite> create table test20080702 (
   ...> cust_id number(10),
   ...> order_id number(10),
   ...> send_date date,
   ...> total varchar(10),
   ...> total_bargin_price varchar(10),
   ...> shipping_fee varchar(10),
   ...> product_name varchar(20),
   ...> bargin_price varchar(10),
   ...> order_quantity number(10),
   ...> allot_quantity number(10),
   ...> payment_provider_id number(10),
   ...> status number(2));
sqlite> .q 创建表完成 perl脚本 #!/usr/local/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Getopt::Long;
use POSIX qw(strftime);
use DBI;
use Net::SMTP;
#$|=1;
print "S: ",strftime("%Y-%m-%d %H:%M:%S",localtime(time())),"\n"; #my $type;
#GetOptions( "type=s" => \$type);
#die("pls special a TYPE\n") unless $type; my $dbh = DBI->connect( "dbi:SQLite:/home/huming/test/test.db","","",{AutoCommit => 0} )
               or die "$DBI::errstr\n"; my $rdbh = DBI->connect( "dbi:Sybase:server=$server;database=$database",
                        "$user",
                        "$password",
                        {AutoCommit => 1} ) or die "$DBI::errstr\n";
my $db_table = "dbo.order_items";
#get all_resouce
get_all_resouce(); $dbh->disconnect();
$rdbh->disconnect();
print "E: ",strftime("%Y-%m-%d %H:%M:%S",localtime(time())),"\n";
exit; #####
# get all_resouce
#####
sub get_all_resouce {
        my ($cust_id,$order_id,$send_date,$total,$total_bargin_price,$shipping_fee,$product_name,$bargin_price,$order_quantity,
                $allot_quantity,$payment_provider_id);
        my $i = 0;
        my $q = qq{select top 100
       o.cust_id,
       o.order_id,
       convert(char(20),o.send_date,120) send_date,
       o.total ,
       o.total_bargin_price,
       o.shipping_fee,
       substring(oi.product_name,1,20) product_name,
       oi.bargin_price,
       oi.order_quantity,
       oi.allot_quantity,
       o.payment_provider_id
from dbo.orders(nolock) o ,dbo.order_items(nolock) oi
where o.order_status = '300'
    and oi.order_id = o.order_id
    and o.order_type = 0
};
        my $sth = $rdbh->prepare($q) or die "$DBI::errstr\n";
        $sth->execute() or die "$DBI::errstr\n";
        $sth->bind_columns(undef, \$cust_id,\$order_id,\$send_date,\$total,\$total_bargin_price,\$shipping_fee,\$product_name,\$bargin_price,\$order_quantity,
                \$allot_quantity,\$payment_provider_id);
while ( $sth->fetch() ) {     $dbh->do(qq{insert into test20080702(cust_id,order_id,send_date,total,total_bargin_price,shipping_fee,product_name,bargin_price,order_quantity,
                allot_quantity,payment_provider_id,status) values ('$cust_id','$order_id','$send_date','$total','$total_bargin_price','$shipping_fee','$product_name','$bargin_price','$order_quantity',
                '$allot_quantity','$payment_provider_id','0')}) ;
        $dbh->commit() if (++$i / 10000 ==0);
}
$dbh->commit();
}