是否可以使用CakePHP与Oracle作为数据库?

Question

我试图从MySQL切换到cakephp4项目上的Oracle数据库，这似乎是不可能的。我使用这个驱动CakePHP Oracle驱动程序，我安装了instantclient19.6, oci8-2.2.0(for php7.4.33)和pdo_oci，因为它是在存储库上解释的。

我只能使工作的查询是手动构建像这样:

$sql = $this->oracleConnection
->newQuery()
->select('USERS.LOCKED, USERS.LOCK_TYPE')
->from('USERS')
->where('USERS.USERNAME = '' . $username . '' AND ROWNUM = 1')
->order([]);

但是CakePHP的默认方法如find()和get()会抛出如下错误:ORA-00911: invalid character

给出错误的人:

$blockedUser = $this->blockedUser->find('all', [
'conditions' => [
'BlockedUsers.blocked_until >' => FrozenTime::now(),
'BlockedUsers.username' => $username,
],
])->first();

以及它发送并得到错误的查询:

SELECT *
FROM (
SELECT cake_paging.*, (ROWNUM) AS _cake_page_rownum_
FROM (
SELECT BlockedUsers.ID AS BlockedUsers__ID,
BlockedUsers.USERNAME AS BlockedUsers__USERNAME,
BlockedUsers.OS AS BlockedUsers__OS,
BlockedUsers.USER_AGENT AS BlockedUsers__USER_AGENT, 
BlockedUsers.CREATED AS BlockedUsers__CREATED,
BlockedUsers.BLOCKED_UNTIL AS BlockedUsers__BLOCKED_UNTIL
FROM   blocked_users BlockedUsers
WHERE (BlockedUsers.blocked_until > :c0 AND BlockedUsers.username = :c1)
) cake_paging
) cake_paging_out
WHERE cake_paging_out."_cake_page_rownum_" <= 1

和BLOCKED_USERS表:

BLOCKED_USERS

这里也是我从app。php连接的:

'default' => [
'className' => 'CakeDCOracleDriverDatabaseOracleConnection',
'driver' => 'CakeDCOracleDriverDatabaseDriverOracleOCI',
'persistent' => false,
'host' => 'myhost',
'port' => 'myport',
'username' => 'myusername',
'password' => 'mypassword',
'database' => 'myDB',
'sid' => 'mySID',
'encoding' => 'WE8ISO8859P1',
'timezone' => '+1:00',
'flags' => [],
'cacheMetadata' => true,
'log' => false,
'quoteIdentifiers' => false,
'server_version' => 10,
'url' => env('DATABASE_URL', null),
],

我已经尝试在app.php中测试多种类型的连接，卸载和安装php和扩展。这一切，我问自己，如果我做错了什么，或者这是不可能的?

Answer 1

错误产生于:

(ROWNUM) AS _cake_page_rownum_

应该是:

(ROWNUM) AS "_cake_page_rownum_"

as_不是无引号标识符的有效起始字符。

orm通常会在它们生成的代码中使用带引号的标识符;你的代码(除了一个例外)没有使用任何看起来很奇怪的带引号的标识符。