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();