dbi.3

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

\&  $h      Any of the handle types above ($dbh, $sth, or $drh)
\&  $rc     General Return Code  (boolean: true=ok, false=error)
\&  $rv     General Return Value (typically an integer)
\&  @ary    List of values returned from the database, typically a row of data
\&  $rows   Number of rows processed (if available, else \-1)
\&  $fh     A filehandle
\&  undef   NULL values are represented by undefined values in Perl
\&  \e%attr  Reference to a hash of attribute values passed to methods
.Ve
.PP
Note that Perl will automatically destroy database and statement handle objects
if all references to them are deleted.
.Sh "Outline Usage"
.IX Subsection "Outline Usage"
To use \s-1DBI\s0,
first you need to load the \s-1DBI\s0 module:
.PP
.Vb 2
\&  use DBI;
\&  use strict;
.Ve
.PP
(The \f(CW\*(C`use strict;\*(C'\fR isn't required but is strongly recommended.)
.PP
Then you need to \*(L"connect\*(R" to your data source and get a \fIhandle\fR for that
connection:
.PP
.Vb 2
\&  $dbh = DBI\->connect($dsn, $user, $password,
\&                      { RaiseError => 1, AutoCommit => 0 });
.Ve
.PP
Since connecting can be expensive, you generally just connect at the
start of your program and disconnect at the end.
.PP
Explicitly defining the required \f(CW\*(C`AutoCommit\*(C'\fR behaviour is strongly
recommended and may become mandatory in a later version.  This
determines whether changes are automatically committed to the
database when executed, or need to be explicitly committed later.
.PP
The \s-1DBI\s0 allows an application to \*(L"prepare\*(R" statements for later
execution.  A prepared statement is identified by a statement handle
held in a Perl variable.
We'll call the Perl variable \f(CW$sth\fR in our examples.
.PP
The typical method call sequence for a \f(CW\*(C`SELECT\*(C'\fR statement is:
.PP
.Vb 4
\&  prepare,
\&    execute, fetch, fetch, ...
\&    execute, fetch, fetch, ...
\&    execute, fetch, fetch, ...
.Ve
.PP
for example:
.PP
.Vb 1
\&  $sth = $dbh\->prepare("SELECT foo, bar FROM table WHERE baz=?");
\&
\&  $sth\->execute( $baz );
\&
\&  while ( @row = $sth\->fetchrow_array ) {
\&    print "@row\en";
\&  }
.Ve
.PP
The typical method call sequence for a \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statement is:
.PP
.Vb 4
\&  prepare,
\&    execute,
\&    execute,
\&    execute.
.Ve
.PP
for example:
.PP
.Vb 1
\&  $sth = $dbh\->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
\&
\&  while(<CSV>) {
\&    chomp;
\&    my ($foo,$bar,$baz) = split /,/;
\&        $sth\->execute( $foo, $bar, $baz );
\&  }
.Ve
.PP
The \f(CW\*(C`do()\*(C'\fR method can be used for non repeated \fInon\fR\-\f(CW\*(C`SELECT\*(C'\fR statement
(or with drivers that don't support placeholders):
.PP
.Vb 1
\&  $rows_affected = $dbh\->do("UPDATE your_table SET foo = foo + 1");
.Ve
.PP
To commit your changes to the database (when \*(L"AutoCommit\*(R" is off):
.PP
.Vb 1
\&  $dbh\->commit;  # or call $dbh\->rollback; to undo changes
.Ve
.PP
Finally, when you have finished working with the data source, you should
\&\*(L"disconnect\*(R" from it:
.PP
.Vb 1
\&  $dbh\->disconnect;
.Ve
.Sh "General Interface Rules & Caveats"
.IX Subsection "General Interface Rules & Caveats"
The \s-1DBI\s0 does not have a concept of a \*(L"current session\*(R". Every session
has a handle object (i.e., a \f(CW$dbh\fR) returned from the \f(CW\*(C`connect\*(C'\fR method.
That handle object is used to invoke database related methods.
.PP
Most data is returned to the Perl script as strings. (Null values are
returned as \f(CW\*(C`undef\*(C'\fR.)  This allows arbitrary precision numeric data to be
handled without loss of accuracy.  Beware that Perl may not preserve
the same accuracy when the string is used as a number.
.PP
Dates and times are returned as character strings in the current
default format of the corresponding database engine.  Time zone effects
are database/driver dependent.
.PP
Perl supports binary data in Perl strings, and the \s-1DBI\s0 will pass binary
data to and from the driver without change. It is up to the driver
implementors to decide how they wish to handle such binary data.
.PP
Perl supports two kinds of strings: unicode (utf8 internally) and non-unicode
(defaults to iso\-8859\-1 if forced to assume an encoding).  Drivers should
accept both kinds of strings and, if required, convert them to the character
set of the database being used. Similarly, when fetching from the database
character data that isn't iso\-8859\-1 the driver should convert it into utf8.
.PP
Multiple \s-1SQL\s0 statements may not be combined in a single statement
handle (\f(CW$sth\fR), although some databases and drivers do support this
(notably Sybase and \s-1SQL\s0 Server).
.PP
Non-sequential record reads are not supported in this version of the \s-1DBI\s0.
In other words, records can only be fetched in the order that the
database returned them, and once fetched they are forgotten.
.PP
Positioned updates and deletes are not directly supported by the \s-1DBI\s0.
See the description of the \f(CW\*(C`CursorName\*(C'\fR attribute for an alternative.
.PP
Individual driver implementors are free to provide any private
functions and/or handle attributes that they feel are useful.
Private driver functions can be invoked using the \s-1DBI\s0 \f(CW\*(C`func()\*(C'\fR method.
Private driver attributes are accessed just like standard attributes.
.PP
Many methods have an optional \f(CW\*(C`\e%attr\*(C'\fR parameter which can be used to
pass information to the driver implementing the method. Except where
specifically documented, the \f(CW\*(C`\e%attr\*(C'\fR parameter can only be used to pass
driver specific hints. In general, you can ignore \f(CW\*(C`\e%attr\*(C'\fR parameters
or pass it as \f(CW\*(C`undef\*(C'\fR.
.Sh "Naming Conventions and Name Space"
.IX Subsection "Naming Conventions and Name Space"
The \s-1DBI\s0 package and all packages below it (\f(CW\*(C`DBI::*\*(C'\fR) are reserved for
use by the \s-1DBI\s0. Extensions and related modules use the \f(CW\*(C`DBIx::\*(C'\fR
namespace (see <http://www.perl.com/CPAN/modules/by\-module/DBIx/>).
Package names beginning with \f(CW\*(C`DBD::\*(C'\fR are reserved for use
by \s-1DBI\s0 database drivers.  All environment variables used by the \s-1DBI\s0
or by individual DBDs begin with "\f(CW\*(C`DBI_\*(C'\fR\*(L" or \*(R"\f(CW\*(C`DBD_\*(C'\fR".
.PP
The letter case used for attribute names is significant and plays an
important part in the portability of \s-1DBI\s0 scripts.  The case of the
attribute name is used to signify who defined the meaning of that name
and its values.
.PP
.Vb 5
\&  Case of name  Has a meaning defined by
\&  \-\-\-\-\-\-\-\-\-\-\-\-  \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-
\&  UPPER_CASE    Standards, e.g.,  X/Open, ISO SQL92 etc (portable)
\&  MixedCase     DBI API (portable), underscores are not used.
\&  lower_case    Driver or database engine specific (non\-portable)
.Ve
.PP
It is of the utmost importance that Driver developers only use
lowercase attribute names when defining private attributes. Private
attribute names must be prefixed with the driver name or suitable
abbreviation (e.g., "\f(CW\*(C`ora_\*(C'\fR\*(L" for Oracle, \*(R"\f(CW\*(C`ing_\*(C'\fR" for Ingres, etc).
.Sh "\s-1SQL\s0 \- A Query Language"
.IX Subsection "SQL - A Query Language"
Most \s-1DBI\s0 drivers require applications to use a dialect of \s-1SQL\s0
(Structured Query Language) to interact with the database engine.
The \*(L"Standards Reference Information\*(R" section provides links
to useful information about \s-1SQL\s0.
.PP
The \s-1DBI\s0 itself does not mandate or require any particular language to
be used; it is language independent. In \s-1ODBC\s0 terms, the \s-1DBI\s0 is in
\&\*(L"pass-thru\*(R" mode, although individual drivers might not be. The only requirement
is that queries and other statements must be expressed as a single
string of characters passed as the first argument to the \*(L"prepare\*(R" or
\&\*(L"do\*(R" methods.
.PP
For an interesting diversion on the \fIreal\fR history of \s-1RDBMS\s0 and \s-1SQL\s0,
from the people who made it happen, see:
.PP
.Vb 1
\&  http://ftp.digital.com/pub/DEC/SRC/technical\-notes/SRC\-1997\-018\-html/sqlr95.html
.Ve
.PP
Follow the \*(L"Full Contents\*(R" then \*(L"Intergalactic dataspeak\*(R" links for the
\&\s-1SQL\s0 history.
.Sh "Placeholders and Bind Values"
.IX Subsection "Placeholders and Bind Values"
Some drivers support placeholders and bind values.
\&\fIPlaceholders\fR, also called parameter markers, are used to indicate
values in a database statement that will be supplied later,
before the prepared statement is executed.  For example, an application
might use the following to insert a row of data into the \s-1SALES\s0 table:
.PP
.Vb 1
\&  INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
.Ve
.PP
or the following, to select the description for a product:
.PP
.Vb 1
\&  SELECT description FROM products WHERE product_code = ?
.Ve
.PP
The \f(CW\*(C`?\*(C'\fR characters are the placeholders.  The association of actual
values with placeholders is known as \fIbinding\fR, and the values are
referred to as \fIbind values\fR.
.PP
Note that the \f(CW\*(C`?\*(C'\fR is not enclosed in quotation marks, even when the
placeholder represents a string.  Some drivers also allow placeholders
like \f(CW\*(C`:\*(C'\fR\fIname\fR and \f(CW\*(C`:\*(C'\fR\fIn\fR (e.g., \f(CW\*(C`:1\*(C'\fR, \f(CW\*(C`:2\*(C'\fR, and so on)
in addition to \f(CW\*(C`?\*(C'\fR, but their use is not portable.
.PP
With most drivers, placeholders can't be used for any element of a
statement that would prevent the database server from validating the
statement and creating a query execution plan for it. For example:
.PP
.Vb 2
\&  "SELECT name, age FROM ?"         # wrong (will probably fail)
\&  "SELECT name, ?   FROM people"    # wrong (but may not \*(Aqfail\*(Aq)
.Ve
.PP
Also, placeholders can only represent single scalar values.
For example, the following
statement won't work as expected for more than one value:
.PP
.Vb 2
\&  "SELECT name, age FROM people WHERE name IN (?)"    # wrong
\&  "SELECT name, age FROM people WHERE name IN (?,?)"  # two names
.Ve
.PP
When using placeholders with the \s-1SQL\s0 \f(CW\*(C`LIKE\*(C'\fR qualifier, you must
remember that the placeholder substitutes for the whole string.
So you should use "\f(CW\*(C`... LIKE ? ...\*(C'\fR" and include any wildcard
characters in the value that you bind to the placeholder.
.PP
\&\fB\s-1NULL\s0 Values\fR
.PP
Undefined values, or \f(CW\*(C`undef\*(C'\fR, are used to indicate \s-1NULL\s0 values.
You can insert and update columns with a \s-1NULL\s0 value as you would a
non-NULL value.  These examples insert and update the column
\&\f(CW\*(C`age\*(C'\fR with a \s-1NULL\s0 value:
.PP
.Vb 4
\&  $sth = $dbh\->prepare(qq{
\&    INSERT INTO people (fullname, age) VALUES (?, ?)
\&  });
\&  $sth\->execute("Joe Bloggs", undef);
\&
\&  $sth = $dbh\->prepare(qq{
\&    UPDATE people SET age = ? WHERE fullname = ?
\&  });
\&  $sth\->execute(undef, "Joe Bloggs");
.Ve
.PP
However, care must be taken when trying to use \s-1NULL\s0 values in a
\&\f(CW\*(C`WHERE\*(C'\fR clause.  Consider:
.PP
.Vb 1
\&  SELECT fullname FROM people WHERE age = ?
.Ve
.PP
Binding an \f(CW\*(C`undef\*(C'\fR (\s-1NULL\s0) to the placeholder will \fInot\fR select rows
which have a \s-1NULL\s0 \f(CW\*(C`age\*(C'\fR!  At least for database engines that
conform to the \s-1SQL\s0 standard.  Refer to the \s-1SQL\s0 manual for your database
engine or any \s-1SQL\s0 book for the reasons for this.  To explicitly select
NULLs you have to say "\f(CW\*(C`WHERE age IS NULL\*(C'\fR".
.PP
A common issue is to have a code fragment handle a value that could be
either \f(CW\*(C`defined\*(C'\fR or \f(CW\*(C`undef\*(C'\fR (non-NULL or \s-1NULL\s0) at runtime.
A simple technique is to prepare the appropriate statement as needed,
and substitute the placeholder for non-NULL cases:
.PP
.Vb 5
\&  $sql_clause = defined $age? "age = ?" : "age IS NULL";
\&  $sth = $dbh\->prepare(qq{
\&    SELECT fullname FROM people WHERE $sql_clause
\&  });
\&  $sth\->execute(defined $age ? $age : ());
.Ve
.PP
The following technique illustrates qualifying a \f(CW\*(C`WHERE\*(C'\fR clause with
several columns, whose associated values (\f(CW\*(C`defined\*(C'\fR or \f(CW\*(C`undef\*(C'\fR) are
in a hash \f(CW%h:\fR
.PP
.Vb 10
\&  for my $col ("age", "phone", "email") {
\&    if (defined $h{$col}) {
\&      push @sql_qual, "$col = ?";
\&      push @sql_bind, $h{$col};
\&    }
\&    else {
\&      push @sql_qual, "$col IS NULL";
\&    }
\&  }
\&  $sql_clause = join(" AND ", @sql_qual);
\&  $sth = $dbh\->prepare(qq{
\&      SELECT fullname FROM people WHERE $sql_clause
\&  });
\&  $sth\->execute(@sql_bind);
.Ve
.PP
The techniques above call prepare for the \s-1SQL\s0 statement with each call to
execute.  Because calls to \fIprepare()\fR can be expensive, performance
can suffer when an application iterates many times over statements
like the above.
.PP