PHPMaker v2023.6.I got the following error message for the field that uses Lookup Table to the Linked Table in MS SQL Server database. The error is triggered when end-users are trying to search the data by typing keyword, for example: sumbawa, from Modal Dialog of that Lookup Table. The error happened also if I disabled Use modal dialog for lookup option and enabled Requires search option under Edit Tag pane.
Lookup Table settings:
Table name: masteroffices
Link field: Office_Code
Display field #1: Office_Name
Display field #2: Office_Code
Please note that both database (main database and Linked Table are using SQL Server database). In this case, the masteroffices is a Linked Table to SQL Server database.
D:\wamp\www\xxx\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php(185): An exception occurred while executing 'SELECT COUNT(*) FROM [dbo].[masteroffices] WHERE CONCAT([Office_Name],', ',[Office_Code]) LIKE N'%sumbawa%'':
An exception occurred while executing 'SELECT COUNT(*) FROM [dbo].[masteroffices] WHERE CONCAT([Office_Name],', ',[Office_Code]) LIKE N'%sumbawa%'':
SQLSTATE [42000, 195]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'CONCAT' is not a recognized built-in function name.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
No. My client cannot use SQL Server 2012 or newer. They only have license for SQL Server 2008, and they cannot upgrade it to v2012.My client really wanted to use PHPMaker 2023. I have been evaluating PHPMaker v2023 so that it can use database SQL Server 2008, and it seems it is working. It can connect to database properly.So, here are the next steps that I have already done.
Since from the documentation I read, DBAL v3 does not support SQL Server 2008 database anymore, so I downgraded the dbal version in composer.json template file, from:
“doctrine/dbal”: “^3.4.4”,to:
“doctrine/dbal”: “^2.12.1”,I re-generated all the script files, I saw from the generated log while system was generating the script files, dbal used version 2.13.9 (since it is the latest minor version for v2), and I think that’s fine. All the script files are generated properly without any error.Then I tested the generated web application via browser, both for localhost server and production server, and both are working properly, too. I can see data displayed properly in List, View, Edit pages properly. I tested the functionality of Add, Edit, and Delete, and they are working properly, too.The problem is only when I was trying to search data in Lookup Table as I explained in my first post above. I know, I read from this article that there are new functions in SQL Server 12, which is one of them is CONCAT function. And of course, this CONCAT function does not support for SQL Server 2008 database.Do you have any solution for this condition?