3 * TDbCommand class file
5 * @author Qiang Xue <qiang.xue@gmail.com>
6 * @link https://github.com/pradosoft/prado
7 * @copyright Copyright © 2005-2016 The PRADO Group
8 * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
15 * TDbCommand represents an SQL statement to execute against a database.
16 * It is usually created by calling {@link TDbConnection::createCommand}.
17 * The SQL statement to be executed may be set via {@link setText Text}.
19 * To execute a non-query SQL (such as insert, delete, update), call
20 * {@link execute}. To execute an SQL statement that returns result data set
21 * (such as select), use {@link query} or its convenient versions {@link queryRow}
22 * and {@link queryScalar}.
24 * If an SQL statement returns results (such as a SELECT SQL), the results
25 * can be accessed via the returned {@link TDbDataReader}.
27 * TDbCommand supports SQL statment preparation and parameter binding.
28 * Call {@link bindParameter} to bind a PHP variable to a parameter in SQL.
29 * Call {@link bindValue} to bind a value to an SQL parameter.
30 * When binding a parameter, the SQL statement is automatically prepared.
31 * You may also call {@link prepare} to explicitly prepare an SQL statement.
33 * @author Qiang Xue <qiang.xue@gmail.com>
34 * @package System.Data
37 class TDbCommand extends TComponent
41 private $_statement=null;
45 * @param TDbConnection the database connection
46 * @param string the SQL statement to be executed
48 public function __construct(TDbConnection $connection,$text)
50 $this->_connection=$connection;
51 $this->setText($text);
55 * Set the statement to null when serializing.
57 public function __sleep()
59 return array_diff(parent::__sleep(),array("\0TDbCommand\0_statement"));
63 * @return string the SQL statement to be executed
65 public function getText()
71 * Specifies the SQL statement to be executed.
72 * Any previous execution will be terminated or cancel.
73 * @param string the SQL statement to be executed
75 public function setText($value)
82 * @return TDbConnection the connection associated with this command
84 public function getConnection()
86 return $this->_connection;
90 * @return PDOStatement the underlying PDOStatement for this command
91 * It could be null if the statement is not prepared yet.
93 public function getPdoStatement()
95 return $this->_statement;
99 * Prepares the SQL statement to be executed.
100 * For complex SQL statement that is to be executed multiple times,
101 * this may improve performance.
102 * For SQL statement with binding parameters, this method is invoked
105 public function prepare()
107 if($this->_statement==null)
111 $this->_statement=$this->getConnection()->getPdoInstance()->prepare($this->getText());
115 throw new TDbException('dbcommand_prepare_failed',$e->getMessage(),$this->getText());
121 * Cancels the execution of the SQL statement.
123 public function cancel()
125 $this->_statement=null;
129 * Binds a parameter to the SQL statement to be executed.
130 * @param mixed Parameter identifier. For a prepared statement
131 * using named placeholders, this will be a parameter name of
132 * the form :name. For a prepared statement using question mark
133 * placeholders, this will be the 1-indexed position of the parameter.
134 * Unlike {@link bindValue}, the variable is bound as a reference and will
135 * only be evaluated at the time that {@link execute} or {@link query} is called.
136 * @param mixed Name of the PHP variable to bind to the SQL statement parameter
137 * @param int SQL data type of the parameter
138 * @param int length of the data type
139 * @see http://www.php.net/manual/en/function.PDOStatement-bindParam.php
141 public function bindParameter($name, &$value, $dataType=null, $length=null)
145 $this->_statement->bindParam($name,$value);
146 else if($length===null)
147 $this->_statement->bindParam($name,$value,$dataType);
149 $this->_statement->bindParam($name,$value,$dataType,$length);
153 * Binds a value to a parameter.
154 * @param mixed Parameter identifier. For a prepared statement
155 * using named placeholders, this will be a parameter name of
156 * the form :name. For a prepared statement using question mark
157 * placeholders, this will be the 1-indexed position of the parameter.
158 * @param mixed The value to bind to the parameter
159 * @param int SQL data type of the parameter
160 * @see http://www.php.net/manual/en/function.PDOStatement-bindValue.php
162 public function bindValue($name, $value, $dataType=null)
166 $this->_statement->bindValue($name,$value);
168 $this->_statement->bindValue($name,$value,$dataType);
172 * Executes the SQL statement.
173 * This method is meant only for executing non-query SQL statement.
174 * No result set will be returned.
175 * @return integer number of rows affected by the execution.
176 * @throws TDbException execution failed
178 public function execute()
182 // Do not trace because it will remain even in
183 // Performance mode or when pradolite.php is used
184 // Prado::trace('Execute Command: '.$this->getDebugStatementText(), 'System.Data');
185 if($this->_statement instanceof PDOStatement)
187 $this->_statement->execute();
188 return $this->_statement->rowCount();
191 return $this->getConnection()->getPdoInstance()->exec($this->getText());
195 throw new TDbException('dbcommand_execute_failed',$e->getMessage(),$this->getDebugStatementText());
200 * @return String prepared SQL text for debugging purposes.
202 public function getDebugStatementText()
204 //if(Prado::getApplication()->getMode() === TApplicationMode::Debug)
205 return $this->_statement instanceof PDOStatement ?
206 $this->_statement->queryString
211 * Executes the SQL statement and returns query result.
212 * This method is for executing an SQL query that returns result set.
213 * @return TDbDataReader the reader object for fetching the query result
214 * @throws TDbException execution failed
216 public function query()
220 // Prado::trace('Query: '.$this->getDebugStatementText(), 'System.Data');
221 if($this->_statement instanceof PDOStatement)
222 $this->_statement->execute();
224 $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText());
225 return new TDbDataReader($this);
229 throw new TDbException('dbcommand_query_failed',$e->getMessage(),$this->getDebugStatementText());
234 * Executes the SQL statement and returns the first row of the result.
235 * This is a convenient method of {@link query} when only the first row of data is needed.
236 * @param boolean whether the row should be returned as an associated array with
237 * column names as the keys or the array keys are column indexes (0-based).
238 * @return array the first row of the query result, false if no result.
239 * @throws TDbException execution failed
241 public function queryRow($fetchAssociative=true)
245 // Prado::trace('Query Row: '.$this->getDebugStatementText(), 'System.Data');
246 if($this->_statement instanceof PDOStatement)
247 $this->_statement->execute();
249 $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText());
250 $result=$this->_statement->fetch($fetchAssociative ? PDO::FETCH_ASSOC : PDO::FETCH_NUM);
251 $this->_statement->closeCursor();
256 throw new TDbException('dbcommand_query_failed',$e->getMessage(),$this->getDebugStatementText());
261 * Executes the SQL statement and returns the value of the first column in the first row of data.
262 * This is a convenient method of {@link query} when only a single scalar
263 * value is needed (e.g. obtaining the count of the records).
264 * @return mixed the value of the first column in the first row of the query result. False is returned if there is no value.
265 * @throws TDbException execution failed
267 public function queryScalar()
271 // Prado::trace('Query Scalar: '.$this->getDebugStatementText(), 'System.Data');
272 if($this->_statement instanceof PDOStatement)
273 $this->_statement->execute();
275 $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText());
276 $result=$this->_statement->fetchColumn();
277 $this->_statement->closeCursor();
278 if(is_resource($result) && get_resource_type($result)==='stream')
279 return stream_get_contents($result);
285 throw new TDbException('dbcommand_query_failed',$e->getMessage(),$this->getDebugStatementText());
290 * Executes the SQL statement and returns the first column of the result.
291 * This is a convenient method of {@link query} when only the first column of data is needed.
292 * Note, the column returned will contain the first element in each row of result.
293 * @return array the first column of the query result. Empty array if no result.
294 * @throws TDbException execution failed
297 public function queryColumn()
299 $rows=$this->query()->readAll();
301 foreach($rows as $row)
302 $column[]=current($row);