/** * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // localize variables foreach (get_object_vars($this->select) as $name => $value) { $this->{$name} = $value; } parent::prepareStatement($adapter, $statementContainer); }
/** * @covers Zend\Db\Sql\Select::prepareStatement */ public function testPrepareStatement() { $mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface'); $mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver)); $mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface'); $mockStatement->expects($this->at(0))->method('setSql')->with($this->equalTo('SELECT "bee", "baz", "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n"')); $mockStatement->expects($this->at(3))->method('setSql')->with($this->equalTo(' WHERE x = y')); $this->select->from('foo')->columns(array('bee', 'baz'))->join('zac', 'm = n')->where('x = y'); $this->select->prepareStatement($mockAdapter, $mockStatement); }
/** * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // localize variables foreach (get_object_vars($this->select) as $name => $value) { $this->{$name} = $value; } if ($this->limit === null && $this->offset !== null) { $this->specifications[self::LIMIT] = 'LIMIT 18446744073709551615'; } parent::prepareStatement($adapter, $statementContainer); }
/** * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // localize variables foreach (get_object_vars($this->select) as $name => $value) { $this->{$name} = $value; } // set specifications unset($this->specifications[self::LIMIT]); unset($this->specifications[self::OFFSET]); $this->specifications['LIMITOFFSET'] = null; parent::prepareStatement($adapter, $statementContainer); }
public function fetchAll() { $select = new Select(); $select->from('person')->columns(array('f_name' => 'person.firstname', 'l_name' => 'person.lastname', 'comp_name' => 'contact.companyname', 'cont_name' => 'contactstate.name'))->join('contact', 'contactstate', 'person.id = contact.person_id', 'contact.state_contactstate_id = contactstate.id'); $statement = $dbAdapter->createStatement(); $select->prepareStatement($dbAdapter, $statement); $driverResult = $statment->execute(); $resultset = new ResultSet(); $resultset->setDataSource($driverResult); foreach ($resultset as $row) { // $row is an ArrayObject } }
/** * Prepare statement * * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer * @return void */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { $driver = $adapter->getDriver(); $platform = $adapter->getPlatform(); $parameterContainer = $statementContainer->getParameterContainer(); if (!$parameterContainer instanceof ParameterContainer) { $parameterContainer = new ParameterContainer(); $statementContainer->setParameterContainer($parameterContainer); } $table = $this->table; $schema = null; // create quoted table name to use in insert processing if ($table instanceof TableIdentifier) { list($table, $schema) = $table->getTableAndSchema(); } $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $columns = array(); $values = array(); if (is_array($this->values)) { foreach ($this->columns as $cIndex => $column) { $columns[$cIndex] = $platform->quoteIdentifier($column); if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) { $exprData = $this->processExpression($this->values[$cIndex], $platform, $driver); $values[$cIndex] = $exprData->getSql(); $parameterContainer->merge($exprData->getParameterContainer()); } else { $values[$cIndex] = $driver->formatParameterName($column); if (isset($this->values[$cIndex])) { $parameterContainer->offsetSet($column, $this->values[$cIndex]); } else { $parameterContainer->offsetSet($column, null); } } } $sql = sprintf($this->specifications[static::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values)); } elseif ($this->values instanceof Select) { $this->values->prepareStatement($adapter, $statementContainer); $columns = array_map(array($platform, 'quoteIdentifier'), $this->columns); $columns = implode(', ', $columns); $sql = sprintf($this->specifications[static::SPECIFICATION_SELECT], $table, $columns ? "({$columns})" : "", $statementContainer->getSql()); } else { throw new Exception\InvalidArgumentException('values or select should be present'); } $statementContainer->setSql($sql); }
/** * @param Sql\Select $select * @return null|ResultSet * @throws \RuntimeException */ public function selectWith(Sql\Select $select) { $selectState = $select->getRawState(); if ($selectState['table'] != $this->tableName || $selectState['schema'] != $this->schema) { throw new Exception\RuntimeException('The table name and schema of the provided select object must match that of the table'); } if ($selectState['join'] != array()) { throw new Exception\RuntimeException('The Select object provided to TableRowGateway cannot include join statements.'); } $statement = $this->adapter->createStatement(); $select->prepareStatement($this->adapter, $statement); $result = $statement->execute(); $resultSet = clone $this->selectResultPrototype; $resultSet->setDataSource($result); return $resultSet; }
/** * @param AdapterInterface $adapter * @param StatementContainerInterface $statementContainer */ public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer) { // localize variables foreach (get_object_vars($this->select) as $name => $value) { $this->{$name} = $value; } // set specifications unset($this->specifications[self::LIMIT]); unset($this->specifications[self::OFFSET]); $this->specifications['LIMITOFFSET'] = null; parent::prepareStatement($adapter, $statementContainer); //set statement cursor type if ($statementContainer instanceof Statement) { $statementContainer->setPrepareOptions(array('Scrollable' => \SQLSRV_CURSOR_STATIC)); } }
/** * * @param Select $select * @param $entityPrototype */ public function selectSingle(Select $select, $entityPrototype = null, HydratorInterface $hydrator = null) { $adapter = $this->getDbAdapter(); $statement = $adapter->createStatement(); $select->prepareStatement($adapter, $statement); $result = $statement->execute(); if (!$hydrator) { $hydrator = $this->getHydrator(); } if (!$entityPrototype) { $entityPrototype = $this->getEntityPrototype(); } $current = $result->current(); if (!is_array($current)) { return null; } return $hydrator->hydrate($current, $entityPrototype); }
public function selectWith(Select $select, $scrollable = true) { // Get the data $adapter = $this->getReadAdapter(); $statement = $adapter->createStatement(); $select->prepareStatement($adapter, $statement); $result = $statement->execute(); if ($scrollable) { // Convert data to an array so we can iterate more than once $resultArray = array(); foreach ($result as $row) { $resultArray[] = $this->toScalarValueArray($row); } $result = $resultArray; } // Create the ResultSet $resultSet = new ResultSet(); $resultSet->setRowObjectPrototype($this->getModelPrototype()); $resultSet->setDataSource($result); return $resultSet; }
protected function processSubSelect(Select $subselect, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null) { if ($driver) { $stmtContainer = new StatementContainer(); // Track subselect prefix and count for parameters $this->processInfo['subselectCount']++; $subselect->processInfo['subselectCount'] = $this->processInfo['subselectCount']; $subselect->processInfo['paramPrefix'] = 'subselect' . $subselect->processInfo['subselectCount']; // call subselect $subselect->prepareStatement(new \Zend\Db\Adapter\Adapter($driver, $platform), $stmtContainer); // copy count $this->processInfo['subselectCount'] = $subselect->processInfo['subselectCount']; $parameterContainer->merge($stmtContainer->getParameterContainer()->getNamedArray()); $sql = $stmtContainer->getSql(); } else { $sql = $subselect->getSqlString($platform); } return $sql; }
protected function _authenticateQuerySelect(Select $dbSelect) { $statement = $this->zendDb->createStatement(); $dbSelect->prepareStatement($this->zendDb, $statement); $resultSet = new ResultSet(); try { $resultSet->initialize($statement->execute(array($this->identity))); $resultIdentities = $resultSet->toArray(); } catch (\Exception $e) { throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e); } return $resultIdentities; }
/** * @testdox unit test: Test prepareStatement() will produce expected sql and parameters based on a variety of provided arguments [uses data provider] * @covers Zend\Db\Sql\Select::prepareStatement * @dataProvider providerData */ public function testPrepareStatement(Select $select, $expectedSqlString, $expectedParameters, $unused1, $unused2, $useNamedParameters = false) { $mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface'); $mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnCallback(function ($name) use($useNamedParameters) { return $useNamedParameters ? ':' . $name : '?'; })); $mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver)); $parameterContainer = new ParameterContainer(); $mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface'); $mockStatement->expects($this->any())->method('getParameterContainer')->will($this->returnValue($parameterContainer)); $mockStatement->expects($this->any())->method('setSql')->with($this->equalTo($expectedSqlString)); $select->prepareStatement($mockAdapter, $mockStatement); if ($expectedParameters) { $this->assertEquals($expectedParameters, $parameterContainer->getNamedArray()); } }
public function dailyGrowth() { $subSelect = new \Zend\Db\Sql\Select(); $subSelect->from('resource')->columns(array('id', 'created_date')); $subSelect->where->notEqualTo('node_status', \Application\Model\Resource::NODE_STATUS_DISABLED); $select = new \Zend\Db\Sql\Select(); $select->from('calendar_table')->columns(array('*', new Expression('COUNT(resource.id) AS total'), new Predicate\Expression('YEAR(calendar_table.calendar_date) AS year'), new Predicate\Expression('MONTH(calendar_table.calendar_date) AS month'), new Predicate\Expression('MONTHNAME(calendar_table.calendar_date) AS monthname'), new Predicate\Expression('DAY(calendar_table.calendar_date) AS day'))); $select->join(array('resource' => $subSelect), 'resource.created_date = calendar_table.calendar_date', array(), Select::JOIN_LEFT); $select->group('calendar_date'); $statement = $this->getAdapter()->createStatement(); $select->prepareStatement($this->getAdapter(), $statement); $resultset = new ResultSet(); return iterator_to_array($resultset->initialize($statement->execute())); }
<?php /** @var $adapter Zend\Db\Adapter\Adapter */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); use Zend\Db\Sql\Select, Zend\Db\ResultSet\ResultSet; $select = new Select(); $select->from('artist')->columns(array())->join('album', 'artist.id = album.artist_id', array('title', 'release_date'))->order(array('release_date', 'title'))->where->like('artist.name', '%Brit%'); $statement = $adapter->createStatement(); $select->prepareStatement($adapter, $statement); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); $albums = array(); foreach ($resultSet as $row) { $albums[] = $row->title . ' released on: ' . date('Y-m-d', strtotime($row->release_date)); } assert_example_works($albums == array(0 => '...Baby One More Time released on: 1999-02-14', 1 => 'Oops!... I Did It Again released on: 2000-10-10', 2 => 'Britney released on: 2001-04-06', 3 => 'Blackout released on: 2007-10-10', 4 => 'Circus released on: 2008-11-23', 5 => 'Femme Fatale released on: 2011-10-10', 6 => 'In the Zone released on: 2011-10-10'));
/** * @testdox unit test: Test prepareStatement() will produce expected sql and parameters based on a variety of provided arguments [uses data provider] * @covers Zend\Db\Sql\Select::prepareStatement * @dataProvider providerData */ public function testPrepareStatement(Select $select, $expectedSqlString, $expectedParameters) { $mockDriver = $this->getMock('Zend\Db\Adapter\Driver\DriverInterface'); $mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnValue('?')); $mockAdapter = $this->getMock('Zend\Db\Adapter\Adapter', null, array($mockDriver)); $parameterContainer = new ParameterContainer(); $mockStatement = $this->getMock('Zend\Db\Adapter\Driver\StatementInterface'); $mockStatement->expects($this->any())->method('getParameterContainer')->will($this->returnValue($parameterContainer)); $mockStatement->expects($this->any())->method('setSql')->with($this->equalTo($expectedSqlString)); $select->prepareStatement($mockAdapter, $mockStatement); if ($expectedParameters) { $this->assertEquals($expectedParameters, $parameterContainer->getNamedArray()); } }