![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Connecting to the database in PHP
Now you'll connect to the same DB2 database from PHP running in Zend
Core For IBM, and create a controller and view that test that
connection. To maintain a similar abstraction to that of the
DataSource on the Java side, you'll create
a class Db2DataSource that's responsible
for maintaining the connection to DB2 and for transactions against
that connection, and that uses the
Properties object for its connection/login
values. You'll also use a StocksDao class
as you did in Java, that uses a
Db2DataSource to access the application's
tables. Using a common structure in the Java and PHP halves of the
application will allow you to move between them more easily, allowing
you to implement features of your Facebook application in whichever
technology is more appropriate without concern for compromising the
code.
First write Db2DataSource to encapsulate DB2 access. In the lib sub-directory, create Db2Database.php as in Listing 20.
Listing 20. Class
Db2DataSource (PHP)
class Db2DataSource extends Injectable {
private $connection;
public function __construct($properties, $persist=true) {
parent::__construct($properties);
$this->connect ($persist);
}
private function connect ($persist=true) {
$connectionString =
"DRIVER={IBM DB2 ODBC DRIVER};" .
"DATABASE=$this->database;" .
"HOSTNAME=$this->hostname;" .
"PORT=$this->port;" .
"PROTOCOL=TCPIP;" .
"UID=$this->username;" .
"PWD=$this->password;";
if ($persist) {
$this->connection = db2_pconnect($connectionString, '', '');
} else {
$this->connection = db2_connect($connectionString, '', '');
}
if (! $this->connection) {
throw new Exception ("Db2DataSource -- couldn't connect to DB2: " .
db2_conn_errormsg());
}
}
public function close() {
if ($this->connection) {
db2_close($this->connection);
}
}
}
|
The constructor takes a Properties object
because it's an Injectable, and a
$persist flag specifying whether to create a persistent
database connection that survives between requests or one that is
created anew each time. connect() creates
the standard DB2 connection string containing the driver, protocol,
and connection information for the specific database. The
close() method closes the connection if a
valid one exists. Note that connect()
refers to these values as instance variables, though they are actually
specified in a properties file (shown below); the Injectable subclass
insulates Db2DataSource from this detail
other than the need to accept a Properties
object in its constructor.
Now that you can connect to DB2, you can query it and write data to it.
Start with a generic executeQuery() method
that wraps the call to db2_exec() and
throws an Exception reporting any error msg obtained through
db2_stmt_errormsg() if the query fails (see
Listing 21).
Listing 21.
Db2DataSource.executeQuery()
public function executeQuery($query) {
$result = db2_exec($this->connection, $query);
if ($result == null) {
throw new Exception("Db2DataSource.executeQuery failed on query [$query],
DB2 error is " . db2_stmt_errormsg());
}
return $result;
}
|
Then add a fetchObjects() method that uses
executeQuery(), returning an array of PHP
objects in response to a SQL query (see listing 22). Note that
db2_fetch_object() puts fields on the
returned objects in ALL CAPS, e.g.,
$stock->TICKER, not
$stock->ticker.
Listing 22.
Db2Database.fetchObjects()
public function fetchObjects($query) {
$result = $this->executeQuery($query);
$objects = array();
while ($rowObject = db2_fetch_object($result)) {
$objects[] = $rowObject;
}
return $objects;
}
|
Now that you have a generic class for connecting to DB2 and executing
DB2 queries, as you did on the Java code side you'll create a
StocksDao class that contains
application-specific queries.
StocksDao will contain all of the actual SQL
queries, as it does in the Java code side. First, let's add a method for
obtaining all the available stocks (see Listing 23).
Listing 23. StocksDao.php
<?php
class StocksDao {
private $db;
public function StocksDao($db) {
$this->db = $db;
}
public function fetchAllStocks() {
return $this->db->fetchObjects('select * from stock');
}
}
?>
|
Note that StocksDao holds onto a
$db object (a
Db2DataSource) that it uses for all
database access, and therefore StocksDao
will contain almost no code except the specific SQL queries you need.
Figure 7 provides a map of the PHP data access components for comparison with the analogous diagram on the Java code side in Figure 6. The Java data source involved more pieces and required more configuration work, but in return provides more security, scalability and an industry standard.
Figure 7. Accessing the data in PHP
Using an abstract controller base class in PHP
Recall that ActionDispatcher will create a
PHP controller, give it a Properties
object, and call an execute() method on it,
expecting back a ModelAndView object to
render the view, exactly as on the Java code side. Since you'll want a
database connection in all of your controllers, make them all
subclasses of a AbstractStockDemoController
class, which itself subclasses
AbstractController (see Listing 24). All of
your application-specific PHP code should go in the app subdirectory
of fb_stock_demo.
Listing 24.
AbstractStockDemoController class (PHP)
abstract class AbstractStockDemoController extends AbstractController {
protected $db;
protected $dao;
public function __construct() {
}
public function execute() {
try {
$this->db = new Db2DataSource($this->properties, 'db');
$this->dao = new StocksDao ($this->db);
$response = $this->executeSpecific();
$this->db->close();
return $response;
} catch (Exception $e) {
$this->db->close();
throw $e;
}
}
protected abstract function executeSpecific();
}
|
AbstractStockDemoController implements the
execute() method called by
ActionDispatcher, connects to the database
using the "injected" properties file, specifying db as the prefix to
use when looking up database connection properties, creates a
StocksDao from it for use by the
subclasses, and calls the abstract
executeSpecific() method. The subclass
implements executeSpecific() to handle the
specifics of the request. Execute() also
makes sure to close the database connection. Note that because PHP
does not provide a finally construct as in
Java code, you need to do so both in the try and
catch blocks. You provide the empty
__construct() so that the subclass can call
parent::__construct() (it shouldn't have to
know that it doesn't have to for this specific base class).
Testing the PHP data source with a controller and view
With the abstract controller class in place you can create a subclass
for testing the database connection,
DbTestController, as in Listing 25. Put
DbTestController.php in the app sub-directory under fb_stock_demo.
Listing 25.
DbTestController PHP class
class DbTestController extends AbstractStockDemoController {
public function executeSpecific() {
return new ModelAndView ("dbTestView.php",
$this->dao->fetchAllStocks());
}
}
|
This probably looks familiar. The
executeSpecific() method, called by the
base class's execute() method, fetches the
stocks and returns the view (dbTestView.php) and model (the array of
Stock objects), just as the controller did
on the Java code side, and ActionDispatcher will
render dbTestView.php using the supplied model of stocks (see Listing
26). Put dbTestView.php in the app directory along with the
controller.
Listing 26. dbTestView.php
The result of calling DbTestController was:
<br/><br/>
<?
foreach ($model as $stock):
?>
Stock <?= $stock->ID ?>: <?= $stock->TICKER ?> <br/>
<?
endforeach;
?>
|
Note that you have to access the returned objects' fields using ALL
CAPS, because this is how
db2_fetch_object() created the row objects.
You've already created an entry for this controller in .htaccess under
the request key phpDbTest to dispatch to
the PHP side of the application; now you need to provide a key for
ActionDispatcher. Since this key is
injected, create an app.properties containing an entry for the new
controller (see Listing 27), in a conf subdirectory under
fb_stock_demo.
Listing 27. app.properties
ActionDispatcher/dbTest=DbTestController
|
Finally, to provide the database connection information, create file db.properties next to app.properties, containing the contents of Listing 28.
Listing 28. db.properties
Db2DataSource/database=stocks
Db2DataSource/hostname=localhost
Db2DataSource/port=50000
Db2DataSource/username=[your operating system username]
Db2DataSource/password=[your operating system password]
|
You could inject any other properties into any other classes you wish
by creating similar entries in app.properties (or another file, as
long as index.php includes it when creating the
Properties object).
To confirm that your lightweight MVC framework and DB2 connection are working on the PHP side, go to http://localhost/fb_stock_demo/phpDbTest in the browser, and you'll see your view populated with stocks from the database.
