3

I'm using Cakephp 3 using sqlserver as datasource server. I am sure there's no problem with my database connection.. as home.ctp prompts that I am connected to my database.. and I'm as well using migrations plugin to create my tables.. it seems like there is no problem working with these tools. but after I bake my MVC, I only got page full of errors..

for example $bin\cake bake all tests

there are no errors I found and MVC are in its specific folder, testController.php, testTable, etc.

and in browsers localhost:8765\tests

but all I got is page of different errors.. Im seeing

Error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.

SELECT * FROM (SELECT Tests.id AS [Tests__id], Tests.desc AS [Tests__desc], (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] FROM tests Tests) _cake_paging_ WHERE _cake_paging_._cake_page_rownum_ <= :c0

and more errors on the left side.

I assume this is because of controllers with wrong queries or queries generated by bake is for mysql only. I just wanna know how to deal with this. is there a setting I forgot to do? please advice. I am new to Cakephp, and English is not my native language, sorry if I can't explain my question properly. thanks in advance.

ndm
  • 59,784
  • 9
  • 71
  • 110
xteroid
  • 57
  • 1
  • 9
  • and by the way im using sql server 2012 – xteroid Jan 09 '15 at 05:36
  • I do not know anything about cakephp, but the issue in the generated statement here is due to reserved keyword 'desc'. If the column name was 'desc1' it would work fine, or if you need to use a reserved keyword it should be enclosed in square braces as 'Tests.[desc]' in query. – Vishal Gajjar Jan 09 '15 at 06:27
  • thanks for your reply.. these codes and errors are generated by bake command of cakephp.. I need to know how to make mysql statements to be converted in mssql automatically as I bake my MVC. or I'm just missing a configuration or something. thanks anyway – xteroid Jan 09 '15 at 06:40

3 Answers3

13

As already mentioned by Vishal Gajjar in the comments, you are using the reserved keyword desc for your column name, hence the error, it's not bakes fault, it's yours.

In order to be able to use such reserved words, the column name needs to be quoted properly, however CakePHP 3 doesn't auto-quote by default anymore, as it's an expensive operation.

If you insist on using reserved words, enable identifier quoting via the quoteIdentifiers option in your app.php config, or enable it manually using the autoQuoting() (enableAutoQuoting() as of CakePHP 3.4) method of the DB driver.

See also

ndm
  • 59,784
  • 9
  • 71
  • 110
  • 1
    Sorry I can't vote this up. I don't have enough reputation to do so. and thank you for giving this detailed answer. I was a fool blaming cakephp bake, though it is a user error. thanks again. – xteroid Jan 09 '15 at 16:19
  • Thanks for this answer – SamHecquet May 05 '17 at 23:31
2

You can use this code before problematic query:

$this->Tests->connection()->driver()->autoQuoting(true);

and when you are finished you can turn auto quoting off:

$this->Tests->connection()->driver()->autoQuoting(false);

So bad performance would be only on problematic query.

TGD
  • 36
  • 5
-1

Use this :

SELECT * FROM (SELECT Tests.id AS [Tests__id], Tests.[desc] AS [Tests__desc], 
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] FROM tests Tests) _cake_paging_
WHERE _cake_paging_._cake_page_rownum_ <= :c0

If you do use a keyword, use it in square braces [ ]

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • I would say it's because the question is not about how to fix a raw SQL statement, but about how to fix the SQL that the CakePHP ORM generates. Also the need for proper quoting was already mentioned twice. – ndm Jan 09 '15 at 18:58
  • Gotcha mate! You rock \m/ – SouravA Jan 09 '15 at 19:46