levanth levanth - 2 years ago 473
PHP Question

Using MS SQL with Symfony 3

Hi there I need some help with Symfony 3 and a MS SQL Server:

I am using Symfony 3 and want to connect to a MS SQL Server, I've done some research and found out that it is not supported by default but there are some Bundles available to use.

After trying some of them I found one Bundle which partially worked for me (realestateconz/mssql-bundle) but I get an error Message everytime I try to query the DB (I formatted it to be more readable):

Uncaught PHP Exception Doctrine\DBAL\DBALException: An exception occurred while executing
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 codeline looks like this

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

My doctrine and symfony settings looks like this:

driver_class: Realestate\MssqlBundle\Driver\PDODblib\Driver
host: "%database_host%"
#port: "%database_port%"
dbname: "%database_name%"
user: "%database_user%"
password: "%database_password%"
#charset: UTF8

database_host: myserver
database_name: database #without schema
database_user: user
database_password: pw

Port and Charset in config.yml are commented out because I found something here on stackoverflow about doctrine using MySQL when port and charset are defined (can't remember where exactly though).
In the parameters.yml I can not add the schema for the DB (full path to table "User" is database.web.[User]) because it will run in another Error.

The Webserver runs Ubuntu 16.04 with apache2, php5.6 (including the package php5-sybase) and I use freetds to connect to the MSSQL-Server (manually connecting works).

Here are my FreeTDS settings:

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

As far as I understood it, Doctrine generates the wrong SQL-Query from the single line of code (MySQL-Syntax instead of MSSQL-Syntax), so what can I do to fix this? Or is there another way of successfully connecting and querying a MSSQL-Server on Linux with Symfony 3?

Answer Source

I found the problem myself, you need to give Doctrine the exact Table name for your Entities in the Syntax the SQL-Server uses.

For the annotation Format Symfony uses it would look like this:

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

After Changing the Tablename to MSSQL Format everything worked as expected.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download