dbi.3

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

A better solution is a single \f(CW\*(C`WHERE\*(C'\fR clause that supports both
\&\s-1NULL\s0 and non-NULL comparisons.  Its \s-1SQL\s0 statement would need to be
prepared only once for all cases, thus improving performance.
Several examples of \f(CW\*(C`WHERE\*(C'\fR clauses that support this are presented
below.  But each example lacks portability, robustness, or simplicity.
Whether an example is supported on your database engine depends on
what \s-1SQL\s0 extensions it provides, and where it supports the \f(CW\*(C`?\*(C'\fR
placeholder in a statement.
.PP
.Vb 7
\&  0)  age = ?
\&  1)  NVL(age, xx) = NVL(?, xx)
\&  2)  ISNULL(age, xx) = ISNULL(?, xx)
\&  3)  DECODE(age, ?, 1, 0) = 1
\&  4)  age = ? OR (age IS NULL AND ? IS NULL)
\&  5)  age = ? OR (age IS NULL AND SP_ISNULL(?) = 1)
\&  6)  age = ? OR (age IS NULL AND ? = 1)
.Ve
.PP
Statements formed with the above \f(CW\*(C`WHERE\*(C'\fR clauses require execute
statements as follows.  The arguments are required, whether their
values are \f(CW\*(C`defined\*(C'\fR or \f(CW\*(C`undef\*(C'\fR.
.PP
.Vb 3
\&  0,1,2,3)  $sth\->execute($age);
\&  4,5)      $sth\->execute($age, $age);
\&  6)        $sth\->execute($age, defined($age) ? 0 : 1);
.Ve
.PP
Example 0 should not work (as mentioned earlier), but may work on
a few database engines anyway (e.g. Sybase).  Example 0 is part
of examples 4, 5, and 6, so if example 0 works, these other
examples may work, even if the engine does not properly support
the right hand side of the \f(CW\*(C`OR\*(C'\fR expression.
.PP
Examples 1 and 2 are not robust: they require that you provide a
valid column value xx (e.g. '~') which is not present in any row.
That means you must have some notion of what data won't be stored
in the column, and expect clients to adhere to that.
.PP
Example 5 requires that you provide a stored procedure (\s-1SP_ISNULL\s0
in this example) that acts as a function: it checks whether a value
is null, and returns 1 if it is, or 0 if not.
.PP
Example 6, the least simple, is probably the most portable, i.e., it
should work with with most, if not all, database engines.
.PP
Here is a table that indicates which examples above are known to
work on various database engines:
.PP
.Vb 9
\&                   \-\-\-\-\-Examples\-\-\-\-\-\-
\&                   0  1  2  3  4  5  6
\&                   \-  \-  \-  \-  \-  \-  \-
\&  Oracle 9         N  Y  N  Y  Y  ?  Y
\&  Informix IDS 9   N  N  N  Y  N  Y  Y
\&  MS SQL           N  N  Y  N  Y  ?  Y
\&  Sybase           Y  N  N  N  N  N  Y
\&  AnyData,DBM,CSV  Y  N  N  N  Y  Y* Y
\&  SQLite 3.3       N  N  N  N  Y  N  N
.Ve
.PP
* Works only because Example 0 works.
.PP
\&\s-1DBI\s0 provides a sample perl script that will test the examples above
on your database engine and tell you which ones work.  It is located
in the \fIex/\fR subdirectory of the \s-1DBI\s0 source distribution, or here:
<http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl>
Please use the script to help us fill-in and maintain this table.
.PP
\&\fBPerformance\fR
.PP
Without using placeholders, the insert statement shown previously would have to
contain the literal values to be inserted and would have to be
re-prepared and re-executed for each row. With placeholders, the insert
statement only needs to be prepared once. The bind values for each row
can be given to the \f(CW\*(C`execute\*(C'\fR method each time it's called. By avoiding
the need to re-prepare the statement for each row, the application
typically runs many times faster. Here's an example:
.PP
.Vb 9
\&  my $sth = $dbh\->prepare(q{
\&    INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
\&  }) or die $dbh\->errstr;
\&  while (<>) {
\&      chomp;
\&      my ($product_code, $qty, $price) = split /,/;
\&      $sth\->execute($product_code, $qty, $price) or die $dbh\->errstr;
\&  }
\&  $dbh\->commit or die $dbh\->errstr;
.Ve
.PP
See \*(L"execute\*(R" and \*(L"bind_param\*(R" for more details.
.PP
The \f(CW\*(C`q{...}\*(C'\fR style quoting used in this example avoids clashing with
quotes that may be used in the \s-1SQL\s0 statement. Use the double-quote like
\&\f(CW\*(C`qq{...}\*(C'\fR operator if you want to interpolate variables into the string.
See \*(L"Quote and Quote-like Operators\*(R" in perlop for more details.
.PP
See also the \*(L"bind_columns\*(R" method, which is used to associate Perl
variables with the output columns of a \f(CW\*(C`SELECT\*(C'\fR statement.
.SH "THE DBI PACKAGE AND CLASS"
.IX Header "THE DBI PACKAGE AND CLASS"
In this section, we cover the \s-1DBI\s0 class methods, utility functions,
and the dynamic attributes associated with generic \s-1DBI\s0 handles.
.Sh "\s-1DBI\s0 Constants"
.IX Subsection "DBI Constants"
Constants representing the values of the \s-1SQL\s0 standard types can be
imported individually by name, or all together by importing the
special \f(CW\*(C`:sql_types\*(C'\fR tag.
.PP
The names and values of all the defined \s-1SQL\s0 standard types can be
produced like this:
.PP
.Vb 3
\&  foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
\&    printf "%s=%d\en", $_, &{"DBI::$_"};
\&  }
.Ve
.PP
These constants are defined by \s-1SQL/CLI\s0, \s-1ODBC\s0 or both.
\&\f(CW\*(C`SQL_BIGINT\*(C'\fR is (currently) omitted, because \s-1SQL/CLI\s0 and \s-1ODBC\s0 provide
conflicting codes.
.PP
See the \*(L"type_info\*(R", \*(L"type_info_all\*(R", and \*(L"bind_param\*(R" methods
for possible uses.
.PP
Note that just because the \s-1DBI\s0 defines a named constant for a given
data type doesn't mean that drivers will support that data type.
.Sh "\s-1DBI\s0 Class Methods"
.IX Subsection "DBI Class Methods"
The following methods are provided by the \s-1DBI\s0 class:
.PP
\fI\f(CI\*(C`parse_dsn\*(C'\fI\fR
.IX Subsection "parse_dsn"
.PP
.Vb 2
\&  ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI\->parse_dsn($dsn)
\&      or die "Can\*(Aqt parse DBI DSN \*(Aq$dsn\*(Aq";
.Ve
.PP
Breaks apart a \s-1DBI\s0 Data Source Name (\s-1DSN\s0) and returns the individual
parts. If \f(CW$dsn\fR doesn't contain a valid \s-1DSN\s0 then \fIparse_dsn()\fR returns
an empty list.
.PP
\&\f(CW$scheme\fR is the first part of the \s-1DSN\s0 and is currently always 'dbi'.
\&\f(CW$driver\fR is the driver name, possibly defaulted to \f(CW$ENV\fR{\s-1DBI_DRIVER\s0},
and may be undefined.  \f(CW$attr_string\fR is the contents of the optional attribute
string, which may be undefined.  If \f(CW$attr_string\fR is not empty then \f(CW$attr_hash\fR
is a reference to a hash containing the parsed attribute names and values.
\&\f(CW$driver_dsn\fR is the last part of the \s-1DBI\s0 \s-1DSN\s0 string. For example:
.PP
.Vb 7
\&  ($scheme, $driver, $attr_string, $attr_hash, $driver_dsn)
\&      = DBI\->parse_dsn("DBI:MyDriver(RaiseError=>1):db=test;port=42");
\&  $scheme      = \*(Aqdbi\*(Aq;
\&  $driver      = \*(AqMyDriver\*(Aq;
\&  $attr_string = \*(AqRaiseError=>1\*(Aq;
\&  $attr_hash   = { \*(AqRaiseError\*(Aq => \*(Aq1\*(Aq };
\&  $driver_dsn  = \*(Aqdb=test;port=42\*(Aq;
.Ve
.PP
The \fIparse_dsn()\fR method was added in \s-1DBI\s0 1.43.
.PP
\fI\f(CI\*(C`connect\*(C'\fI\fR
.IX Subsection "connect"
.PP
.Vb 4
\&  $dbh = DBI\->connect($data_source, $username, $password)
\&            or die $DBI::errstr;
\&  $dbh = DBI\->connect($data_source, $username, $password, \e%attr)
\&            or die $DBI::errstr;
.Ve
.PP
Establishes a database connection, or session, to the requested \f(CW$data_source\fR.
Returns a database handle object if the connection succeeds. Use
\&\f(CW\*(C`$dbh\->disconnect\*(C'\fR to terminate the connection.
.PP
If the connect fails (see below), it returns \f(CW\*(C`undef\*(C'\fR and sets both \f(CW$DBI::err\fR
and \f(CW$DBI::errstr\fR. (It does \fInot\fR explicitly set \f(CW$!\fR.) You should generally
test the return status of \f(CW\*(C`connect\*(C'\fR and \f(CW\*(C`print $DBI::errstr\*(C'\fR if it has failed.
.PP
Multiple simultaneous connections to multiple databases through multiple
drivers can be made via the \s-1DBI\s0. Simply make one \f(CW\*(C`connect\*(C'\fR call for each
database and keep a copy of each returned database handle.
.PP
The \f(CW$data_source\fR value must begin with "\f(CW\*(C`dbi:\*(C'\fR\fIdriver_name\fR\f(CW\*(C`:\*(C'\fR".
The \fIdriver_name\fR specifies the driver that will be used to make the
connection. (Letter case is significant.)
.PP
As a convenience, if the \f(CW$data_source\fR parameter is undefined or empty,
the \s-1DBI\s0 will substitute the value of the environment variable \f(CW\*(C`DBI_DSN\*(C'\fR.
If just the \fIdriver_name\fR part is empty (i.e., the \f(CW$data_source\fR
prefix is "\f(CW\*(C`dbi::\*(C'\fR"), the environment variable \f(CW\*(C`DBI_DRIVER\*(C'\fR is
used. If neither variable is set, then \f(CW\*(C`connect\*(C'\fR dies.
.PP
Examples of \f(CW$data_source\fR values are:
.PP
.Vb 3
\&  dbi:DriverName:database_name
\&  dbi:DriverName:database_name@hostname:port
\&  dbi:DriverName:database=database_name;host=hostname;port=port
.Ve
.PP
There is \fIno standard\fR for the text following the driver name. Each
driver is free to use whatever syntax it wants. The only requirement the
\&\s-1DBI\s0 makes is that all the information is supplied in a single string.
You must consult the documentation for the drivers you are using for a
description of the syntax they require.
.PP
It is recommended that drivers support the \s-1ODBC\s0 style, shown in the
last example above. It is also recommended that that they support the
three common names '\f(CW\*(C`host\*(C'\fR', '\f(CW\*(C`port\*(C'\fR', and '\f(CW\*(C`database\*(C'\fR' (plus '\f(CW\*(C`db\*(C'\fR'
as an alias for \f(CW\*(C`database\*(C'\fR). This simplifies automatic construction
of basic DSNs: \f(CW"dbi:$driver:database=$db;host=$host;port=$port"\fR.
Drivers should aim to 'do something reasonable' when given a \s-1DSN\s0
in this form, but if any part is meaningless for that driver (such
as 'port' for Informix) it should generate an error if that part
is not empty.
.PP
If the environment variable \f(CW\*(C`DBI_AUTOPROXY\*(C'\fR is defined (and the
driver in \f(CW$data_source\fR is not "\f(CW\*(C`Proxy\*(C'\fR") then the connect request
will automatically be changed to:
.PP
.Vb 1
\&  $ENV{DBI_AUTOPROXY};dsn=$data_source
.Ve
.PP
\&\f(CW\*(C`DBI_AUTOPROXY\*(C'\fR is typically set as "\f(CW\*(C`dbi:Proxy:hostname=...;port=...\*(C'\fR".
If \f(CW$ENV\fR{\s-1DBI_AUTOPROXY\s0} doesn't begin with '\f(CW\*(C`dbi:\*(C'\fR' then \*(L"dbi:Proxy:\*(R"
will be prepended to it first.  See the DBD::Proxy documentation
for more details.
.PP
If \f(CW$username\fR or \f(CW$password\fR are undefined (rather than just empty),
then the \s-1DBI\s0 will substitute the values of the \f(CW\*(C`DBI_USER\*(C'\fR and \f(CW\*(C`DBI_PASS\*(C'\fR
environment variables, respectively.  The \s-1DBI\s0 will warn if the
environment variables are not defined.  However, the everyday use
of these environment variables is not recommended for security
reasons. The mechanism is primarily intended to simplify testing.
See below for alternative way to specify the username and password.
.PP
\&\f(CW\*(C`DBI\->connect\*(C'\fR automatically installs the driver if it has not been
installed yet. Driver installation either returns a valid driver
handle, or it \fIdies\fR with an error message that includes the string
"\f(CW\*(C`install_driver\*(C'\fR" and the underlying problem. So \f(CW\*(C`DBI\->connect\*(C'\fR
will die
on a driver installation failure and will only return \f(CW\*(C`undef\*(C'\fR on a
connect failure, in which case \f(CW$DBI::errstr\fR will hold the error message.
Use \f(CW\*(C`eval { ... }\*(C'\fR if you need to catch the "\f(CW\*(C`install_driver\*(C'\fR" error.
.PP
The \f(CW$data_source\fR argument (with the "\f(CW\*(C`dbi:...:\*(C'\fR" prefix removed) and the
\&\f(CW$username\fR and \f(CW$password\fR arguments are then passed to the driver for
processing. The \s-1DBI\s0 does not define any interpretation for the
contents of these fields.  The driver is free to interpret the
\&\f(CW$data_source\fR, \f(CW$username\fR, and \f(CW$password\fR fields in any way, and supply
whatever defaults are appropriate for the engine being accessed.
(Oracle, for example, uses the \s-1ORACLE_SID\s0 and \s-1TWO_TASK\s0 environment
variables if no \f(CW$data_source\fR is specified.)
.PP
The \f(CW\*(C`AutoCommit\*(C'\fR and \f(CW\*(C`PrintError\*(C'\fR attributes for each connection
default to \*(L"on\*(R". (See \*(L"AutoCommit\*(R" and \*(L"PrintError\*(R" for more information.)
However, it is strongly recommended that you explicitly define \f(CW\*(C`AutoCommit\*(C'\fR
rather than rely on the default. The \f(CW\*(C`PrintWarn\*(C'\fR attribute defaults to
on if $^W is true, i.e., perl is running with warnings enabled.
.PP
The \f(CW\*(C`\e%attr\*(C'\fR parameter can be used to alter the default settings of
\&\f(CW\*(C`PrintError\*(C'\fR, \f(CW\*(C`RaiseError\*(C'\fR, \f(CW\*(C`AutoCommit\*(C'\fR, and other attributes. For example:
.PP
.Vb 4
\&  $dbh = DBI\->connect($data_source, $user, $pass, {
\&        PrintError => 0,
\&        AutoCommit => 0
\&  });
.Ve
.PP
The username and password can also be specified using the attributes
\&\f(CW\*(C`Username\*(C'\fR and \f(CW\*(C`Password\*(C'\fR, in which case they take precedence
over the \f(CW$username\fR and \f(CW$password\fR parameters.
.PP
You can also define connection attribute values within the \f(CW$data_source\fR
parameter. For example:
.PP
.Vb 1
\&  dbi:DriverName(PrintWarn=>1,PrintError=>0,Taint=>1):...
.Ve
.PP
Individual attributes values specified in this way take precedence over
any conflicting values specified via the \f(CW\*(C`\e%attr\*(C'\fR parameter to \f(CW\*(C`connect\*(C'\fR.
.PP
The \f(CW\*(C`dbi_connect_method\*(C'\fR attribute can be used to specify which driver
method should be called to establish the connection. The only useful
values are 'connect', 'connect_cached', or some specialized case like
\&'Apache::DBI::connect' (which is automatically the default when running
within Apache).
.PP
Where possible, each session (\f(CW$dbh\fR) is independent from the transactions
in other sessions. This is useful when you need to hold cursors open
across transactions\*(--for example, if you use one session for your long lifespan
cursors (typically read-only) and another for your short update
transactions.
.PP
For compatibility with old \s-1DBI\s0 scripts, the driver can be specified by
passing its name as the fourth argument to \f(CW\*(C`connect\*(C'\fR (instead of \f(CW\*(C`\e%attr\*(C'\fR):
.PP
.Vb 1
\&  $dbh = DBI\->connect($data_source, $user, $pass, $driver);
.Ve
.PP
In this \*(L"old-style\*(R" form of \f(CW\*(C`connect\*(C'\fR, the \f(CW$data_source\fR should not start
with "\f(CW\*(C`dbi:driver_name:\*(C'\fR". (If it does, the embedded driver_name
will be ignored). Also note that in this older form of \f(CW\*(C`connect\*(C'\fR,
the \f(CW\*(C`$dbh\->{AutoCommit}\*(C'\fR attribute is \fIundefined\fR, the
\&\f(CW\*(C`$dbh\->{PrintError}\*(C'\fR attribute is off, and the old \f(CW\*(C`DBI_DBNAME\*(C'\fR
environment variable is
checked if \f(CW\*(C`DBI_DSN\*(C'\fR is not defined. Beware that this \*(L"old-style\*(R"
\&\f(CW\*(C`connect\*(C'\fR will soon be withdrawn in a future version of \s-1DBI\s0.
.PP
\fI\f(CI\*(C`connect_cached\*(C'\fI\fR
.IX Subsection "connect_cached"
.PP
.Vb 4
\&  $dbh = DBI\->connect_cached($data_source, $username, $password)