CODEWITHSUNDEEP
phpsql-server-2008doctrine-ormsymfony
user4010555
user4010555

Reputation:

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 
'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 codeline looks like this

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

My doctrine and symfony settings looks like this:

#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

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:

[MYSERVER]
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?

Upvotes: 6

Views: 5409

Answers (1)

user4010555
user4010555

Reputation:

I found the solution 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.

Upvotes: 3

Related Questions