PDO

Question 1

What is PDO?

Answer 1

PHP Data Objects - defines a lightweight, consistent interface for accessing databases in PHP.

Question 2

How are PDO connections established?

Answer 2

By creating instances of the PDO base class.

Question 3

What happens if your application does not catch the exception thrown from the PDO constructor?

Answer 3

The default action taken by the zend engine is to terminate the script and display a back trace. It’s your responsibility to catch the exception, either explicitly (via a catch statement) or implicitly via set_exception_handler().

Question 4

How long does a PDO connection remain active?

Answer 4

For the lifetime of the PDO object representing it.

Question 5

How can you close a PDO connection?

Answer 5

You need to destroy the object by ensuring that all remaining references to it are deleted. You do this by assigning NULL to the variable that holds the object.

Question 6

What happens if you don’t explicitly close a PDO connection?

Answer 6

PHP will automatically close the connection when the script ends.

Question 7

How can you use persistent connections with PDO?

Answer 7

Set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. If setting this attribute with PDO::setAttribute() after instantiation of the object, the driver will not use persistent connections.

Question 8

When should you not use persistent connections with PDO?

Answer 8

When you’re using the PDO ODBC driver and your libraries support ODBC Connection Pooling.

Question 9

What 4 major features do database transactions offer?

Answer 9

Atomicity, Consistency, Isolation, and Durability (ACID).

Question 10

What does it mean when PDO is running in “auto-commit” mode?

Answer 10

It means that every query you run has its own implicit transaction, if the database supports transactions, or no transaction if the database doesn’t support transactions.

Question 11

What do you use if you need a transaction in PDO?

Answer 11

PDO::beginTransaction() to initiate a transaction. If the underlying driver does not support transactions, a PDOException will be thrown.

Question 12

What do you use to finish transactions in PDO?

Answer 12

PDO::commit() or PDO::rollBack.

Question 13

When will PDO::beginTransaction return true, even though the transaction will fail?

Answer 13

When certain runtime conditions mean that transactions are unavailable, even though they’re supported by the driver. For example, when trying to use transactions on MyISAM tables.

Question 14

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, what will happen?

Answer 14

PDO will automatically roll it back. This only happens if you initiate the transaction with PDO::beginTransaction. If you manually issue a query that begins a transaction, PDO will not roll it back.

Question 15

What are prepared statements?

Answer 15

A kind of compiled template for the SQL that an application wants to run, than can be customized using variable parameters.

Question 16

What are the benefits of prepared statements?

Answer 16

1. The query only needs to be parsed and prepared once, but can be executed multiple times.
2. The parameters to prepared statements don’t need to be quoted; the driver automatically handles this.

Question 17

What is the only feature that PDO will emulate for drivers that don’t support it?

Answer 17

Prepared statements.

Question 18

What are stored procedures?

Answer 18

Subroutines stored in the database catalog, that can be called and executed by applications.

Question 19

What is PDO::ERRORMODE_SILENT?

Answer 19

This is the default PDO error mode. PDO will set the error code for you to inspect in PDO::errorCode() and PDO::errorInfo() methods on both the statement and database objects. If the error resulted from a call on a statement object, you would invoke the PDOStatement::errorCode() or PDOStatement::errorInfo() method on that object. If the error resulted from a call on the database object, you would invoke those methods on the database object instead.

Question 20

What are the three PDO error modes?

Answer 20

PDO::ERRORMODE_SILENT, PDO::ERRORMODE_WARNING, PDO::ERRORMODE_EXCEPTION.

Question 21

What is PDO::ERRORMODE_WARNING?

Answer 21

In addition to setting the error code, as for PDO::ERRORMODE_SILENT, it emits a traditional E_WARNING message. This is useful during debugging/testing.

Question 22

What is PDO::ERRORMODE_EXCEPTION?

Answer 22

In addition to setting the error code, as for PDO::ERRORMODE_SILENT, PDO will throw a PDOException and set its properties to reflect the error code and error information.

Question 23

What does PDO::errorCode() return?

Answer 23

A single SQLSTATE code.

Question 24

What does PDO::errorInfo() return?

Answer 24

An array containing the SQLSTATE code, the driver specific error code and driver specific error string.

Question 25

When does a PDOException get thrown, regardless of error mode?

Answer 25

When PDO::__construct() fails to make a connection. Uncaught exceptions are fatal.

Question 26

How do you work with blobs in PDO?

Answer 26

Use the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls.

Question 27

What does PDO::PARAM_LOB do?

Answer 27

It tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

Question 28

What exactly does the PDO class represent?

Answer 28

A connection between PHP and a database server.

Question 29

What exactly does the PDOStatement class represent?

Answer 29

A prepared statement and, after the statement is executed, an associated result set.

Question 30

What’s the difference between a database driver and a database connector?

Answer 30

In MySQL documentation, the term “driver” is reserved for software that provides the database-specific part of a connector package.

Question 31

What 3 different APIs does PHP offer to connect to MySQL?

Answer 31

mysql, mysqli, and PDO

Question 32

Why shouldn’t you use the old mysql extension for new development?

Answer 32

It’s deprecated in PHP 5.5.0, and will be removed in the future.

Question 33

Which is the best-performing PHP MySQL extension?

Answer 33

The overall performance of all 3 is considered to be about the same.

Question 34

Which compile-time library for MySQL is recommended?

Answer 34

mysqlnd, and not libmysqlclient

Question 35

Do MySQL queries use buffered or unbuffered mode by default?

Answer 35

Buffered.

Question 36

What is MySQL query buffered mode?

Answer 36

Query results are immediately transferred from the MySQL server to PHP, and is then kept in the memory of the PHP process.

Question 37

What are the advantages of buffered queries?

Answer 37

It allows additional operations like counting the number of rows, moving (seeking) the current result pointer, and issuing further queries on the same connection while working on the result set.

Question 38

What is the downside of buffered queries?

Answer 38

Larger result sets might require quite a lot of memory.

Question 39

Regarding result set memory, what is the difference between using libmysqlclient vs. mysqlnd?

Answer 39

When using libmysqlclient as a library, PHP’s memory limit won’t count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd, the memory accounted for will include the full result set.

Question 40

When should buffered queries be used?

Answer 40

When you expect only a limited result set, or need to know the amount of returned rows before reading all rows.

Question 41

When should unbuffered queries be used?

Answer 41

When you expect larger results.

Question 42

Can you set a MySQL character set at runtime?

Answer 42

Yes.

Question 43

Will the escaping mechanisms for the MySQL APIs use character sets defined within a query?

Answer 43

No. Each API’s charset altering method should be used.