This function will compile this query to make it compatible
with the SQL dialect that is used by the connection, This process might
add, remove or alter any query part or internal expression to make it
executable in the target platform.
The resulting query may have placeholders that will be replaced with the actual
values when the query is executed, hence it is most suitable to use with
prepared statements.
/**
* Prepares a sql statement to be executed
*
* @param string|\Cake\Database\Query $query The query to prepare.
* @return \Cake\Database\StatementInterface
*/
public function prepare($query)
{
$this->connect();
$options = [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL];
$isObject = $query instanceof Query;
if ($isObject && $query->bufferResults() === false) {
$options = [];
}
$statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
return new SqlserverStatement($statement, $this);
}
/**
* Prepares a sql statement to be executed
*
* @param string|\Cake\Database\Query $query The query to turn into a prepared statement.
* @return \Cake\Database\StatementInterface
*/
public function prepare($query)
{
$this->connect();
$isObject = $query instanceof Query;
$statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
return new PDOStatement($statement, $this);
}
/**
* Prepares a sql statement to be executed
*
* @param string|\Cake\Database\Query $query The query to prepare.
* @return \Cake\Database\StatementInterface
*/
public function prepare($query)
{
$this->connect();
$isObject = $query instanceof Query;
$statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
$result = new SqliteStatement(new PDOStatement($statement, $this), $this);
if ($isObject && $query->bufferResults() === false) {
$result->bufferResults(false);
}
return $result;
}
/**
* Prepares a sql statement to be executed
*
* @param string|\Cake\Database\Query $query The query to prepare.
* @return \Cake\Database\StatementInterface
*/
public function prepare($query)
{
$this->connect();
$options = [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY];
$isObject = $query instanceof Query;
if ($isObject && $query->bufferResults() === false) {
$options = [];
}
$statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
$result = new CustomSqlserverStatement(new SqlserverStatement($statement, $this), $this);
if ($isObject && $query->bufferResults() === false) {
$result->bufferResults(false);
}
return $result;
}
/**
* {@inheritDoc}
*/
public function sql(ValueBinder $binder = null)
{
$this->triggerBeforeFind();
$this->_transformQuery();
$sql = parent::sql($binder);
return $sql;
}
/**
* Tests that strings passed to isNull and isNotNull will be treated as identifiers
* when using autoQuoting
*
* @return void
*/
public function testIsNullAutoQuoting()
{
$this->connection->driver()->autoQuoting(true);
$query = new Query($this->connection);
$query->select('*')->from('things')->where(function ($exp) {
return $exp->isNull('field');
});
$this->assertQuotedQuery('WHERE \\(<field>\\) IS NULL', $query->sql());
$query = new Query($this->connection);
$query->select('*')->from('things')->where(function ($exp) {
return $exp->isNotNull('field');
});
$this->assertQuotedQuery('WHERE \\(<field>\\) IS NOT NULL', $query->sql());
}
/**
* Test that insert queries have results available to them.
*
* @return void
*/
public function testInsertUsesOutput()
{
$driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection'], [[]]);
$connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
$connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
$query = new \Cake\Database\Query($connection);
$query->insert(['title'])->into('articles')->values(['title' => 'A new article']);
$expected = 'INSERT INTO articles (title) OUTPUT INSERTED.* VALUES (:c0)';
$this->assertEquals($expected, $query->sql());
}
/**
* Test select with limit on lte SQLServer2008
*
* @return void
*/
public function testSelectLimitOldServer()
{
$driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection', '_version'], [['dsn' => 'foo']]);
$driver->expects($this->any())->method('_version')->will($this->returnValue(8));
$connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
$connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
$query = new \Cake\Database\Query($connection);
$query->select(['id', 'title'])->from('articles')->limit(10);
$expected = 'SELECT TOP 10 id, title FROM articles';
$this->assertEquals($expected, $query->sql());
$query = new \Cake\Database\Query($connection);
$query->select(['id', 'title'])->from('articles')->offset(10);
$expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
$this->assertEquals($expected, $query->sql());
$query = new \Cake\Database\Query($connection);
$query->select(['id', 'title'])->from('articles')->order(['id'])->offset(10);
$expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
$this->assertEquals($expected, $query->sql());
$query = new \Cake\Database\Query($connection);
$query->select(['id', 'title'])->from('articles')->order(['id'])->where(['title' => 'Something'])->limit(10)->offset(50);
$expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles WHERE title = :c0) AS _cake_paging_ ' . 'WHERE (_cake_paging_._cake_page_rownum_ > :c1 AND _cake_paging_._cake_page_rownum_ <= :c2)';
$this->assertEquals($expected, $query->sql());
}
/**
* {@inheritDoc}
*/
public function sql(ValueBinder $binder = null)
{
$this->_transformQuery();
return parent::sql($binder);
}
/**
* Create a NodeStatement from query and returns it
*
* @param Query $query
* @return NodeStatement
*/
public function prepare($query)
{
$this->connect();
$isObject = $query instanceof Query;
return new NodeStatement($isObject ? $query->sql() : $query, $this);
}
/**
* Test that INSERT INTO ... SELECT works.
*
* @return void
*/
public function testInsertFromSelect()
{
$select = (new Query($this->connection))->select(['name', "'some text'", 99])->from('authors')->where(['id' => 1]);
$query = new Query($this->connection);
$query->insert(['title', 'body', 'author_id'], ['title' => 'string', 'body' => 'string', 'author_id' => 'integer'])->into('articles')->values($select);
$result = $query->sql();
$this->assertQuotedQuery('INSERT INTO <articles> \\(<title>, <body>, <author_id>\\) SELECT', $result, true);
$this->assertQuotedQuery('SELECT <name>, \'some text\', 99 FROM <authors>', $result, true);
$result = $query->execute();
$this->assertCount(1, $result);
$result = (new Query($this->connection))->select('*')->from('articles')->where(['author_id' => 99])->execute();
$this->assertCount(1, $result);
$expected = ['id' => 4, 'title' => 'mariano', 'body' => 'some text', 'author_id' => 99, 'published' => 'N'];
$this->assertEquals($expected, $result->fetch('assoc'));
}
/**
* Prepares a sql statement to be executed
*
* @param string|\Cake\Database\Query $query The query to convert into a statement.
* @return \Cake\Database\StatementInterface
*/
public function prepare($query)
{
$this->connect();
$isObject = $query instanceof \Cake\ORM\Query || $query instanceof \Cake\Database\Query;
$queryStringRaw = $isObject ? $query->sql() : $query;
Log::write('debug', $queryStringRaw);
// debug($queryStringRaw);
$queryString = $this->_fromDualIfy($queryStringRaw);
list($queryString, $paramMap) = self::convertPositionalToNamedPlaceholders($queryString);
$innerStatement = $this->_connection->prepare($queryString);
$statement = $this->_wrapStatement($innerStatement);
$statement->queryString = $queryStringRaw;
$statement->paramMap = $paramMap;
$disableBuffer = false;
$normalizedQuery = substr(strtolower(trim($queryString, " \t\n\r\v(")), 0, 6);
if ($normalizedQuery !== 'select') {
$disableBuffer = true;
}
if ($isObject && $query->bufferResults() === false || $disableBuffer) {
$statement->bufferResults(false);
}
return $statement;
}