MySQL Java Connectivity (JDBC)
==============================
There are 2 supported JDBC drivers for MySQL:
* The Resin JDBC driver, which can be found at
`http://www.caucho.com/projects/jdbc-mysql/index.xtp'.
For documentation, consult any JDBC documentation, plus each driver's
own documentation for MySQL-specific features.
MySQL PHP API
=============
program or
compiled as a module for use with the Apache web server.
The distribution and documentation are available at the PHP web site
(`http://www.php.net/').
Common Problems with MySQL and PHP
----------------------------------
not a
bad idea to double the ram allowed per script to 16 MB instead of
8 MB.
MySQL module and load it into PHP or recompile PHP with built-in
MySQL support. This is described in detail in the PHP manual.
* Error: "undefined reference to `uncompress'" This means that the
client library is compiled with support for a compressed
client/server protocol. The fix is to add `-lz' last when linking
with `-lmysqlclient'.
MySQL Perl API
==============
`DBI' with `DBD::mysql'
-----------------------
`DBI' is a generic interface for many databases. That means that you
can write a script that works with many different database engines
without change. You need a DataBase Driver (DBD) defined for each
database type. For MySQL, this driver is called `DBD::mysql'.
For more information on the Perl5 DBI, please visit the `DBI' web page
and read the documentation:
`http://dbi.perl.org/'
Note that if you want to use transactions with Perl, you need to have
`DBD-mysql' version 1.2216 or newer. Version 2.1022 or newer is
recommended.
Installation instructions for MySQL Perl support are given in *Note
Perl support::.
If you have the MySQL module installed, you can find information about
specific MySQL functionality with one of the following command
shell> `perldoc DBD/mysql'
shell> `perldoc mysql'
The `DBI' Interface
-------------------
*Portable DBI Methods and Attributes*
*Method/Attribute* *Description*
`connect' Establishes a connection to a database
server.
`disconnect' Disconnects from the database server.
`prepare' Prepares an SQL statement for execution.
`execute' Executes prepared statements.
`do' Prepares and executes an SQL statement.
`quote' Quotes string or `BLOB' values to be
inserted.
`fetchrow_array' Fetches the next row as an array of fields.
`fetchrow_arrayref'Fetches next row as a reference array of
fields.
`fetchrow_hashref' Fetches next row as a reference to a
hashtable.
`fetchall_arrayref'Fetches all data as an array of arrays.
`finish' Finishes a statement and lets the system
free resources.
`rows' Returns the number of rows affected.
`data_sources' Returns an array of databases available on
localhost.
`ChopBlanks' Controls whether `fetchrow_*' methods trim
spaces.
`NUM_OF_PARAMS' The number of placeholders in the prepared
statement.
`NULLABLE' Which columns can be `NULL'.
`trace' Perform tracing for debugging.
*MySQL-specific Methods and Attributes*
*Method/Attribute* *Description*
`mysql_insertid' The latest `AUTO_INCREMENT' value.
`is_blob' Which columns are `BLOB' values.
`is_key' Which columns are keys.
`is_num' Which columns are numeric.
`is_pri_key' Which columns are primary keys.
`is_not_null' Which columns CANNOT be `NULL'. See
`NULLABLE'.
`length' Maximum possible column sizes.
`max_length' Maximum column sizes actually present in
result.
`NAME' Column names.
`NUM_OF_FIELDS' Number of fields returned.
`table' Table names in returned set.
`type' All column types.
The Perl methods are described in more detail in the following sections.
Variables used for method return values have these meanings:
`$dbh'
Database handle
`$sth'
Statement handle
`$rc'
Return code (often a status)
`$rv'
Return value (often a row count)
*Portable DBI Methods and Attributes*
`connect($data_source, $username, $password)'
Use the `connect' method to make a database connection to the data
source. The `$data_source' value should begin with
`DBI:driver_name:'. Example uses of `connect' with the
`DBD::mysql' driver:
$dbh = DBI->connect("DBI:mysql:$database", $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname",
$user, $password);
$dbh = DBI-> `DBI_PASS' environment variables,
respectively. If you don't specify a hostname, it defaults to
`'localhost''. If you don't specify a port number, it defaults to
the default MySQL port (3306).
As of `Msql-Mysql-modules' Version 1.2009, the `$data_source'
value allows certain modifiers:
`mysql_read_default_file=file_name'
Read `file_name' as an option file. For information on
option files, see *Note Option files::.
the default group becomes
the `[group_name]' group.
`mysql_compression=1'
Use compressed communication between the client and server
(MySQL Version 3.22.3 or later).
`mysql_socket=/path/to/socket'
Specify the pathname of the Unix socket that is used to
connect to the server (MySQL Version 3.21.15 or later).
Multiple modifiers may be given; each must be preceded by a
semicolon.
For example, if you want to avoid hardcoding the user name and
password into a `DBI' script, you can take them from the user's
`~/.my.cnf' option file instead by writing your `connect' call
like this:
$dbh = DBI->connect("DBI:mysql:$database"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
$user, $password);
this:
$dbh = DBI->connect("DBI:mysql:$database"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
. ";mysql_read_default_group=perl",
$user, $password);
`disconnect'
The `disconnect' method disconnects the database handle from the
database. This is typically called right before you exit from the
program. Example:
$rc = $dbh->disconnect;
`prepare($statement)'
Prepares an SQL statement for execution by the database engine and
returns a statement handle `($sth)', which you can use to invoke
the `execute' method.
Typically you handle `SELECT' statements (and `SELECT'-like
statements such as `SHOW', `DESCRIBE', and `EXPLAIN') by means of
`prepare' and `execute'. Example:
$sth = $dbh->prepare($statement)
or die "Can't prepare $statement: $dbh->errstr\n";
If you want to read big results to your client you can tell Perl
to use `mysql_use_result()' with:
my $sth = $dbh->prepare($statement { "mysql_use_result" => 1});
`execute'
The `execute' method executes a prepared statement. For
non-`SELECT' statements, `execute' returns the number of rows
affected. If no rows are affected, `execute' returns `"0E0"',
which Perl treats as zero but regards as true. If an error occurs,
`execute' returns `undef'. For `SELECT' statements, `execute' only
starts the SQL query in the database; you need to use one of the
`fetch_*' methods described here to retrieve the data. Example:
$rv = $sth->execute
or die "can't execute the query: " . $sth->errstr;
`do($statement)'
The `do' method prepares and executes an SQL statement and returns
the number of rows affected. If no rows are affected, `do' returns
`"0E0"', which Perl treats as zero but regards as true. This
method is generally used for non-`SELECT' statements that cannot
be prepared in advance (due to driver limitations) or that do not
need to be executed more than once (inserts, deletes, etc.).
Example:
$rv = $dbh->do($statement)
or die "Can't execute $statement: $dbh- >errstr\n";
Generally the 'do' statement is much faster (and is preferable)
than prepare/execute for statements that don't contain parameters.
`quote($string)'
The `quote' method is used to "escape" any special characters
contained in the string and to add the required outer quotation
marks. Example:
$sql = $dbh->quote($string)
`fetchrow_array'
This method fetches the next row of data and returns it as an
array of field values. Example:
while(@row = $sth->fetchrow_array) {
print qw($row[0]\t$row[1]\t$row[2]\n);
}
`fetchrow_arrayref'
This method fetches the next row of data and returns it as a
reference to an array of field values. Example:
while($row_ref = $sth->fetchrow_arrayref) {
print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
}
`fetchrow_hashref'
This method fetches a row of data and returns a reference to a hash
table containing field name/value pairs. This method is not nearly
as efficient as using array references as demonstrated above.
Example:
while($hash_ref = $sth->fetchrow_hashref) {
print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
$hash_ref->{title}\n);
}
`fetchall_arrayref'
This method is used to get all the data (rows) to be returned from
the SQL statement. It returns a reference to an array of
references to arrays for each row. You access or print the data by
using a nested loop. Example:
my $table = $sth->fetchall_arrayref
or die "$sth->errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} ) {
for $j ( 0 .. $#{$table->[$i]} ) {
print "$table->[$i][$j]\t";
}
print "\n";
}
`finish'
Indicates that no more data will be fetched from this statement
handle. You call this method to free up the statement handle and
any system resources associated with it. Example:
$rc = $sth->finish;
`rows'
Returns the number of rows changed (updated, deleted, etc.) by the
last command. This is usually used after a non-`SELECT' `execute'
statement. Example:
$rv = $sth->rows;
`NULLABLE'
Returns a reference to an array of values that indicate whether
columns may contain `NULL' values. The possible values for each
array element are 0 or the empty string if the column cannot be
`NULL', 1 if it can, and 2 if the column's `NULL' status is
unknown. Example:
$null_possible = $sth->{NULLABLE};
`NUM_OF_FIELDS'
This attribute indicates the number of fields returned by a
`SELECT' or `SHOW FIELDS' statement. You may use this for
checking whether a statement returned a result: A zero value
indicates a non-`SELECT' statement like `INSERT', `DELETE', or
`UPDATE'. Example:
$nr_of_fields = $sth->{NUM_OF_FIELDS};
`data_sources($driver_name)'
This method returns an array containing names of databases
available to the MySQL server on the host `'localhost''. Example:
@dbs = DBI->data_sources("mysql");
`ChopBlanks'
This attribute determines whether the `fetchrow_*' methods will
chop leading and trailing blanks from the returned values.
Example:
$sth->{'ChopBlanks'} =1;
`trace($trace_level)'
`trace($trace_level, $trace_filename)'
The `trace' method enables or disables tracing. When invoked as a
`DBI' class method, it affects tracing for all handles. When
invoked as a database or statement handle method, it affects
tracing for the given handle (and any future children of the
handle). Setting `$trace_level' to 2 provides detailed trace
information. Setting `$trace_level' to 0 disables tracing. Trace
output goes to the standard error output by default. If
`$trace_filename' is specified, the file is opened in append mode
and output for *all* traced handles is written to that file.
Example:
DBI->trace(2); # trace everything
DBI->trace(2,"/tmp/dbi.out"); # trace everything to
# /tmp/dbi.out
$dth->trace(2); # trace this database handle
$sth->trace(2); # trace this statement handle
You can also enable `DBI' tracing by setting the `DBI_TRACE'
environment variable. Setting it to a numeric value is equivalent
to calling `DBI->(value)'. Setting it to a pathname is equivalent
to calling `DBI->(2,value)'.
*MySQL-specific Methods and Attributes*
and
`table'. Where `DBI'-standard alternatives exist, they are noted here:
`mysql_insertid'
If you use the `AUTO_INCREMENT' feature of MySQL, the new
auto-incremented values will be stored here. Example:
$new_id = $sth->{mysql_insertid};
With old versions of the DBI interface, you could use
`$sth->{'insertid'}'.
`is_blob'
Returns a reference to an array of boolean values; for each
element of the array, a value of TRUE indicates that the
respective column is a `BLOB'. Example:
$keys = $sth->{is_blob};
`is_key'
Returns a reference to an array of boolean values; for each
element of the array, a value of TRUE indicates that the
respective column is a key. Example:
$keys = $sth->{is_key};
`is_num'
Returns a reference to an array of boolean values; for each
element of the array, a value of TRUE indicates that the
respective column contains numeric values. Example:
$nums = $sth->{is_num};
`is_pri_key'
Returns a reference to an array of boolean values; for each
element of the array, a value of TRUE indicates that the
respective column is a primary key. Example:
$pri_keys = $sth->{is_pri_key};
`is_not_null'
Returns a reference to an array of boolean values; for each
element of the array, a value of FALSE indicates that this column
may contain `NULL' values. Example:
$not_nulls = $sth->{is_not_null};
`is_not_null' is deprecated; it is preferable to use the
`NULLABLE' attribute (described above), because that is a DBI
standard.
in the table description).
The `max_length' array indicates the maximum sizes actually
present in the result table. Example:
$lengths = $sth->{length};
$max_lengths = $sth->{max_length};
`NAME'
Returns a reference to an array of column names. Example:
$names = $sth->{NAME};
`table'
Returns a reference to an array of table names. Example:
$tables = $sth->{table};
`type'
Returns a reference to an array of column types. Example:
$types = $sth->{type};
More `DBI'/`DBD' Information
----------------------------
You can use the `perldoc' command to get more information about `DBI'.
perldoc DBI
perldoc DBI::FAQ
perldoc DBD::mysql
You can also use the `pod2man', `pod2html', etc., tools to translate to
other formats.
You can find the latest `DBI' information at the `DBI' web page:
`http://dbi.perl.org/'.
[Назад] [Содержание] [Вперед]
| Главная |