VastbaseG100

执行SQL语句并处理结果

向数据库发出执行SQL的请求时，调用do，prepare方法执行相关语句，其中do只能用于非select语句，它不返回句柄。

执行查询

使用prepare查询

my $alexa = 3;
my $stmt = qq(SELECT id, name, address, salary  from COMPANY where id<?;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute($alexa) or die $DBI::errstr;
if($rv < 0){
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n";
}
$sth->finish;

执行更新

删除

# do方法
my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv\n";
}

# do绑定变量
$stmt = qq(DELETE from COMPANY where ID=?;);
$rv = $dbh->do($stmt,undef,2) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv\n";
}

# prepare方法
my $sth = $dbh->prepare(q{
    DELETE from COMPANY where ID=?});
$sth->bind_param( 1, 3 );
$sth->execute() or die $DBI::errstr;
$sth->finish;

插入

Insert插入数据

# do方法
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

# prepare方法
my $sth = $dbh->prepare(q{
    INSERT INTO t1(id,name,age,address,salary)
      VALUES (?,?,?,?,?)
});
$sth->bind_param( 1, 2 );
$sth->bind_param( 2, 'Allen' );
$sth->bind_param( 3, 25 );
$sth->bind_param( 4, 'Texas' );
$sth->bind_param( 5, 15000.00 );
$sth->execute() or die $DBI::errstr;
$sth->finish;

# 事务
my @data = ([1,'Paul', 32, 'California', 20000.00], [2,'Allen', 25, 'Texas', 15000.00]);
$sth = $dbh->prepare(qq{INSERT INTO t1(id,name,age,address,salary)
      VALUES (?,?,?,?,?)});
$dbh->begin_work();  # start transaction
foreach my $row(@data) {
    $sth->execute(@$row);
}
$dbh->commit();  # end transaction

copy插入数据

my @data= (
    "1,Paul,32,California,20000\n",
    "2,Allen,25,Texas,15000\n",
    "3,Teddy,23,Norway,20000\n",
    "4,Mark,25,Rich-Mond,65000\n",
);
my $sql=qq{COPY COMPANY(ID,NAME,AGE,ADDRESS,SALARY) FROM STDIN WITH DELIMITER ','};
$dbh->do($sql);
foreach my $line (@data) {
    $dbh->pg_putcopydata($line);
}
$dbh->pg_putcopyend();

更新

# do方法
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
    print "Total number of rows updated : $rv\n";
}

# prepare方法
my $sth = $dbh->prepare(q{
    UPDATE COMPANY set SALARY = ? where ID=?});
$sth->bind_param( 1, 25000.00 );
$sth->bind_param( 2, 2 );
$sth->execute() or die $DBI::errstr;
$sth->finish;

LOB类型读写

BYTEA类型

Create table generic_file_table (
column_1 varchar(20),
column_2 varchar(20),
bytea_column bytea);

插入数据：

my $datum1 = "foo";
my $datum2 = "123";
my $file = "/path/to/file.dat";
my $contents;

##open the file and store it
open my $FH, $file or die "Could not open file: $!";
{
local $/ = undef;
$contents = <$FH>;
};
close $FH;
print "$contents";

##preparte SQL
my $q = q{
INSERT INTO generic_file_table (column_1,column_2,bytea_column) VALUES (?, ?, ?)};
my $sth = $dbh->prepare($q);
##bind variables and specifically set #3 to bytea; then execute.
$sth->bind_param(1,$datum1);
$sth->bind_param(2,$datum2);
$sth->bind_param(3,$contents, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();
$sth->finish();

读取数据：

my $sParam = "123";
$dbh->{LongReadLen}=2_000_000;
$dbh->{LongTruncOk}=1;
my $sth= $dbh->prepare( "SELECT bytea_column FROM generic_file_table WHERE column_2 = ?" );
$sth->execute($sParam);
my $sRes = $sth->fetchrow_array();
$sth->finish();
$dbh->disconnect();

BLOB类型

Create table test_blob (
column_1 varchar(20),
column_2 varchar(20),
blob_column blob);

插入数据：

my $datum1 = "foo";
my $datum2 = "123";
my $file = "/path/to/file.dat";
my $contents;

##open the file and store it
open my $FH, $file or die "Could not open file: $!";
{
local $/ = undef;
$contents = <$FH>;
};
close $FH;
$contents =~ s/(.)/sprintf '%02x', ord $1/seg; # 转换为hexstring
print "$contents";

##preparte SQL
my $q = q{
INSERT INTO test_blob (column_1,column_2,blob_column) VALUES (?, ?, ?)};
my $sth = $dbh->prepare($q);
$sth->bind_param(1,$datum1);
$sth->bind_param(2,$datum2);
$sth->bind_param(3,$contents);
$sth->execute();
$sth->finish();

读取数据：

my $sParam = "123";
$dbh->{LongReadLen}=2_000_000;
$dbh->{LongTruncOk}=1;
my $sth= $dbh->prepare( "SELECT blob_column FROM test_blob WHERE column_2 = ?" );
$sth->execute($sParam);
my $sRes = $sth->fetchrow_array();
$sth->finish();
$dbh->disconnect();

CLOB类型、TEXT类型

CREATE TABLE COMPANY (
ID INT PRIMARY KEY     NOT NULL,
NAME           CLOB    NOT NULL,
AGE            INT     NOT NULL,
ADDRESS        TEXT
);

插入数据：

my $id= 1;
my $name = "Twitter";
my $age = 22;
my $address = "1 Queen's Road Central , Hongkong";
my $sth = $dbh->prepare("INSERT INTO company
                       (id, name, age, address)
                        values
                       (?,?,?,?)");
$sth->execute($id,$name,$age, $address) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

读取数据：

my $age= 20;
my $sth = $dbh->prepare("SELECT name, age,address
                       FROM company
                       WHERE age> ?");
$sth->execute( $age) or die $DBI::errstr;
 
# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}
 
$sth->finish();

$dbh->disconnect();

创建或修改数据库对象

# do方法
my $stmt = qq(CREATE TABLE T1(ID INT, COL VARCHAR(20)););
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
    print "Create Success\n";
}