From 11e410a533bc7ae4009dd5467f798d6c387088ad Mon Sep 17 00:00:00 2001 From: xue <> Date: Tue, 28 Nov 2006 16:05:43 +0000 Subject: Added quickstart tutorial about DAO. --- demos/quickstart/protected/pages/Database/DAO.page | 160 +++++++++++++++++++++ 1 file changed, 160 insertions(+) create mode 100644 demos/quickstart/protected/pages/Database/DAO.page (limited to 'demos/quickstart') diff --git a/demos/quickstart/protected/pages/Database/DAO.page b/demos/quickstart/protected/pages/Database/DAO.page new file mode 100644 index 00000000..0f60b0ef --- /dev/null +++ b/demos/quickstart/protected/pages/Database/DAO.page @@ -0,0 +1,160 @@ + + +

Data Access Objects (DAO)

+

+Data Access Objects (DAO) separates a data resource's client interface from its data access mechanisms. It adapts a specific data resource's access API to a generic client interface. As a result, data access mechanisms can be changed independently of the code that uses the data. +

+

+Since version 3.1, PRADO starts to provide a DAO that is a thin wrap around PHP Data Objects (PDO). Although PDO has a nice feature set and good APIs, we choose to implement the PRADO DAO on top of PDO because the PRADO DAO classes are component classes and are thus configurable in a PRADO application. Users can use these DAO classes in a more PRADO-preferred way. +

+

+The PRADO DAO mainly consists of the following four classes (in contrast to PDO which uses only two classes, PDO and PDOStatement): +

+ +

+In the following, we introduce the usage of PRADO DAO in different scenarios. +

+ +

Establishing Database Connection

+

+To establish a database connection, one creates a TDbConnection instance and activate it. A data source name (DSN) is needed to specify the information required to connect to the database. The database username and password may need to be supplied to establish the connection. +

+ +$connection=new TDbConnection($dsn,$username,$password); +// call setAttribute() to pass in additional connection parameters +// $connection->Persistent=true; // use persistent connection +$connection->Active=true; // connection is established +.... +$connection->Active=false; // connection is closed + +

+Complete specification of DSN may be found in the PDO documentation. Below is a list of commonly used DSN formats: +

+ +

+In case any error occurs when establishing the connection (such as bad DSN or username/password), a TDbException will be raised. +

+ +

Executing SQL Statements

+

+Once a database connection is established, SQL statements can be executed through TDbCommand. One creates a TDbCommand by calling TDbConnection.createCommand() with the specified SQL statement: +

+ +$command=$connection->createCommand($sqlStatement); +// if needed, the SQL statement may be updated as follows: +$command->Text=$newSqlStatement; + + +

+An SQL statement is executed via TDbCommand in one of the following two ways: +

+ + +$affectedRowCount=$command->execute(); // execute the non-query SQL +$dataReader=$command->query(); // execute a query SQL +$row=$command->queryRow(); // execute a query SQL and return the first row of result +$value=$command->queryScalar(); // execute a query SQL and return the first column value + +

+In case an error occurs during the execution of SQL statements, a TDbException will be raised. +

+ +

Fetching Query Results

+

+After TDbCommand.query() generates the TDbDataReader instance, one can retrieve rows of resulting data by calling TDbDataReader.read() repeatedly. One can also use TDbDataReader in PHP's foreach language construct to retrieve row by row. +

+ +// calling read() repeatedly until it returns false +while(($row=$dataReader->read())!==false) { ... } +// using foreach to traverse through every row of data +foreach($dataReader as $row) { ... } +// retrieving all rows at once in a single array +$rows=$dataReader->readAll(); + + +

Using Transactions

+

+When an application executes a few queries, each reading and/or writing information in the database, it is important to be sure that the database is not left with only some of the queries carried out. A transaction, represented as a TDbTransaction instance in PRADO, may be initiated in this case: +

+ + +$transaction=$connection->beginTransaction(); +try +{ + $connection->createCommand($sql1)->execute(); + $connection->createCommand($sql2)->execute(); + //.... other SQL executions + $transaction->commit(); +} +catch(Exception $e) // an exception is raised if a query fails will be raised +{ + $transaction->rollBack(); +} + + + +

Binding Parameters

+

+To avoid SQL injection attacks and to improve performance of executing repeatedly used SQL statements, one can "prepare" an SQL statement with optional parameter placeholders that are to be replaced with the actual parameters during the parameter binding process. +

+

+The parameter placeholders can be either named (represented as unique tokens) or unnamed (represented as question marks). Call TDbCommand.bindParameter() or TDbCommand.bindValue() to replace these placeholders with the actual parameters. The parameters do not need to be quoted: the underlying database driver does it for you. Parameter binding must be done before the SQL statement is executed. +

+ +// an SQL with two placeholders ":username" and ":email" +$sql="INSERT INTO users(username, email) VALUES(:username,:email)"; +$command=$connection->createCommand($sql); +// replace the placeholder ":username" with the actual username value +$command->bindParameter(":username",$username,PDO::PARAM_STR); +// replace the placeholder ":email" with the actual email value +$command->bindParameter(":email",$email,PDO::PARAM_STR); +$command->execute(); +// insert another row with a new set of parameters +$command->bindParameter(":username",$username2,PDO::PARAM_STR); +$command->bindParameter(":email",$email2,PDO::PARAM_STR); +$command->execute(); + +

+The methods bindParameter() and bindValue() are very similar. The only difference is that the former binds a parameter with a PHP variable reference while the latter with a value. For parameters that represent large block of data memory, the former is preferred for performance consideration. +

+

+For more details about binding parameters, see the relevant PHP documentation. +

+ + +

Binding Columns

+

+When fetching query results, one can also bind columns with PHP variables so that they are automatically populated with the latest data each time a row is fetched. +

+ +$sql="SELECT username, email FROM users"; +$dataReader=$connection->createCommand($sql)->query(); +// bind the 1st column (username) with the $username variable +$dataReader->bindColumn(1,$username); +// bind the 2nd column (email) with the $email variable +$dataReader->bindParameter(2,$email); +while($dataReader->read()!==false) +{ + // $username and $email contain the username and email in the current row +} + + + \ No newline at end of file -- cgit v1.2.3