I am working on a Symfony 4 project with a MSSQL server (not optimal no, but corporate decision).
We are experiencing an issue when we use named variables.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ':'.
ex:
$query->where("status = :status");
we apply the values with setParameter()
$query->setParameter('status', $status);
We receive a syntax error. I have attempted to store quotes with the variable itself to make sure that is not the case, however it returns the same error. If I replace the named variables with ex: 'ACTIVE' it works as expected. I have also printed the sql with getSQL() and tested this directly in SQL Management Studio, this works fine also.
Debugging the statement is rough, and I am considering if we need to make a connection wrapper class and manipulate in order to support MSSQL, but before going to that extend, maybe some of you guys have an idea?
Full error response:
An exception occurred while executing 'SELECT U.CIID AS ciid, U.USERID as userid, U.FIRSTNAME as firstname, U.MIDDLENAME as middlename, U.LASTNAME as lastname, ACI.emailid as email, U.JOBTITLE as position, ACI.landline as phone_local, ACI.mobile as phone_mobile, P.ATTRIBUTE_1801 as phone_cisco FROM SDUSER U INNER JOIN AaaUser AU ON U.USERID=AU.USER_ID INNER JOIN People P ON U.CIID=P.CIID INNER JOIN AaaUserContactInfo AUCI ON AU.USER_ID=AUCI.USER_ID INNER JOIN AaaContactInfo ACI ON AUCI.CONTACTINFO_ID=ACI.CONTACTINFO_ID WHERE U.STATUS=:status':
SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ':'.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (500 Internal Server Error)
As you can see, it looks like the named parameters is forcing a wrap of quotes of the entire sql that mssql cannot accept.
MSSQL 2012 PHP 7.1.3 Symfony 4 Doctrine 2
