这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

Perl

Perl 脚本可以通过使用 Perl DBI 模块和 DBD::ODBC 数据库驱动程序连接到 Vertica 的 ODBC 驱动程序接口来与 Vertica 交互。

先决条件

在创建 Perl 客户端应用程序之前，必须配置 Perl 开发环境。

1 - 配置 Perl 开发环境

Perl 包含一个数据库接口 (DBI) 模块，此接口模块可为 Perl 脚本创建与数据库交互的标准接口。此接口模块依赖数据库驱动程序 (Database Driver, DBD) 模块处理所有特定于数据库的通信任务。结果是一个接口提供了一种一致的方式以供 Perl 脚本用来与许多不同类型的数据库交互。

Perl 脚本可以通过使用 Perl DBI 模块和 DBD::ODBC 数据库驱动程序连接到 Vertica 的 ODBC 驱动程序接口来与 Vertica 交互。有关详细文档，请参阅 Perl 的 DBI 模块和 DBD::ODBC 模块的 CPAN 页面。

Vertica-Perl 架构

Perl 开发环境依赖于 Vertica ODBC 驱动程序以及 DBI 和 DBD::ODBC 模块。

  1. 安装和配置 ODBC。

  2. 使用以下命令验证是否安装了 Perl。如果此命令不返回版本信息，您必须安装 Perl。有关版本支持，请参阅 Perl 驱动程序要求。

    $ perl -v

  3. 安装 Perl 模块 DBI 和 DBD::ODBC 的兼容版本。安装方法因环境不同而异。有关安装 Perl 模块的详细信息，请参阅 cpan 文档。

  4. 运行以下命令以验证是否已安装 DBI 和 DBD::ODBC。如果已安装，这些命令不应返回任何内容。否则，它们会返回错误：

    $ perl -e "use DBI;"
$ perl -e "use DBD::ODBC;"

列出 DSN 并验证安装

验证安装的另一种方法是使用以下 Perl 脚本。此脚本验证是否已安装 DBI 和 DBD::ODBC 并输出您的 ODBC DSN（如果有）：

#!/usr/bin/perl
use strict;
# Attempt to load the DBI module in an eval using require. Prevents
# script from erroring out if DBI is not installed.
eval
{
    require DBI;
    DBI->import();
};
if ($@) {
    # The eval failed, so DBI must not be installed
    print "DBI module is not installed\n";
} else {
    # Eval was successful, so DBI is installed
    print "DBI Module is installed\n";
    # List the drivers that DBI knows about.
    my @drivers = DBI->available_drivers;
    print "Available Drivers: \n";
    foreach my $driver (@drivers) {
        print "\t$driver\n";
    }
    # See if DBD::ODBC is installed by searching driver array.
    if (grep {/ODBC/i} @drivers) {
        print "\nDBD::ODBC is installed.\n";
        # List the ODBC data sources (DSNs) defined on the system
        print "Defined ODBC Data Sources:\n";
        my @dsns = DBI->data_sources('ODBC');
        foreach my $dsn (@dsns) {
            print "\t$dsn\n";
        }
    } else {
        print "DBD::ODBC is not installed\n";
    }
}

如果您的系统配置正确，则输出应类似如下内容：

DBI Module is installed
Available Drivers:
        ADO
        DBM
        ExampleP
        File
        Gofer
        ODBC
        Pg
        Proxy
        SQLite
        Sponge
        mysql
DBD::ODBC is installed.
Defined ODBC Data Sources:
        dbi:ODBC:dBASE Files
        dbi:ODBC:Excel Files
        dbi:ODBC:MS Access Database
        dbi:ODBC:VerticaDSN

2 - 使用 Perl 连接到 Vertica

可以使用 Perl DBI 模块的 connect 函数连接到 Vertica。此函数使用必需的数据源字符串参数，以及用于用户名、密码和连接属性的可选参数。

数据源字符串必须以 "dbi:ODBC:" 开头，以指示 DBI 模块使用 DBD::ODBC 驱动程序来连接到 Vertica。该字符串的剩余部分由 DBD::ODBC 驱动程序解释。该字符串通常包含 DSN 的名称（包含连接到 Vertica 数据库所需的连接信息）。例如，若要指示 DBD::ODBC 驱动程序使用名为 VerticaDSN 的 DSN，您应使用如下所示的数据源字符串：

"dbi:ODBC:VerticaDSN"

用户名参数和密码参数是可选的。但是，如果不提供这两个参数（或者仅提供免密码帐户的用户名）并且未在 DSN 中设置这两个参数，则连接尝试将始终失败。

connect 函数将在连接到 Vertica 后返回数据库句柄。如果未连接，则会返回 undef。在这种情况下，您可以访问 DBI 模块的错误字符串属性 ($DBI::errstr) 以获取错误消息。

以下示例演示了使用名为 VerticaDSN 的 DSN 连接到 Vertica。对 connect 的调用提供了用户名和密码。连接后，此示例将调用数据库句柄的 disconnect 函数以关闭连接:

#!/usr/bin/perl -w
use strict;
use DBI;
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123");
unless (defined $dbh) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connected!\n";
$dbh->disconnect();

2.1 - 在 Perl 中设置 ODBC 连接参数

若要设置 ODBC 连接参数，请在源数据字符串中将 DSN 名称替换为参数名称/值对的分号分隔列表。可以使用 DSN 参数向 DBD::ODBC 说明应使用哪个 DSN，然后添加要设置的其他 ODBC 参数。例如，以下代码使用名为 VerticaDSN 的 DSN 进行连接并将该连接的区域设置设置为 en_GB。

#!/usr/bin/perl -w
use strict;
use DBI;
# Instead of just using the DSN name, use name and value pairs.
my $dbh = DBI->connect("dbi:ODBC:DSN=VerticaDSN;Locale=en_GB@collation=binary","ExampleUser","password123");
unless (defined $dbh) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connected!\n";
$dbh->disconnect();

有关可在源数据字符串中设置的连接参数的列表，请参阅 ODBC DSN 连接属性。

2.2 - 设置 Perl DBI 连接属性

Perl DBI 模块包含一些属性，您可以使用这些属性控制其数据库连接的行为。这些属性与 ODBC 连接参数相似（在某些情况下，它们复制彼此的功能）。DBI 连接属性是一种用于控制数据库连接的行为的跨平台方法。

可以在建立连接时通过向 DBI connect 函数传递包含属性/值对的哈希值来设置 DBI 连接属性。例如，若要将 DBI 连接属性 AutoCommit 设置为 false，您应使用以下语句：

# Create a hash that holds attributes for the connection
my $attr = {AutoCommit => 0};
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);

有关可在数据库连接上设置的属性的完整描述，请参阅 DBI 文档的 Database Handle Attributes（“数据库句柄属性”）一节。

脚本已连接之后，它可以通过将数据库句柄用作哈希引用来访问和修改连接属性。例如：

print "The AutoCommit attribute is: " . $dbh->{AutoCommit} . "\n";

以下示例演示了设置两个连接属性：

  • RaiseError 控制 DBI 驱动程序是否在遇到数据库错误时生成 Perl 错误。通常，如果希望 Perl 脚本在遇到数据库错误时退出，您可以将此属性设置为 true (1)。

  • AutoCommit 控制语句是否在完成后自动提交其事务。DBI 默认设置为 Vertica 的 AutoCommit 默认值 (true)。批量加载数据时，始终应将 AutoCommit 设置为 false (0) 以提高数据库效率。

#!/usr/bin/perl
use strict;
use DBI;
# Create a hash that holds attributes for the connection
 my $attr = {
                RaiseError => 1, # Make database errors fatal to script
                AutoCommit => 0, # Prevent statements from committing
                                 # their transactions.
            };
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);

if (defined $dbh->err) {
    # Connection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connected!\n";
# The database handle lets you access the connection attributes directly:
print "The AutoCommit attribute is: " . $dbh->{AutoCommit} . "\n";
print "The RaiseError attribute is: " . $dbh->{RaiseError} . "\n";
# And you can change values, too...
$dbh->{AutoCommit} = 1;
print "The AutoCommit attribute is now: " . $dbh->{AutoCommit} . "\n";
$dbh->disconnect();

运行该示例后，将输出以下内容：

Connected!The AutoCommit attribute is: 0
The RaiseError attribute is: 1
The AutoCommit attribute is now: 1

2.3 - 不使用 DSN 从 Perl 进行连接

如果不想为数据库设置数据源名称 (DSN)，您可以在数据源字符串中提供 Perl 的 DBD::ODBC 驱动程序连接到 Vertica 数据库所需的所有信息。此源字符串必须包含 DRIVER= 参数，以向 DBD::ODBC 说明应使用哪个驱动程序库进行连接。此参数的值是客户端系统的驱动程序管理器分配给驱动程序的名称。

  • 在 Windows 上，驱动程序管理器分配给 Vertica ODBC 驱动程序的名称是 Vertica。

  • 在 Linux 和其他类 UNIX 操作系统上，Vertica ODBC 驱动程序的名称在系统的 odbcinst.ini 文件中分配。例如，如果 /etc/odbcint.ini 包含以下内容：

    [Vertica]
Description = Vertica ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so

    您应使用名称 Vertica。有关 odbcinst.ini 文件的详细信息，请参阅为 Linux 创建 ODBC DSN。

可以在字符串中利用 Perl 的变量扩展，以将变量用于大部分连接属性，如以下示例所示。

#!/usr/bin/perl
use strict;
use DBI;
my $server='VerticaHost';
my $port = '5433';
my $database = 'VMart';
my $user = 'ExampleUser';
my $password = 'password123';
# Connect without a DSN by supplying all of the information for the connection.
# The DRIVER value on UNIX platforms depends on the entry in the odbcinst.ini
# file.
my $dbh = DBI->connect("dbi:ODBC:DRIVER={Vertica};Server=$server;" .
        "Port=$port;Database=$database;UID=$user;PWD=$password")
        or die "Could not connect to database: " . DBI::errstr;
print "Connected!\n";
$dbh->disconnect();

3 - 使用 Perl 执行语句

Perl 脚本已连接到 Vertica（请参阅使用 Perl 连接到 Vertica）之后，它可以使用 Perl DBI 模块的 do 函数执行将返回值而非结果集的简单语句。通常可以使用此函数执行 DDL 语句或诸如 COPY 等数据加载语句（请参阅在 Perl 中使用 COPY LOCAL 加载数据）。

#!/usr/bin/perl
use strict;
use DBI;
# Disable autocommit
 my $attr = {AutoCommit => 0};
# Open a connection using a DSN.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);
unless (defined $dbh) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
# You can use the do function to perform DDL commands.
# Drop any existing table.
$dbh->do("DROP TABLE IF EXISTS TEST CASCADE;");
# Create a table to hold data.
$dbh->do("CREATE TABLE TEST( \
               C_ID  INT, \
               C_FP  FLOAT,\
               C_VARCHAR VARCHAR(100),\
               C_DATE DATE, C_TIME TIME,\
               C_TS TIMESTAMP,\
               C_BOOL BOOL)");
# Commit changes and exit.
$dbh->commit();
$dbh->disconnect();

4 - 使用 Perl 批量加载数据

要使用 Perl 将大批量数据加载到 Vertica：

  1. 将 DBI 的 AutoCommit 连接属性设置为 false，以提高批量加载速度。有关禁用 AutoCommit 的示例，请参阅设置 Perl DBI 连接属性。

  2. 调用数据库句柄的 prepare 函数以准备 SQL INSERT 语句（包含要插入的数据值的占位符）。例如：

    # Prepare an INSERT statement for the test table
$sth = $dbh->prepare("INSERT into test values(?,?,?,?,?,?,?)");

    prepare 函数将返回一个语句句柄，您可以使用此句柄来插入数据。

  3. 为占位符分配数据。有几种方法可用于执行此操作。最简单的方法是使用 INSERT 语句中每个占位符的值来填充数组。

  4. 调用该语句句柄的 execute 函数以将数据行插入到 Vertica。此函数调用的返回值指示 Vertica 已接受还是已拒绝该行。

  5. 重复步骤 3 和 4，直至已加载所需的所有数据为止。

  6. 调用数据库句柄的 commit 函数以提交已插入的数据。

以下示例演示了通过以下方法插入小批量数据：使用数据填充一系列数组，然后在其中循环并插入每个行。

#!/usr/bin/perl
use strict;
use DBI;
# Create a hash reference that holds a hash of parameters for the
# connection.
 my $attr = {AutoCommit => 0, # Turn off autocommit
             PrintError => 0   # Turn off automatic error printing.
                               # This is handled manually.
             };
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);
if (defined DBI::err) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connection AutoCommit state is: " . $dbh->{AutoCommit} . "\n";
# Create table to hold inserted data
$dbh->do("DROP TABLE IF EXISTS TEST CASCADE;") or die "Could not drop table";
$dbh->do("CREATE TABLE TEST( \
               C_ID  INT, \
               C_FP  FLOAT,\
               C_VARCHAR VARCHAR(100),\
               C_DATE DATE, C_TIME TIME,\
               C_TS TIMESTAMP,\
               C_BOOL BOOL)") or die "Could not create table";
# Populate an array of arrays with values. One of these rows contains
# data that will not be sucessfully inserted. Another contains an
# undef value, which gets inserted into the database as a NULL.
my @data = (
                [1,1.111,'Hello World!','2001-01-01','01:01:01'
                    ,'2001-01-01 01:01:01','t'],
                [2,2.22222,'How are you?','2002-02-02','02:02:02'
                    ,'2002-02-02 02:02:02','f'],
                ['bad value',2.22222,'How are you?','2002-02-02','02:02:02'
                    ,'2002-02-02 02:02:02','f'],
                [4,4.22222,undef,'2002-02-02','02:02:02'
                    ,'2002-02-02 02:02:02','f'],
             );
# Create a prepared statement to use parameters for inserting values.
my $sth = $dbh->prepare_cached("INSERT into test values(?,?,?,?,?,?,?)");
my $rowcount = 0; # Count # of rows
# Loop through the arrays to insert values
foreach my $tuple (@data) {
    $rowcount++;
    # Insert the row
    my $retval = $sth->execute(@$tuple);

    # See if the row was successfully inserted.
    if ($retval == 1) {
        # Value of 1 means the row was inserted (1 row was affected by insert)
        print "Row $rowcount successfully inserted\n";
    } else {
        print "Inserting row $rowcount failed";
        # Error message is not set on some platforms/versions of DBUI. Check to
        # ensure a message exists to avoid getting an unitialized var warning.
        if ($sth->err()) {
                print ": " . $sth->errstr();
        }
        print "\n";
    }
}
# Commit changes. With AutoCommit off, you need to use commit for batched
# data to actually be committed into the database. If your Perl script exits
# without committing its data, Vertica rolls back the transaction and the
# data is not committed.
$dbh->commit();
$dbh->disconnect();

成功运行以上示例后，将显示以下内容：

Connection AutoCommit state is: 0
Row 1 successfully inserted
Row 2 successfully inserted
Inserting row 3 failed with error 01000 [Vertica][VerticaDSII] (20) An
error occurred during query execution: Row rejected by server; see
server log for details (SQL-01000)
Row 4 successfully inserted

请注意，未插入其中一个行，因为该行包含无法存储在整数列中的字符串值。有关与 Vertica 通信的 Perl 脚本中数据类型处理的详细信息，请参阅 Perl 数据类型和 Vertica 数据类型之间的转换。

5 - 在 Perl 中使用 COPY LOCAL 加载数据

您可以使用 COPY LOCAL 将客户端系统上的分隔文件（例如具有逗号分隔值的文件）加载到 Vertica 中。COPY LOCAL 不是使用 Perl 读取、解析并批量插入文件数据，而是直接将文件数据从本地文件系统加载到 Vertica 中。执行完成后，COPY LOCAL 返回它成功插入的行数。

以下示例使用 COPY LOCAL 加载到 Vertica 本地文件 data.txt 中，该文件与 Perl 文件位于同一目录中。

#!/usr/bin/perl
use strict;
use DBI;
# Filesystem path handling module
use File::Spec;
# Create a hash reference that holds a hash of parameters for the
# connection.
 my $attr = {AutoCommit => 0}; # Turn off AutoCommit
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr) or die "Failed to connect: $DBI::errstr";
print "Connected!\n";
# Drop any existing table.
$dbh->do("DROP TABLE IF EXISTS Customers CASCADE;");
# Create a table to hold data.
$dbh->do("CREATE TABLE Customers( \
               ID  INT, \
               FirstName  VARCHAR(100),\
               LastName   VARCHAR(100),\
               Email      VARCHAR(100),\
               Birthday   DATE)");
# Find the absolute path to the data file located in the current working
# directory and named data.txt
my $currDir = File::Spec->rel2abs(File::Spec->curdir());
my $dataFile = File::Spec->catfile($currDir, 'data.txt');
print "Loading file $dataFile\n";
# Load local file using copy local. Return value is the # of rows affected
# which equates to the number of rows inserted.
my $rows = $dbh->do("COPY Customers FROM LOCAL '$dataFile' DIRECT")
     or die $dbh->errstr;
print "Copied $rows rows into database.\n";
$dbh->commit();
# Prepare a query to get the first 15 rows of the results
my $sth = $dbh->prepare("SELECT * FROM Customers WHERE ID < 15 \
                                 ORDER BY ID");

$sth->execute() or die "Error querying table: " . $dbh->errstr;
my @row; # Pre-declare variable to hold result row used in format statement.
# Use Perl formats to pretty print the output. Declare the heading for the
# form.
format STDOUT_TOP =
ID  First           Last          EMail                         Birthday
==  =====           ====          =====                         ========
.
# The Perl write statement will output a formatted line with values from the
# @row array. See http://perldoc.perl.org/perlform.html for details.
format STDOUT =
@>  @<<<<<<<<<<<<<  @<<<<<<<<<<<  @<<<<<<<<<<<<<<<<<<<<<<<<<<<  @<<<<<<<<<
@row
.
# Loop through result rows while we have them
while (@row = $sth->fetchrow_array()) {
         write; # Format command does the work of extracting the columns from
                # the @row array and writing them out to STDOUT.
}
# Call commit to prevent Perl from complaining about uncommitted transactions
# when disconnecting
$dbh->commit();
$dbh->disconnect();

data.txt 是一个文本文件，其中的每一行都包含一行数据。列由管线字符 (|) 分隔。这是命令接受的默认 COPY 分隔符，它简化了 COPY LOCAL 语句。

下面是文件内容的示例：

1|Georgia|Gomez|Rhiannon@magna.us|1937-10-03
2|Abdul|Alexander|Kathleen@ipsum.gov|1941-03-10
3|Nigel|Contreras|Tanner@et.com|1955-06-01
4|Gray|Holt|Thomas@Integer.us|1945-12-06
5|Candace|Bullock|Scott@vitae.gov|1932-05-27
6|Matthew|Dotson|Keith@Cras.com|1956-09-30
7|Haviva|Hopper|Morgan@porttitor.edu|1975-05-10
8|Stewart|Sweeney|Rhonda@lectus.us|2003-06-20
9|Allen|Rogers|Alexander@enim.gov|2006-06-17
10|Trevor|Dillon|Eagan@id.org|1988-11-27
11|Leroy|Ashley|Carter@turpis.edu|1958-07-25
12|Elmo|Malone|Carla@enim.edu|1978-08-29
13|Laurel|Ball|Zelenia@Integer.us|1989-09-20
14|Zeus|Phillips|Branden@blandit.gov|1996-08-08
15|Alexis|Mclean|Flavia@Suspendisse.org|2008-01-07

对大型示例文件运行以上示例代码后，将生成以下输出：

Connected!
Loading file /home/dbadmin/Perl/data.txt
Copied 1000000 rows into database.
ID  First           Last          EMail                         Birthday
==  =====           ====          =====                         ========
 1  Georgia         Gomez         Rhiannon@magna.us             1937-10-03
 2  Abdul           Alexander     Kathleen@ipsum.gov            1941-03-10
 3  Nigel           Contreras     Tanner@et.com                 1955-06-01
 4  Gray            Holt          Thomas@Integer.us             1945-12-06
 5  Candace         Bullock       Scott@vitae.gov               1932-05-27
 6  Matthew         Dotson        Keith@Cras.com                1956-09-30
 7  Haviva          Hopper        Morgan@porttitor.edu          1975-05-10
 8  Stewart         Sweeney       Rhonda@lectus.us              2003-06-20
 9  Allen           Rogers        Alexander@enim.gov            2006-06-17
10  Trevor          Dillon        Eagan@id.org                  1988-11-27
11  Leroy           Ashley        Carter@turpis.edu             1958-07-25
12  Elmo            Malone        Carla@enim.edu                1978-08-29
13  Laurel          Ball          Zelenia@Integer.us            1989-09-20
14  Zeus            Phillips      Branden@blandit.gov           1996-08-08

6 - 使用 Perl 执行查询

要使用 Perl 查询 Vertica：

  1. 使用 Perl DBI 模块的 prepare 函数准备查询语句。此函数将返回一个语句句柄，您可以使用此句柄来执行查询和获取结果集。

  2. 通过对该语句句柄调用 execute 函数来执行预定义的语句。

  3. 使用多种方法之一从语句句柄检索查询结果，例如，调用语句句柄的 fetchrow_array 函数以检索数据行，或者调用 fetchall_array 以获取包含整个结果集的一系列数组（如果结果集可能非常大，则此方法不太适用！）。

以下示例演示了查询由使用 Perl 批量加载数据中所示的示例创建的表。此示例将执行查询以检索该表的所有内容，然后重复调用 fetchrow_array 函数以获取数组中的数据行。此示例将重复上述过程直至 fetchrow_array 返回 undef 为止，该返回值表示没有更多数据行可供读取。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
                        $attr);
# Prepare a query to get the content of the table
my $sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
# Execute the query by calling execute on the statement handle
$sth->execute();
# Loop through result rows while we have them, getting each row as an array
while (my @row = $sth->fetchrow_array()) {
    # The @row array contains the column values for this row of data
    # Loop through the column values
    foreach my $column (@row) {
        if (!defined $column) {
            # NULLs are signaled by undefs. Set to NULL for clarity
            $column = "NULL";
        }
        print "$column\t"; # Output the column separated by a tab
    }
    print "\n";
}
$dbh->disconnect();

该示例在运行后输出了以下内容：

1    1.111    Hello World!    2001-01-01    01:01:01    2001-01-01 01:01:01    1
2    2.22222    How are you?    2002-02-02    02:02:02    2002-02-02 02:02:02    0
4    4.22222    NULL    2002-02-02    02:02:02    2002-02-02 02:02:02    0

将变量绑定到列值

检索查询结果的另一种方法是使用语句句柄的 bind_columns 函数将变量绑定到结果集中的列。如果需要对返回的数据执行大量处理，您可能会这种方法很方便，因为代码可以使用变量而非数组引用来访问数据。以下示例演示了将变量绑定到结果集而非在行值和列值中循环。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make SQL errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN32","ExampleUser","password123",
                        $attr);
# Prepare a query to get the content of the table
my $sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
$sth->execute();
# Create a set of variables to bind to the column values.
my ($C_ID, $C_FP, $C_VARCHAR, $C_DATE, $C_TIME, $C_TS, $C_BOOL);
# Bind the variable references to the columns in the result set.
$sth->bind_columns(\$C_ID, \$C_FP, \$C_VARCHAR, \$C_DATE, \$C_TIME,
                    \$C_TS, \$C_BOOL);

# Now, calling fetch() to get a row of data updates the values of the bound
# variables. Continue calling fetch until it returns undefined.
while ($sth->fetch()) {
    # Note, you should always check that values are defined before using them,
    # since NULL values are translated into Perl as undefined. For this
    # example, just check the VARCHAR column for undefined values.
    if (!defined $C_VARCHAR) {
        $C_VARCHAR = "NULL";
    }
    # Just print values separated by tabs.
    print "$C_ID\t$C_FP\t$C_VARCHAR\t$C_DATE\t$C_TIME\t$C_TS\t$C_BOOL\n";
}
$dbh->disconnect();

此示例的输出与上一个示例的输出相同。

准备、查询和返回单个行

如果希望查询结果是单个行（例如在执行 COUNT (*) 查询时），您可以使用 DBI 模块的 selectrow_array 函数将语句执行和数组检索合并为单个结果。

以下示例显示了使用 selectrow_array 执行 SHOW LOCALE 语句并获取其结果。此示例还演示了使用 do 函数更改区域设置。

#!/usr/bin/perl
use strict;
use DBI;
my $attr = {RaiseError => 1 }; # Make SQL errors fatal to the Perl script.
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
                        $attr);
# Demonstrate setting/getting locale.
# Use selectrow_array to combine preparing a statement, executing it, and
# getting an array as a result.
my @localerv = $dbh->selectrow_array("SHOW LOCALE;");
# The locale name is the 2nd column (array index 1) in the result set.
print "Locale: $localerv[1]\n";
# Use do() to execute a SQL statement to set the locale.
$dbh->do("SET LOCALE TO en_GB");
# Get the locale again.
@localerv = $dbh->selectrow_array("SHOW LOCALE;");
print "Locale is now: $localerv[1]\n";
$dbh->disconnect();

运行此示例后的结果如下所示：

Locale: en_US@collation=binary (LEN_KBINARY)
Locale is now: en_GB (LEN)

执行查询和 ResultBufferSize 设置

当您在准备好的语句上调用 execute() 函数时，客户端库会一直检索结果，直到达到结果缓冲区大小。结果缓冲区大小是使用 ODBC 的 ResultBufferSize 设置进行设置的。

Vertica 不允许每个连接有多个活动查询。不过，您可以模拟多个活动查询，方法是将结果缓冲区设置得大到足以容纳第一个查询的全部结果。为了确保 ODBC 客户端驱动程序的缓冲区大到足以存储第一个查询的结果集，您可以将 ResultBufferSize 设置为 0。将此参数设置为 0 会使结果缓冲区大小不受限制。ODBC 驱动程序分配足够的内存来读取整个结果集。将第一个查询的整个结果集存储在结果集缓冲区后，数据库连接便可以自由地执行另一个查询。您的客户端可以执行第二个查询，即使它尚未处理第一个查询中的整个结果集。

但是，如果您将 ResultBufferSize 设置为 0，您可能会发现对 execute() 的调用会导致操作系统终止您的 Perl 客户端脚本。如果 ODBC 驱动程序分配了太多内存来存储大型结果集，操作系统可能会终止您的脚本。

此行为的解决方法是限制查询返回的行数。然后，您可以将 ResultBufferSize 设置为适合此有限结果集的值。例如，您可以估计为了存储单行查询结果而所需的内存量。然后，使用 LIMIT 和 OFFSET 子句来获得适合您借助于 ResultBufferSize 所分配空间的具体行数。如果查询结果能够适合有限结果集缓冲区，则您可以使用相同的数据库连接来执行其他查询。此解决方案使您的代码更加复杂，因为您需要执行多个查询才能获得整个结果集。此外，如果您需要一次对整个结果集进行操作，而不是一次只对其一部分进行操作，那么此解决方案也不适合。

更好的解决方案是针对要执行的每个查询使用单独的数据库连接。与处理大型数据集所需的资源相比，建立额外数据库连接所需的开销很小。

7 - Perl 数据类型和 Vertica 数据类型之间的转换

Perl 是一种弱类型编程语言，这种语言不为值分配特定的数据类型。这种语言基于对值执行的操作在字符串和数值之间转换。因此，Perl 在从 Vertica 提取大部分字符串和数值数据类型时遇到的问题较小。所有时间间隔数据类型（DATE 和 TIMESTAMP 等）会转换为字符串。可以使用多个不同的 Perl 日期和时间处理模块在脚本中处理这些值。

Vertica NULL 值会转换为 Perl 的未定义的值 (undef)。从包含 NULL 值的列读取数据时，始终应在使用某个值之前测试是否已定义该值。

将数据插入到 Vertica 时，Perl DBI 模块会尝试将数据强制转换为正确格式。默认情况下，该模块假设列值为 VARCHAR，除非它能够确定这些列值为某种其他数据类型。如果提供了一个字符串值以插入到具有整数或数值数据类型的列，DBI 会尝试将该字符串的内容转换为正确的数据类型。如果整个字符串可以转换为适当数据类型的值，该模块会将值插入到列中。否则，插入数据行将失败。

当插入到 FLOAT、NUMERIC 或类似数据类型的列时，DBI 以透明方式将整数值转换为数值或浮点值。仅当不会降低精度（小数点右边的值为 0）时，该模块才会将数值或浮点值转换为整数。例如，该模块可以将值 3.0 插入到 INTEGER 列中，因为将该值转换为整数时不会降低精度。该模块无法将 3.1 插入到 INTEGER 列中，因为这样会降低精度。该模块将返回错误，而非将该值截断为 3。

以下示例演示了 DBI 模块在将数据插入到 Vertica 时执行的一些转换。

#!/usr/bin/perl
use strict;
use DBI;
# Create a hash reference that holds a hash of parameters for the
# connection.
 my $attr = {AutoCommit => 0, # Turn off autocommit
             PrintError => 0   # Turn off print error. Manually handled
             };
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);
if (defined DBI::err) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connection AutoCommit state is: " . $dbh->{AutoCommit} . "\n";
# Create table to hold inserted data
$dbh->do("DROP TABLE IF EXISTS TEST CASCADE;");
$dbh->do("CREATE TABLE TEST( \
               C_ID  INT, \
               C_FP  FLOAT,\
               C_VARCHAR VARCHAR(100),\
               C_DATE DATE, C_TIME TIME,\
               C_TS TIMESTAMP,\
               C_BOOL BOOL)");
# Populate an array of arrays with values.
my @data = (
                # Start with matching data types
                [1,1.111,'Matching datatypes','2001-01-01','01:01:01'
                    ,'2001-01-01 01:01:01','t'],
                # Force floats -> int and int -> float.
                [2.0,2,"Ints <-> floats",'2002-02-02','02:02:02'
                    ,'2002-02-02 02:02:02',1],
                # Float -> int *only* works when there is no loss of precision.
                # this row will fail to insert:
                [3.1,3,"float -> int with trunc?",'2003-03-03','03:03:03'
                    ,'2003-03-03 03:03:03',1],
                # String values are converted into numbers
                ["4","4.4","Strings -> numbers", '2004-04-04','04:04:04',
                    ,'2004-04-04 04:04:04',0],
                # String -> numbers only works if the entire string can be
                # converted into a number
                ["5 and a half","5.5","Strings -> numbers", '2005-05-05',
                    '05:05:05', ,'2005-05-05 05:05:05',0],
                # Number are converted into string values automatically,
                # assuming they fit into the column width.
                [6,6.6,3.14159, '2006-06-06','06:06:06',
                    ,'2006-06-06 06:06:06',0],
                # There are some variations in the accepted date strings
                [7,7.7,'Date/time formats', '07/07/2007','07:07:07',
                    ,'07-07-2007 07:07:07',1],
             );
# Create a prepared statement to use parameters for inserting values.
my $sth = $dbh->prepare_cached("INSERT into test values(?,?,?,?,?,?,?)");
my $rowcount = 0; # Count # of rows
# Loop through the arrays to insert values
foreach my $tuple (@data) {
    $rowcount++;
    # Insert the row
    my $retval = $sth->execute(@$tuple);

    # See if the row was successfully inserted.
    if ($retval == 1) {
        # Value of 1 means the row was inserted (1 row was affected by insert)
        print "Row $rowcount successfully inserted\n";
    } else {
        print "Inserting row $rowcount failed with error " .
                $sth->state . " " . $sth->errstr . "\n";
    }
}
# Commit the data
$dbh->commit();
# Prepare a query to get the content of the table
$sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
$sth->execute() or die "Error: " . $dbh->errstr;
my @row; # Need to pre-declare to use in the format statement.
# Use Perl formats to pretty print the output.
format STDOUT_TOP =
Int  Float          VarChar        Date      Time      Timestamp     Bool
===  =====  ==================  ========== ======== ================ ====
.
format STDOUT =
@>>  @<<<<  @<<<<<<<<<<<<<<<<<  @<<<<<<<<< @<<<<<<< @<<<<<<<<<<<<<<< @<<<<
@row
.
# Loop through result rows while we have them
while (@row = $sth->fetchrow_array()) {
         write; # Format command does the work of extracting the columns from
                 # the array.
}
# Commit to stop Perl complaining about in-progress transactions.
$dbh->commit();
$dbh->disconnect();

运行该示例后，将生成以下输出：

Connection AutoCommit state is: 0
Row 1 successfully inserted
Row 2 successfully inserted
Inserting row 3 failed with error 01000 [Vertica][VerticaDSII] (20) An error
occurred during query execution: Row rejected by server; see server log for
details (SQL-01000)
Row 4 successfully inserted
Inserting row 5 failed with error 01000 [Vertica][VerticaDSII] (20) An error
occurred during query execution: Row rejected by server; see server log for
details (SQL-01000)
Row 6 successfully inserted
Row 7 successfully inserted
Int  Float          VarChar        Date      Time      Timestamp     Bool
===  =====  ==================  ========== ======== ================ ====
  1  1.111  Matching datatypes  2001-01-01 01:01:01 2001-01-01 01:01 1
  2  2      Ints <-> floats     2002-02-02 02:02:02 2002-02-02 02:02 1
  4  4.4    Strings -> numbers  2004-04-04 04:04:04 2004-04-04 04:04 0
  6  6.6    3.14159             2006-06-06 06:06:06 2006-06-06 06:06 0
  7  7.7    Date/time formats   2007-07-07 07:07:07 2007-07-07 07:07 1

8 - Perl Unicode 支持

Perl 支持 Unicode 数据，但具有一些限制。有关详细信息，请参阅 perlunicode 手册页和 perlunitut（Perl Unicode 教程）手册页。（务必参阅随客户端系统上安装的 Perl 版本附带提供的这些手册页副本，因为 Unicode 支持在最新版本的 Perl 中已更改。）Perl DBI 和 DBD::ODBC 也支持 Unicode，但必须为 DBD::ODBC 编译 Unicode 支持。有关详细信息，请参阅 DBD::ODBC 文档。可以检查名为 odbc_has_unicode 的特定于 DBD::ODBC 的连接属性，以查看是否在该驱动程序中启用了 Unicode 支持。

以下示例 Perl 脚本演示了直接将 UTF-8 字符串插入到 Vertica 再从中读回这些字符串。此示例将输出写入到文本文件中，因为在终端窗口或控制台中显示 Unicode 字符时出现许多问题。

#!/usr/bin/perl
use strict;
use DBI;
# Open a connection using a DSN.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123");
unless (defined $dbh) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
# Output to a file. Displaying Unicode characters to a console or terminal
# window has many problems. This outputs a UTF-8 text file that can
# be handled by many Unicode-aware text editors:
open OUTFILE, '>:utf8', "unicodeout.txt";
# See if the DBD::ODBC driver was compiled with Unicode support. If this returns
# 1, your Perl script will get get strings from the driver with the UTF-8
# flag set on them, ensuring that Perl handles them correctly.
print OUTFILE "Was DBD::ODBC compiled with Unicode support? " .
    $dbh->{odbc_has_unicode} . "\n";

# Create a table to hold VARCHARs
$dbh->do("DROP TABLE IF EXISTS TEST CASCADE;");

# Create a table to hold data. Remember that the width of the VARCHAR column
# is the number of bytes set aside to store strings, which often does not equal
# the number of characters it can hold when it comes to Unicode!
$dbh->do("CREATE TABLE test( C_VARCHAR VARCHAR(100) )");
print OUTFILE "Inserting data...\n";
# Use Do to perform simple inserts
$dbh->do("INSERT INTO test VALUES('Hello')");
# This string contains several non-latin accented characters and symbols, encoded
# with Unicode escape notation. They are converted by Perl into UTF-8 characters
$dbh->do("INSERT INTO test VALUES('My favorite band is " .
    "\N{U+00DC}ml\N{U+00E4}\N{U+00FC}t \N{U+00D6}v\N{U+00EB}rk\N{U+00EF}ll" .
    " \N{U+263A}')");
# Some Chinese (Simplified) characters. This again uses escape sequence
# that Perl translates into UTF-8 characters.
$dbh->do("INSERT INTO test VALUES('\x{4F60}\x{597D}')");
print OUTFILE "Getting data...\n";
# Prepare a query to get the content of the table
my $sth = $dbh->prepare_cached("SELECT * FROM test");
# Execute the query by calling execute on the statement handle
$sth->execute();
# Loop through result rows while we have them
while (my @row = $sth->fetchrow_array()) {
    # Loop through the column values
    foreach my $column (@row) {
        print OUTFILE "$column\t";
    }
    print OUTFILE "\n";
}
close OUTFILE;
$dbh->disconnect();

在支持 UTF-8 的文本编辑器或查看器中查看 unicodeout.txt 文件时，显示以下内容：

Was DBD::ODBC compiled with Unicode support? 1
Inserting data...
Getting data...
My favorite band is Ümläüt Övërkïll ☺
你好
Hello

另请参阅