[DBAL-1058] It seems that MSSQL syntax was changed Created: 05/Dec/14  Updated: 05/Dec/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4, 2.5
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: man4red Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, sqlserver

Issue Links:
Reference
is referenced by DBAL-1060 [GH-736] [DBAL-1058] Fix database and... Open
is referenced by DBAL-1061 [GH-737] [DBAL-1058] [2.4] Fix databa... Open

 Description   

I'm using dblib, MSSQL (2012).
So, problem is here:

doctrine-module orm:schema-tool:update --dump-sql

Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 20018 Invalid object name 'SYS.SCHEMAS'. [20018] (severity 16) [SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')] in /var/www/domains/internal.dc.hayas.ru/data/partners.zf2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php on line 106

So it seems, that problems is here:

Doctrine\DBAL\Platforms\SQLServerPlatform.php
At Line 1036

    public function getListNamespacesSQL()
    {
        return "SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
    }

SQL Server >= 2005 uses sys.schemas (lowercase)

Maybe need to add to SQLServer2005Platform.php

SELECT name FROM sys.schemas ...

and also at line 1028 SQLServerPlatform.php

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM SYS.DATABASES';
    }

add to SQLServer2005Platform.php

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM sys.databases';
    }


 Comments   
Comment by Steve Müller [ 05/Dec/14 ]

man4red thanks for reporting. I'll have a look at it this evening. Weird that the functional tests pass though in my setup :S

Comment by Marco Pivetta [ 05/Dec/14 ]

Steve Müller please note that he is using dblib, which (afaik) we do not officially support.

Comment by man4red [ 05/Dec/14 ]

I've checked by direct query to SQL via SQL Management Studio.
Got multiple servers with a diffirent versions.

Here some test

QUERY:

 SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys') 

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) FAIL
11.0.5058 (SQL Server 2012) FAIL

QUERY:

SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

I've tested on 5 servers 11.0.5058 (SQL Server 2012).
QUERY:

SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')

Failed on each of them

Other tests:

QUERY:

SELECT * FROM SYS.DATABASES

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

QUERY:

SELECT * FROM sys.databases

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

by the way - is it neccessary to query * from SYS.DATABASES ?

Doctrine\DBAL\Platforms\SQLServerPlatform.php

Line 1030

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM SYS.DATABASES';
    }

Maybe need to query only names? (name field)
Just asking

Comment by man4red [ 05/Dec/14 ]

According to tests I've added next code to SQLServer2008Platform.php

    /**
     * {@inheritDoc}
     */
    public function getListNamespacesSQL()
    {
        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
    }

And modified my ZF2 application doctrine config config/autoload/doctrine.local.php (platform added):

return array(
    'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'driverClass' => 'class to work with dblib',
                'params' => array(
                    'host' => 'hostname',
                    'port' => 1433,
                    'user' => 'user',
                    'password' => 'pass',
                    'dbname' => 'database',
                    'platform' => new Doctrine\DBAL\Platforms\SQLServer2012Platform()
                )
            )
        )
    )
);

Now I've got no issues with MSSQL 2012
I hope my fix was correct

Comment by Steve Müller [ 05/Dec/14 ]

Patch provided: https://github.com/doctrine/dbal/pull/736

Comment by Doctrine Bot [ 05/Dec/14 ]

A related Github Pull-Request [GH-736] was assigned:
https://github.com/doctrine/dbal/pull/736

Comment by man4red [ 05/Dec/14 ]

Dear friends,

I'm new here, and I don't know how all this works here, but can you help me?
As always when one bug fixed - another two produced

Now I've got another problem.
ZendDeveloperTool throws Exception

Uncaught exception 'PDOException' with message 'You cannot serialize or unserialize PDO instances'

of course because of my

    'platform' => new Doctrine\DBAL\Platforms\SQLServer2012Platform()

ok... my mistake

let's fix it in ZF2 way

    'platform' => 'Doctrine\DBAL\Platforms\SQLServer2012Platform'

Now we got another exception:

Doctrine\DBAL\DBALException: Invalid 'platform' option specified, need to give an instance of \Doctrine\DBAL\Platforms\AbstractPlatform.

let's look to doctrine\dbal\lib\Doctrine\DBAL\Connection.php Line: 387

    private function detectDatabasePlatform()
    {
        ...
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } else {
            throw DBALException::invalidPlatformSpecified();
        }
        ...
    }

So my question is

Can we implemet a feature and change this

    private function detectDatabasePlatform()
    {
        if ( ! isset($this->_params['platform'])) {
            $version = $this->getDatabasePlatformVersion();

            if (null !== $version) {
                $this->platform = $this->_driver->createDatabasePlatformForVersion($version);
            } else {
                $this->platform = $this->_driver->getDatabasePlatform();
            }
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } else {
            throw DBALException::invalidPlatformSpecified();
        }

        $this->platform->setEventManager($this->_eventManager);
    }

to this (or similar)

    private function detectDatabasePlatform()
    {
        if (! isset($this->_params['platform'])) {
            $version = $this->getDatabasePlatformVersion();

            if (null !== $version) {
                $this->platform = $this->_driver->createDatabasePlatformForVersion($version);
            } else {
                $this->platform = $this->_driver->getDatabasePlatform();
            }
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } elseif (is_subclass_of($this->_params['platform'], 'Doctrine\DBAL\Platforms\AbstractPlatform')) {
            $this->platform = new $this->_params['platform']();
        } else {
            throw DBALException::invalidPlatformSpecified();
        }

        $this->platform->setEventManager($this->_eventManager);
    }

or this problem is only mine and I need to fix it by my self and to write some forks/mods etc?

Thx for your help anyway

Comment by Marco Pivetta [ 05/Dec/14 ]

man4red that seems to be related with DBAL-1057 - I'll mark this issue as resolved.

Comment by man4red [ 05/Dec/14 ]

Marco Pivetta, thx! Is there planned some big reworking of this section, am I right?
Am I need to post my last comment to DBAL-1057 thread?

Comment by Marco Pivetta [ 05/Dec/14 ]

man4red this section needs some work for 2.5.1, yes. As for posting to DBAL-1057, please do, but only the bits that may be relevant and that you feel that add up to the discussion without cluttering it.





Generated at Sat Dec 20 21:37:01 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.