MySQL Functions (PDO_MYSQL)
Введение
PDO_MYSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL 3.x, 4.x and 5.x databases.
PDO_MYSQL will take advantage of native prepared statement support present in MySQL 4.1 and higher. If you're using an older version of the mysql client libraries, PDO will emulate them for you.
Beware: Some MySQL table types (storage engines) do not support transactions. When writing transactional database code using a table type that does not support transactions, MySQL will pretend that a transaction was initiated successfully. In addition, any DDL queries issued will implicitly commit any pending transactions.
Предопределенные константы
The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using mysql-specific attributes with the postgres driver may result in unexpected behaviour. PDO::getAttribute() may be used to obtain the PDO_ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.
- PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (integer)
-
If this attribute is set to TRUE on a
PDOStatement, the MySQL driver will use the
buffered versions of the MySQL API. If you're writing portable code, you
should use PDOStatement::fetchAll() instead.
Пример #1 Forcing queries to be buffered in mysql
<?php
if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') {
$stmt = $db->prepare('select * from foo',
array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
} else {
die("my application only works with mysql; I should use \$stmt->fetchAll() instead");
}
?> - PDO::MYSQL_ATTR_LOCAL_INFILE (integer)
-
Enable LOAD LOCAL INFILE.
- PDO::MYSQL_ATTR_INIT_COMMAND (integer)
-
Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting.
- PDO::MYSQL_ATTR_READ_DEFAULT_FILE (integer)
-
Read options from the named option file instead of from my.cnf.
- PDO::MYSQL_ATTR_READ_DEFAULT_GROUP (integer)
-
Read options from the named group from my.cnf or the file specified with MYSQL_READ_DEFAULT_FILE.
- PDO::MYSQL_ATTR_MAX_BUFFER_SIZE (integer)
-
Maximum buffer size. Defaults to 1 MiB.
- PDO::MYSQL_ATTR_DIRECT_QUERY (integer)
-
Perform direct queries, don't use prepared statements.
Содержание
- PDO_MYSQL DSN — Connecting to MySQL databases
- PHP Руководство
- Функции по категориям
- Индекс функций
- Справочник функций
- Расширения для работы с базами данных
- Уровни абстракции
- Объекты данных PHP
- CUBRID Functions (PDO_CUBRID)
- Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
- Firebird Functions (PDO_FIREBIRD)
- IBM Functions (PDO_IBM)
- Informix Functions (PDO_INFORMIX)
- MySQL Functions (PDO_MYSQL)
- Microsoft SQL Server Functions (PDO_SQLSRV)
- Oracle Functions (PDO_OCI)
- ODBC and DB2 Functions (PDO_ODBC)
- PostgreSQL Functions (PDO_PGSQL)
- SQLite Functions (PDO_SQLITE)
- 4D Functions (PDO_4D)
Коментарии
To use "PDO::MYSQL_ATTR_USE_BUFFERED_QUERY" you should call
PDO::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
It will not work when passed into PDO::prepare()
I have been getting the error below when performing multiple queries within a single page.
Setting the attribute below did not seem to work for me.
So building on previous example i am initilizing my stmt variable on every query and a fetch all into an array. Seems to be working for me.
Error:
PDO Error 1.1: Array ( [0] => xxx[1] => yyy[2] => Lost connection to MySQL server during query )
Fix:
(PDO::setAttribute("PDO::MYSQL_ATTR_USE_BUFFERED_QUERY", true);)
<?
try {
$dbh = new PDO('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array( PDO::ATTR_PERSISTENT => false));
$stmt = $dbh->prepare("CALL getname()");
// call the stored procedure
$stmt->execute();
// fetch all rows into an array.
$rows = $stmt->fetchAll();
foreach ($rows as $rs)
{
$id = $rs['id'];
}
//initilise the statement
unset($stmt);
$stmt = $dbh->prepare("call secondprocedure(?);");
$stmt->bindValue(1, $id);
if ( ! $stmt->execute() )
{
echo "PDO Error 1.1:\n";
print_r($stmt->errorInfo());
exit;
}
unset($stmt);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>
SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. ...
After spending hours trying to track down why we were getting this error on a new server, after the same code ran fine on other servers, we found the problem to be an old MySQL _client_ library running on our web server, and a latest-version MySQL _server_ running on the database server's box.
Upgraded the MySQL client on the web server to the current revision and the problem went away.
Today's PHP snapshot now has SSL support for PDO. Follow the directions here ( http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html ) to set up MySQL and then use the following connection options:
<?php
$pdo = new PDO(
'mysql:host=hostname;dbname=ssldb',
'username',
'password',
array(
PDO::MYSQL_ATTR_SSL_KEY =>'/path/to/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/path/to/ca-cert.pem'
)
);
?>
There is an important undocumented attribute which disables certificate CN verification available after
5.6.22 (not sure), 7.0.18 (verified) and 7.1.15 (not sure)
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
possible values: true, false
default value: true
Related PHP bugs:
https://bugs.php.net/bug.php?id=71845
https://bugs.php.net/bug.php?id=71003
and github PR: https://github.com/php/php-src/pull/1913