Using Sqlserver "WITH" clause in CakePhp2

[Alibert-07]

I use the Sqlserver "WITH common_table_expression" expression in CakePhp 2.

In Sqlserver.php in the Method "_execute" the Code tests for "SELECT" or "EXEC" to determine if an SQL Statement is valid for Execution.

cakephp2-php8/lib/Cake/Model/Datasource/Database/Sqlserver.php

Lines 798 to 802 in ce3e0c3

	$sql = trim($sql);
	if (strncasecmp($sql, 'SELECT', 6) === 0 || preg_match('/^EXEC(?:UTE)?\s/mi', $sql) > 0) {
	$prepareOptions += array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL);
	return parent::_execute($sql, $params, $prepareOptions);
	}

May it be possible to change the Code in Line 799 to
if ((strncasecmp($sql, 'SELECT', 6) === 0) || (strncasecmp($sql, 'WITH', 4) === 0) || preg_match('/^EXEC(?:UTE)?\s/mi', $sql) > 0) {

so the WITH clause can be used out of the box.

Sorry if the is not the correct way to make suggestions, but i am relatively new to GitHub. ;-)

[kamilwylegala]

Hey @Alibert-07 Thanks for creating the issue!

My intention of this fork is to just support PHP8.* and provide necessary fixes. I didn't plan to add new features to the framework. Unfortunately I have limited amount of time I can spend working on this work.

Could you try running raw query as a workaround? E.g.:

$this->AppModel->query("SELECT * WHERE id = ?", [$id]);

I think first param should accept also a table expression query.

[Alibert-07]

Hi!
I know the intention of your fork and i am very thankful. It was just because i am using "with" for same time and thought it would be nice to have it in the fork. Don't bother any further.

I am quite new to github. I have to use cakephp 2 for sometime in the future because of massive amount of code, ported from 1.1, 1.2, 1.3 to 2 but didn't have time and resources to port it to 3 and higher.

I am glad, if i can help maintain the fork. Should i fork the project and use pull requests to suggest updates?
Thanks for your reply
greetings Harald

[kamilwylegala]

@Alibert-07 Yes, please fork the repository and create PR if you have some spare time. I will really appreciate it. 😊