Using MS SQL with Symfony 3

Question

Hi, I need help with Symfony 3 and MS SQL Server:

I use Symfony 3 and want to connect to MS SQL Server, I did some research and found out that it is not supported by default, but there are several packages available.

After trying some of them, I found one Bundle that partially worked for me (realestateconz / mssql-bundle), but I get an error message every time I try to query the database (I formatted it to be more readable):

Uncaught PHP Exception Doctrine\DBAL\DBALException: An exception occurred while executing 'SELECT t0.id AS id_1, t0.username AS username_2 FROM user t0 WHERE t0.username = ?' with params ["testusername"]: SQLSTATE[HY000]: General error: 156 General SQL Server error: Check messages from the SQL Server [156] (severity 15) [(null)] 

The code looks like this:

 $user = $em->getRepository('AppBundle:User')->findBy(array('username' => $username)); 

The settings of my doctrine and symfony are as follows:

 #config.yml doctrine: dbal: driver_class: Realestate\MssqlBundle\Driver\PDODblib\Driver host: "%database_host%" #port: "%database_port%" dbname: "%database_name%" user: "%database_user%" password: "%database_password%" #charset: UTF8 #parameters.yml parameters: database_host: myserver database_name: database #without schema database_user: user database_password: pw 

The port and Charset in config.yml are commented out because I found something here in stackoverflow about the doctrine using MySQL when the port and encoding are defined (I don’t remember exactly where it was). In the .yml parameters I can’t add a schema for the database (the full path to the User table is database.web. [User]), because it will work in a different error.

The web server starts Ubuntu 16.04 with apache2, php5.6 (including the php5-sybase package), and I use freetds to connect to the MSSQL server (manual connection work).

Here are my FreeTDS settings:

 [MYSERVER] host = myserveradress port = 1433 tds version = 8.0 client charset = UTF-8 text size = 20971520 

As I understand it, Doctrine generates the wrong SQL query from a single line of code (MySQL syntax instead of MSSQL syntax), so what can I do to fix this? Or is there another way to successfully connect and query MSSQL-Server on Linux with Symfony 3?

Answer 1

I found the solution myself, you need to give Doctrine the exact table name for your objects in the syntax that the SQL server uses.

For Format Symfony annotation format, it will look like this:

 /** Annotation for your Classfile * ExampleClass * * @ORM\Table(name="[ExampleClass]") <-- Square Brackets for MSSQL * @ORM\Entity */ 

After changing the table name in the MSSQL format, everything worked as expected.