Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Some SQL queries are not supported in MSSQL #10504

Open
MilanRaval opened this issue Aug 21, 2024 · 1 comment
Open

Some SQL queries are not supported in MSSQL #10504

MilanRaval opened this issue Aug 21, 2024 · 1 comment
Labels
Area: Databases Issues & PRs related to all things regarding databases Priority:Important Issues & PRs that are important; broken functions, errors - there are workarounds Severity: Moderate Key function failed, but no or little impact Type: Bug Bugs within the core SuiteCRM codebase

Comments

@MilanRaval
Copy link

Issue

Some SQL queries are not supported in MSSQL. Noticed below issues so far...

  • Backticks(`)
  • LIMIT 1 is not supported in MSSQL
  • Method "EXTRACT(MONTH FROM" is not supported in MSSQL
  • Method 'YEAR(YEAR FROM is not supported in MSSQL

Possible Fix

As a work around fixed the issues by modifying function "query" in MssqlManager.php and SqlsrvManager.php to replace the keywords with keywords supported in MSSQL, and replace backticks(`) with empty string

$sql = str_replace("`", "", $sql);
$sql = str_replace('LIMIT 1', 'TOP 1', $sql);
$sql = str_replace('EXTRACT(MONTH FROM', 'MONTH(', $sql);
$sql = str_replace('YEAR(YEAR FROM', 'YEAR(', $sql);

image

Steps to Reproduce the Issue

When SuiteCRM is configured to use MSSQL as a database.

Context

Upgrade SuiteCRM from 8.2.1 to 8.6.1

Version

8.6.1

What browser are you currently using?

Chrome

Browser Version

Version 127.0.6533.120 (Official Build) (64-bit)

Environment Information

PHP 8.2

Operating System and Version

Windows

@MilanRaval MilanRaval added the Type: Bug Bugs within the core SuiteCRM codebase label Aug 21, 2024
@chris001
Copy link
Contributor

Nice fix. This needs a pull request.

@johnM2401 johnM2401 added Priority:Important Issues & PRs that are important; broken functions, errors - there are workarounds Area: Databases Issues & PRs related to all things regarding databases Severity: Moderate Key function failed, but no or little impact labels Aug 22, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Area: Databases Issues & PRs related to all things regarding databases Priority:Important Issues & PRs that are important; broken functions, errors - there are workarounds Severity: Moderate Key function failed, but no or little impact Type: Bug Bugs within the core SuiteCRM codebase
Projects
None yet
Development

No branches or pull requests

3 participants