Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

CHAPTER 23 INTRODUCING PDO The execute() method is responsible for executing a prepared query. To do so, it requires the input parameters that should be substituted with each iterative execution. This is accomplished in one of two ways: either pass the values into the method as an array, or bind the values to their respective variable name or positional offset in the query using the bindParam() method. The first option is covered next, and the second option is covered in the upcoming introduction to bindParam(). The following example shows how a statement is prepared and repeatedly executed by execute(), each time with different parameters: // Connect to the database server $dbh = new PDO(”pgsql:host=localhost;dbname=corporate”, “websiteuser”, “secret”); // Create and prepare the query $query = “INSERT INTO product SET sku = :sku, name = :name”; $stmt = $dbh->prepare($query); // Execute the query $stmt->execute(array(’:sku’ => ‘MN873213′, ‘:name’ => ‘Minty Mouthwash’)); // Execute again $stmt->execute(array(’:sku’ => ‘AB223234′, ‘:name’ => ‘Lovable Lipstick’)); This example is revisited below, where you ll learn how to pass along query parameters by binding them using the bindParam() method. bindParam() boolean PDOStatement::bindParam (mixed parameter, mixed &variable [, int datatype [, int length [, mixed driver_options]]]) You might have noted in the previous introduction to execute() that the input_parameters parameter was optional. This is convenient because if you need to pass along numerous variables, providing an array in this manner can quickly become unwieldy. So what s the alternative? The bindParam()method offers a somewhat cleaner method for binding parameters to corresponding query placeholders. When using named parameters, parameter is the name of the column value placeholder specified in the prepared statement using the syntax :name. When using question mark parameters, parameter is the index offset of the column value placeholder as located in the query. The variable parameter stores the value to be assigned to the placeholder. It s depicted as passed by reference, because when using this method in conjunction with a prepared stored procedure, the value could be changed according to some action in the stored procedure. This feature won t be demonstrated in this section; however, after you read Chapter 32, the process should be fairly obvious. The datatype parameter explicitly sets the parameter datatype, and can be any of the following values: PDO_PARAM_NULL: SQL NULL datatype PDO_PARAM_INT: SQL INTEGER datatype PDO_PARAM_STR: SQL CHAR, VARCHAR, and other string datatypes
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

564 CHAPTER 23 INTRODUCING PDO Prepared Statements Each time a query is sent to the PostgreSQL server, the query syntax must be parsed to ensure a proper structure and to ready it for execution. This is a necessary step of the process, and it does incur some overhead. Doing so once is a necessity, but what if you re repeatedly executing the same query, only changing the column values as you might do when batch-inserting several rows? A prepared statement will eliminate this additional overhead by caching the query syntax and execution process to the server, and traveling to and from the client only to retrieve the changing column value(s). PDO offers prepared-statement capabilities for those databases supporting this feature. Because PostgreSQL supports it, you re free to use prepared statements as you see fit. Prepared statements are accomplished using two methods, prepare(), which is responsible to ready the query for execution, and execute(), which is used to repeatedly execute the query using a provided set of column parameters. These parameters can be provided to execute() either explicitly by passing them into the method as an array, or by using bound parameters assigned using the bindParam() method. All three of these methods are introduced next. prepare() PDOStatement PDO::prepare (string query [, array driver_options]) The prepare()method is responsible for readying the query for execution. A query intended for use as a prepared statement looks a bit different from those you might be used to, however, because placeholders must be used instead of actual column values for those that will change across execution iterations. Two syntax variations are supported, named parameters and question mark parameters. For example, a query using the former variation might look like this: INSERT INTO product SET sku = :sku, name = :name; While the same query using the latter variation would look like this: INSERT INTO product SET sku = ?, name = ?; The variation you choose is entirely a matter of preference, although perhaps the former is a tad more explicit. For this reason, this variation will be used in relevant examples. To begin, let s use prepare() to ready a query for iterative execution: $dbh = new PDO(”pgsql:host=localhost;dbname=corporate”, “websiteuser”, “secret”); $query = “INSERT INTO product SET sku = :sku, name = :name”; $stmt = $dbh->prepare($query); Once the query is prepared, we can go about executing it, accomplished using the execute() method, which is introduced next. In addition to the query, you can also pass along database driver specific options via the driver_options parameter. execute() boolean PDOStatement::execute ([array input_parameters])
Check Tomcat Web Hosting services for best quality webspace to host your web application.

CHAPTER 23 INTRODUCING PDO The methods mentioned in the first two bullets are introduced in this section, and those referenced in the third bullet are discussed in the section that follows, Prepared Statements. exec() int PDO::exec (string query) The exec() method executes query and returns the number of rows affected by it. Consider the following example: $query = “UPDATE product SET name=’Painful Aftershave’ WHERE sku=’ZP457321′”; $affected = $dbh->exec($query); echo “Total rows affected: $affected”; Based on the sample data, this example would return: Total rows affected: 1 Note that this method shouldn t be used in conjunction with SELECT queries; instead, the query() method should be used for these purposes. query() PDOStatement query (string query) The query() method executes the query specified by query, returning it as a PDOStatement object. An example follows: $query = “SELECT sku, name FROM product ORDER BY rowid”; foreach ($dbh->query($query) AS $row) { $sku = $row[’sku’]; $name = $row[’name’]; echo “Product: $name ($sku)
“; } Based on the sample data introduced earlier in the chapter, this produces: Product: AquaSmooth Toothpaste (TY232278) Product: HeadsFree Shampoo (PO988932) Product: Painless Aftershave (ZP457321) Product: WhiskerWrecker Razors (KL334899) Tip If you use query() and would like to learn more about the total number of rows affected, use the rowCount() method.
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

562 CHAPTER 23 INTRODUCING PDO To set the error mode, just use the setAttribute() method, like so: $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION); There are also two methods available for retrieving error information. Both are introduced next. errorCode() int PDOStatement::errorCode() The SQL standard offers a list of diagnostic codes used to signal the outcome of SQL queries, known as SQLSTATE codes. A complete list of PostgreSQL-supported codes and their corresponding meanings can be found in the online documentation available at http://www. postgresql.org/. The errorCode() method is used to return this standard SQLSTATE code, which you might choose to store for logging purposes or even for producing your own custom error messages. errorInfo() array PDOStatement::errorInfo() The errorInfo() method produces an array consisting of error information pertinent to the most recently executed database operation. This array consists of three values, each referenced by a numerically indexed value between 0 and 2: 0: Stores the SQLSTATE code as defined in the SQL standard 1: Stores the database driver specific error code 2: Stores the database driver specific error message Query Execution Thus far we ve been discussing several of the key features that you should keep in mind to maximize your interaction with the PDO extension. However, we haven t actually done anything particularly interesting! That trend stops with this section, where you ll learn how to interact with the database by executing queries. There are three different tactics to take when executing queries, and the methods you use are dependent on your intent. These tactics can be categorized as such: Executing a query with no result set: When executing queries such as INSERT, UPDATE, and DELETE, no result set is returned. In such cases, the exec() method will return the number of rows affected by the query. Executing a query a single time: When executing a query that returns a result set, or when the number of affected rows is irrelevant, you should use the query() method. Executing a query multiple times: Although it s possible to execute a query numerous times using a while loop and the query() method, passing in different column values for each iteration, doing so is more efficient using a prepared statement. Doing so requires use of two methods, namely prepare() and execute().
If you are searching for cheap webhost for your web application, please visit MySQL5 Web Hosting services.

CHAPTER 23 INTRODUCING PDO Once a connection has been established, it s time to begin using it. This is the topic of the rest of this chapter. Getting and Setting Attributes Quite a few attributes are available for tweaking PDO s behavior, the most complete list of which is made available in the PHP documentation. As this was still in a state of flux at the time of writing, it makes the most sense to point you to the documentation rather than provide what would surely be an incomplete or incorrect summary. Therefore, see http://www.php.net/pdo for the latest information. Two methods are available for both setting and retrieving the values of these attributes. Both are introduced next. getAttribute() mixed PDOStatement::getAttribute (int attribute) The getAttribute() method will retrieve the value of the attribute specified by attribute. An example follows: $dbh = new PDO(’pgsql:dbname=corporate;host=localhost’, ‘root’, ‘jason’); echo $dbh->getAttribute(PDO_ATTR_CONNECTION_STATUS); Here s the result: Connection OK; waiting to send. setAttribute() boolean PDOStatement::setAttribute (int attribute, mixed value) The setAttribute() method assigns the value specified by value to the attribute specified by attribute. For example, to set PDO s error mode, you d need to set PDO_ATTR_ERRMODE like so: $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION); Error Handling PDO offers three error modes, allowing you to tweak the way in which errors are handled by the extension: PDO_ERRMODE_EXCEPTION: Throws an exception using the PDOException class, which will immediately halt script execution and offer information pertinent to the problem. PDO_ERRMODE_SILENT: Does nothing if an error occurs, leaving it to the developer to both check for errors and determine what to do with them. This is the default setting. PDO_ERRMODE_WARNING: Produces a PHP E_WARNING message if an error occurs while using the PDO extension.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

560 CHAPTER 23 INTRODUCING PDO Like the previous method, this method doesn t allow for the username and password to be included in the DSN. PDO s Connection-Related Options There are several connection-related options that you might consider tweaking by passing them into the driver_opts array. These options are enumerated here: PDO_ATTR_AUTOCOMMIT: Determines whether PDO will commit each query as it s executed, or will wait for the commit() method to be executed before effecting the changes. PDO_ATTR_CASE: You can force PDO to convert the retrieved column character casing to all uppercase or all lowercase, or have PDO use the columns exactly as they re found in the database. Such control is accomplished by setting this option to one of three values: PDO_CASE_UPPER, PDO_CASE_LOWER, and PDO_CASE_NATURAL, respectively. PDO_ATTR_ERRMODE: PDO supports three error-reporting modes, PDO_ERRMODE_EXCEPTION, PDO_ERRMODE_SILENT, and PDO_ERRMODE_WARNING. These modes determine what circumstances will cause PDO to report an error. Set this option to one of these three values to change the default behavior, which is PDO_ERRMODE_EXCEPTION. This feature is discussed in further detail in the later section Error Handling. PDO_ATTR_ORACLE_NULLS: When set to TRUE, this attribute causes empty strings to be converted to NULL when retrieved. By default this is set to FALSE. PDO_ATTR_PERSISTENT: Determines whether the connection is persistent. By default this is set to FALSE. PDO_ATTR_PREFETCH: Prefetching is a database feature that retrieves several rows even if the client is requesting one row at a time, the reasoning being that if the client requests one row, she s likely going to want others. Doing so decreases the number of database requests and therefore increases efficiency. This option sets the prefetch size, in kilobytes, for drivers that support this feature. PDO_ATTR_TIMEOUT: This option sets the number of seconds to wait before timing out. The following four attributes help you to learn more about the client, server, and connection status. The attribute values can be retrieved using the method getAttribute(), introduced in the later section Getting and Setting Attributes. PDO_ATTR_SERVER_INFO: Contains database-specific server information. In the case of PostgreSQL, it retrieves data pertinent to the process ID, client encoding, whether it s the superuser account that is connecting, and other important information. PDO_ATTR_SERVER_VERSION: Contains information pertinent to the database server s version number. PDO_ATTR_CLIENT_VERSION: Contains information pertinent to the database client s version number. PDO_ATTR_CONNECTION_STATUS: Contains database-specific information about the connection status. For instance, after a successful PostgreSQL connection, it contains Connection OK; waiting to send.
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

CHAPTER 23 INTRODUCING PDO 559 Oracle: Accessible via the OCI driver. PostgreSQL: Accessible via the PGSQL driver. SQLite 3.X: Accessible via the SQLITE driver. Sybase: Accessible via the SYBASE driver. Tip You can determine which PDO drivers are available to your environment either by loading phpinfo() into the browser and reviewing the list provided under the PDO section header, or by executing the pdo_drivers() function like so: . Connecting to a Database Server and Selecting a Database Before interacting with a database using PDO, you need to establish a server connection and select a database. This is accomplished through PDO s constructor. Its prototype follows: PDO PDO::__construct(string DSN [, string username [, string password [, array driver_opts]]]) The Data Source Name (DSN) parameter consists of two items: the desired database driver name, and any necessary database connection variables such as the hostname, port, and database name. The username and password parameters specify the username and password used to connect to the database, respectively. Finally, the driver_opts array specifies any additional options that might be required or desired for the connection. A list of available options is offered at the conclusion of this section. You re free to invoke the constructor in any of several ways, which are introduced next. Embedding the Parameters into the Constructor The first way to invoke the PDO constructor is to embed parameters into it. For instance, it can be invoked like this (PostgreSQL-specific): $dbh = new PDO(”pgsql:host=localhost;dbname=corporate”, “websiteuser”, “secret”); Referring to the php.ini File It s also possible to maintain the DSN information in the php.ini file by assigning it to a configuration parameter named pdo.dsn.aliasname, where aliasnameis a chosen alias for the DSN that is subsequently supplied to the constructor. For instance, the following example aliases the DSN to pgsqlpdo: [PDO] pdo.dsn.pgsqlpdo = “pgsql:dbname=corporate;host=localhost” The alias can subsequently be called by the PDO constructor, like so: $dbh = new PDO(”pgsqlpdo”, “websiteuser”, “secret”);
If you are looking for affordable and reliable webhost to host and run your business application visit our ftp web hosting services.

558 CHAPTER 23 INTRODUCING PDO rowid sku name 1 ZP457321 Painless Aftershave 2 TY232278 AquaSmooth Toothpaste 3 PO988932 HeadsFree Shampoo 4 KL334899 WhiskerWrecker Razors Installing PDO As mentioned, PDO comes packaged with PHP 5.1 and newer by default, so if you re running this version, you do not need to take any additional steps. If you re using a version older than 5.1, you can still use PDO by downloading it from PECL; however, because PDO takes full advantage of PHP 5 s new object-oriented features, it s not possible to use it in conjunction with any pre 5.0 release. Regardless, when configuring PHP, you ll still need to explicitly specify the drivers you d like to include (except for the SQLITE driver, which is included by default). For example, to enable support for the PostgreSQL PDO driver, you need to add the following flag to the configure command: –with-pdo-pgsql=/path/to/postgresql/installation Execute configure –help for more information about each specific PDO driver. If you re using PHP 5.1 or newer on the Windows platform, at the time of writing, the drivers did not come bundled with the distribution. Therefore, navigate to http://snaps.php.net/ win32/, enter the appropriate PECL directory, and download the PDO DLL to the directory specified by PHP s extension_dir directive. Next, you need to add references to the driver extensions within the php.ini file. For example, to enable support for PostgreSQL, add the following line to the Windows Extensions section: extension=php_pdo_pgsql.dll PDO s Database Support As of the time of writing, PDO supported nine databases, in addition to any database accessible via FreeTDS and ODBC. A summary of available drivers follows: Firebird: Accessible via the FIREBIRD driver. FreeTDS: Not a database, but a set of Unix libraries that enables Unix-based programs to talk to MSSQL and Sybase. Accessible via the DBLIB driver. IBM DB2: Accessible via the ODBC driver. Interbase 6: Accessible via the FIREBIRD driver. Microsoft SQL Server: Accessible via the MSSQL driver. MySQL 3.X/4.0: Accessible via the MYSQLdriver. Note that at the time of writing, an interface for MySQL 5 was not available. One can only imagine this is high on the developers priority list and will be resolved soon. ODBC v3: Not a database per se, but enables PDO to be used in conjunction with any ODBC-compatible database not found in this lit. Accessible via the ODBC driver.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

CHAPTER 23 INTRODUCING PDO perhaps too focused on PDO s database abstraction features rather than the entire array of capabilities it offers. Indeed, PDO serves as an ideal replacement for the DB package and similar solutions. However, PDO is actually much more than just a database abstraction layer, providing the following: Coding consistency: Because the various database extensions available to PHP are written by a host of different contributors, there is no coding consistency despite the fact that all of these extensions offer basically the same features. PDO removes this inconsistency by offering a singular interface that is used no matter the database. Furthermore, the extension is broken into two distinct components: the PDO core contains most of the PHP-specific code, leaving the various drivers to focus solely on the data. Also, the PDO developers took advantage of considerable knowledge and experience while building the database extensions over the past few years, capitalizing upon what was successful and being careful to omit what was not. Flexibility: Because PDO loads the necessary database driver at run time, there s no need to reconfigure and recompile PHP every time a different database is used. For instance, if your database needs suddenly switch from Oracle to PostgreSQL, just load the PDO_PGSQL driver and go (more about how to do this later). Object-oriented features: PDO takes advantage of PHP 5 s object-oriented features, resulting in more powerful and efficient database communication. Performance: PDO is written in C and compiled into PHP, which, all other things being equal, provides a considerable performance increase over solutions written in PHP. Given such advantages, what s not to like? This chapter serves to fully acquaint you with PDO and the myriad of features it has to offer. Using PDO PDO bears a striking resemblance to all of the database extensions long supported by PHP; therefore, if you have used PHP in conjunction with a database, the material presented in this section should be quite familiar. As mentioned, PDO was built with the best features of the preceding database extensions in mind, so it makes sense that you ll see a marked similarity in its methods. This section commences with a quick overview of the PDO installation process, and follows with a summary of its presently supported database servers. For the purposes of the examples found throughout the remainder of this chapter, we ll use the following PostgreSQL table: CREATE TABLE product ( rowid SERIAL, sku CHAR(8) NOT NULL, name VARCHAR(35) NOT NULL, PRIMARY KEY(rowid) ); The table has been populated with the following products:
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

556 CHAPTER 23 INTRODUCING PDO Figure 23-1. Using a database abstraction layer to decouple the application and data layers It s likely you ve heard of some of the more widespread implementations, a few of which are listed here: DB: DB is a database abstraction layer written in PHP and available as a PEAR package. (See Chapter 11 for more information about PEAR.) It presently supports FrontBase, InterBase, Informix, Mini SQL, MySQL, Oracle, ODBC, PostgreSQL, SQLite, and Sybase. JDBC: As its name implies, the Java Database Connectivity (JDBC) standard allows Java programs to interact with any database for which a JDBC driver is available. Among others, this includes MSSQL, MySQL, Oracle, and PostgreSQL. ODBC: The Open Database Connectivity (ODBC) interface is one of the most widespread abstraction implementations in use today, supported by a wide range of applications and languages, PHP included. ODBC drivers are offered by all mainstream databases, including those referenced in the above JDBC introduction. Perl DBI: The Perl Database Interface module is Perl s standardized means for communicating with a database, and was the inspiration behind PHP s DB package. As you can see, PHP offers DB and supports ODBC; therefore, it seems that your database abstraction needs are resolved when developing PHP-driven applications, right? While these (and many other) solutions are readily available, an even better solution has been in development for some time, and has been officially released with PHP 5.1. This solution is known as the PHP Data Objects (PDO) abstraction layer. Another Database Abstraction Layer? As PDO came to fruition over the past two years, it was met with no shortage of rumblings from developers either involved in the development of alternative database abstraction layers, or
In case you need quality webspace to host and run your web applications, try our personal web hosting services.