dbd::mysql.3

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· 3 代码 · 共 1,260 行 · 第 1/3 页

.IP "mysql_ssl_client_cert" 4
.IX Item "mysql_ssl_client_cert"
.IP "mysql_ssl_ca_file" 4
.IX Item "mysql_ssl_ca_file"
.IP "mysql_ssl_ca_path" 4
.IX Item "mysql_ssl_ca_path"
.IP "mysql_ssl_cipher" 4
.IX Item "mysql_ssl_cipher"
.PD
These are used to specify the respective parameters of a call
to mysql_ssl_set, if mysql_ssl is turned on.
.IP "mysql_local_infile" 4
.IX Item "mysql_local_infile"
As of MySQL 3.23.49, the \s-1LOCAL\s0 capability for \s-1LOAD\s0 \s-1DATA\s0 may be disabled
in the MySQL client library by default. If your \s-1DSN\s0 contains the option
\&\*(L"mysql_local_infile=1\*(R", \s-1LOAD\s0 \s-1DATA\s0 \s-1LOCAL\s0 will be enabled.  (However,
this option is *ineffective* if the server has also been configured to
disallow \s-1LOCAL\s0.)
.IP "mysql_multi_statements" 4
.IX Item "mysql_multi_statements"
As of MySQL 4.1, support for multiple statements seperated by a semicolon
(;) may be enabled by using this option. Enabling this option may cause
problems if server-side prepared statements are also enabled.
.IP "Prepared statement support (server side prepare)" 4
.IX Item "Prepared statement support (server side prepare)"
As of 3.0002_1, server side prepare statements were on by default (if your
server was >= 4.1.3). As of 3.0009, they were off by default again due to 
issues with the prepared statement \s-1API\s0 (all other mysql connectors are
set this way until C \s-1API\s0 issues are resolved). The requirement to use
prepared statements still remains that you have a server >= 4.1.3
.Sp
To use server side prepared statements, all you need to do is set the variable 
mysql_server_prepare in the connect:
.Sp
\&\f(CW$dbh\fR = \s-1DBI\-\s0>connect(
                    \*(L"DBI:mysql:database=test;host=localhost;mysql_server_prepare=1\*(R",
                    "\*(L",
                    \*(R"",
                    { RaiseError => 1, AutoCommit => 1 }
                    );
.Sp
* Note: delimiter for this param is ';'
.Sp
There are many benefits to using server side prepare statements, mostly if you are 
performing many inserts because of that fact that a single statement is prepared 
to accept multiple insert values.
.Sp
To make sure that the 'make test' step tests whether server prepare works, you just
need to export the env variable \s-1MYSQL_SERVER_PREPARE:\s0
.Sp
export MYSQL_SERVER_PREPARE=1
.IP "mysql_embedded_options" 4
.IX Item "mysql_embedded_options"
The option <mysql_embedded_options> can be used to pass 'command\-line' 
options to embedded server.
.Sp
Example:
.Sp
\&\f(CW$testdsn\fR=\*(L"DBI:mysqlEmb:database=test;mysql_embedded_options=\-\-help,\-\-verbose\*(R";
.IP "mysql_embedded_groups" 4
.IX Item "mysql_embedded_groups"
The option <mysql_embedded_groups> can be used to specify the groups in the 
config file(\fImy.cnf\fR) which will be used to get options for embedded server. 
If not specified [server] and [embedded] groups will be used.
.Sp
Example:
.Sp
\&\f(CW$testdsn\fR=\*(L"DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common\*(R";
.RE
.RS 4
.RE
.Sh "Private MetaData Methods"
.IX Subsection "Private MetaData Methods"
.IP "\fBListDBs\fR" 4
.IX Item "ListDBs"
.Vb 4
\&    my $drh = DBI\->install_driver("mysql");
\&    @dbs = $drh\->func("$hostname:$port", \*(Aq_ListDBs\*(Aq);
\&    @dbs = $drh\->func($hostname, $port, \*(Aq_ListDBs\*(Aq);
\&    @dbs = $dbh\->func(\*(Aq_ListDBs\*(Aq);
.Ve
.Sp
Returns a list of all databases managed by the MySQL server
running on \f(CW$hostname\fR, port \f(CW$port\fR. This is a legacy
method.  Instead, you should use the portable method
.Sp
.Vb 1
\&    @dbs = DBI\->data_sources("mysql");
.Ve
.Sh "Server Administration"
.IX Subsection "Server Administration"
.IP "admin" 4
.IX Item "admin"
.Vb 4
\&    $rc = $drh\->func("createdb", $dbname, [host, user, password,], \*(Aqadmin\*(Aq);
\&    $rc = $drh\->func("dropdb", $dbname, [host, user, password,], \*(Aqadmin\*(Aq);
\&    $rc = $drh\->func("shutdown", [host, user, password,], \*(Aqadmin\*(Aq);
\&    $rc = $drh\->func("reload", [host, user, password,], \*(Aqadmin\*(Aq);
\&
\&      or
\&
\&    $rc = $dbh\->func("createdb", $dbname, \*(Aqadmin\*(Aq);
\&    $rc = $dbh\->func("dropdb", $dbname, \*(Aqadmin\*(Aq);
\&    $rc = $dbh\->func("shutdown", \*(Aqadmin\*(Aq);
\&    $rc = $dbh\->func("reload", \*(Aqadmin\*(Aq);
.Ve
.Sp
For server administration you need a server connection. For obtaining
this connection you have two options: Either use a driver handle (drh)
and supply the appropriate arguments (host, defaults localhost, user,
defaults to '' and password, defaults to ''). A driver handle can be
obtained with
.Sp
.Vb 1
\&    $drh = DBI\->install_driver(\*(Aqmysql\*(Aq);
.Ve
.Sp
Otherwise reuse the existing connection of a database handle (dbh).
.Sp
There's only one function available for administrative purposes, comparable
to the m(y)sqladmin programs. The command being execute depends on the
first argument:
.RS 4
.IP "createdb" 4
.IX Item "createdb"
Creates the database \f(CW$dbname\fR. Equivalent to \*(L"m(y)sqladmin create \f(CW$dbname\fR\*(R".
.IP "dropdb" 4
.IX Item "dropdb"
Drops the database \f(CW$dbname\fR. Equivalent to \*(L"m(y)sqladmin drop \f(CW$dbname\fR\*(R".
.Sp
It should be noted that database deletion is
\&\fInot prompted for\fR in any way.  Nor is it undo-able from \s-1DBI\s0.
.Sp
.Vb 1
\&    Once you issue the dropDB() method, the database will be gone!
.Ve
.Sp
These method should be used at your own risk.
.IP "shutdown" 4
.IX Item "shutdown"
Silently shuts down the database engine. (Without prompting!)
Equivalent to \*(L"m(y)sqladmin shutdown\*(R".
.IP "reload" 4
.IX Item "reload"
Reloads the servers configuration files and/or tables. This can be particularly
important if you modify access privileges or create new users.
.RE
.RS 4
.RE
.SH "DATABASE HANDLES"
.IX Header "DATABASE HANDLES"
The DBD::mysql driver supports the following attributes of database
handles (read only):
.PP
.Vb 9
\&  $errno = $dbh\->{\*(Aqmysql_errno\*(Aq};
\&  $error = $dbh\->{\*(Aqmysql_error\*(Aq};
\&  $info = $dbh\->{\*(Aqmysql_hostinfo\*(Aq};
\&  $info = $dbh\->{\*(Aqmysql_info\*(Aq};
\&  $insertid = $dbh\->{\*(Aqmysql_insertid\*(Aq};
\&  $info = $dbh\->{\*(Aqmysql_protoinfo\*(Aq};
\&  $info = $dbh\->{\*(Aqmysql_serverinfo\*(Aq};
\&  $info = $dbh\->{\*(Aqmysql_stat\*(Aq};
\&  $threadId = $dbh\->{\*(Aqmysql_thread_id\*(Aq};
.Ve
.PP
These correspond to \fImysql_errno()\fR, \fImysql_error()\fR, \fImysql_get_host_info()\fR,
\&\fImysql_info()\fR, \fImysql_insert_id()\fR, \fImysql_get_proto_info()\fR,
\&\fImysql_get_server_info()\fR, \fImysql_stat()\fR and \fImysql_thread_id()\fR,
respectively.
.PP
.Vb 1
\& $info_hashref = $dhb\->{mysql_dbd_stats}
.Ve
.PP
DBD::mysql keeps track of some statistics in the mysql_dbd_stats attribute.
The following stats are being maintained:
.IP "auto_reconnects_ok" 4
.IX Item "auto_reconnects_ok"
The number of times that DBD::mysql successfully reconnected to the mysql 
server.
.IP "auto_reconnects_failed" 4
.IX Item "auto_reconnects_failed"
The number of times that DBD::mysql tried to reconnect to mysql but failed.
.PP
The DBD::mysql driver also supports the following attribute(s) of database
handles (read/write):
.PP
.Vb 2
\& $bool_value = $dbh\->{mysql_auto_reconnect};
\& $dbh\->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0;
.Ve
.IP "mysql_auto_reconnect" 4
.IX Item "mysql_auto_reconnect"
This attribute determines whether DBD::mysql will automatically reconnect
to mysql if the connection be lost. This feature defaults to off; however,
if either the \s-1GATEWAY_INTERFACE\s0 or \s-1MOD_PERL\s0 envionment variable is set, 
DBD::mysql will turn mysql_auto_reconnect on.  Setting mysql_auto_reconnect 
to on is not advised if 'lock tables' is used because if DBD::mysql reconnect 
to mysql all table locks will be lost.  This attribute is ignored when
AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will
not automatically reconnect to the server.
.IP "mysql_use_result" 4
.IX Item "mysql_use_result"
This attribute forces the driver to use mysql_use_result rather than
mysql_store_result. The former is faster and less memory consuming, but
tends to block other processes. (That's why mysql_store_result is the
default.)
.Sp
It is possible to set default value of the \f(CW\*(C`mysql_use_result\*(C'\fR attribute 
for \f(CW$dbh\fR using several ways:
.Sp
.Vb 1
\& \- through DSN 
\&
\&   $dbh= DBI\->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
\&
\& \- after creation of database handle
\&
\&   $dbh\->{\*(Aqmysql_use_result\*(Aq}=0; #disable
\&   $dbh\->{\*(Aqmysql_use_result\*(Aq}=1; #enable
.Ve
.Sp
It is possible to set/unset the \f(CW\*(C`mysql_use_result\*(C'\fR attribute after 
creation of statement handle. See below.
.IP "mysql_enable_utf8" 4
.IX Item "mysql_enable_utf8"
This attribute determines whether DBD::mysql should assume strings
stored in the database are utf8.  This feature defaults to off.
.Sp
When set, a data retrieved from a textual column type (char, varchar,
etc) will have the \s-1UTF\-8\s0 flag turned on if necessary.  This enables
character semantics on that string.  You will also need to ensure that
your database / table / column is configured to use \s-1UTF8\s0.  See Chapter
10 of the mysql manual for details.
.Sp
Additionally, turning on this flag tells MySQL that incoming data should
be treated as \s-1UTF\-8\s0.  This will only take effect if used as part of the
call to \fIconnect()\fR.  If you turn the flag on after connecting, you will
need to issue the command \f(CW\*(C`SET NAMES utf8\*(C'\fR to get the same effect.
.Sp
This option is experimental and may change in future versions.
.SH "STATEMENT HANDLES"
.IX Header "STATEMENT HANDLES"
The statement handles of DBD::mysql support a number
of attributes. You access these by using, for example,
.PP
.Vb 1
\&  my $numFields = $sth\->{\*(AqNUM_OF_FIELDS\*(Aq};
.Ve
.PP
Note, that most attributes are valid only after a successfull \fIexecute\fR.
An \f(CW\*(C`undef\*(C'\fR value will returned in that case. The most important exception
is the \f(CW\*(C`mysql_use_result\*(C'\fR attribute: This forces the driver to use
mysql_use_result rather than mysql_store_result. The former is faster
and less memory consuming, but tends to block other processes. (That's why
mysql_store_result is the default.)
.PP
To set the \f(CW\*(C`mysql_use_result\*(C'\fR attribute, use either of the following:
.PP
.Vb 1
\&  my $sth = $dbh\->prepare("QUERY", { "mysql_use_result" => 1});
.Ve
.PP
or
.PP
.Vb 2
\&  my $sth = $dbh\->prepare("QUERY");
\&  $sth\->{"mysql_use_result"} = 1;
.Ve
.PP
Column dependent attributes, for example \fI\s-1NAME\s0\fR, the column names,
are returned as a reference to an array. The array indices are
corresponding to the indices of the arrays returned by \fIfetchrow\fR
and similar methods. For example the following code will print a
header of table names together with all rows:
.PP
.Vb 10
\&  my $sth = $dbh\->prepare("SELECT * FROM $table");
\&  if (!$sth) {
\&      die "Error:" . $dbh\->errstr . "\en";
\&  }
\&  if (!$sth\->execute) {
\&      die "Error:" . $sth\->errstr . "\en";
\&  }
\&  my $names = $sth\->{\*(AqNAME\*(Aq};
\&  my $numFields = $sth\->{\*(AqNUM_OF_FIELDS\*(Aq};
\&  for (my $i = 0;  $i < $numFields;  $i++) {
\&      printf("%s%s", $i ? "," : "", $$names[$i]);
\&  }
\&  print "\en";
\&  while (my $ref = $sth\->fetchrow_arrayref) {
\&      for (my $i = 0;  $i < $numFields;  $i++) {
\&          printf("%s%s", $i ? "," : "", $$ref[$i]);
\&      }
\&      print "\en";
\&  }
.Ve
.PP
For portable applications you should restrict yourself to attributes with
capitalized or mixed case names. Lower case attribute names are private
to DBD::mysql. The attribute list includes:
.IP "ChopBlanks" 4
.IX Item "ChopBlanks"
this attribute determines whether a \fIfetchrow\fR will chop preceding
and trailing blanks off the column values. Chopping blanks does not
have impact on the \fImax_length\fR attribute.
.IP "mysql_insertid" 4
.IX Item "mysql_insertid"
MySQL has the ability to choose unique key values automatically. If this
happened, the new \s-1ID\s0 will be stored in this attribute. An alternative
way for accessing this attribute is via \f(CW$dbh\fR\->{'mysql_insertid'}.
(Note we are using the \f(CW$dbh\fR in this case!)
.IP "mysql_is_blob" 4
.IX Item "mysql_is_blob"
Reference to an array of boolean values; \s-1TRUE\s0 indicates, that the
respective column is a blob. This attribute is valid for MySQL only.
.IP "mysql_is_key" 4
.IX Item "mysql_is_key"
Reference to an array of boolean values; \s-1TRUE\s0 indicates, that the
respective column is a key. This is valid for MySQL only.
.IP "mysql_is_num" 4
.IX Item "mysql_is_num"
Reference to an array of boolean values; \s-1TRUE\s0 indicates, that the
respective column contains numeric values.
.IP "mysql_is_pri_key" 4
.IX Item "mysql_is_pri_key"
Reference to an array of boolean values; \s-1TRUE\s0 indicates, that the
respective column is a primary key.
.IP "mysql_is_auto_increment" 4
.IX Item "mysql_is_auto_increment"
Reference to an array of boolean values; \s-1TRUE\s0 indicates that the
respective column is an \s-1AUTO_INCREMENT\s0 column.  This is only valid
for MySQL.
.IP "mysql_length" 4
.IX Item "mysql_length"
.PD 0
.IP "mysql_max_length" 4
.IX Item "mysql_max_length"
.PD
A reference to an array of maximum column sizes. The \fImax_length\fR is
the maximum physically present in the result table, \fIlength\fR gives
the theoretically possible maximum. \fImax_length\fR is valid for MySQL
only.
.IP "\s-1NAME\s0" 4
.IX Item "NAME"
A reference to an array of column names.
.IP "\s-1NULLABLE\s0" 4
.IX Item "NULLABLE"
A reference to an array of boolean values; \s-1TRUE\s0 indicates that this column
may contain \s-1NULL\s0's.
.IP "\s-1NUM_OF_FIELDS\s0" 4
.IX Item "NUM_OF_FIELDS"
Number of fields returned by a \fI\s-1SELECT\s0\fR or \fI\s-1LISTFIELDS\s0\fR statement.
You may use this for checking whether a statement returned a result:
A zero value indicates a non-SELECT statement like \fI\s-1INSERT\s0\fR,
\&\fI\s-1DELETE\s0\fR or \fI\s-1UPDATE\s0\fR.
.IP "mysql_table" 4
.IX Item "mysql_table"
A reference to an array of table names, useful in a \fI\s-1JOIN\s0\fR result.
.IP "\s-1TYPE\s0" 4
.IX Item "TYPE"
A reference to an array of column types. The engine's native column
types are mapped to portable types like \s-1\fIDBI::SQL_INTEGER\s0()\fR or
\&\s-1\fIDBI::SQL_VARCHAR\s0()\fR, as good as possible. Not all native types have
a meaningfull equivalent, for example DBD::mysql::FIELD_TYPE_INTERVAL
is mapped to \s-1\fIDBI::SQL_VARCHAR\s0()\fR.
If you need the native column types, use \fImysql_type\fR. See below.
.IP "mysql_type" 4
.IX Item "mysql_type"
A reference to an array of MySQL's native column types, for example
\&\fIDBD::mysql::FIELD_TYPE_SHORT()\fR or \fIDBD::mysql::FIELD_TYPE_STRING()\fR.
Use the \fI\s-1TYPE\s0\fR attribute, if you want portable types like
\&\s-1\fIDBI::SQL_SMALLINT\s0()\fR or \s-1\fIDBI::SQL_VARCHAR\s0()\fR.
.IP "mysql_type_name" 4
.IX Item "mysql_type_name"
Similar to mysql, but type names and not numbers are returned.
Whenever possible, the \s-1ANSI\s0 \s-1SQL\s0 name is preferred.
.IP "mysql_warning_count" 4
.IX Item "mysql_warning_count"
The number of warnings generated during execution of the \s-1SQL\s0 statement.
.SH "TRANSACTION SUPPORT"
.IX Header "TRANSACTION SUPPORT"
Beginning with DBD::mysql 2.0416, transactions are supported.
The transaction support works as follows:
.IP "\(bu" 4
By default AutoCommit mode is on, following the \s-1DBI\s0 specifications.
.IP "\(bu" 4
If you execute
.Sp
.Vb 1
\&    $dbh\->{\*(AqAutoCommit\*(Aq} = 0;
.Ve
.Sp
or
.Sp
.Vb 1
\&    $dbh\->{\*(AqAutoCommit\*(Aq} = 1;
.Ve
.Sp
then the driver will set the MySQL server variable autocommit to 0 or
1, respectively. Switching from 0 to 1 will also issue a \s-1COMMIT\s0,
following the \s-1DBI\s0 specifications.
.IP "\(bu" 4
The methods
.Sp
.Vb 2
\&    $dbh\->rollback();
\&    $dbh\->commit();
.Ve
.Sp
will issue the commands \s-1COMMIT\s0 and \s-1ROLLBACK\s0, respectively. A
\&\s-1ROLLBACK\s0 will also be issued if AutoCommit mode is off and the
database handles \s-1DESTROY\s0 method is called. Again, this is following
the \s-1DBI\s0 specifications.
.PP
Given the above, you should note the following:
.IP "\(bu" 4
You should never change the server variable autocommit manually,
unless you are ignoring \s-1DBI\s0's transaction support.