DBAL-1058: It seems that MSSQL syntax was changed

[doctrinebot]

Jira issue originally created by user it.manfred:

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

{quote}doctrine-module orm:schema-tool:update --dump-sql{quote}

{quote}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
{quote}

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';
    }

[doctrinebot]

• is referenced by DBAL-1060: [GH-736] [DBAL-1058] Fix database and namespace introspection for SQL Server
• is referenced by DBAL-1061: [GH-737] [DBAL-1058] [2.4] Fix database names introspection for SQL Server

[doctrinebot]

Comment created by @deeky666:

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

[doctrinebot]

Comment created by @Ocramius:

[~deeky666] please note that he is using dblib, which (afaik) we do not officially support.

[doctrinebot]

Comment created by it.manfred:

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')
{quote}
9.0.5069 (SQL Server 2005 Service Pack 4) {color:green}PASS{color}
10.50.4000.0 (2008 R2 SP2) {color:red}FAIL{color}
11.0.5058 (SQL Server 2012) {color:red}FAIL{color}
{quote}

QUERY: SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')
{quote}
9.0.5069 (SQL Server 2005 Service Pack 4) {color:green}PASS{color}
10.50.4000.0 (2008 R2 SP2) {color:green}PASS{color}
11.0.5058 (SQL Server 2012) {color:green}PASS{color}
{quote}

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')
{color:red}Failed on each of them{color}

Other tests:

QUERY: SELECT * FROM SYS.DATABASES
{quote}
9.0.5069 (SQL Server 2005 Service Pack 4) {color:green}PASS{color}
10.50.4000.0 (2008 R2 SP2) {color:green}PASS{color}
11.0.5058 (SQL Server 2012) {color:green}PASS{color}
{quote}

QUERY: SELECT * FROM sys.databases
{quote}
9.0.5069 (SQL Server 2005 Service Pack 4) {color:green}PASS{color}
10.50.4000.0 (2008 R2 SP2) {color:green}PASS{color}
11.0.5058 (SQL Server 2012) {color:green}PASS{color}
{quote}

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 :D

[doctrinebot]

Comment created by it.manfred:

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

[doctrinebot]

Comment created by @deeky666:

Patch provided: #736

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-736] was assigned:
#736

[doctrinebot]

Comment created by it.manfred:

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
{quote}
Uncaught exception 'PDOException' with message 'You cannot serialize or unserialize PDO instances'
{quote}

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:
{quote}
Doctrine\DBAL\DBALException: Invalid 'platform' option specified, need to give an instance of \Doctrine\DBAL\Platforms\AbstractPlatform.
{quote}

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

[doctrinebot]

Comment created by @Ocramius:

[~it.manfred] that seems to be related with DBAL-1057 - I'll mark this issue as resolved.

[doctrinebot]

Comment created by it.manfred:

[~ocramius], thx! Is there planned some big reworking of this section, am I right?
Am I need to post my last comment to [DBAL-1057](http://www.doctrine-project.org/jira/browse/[DBAL-1057]%28http://www.doctrine-project.org/jira/browse/DBAL-1057%29) thread?

[doctrinebot]

Comment created by @Ocramius:

[~it.manfred] 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.

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-736] was merged:
#736

[doctrinebot]

Comment created by @Ocramius:

Fixed in DBAL-1060

[doctrinebot]

Issue was closed with resolution "Fixed"

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-783] was closed:
#783

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-783] was closed:
#783

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.