New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PDO Connections to MS-SQL Server are using wrong SQL syntax #3142

Closed
frank-mueller opened this Issue Dec 3, 2012 · 8 comments

Comments

Projects
None yet
5 participants
@frank-mueller
Copy link

frank-mueller commented Dec 3, 2012

There is the problem, that all PDO-Connections to SQL-Server are using the SQL92-Platform-Syntax. So it is currently not possible to use ZF2 with an MS-SQL-Server using the PDO drivers!

This is done by \Zend\Db\Adapter\Adapter.php in the function createPlatformFromDriver.

So in example a simple query to a MS-SQL-Database builded by TableGateways fetchall() looks like this an causes an error:

SELECT "Tablename".* FROM "Tablename"

The "native" Driver "SqlServer" for Microsoft-SQL-Server can only be used on windows platforms. This is not applicable in most cases.

@atukai

This comment has been minimized.

Copy link

atukai commented Dec 9, 2012

It's the same with Mysql. I updated

public function getDatabasePlatformName($nameFormat = self::NAME_FORMAT_CAMELCASE)
{
$name = $this->getConnection()->getDriverName();

    if ($nameFormat == self::NAME_FORMAT_CAMELCASE) {
        switch ($name) {
            case 'sqlserver':
                return 'SqlServer';
            case 'sqlite':
                return 'Sqlite';
            case 'mysql':
                return 'Mysql';
            case 'pgsql':
                return 'Postgresql';
            default:
                return ucfirst($name);
        }
    } else {
        switch ($name) {
            case 'sqlserver':
                return 'SQLServer';
            case 'sqlite':
                return 'SQLite';
            case 'mysql':
                return 'MySQL';
            case 'pgsql':
                return 'PostgreSQL';
            default:
                return ucfirst($name);
        }
    }
}

Then do $adapter->getPlatfrom() return 'Mysql', but table and fields names quoted with " as in Sql92 defined, not with ` as in Mysql platform.

@frank-mueller

This comment has been minimized.

Copy link
Author

frank-mueller commented Dec 18, 2012

Sorry. But this not solves the problem for me using MS-SQL with pdo adapter.
Until the problem is fixed, i am using a workaround by changing the return of the function createPlatformFromDriver() in Db\Adapter\Adapter.php to the following by default:

return new Platform\SqlServer();

But that's not very nice :-(

@fabiorphp

This comment has been minimized.

Copy link

fabiorphp commented Jan 3, 2013

I have the same problem. PDO Connection not use SqlServer Decorator for queries.

@hussfelt

This comment has been minimized.

Copy link

hussfelt commented Apr 4, 2013

We're currently experiencing the same isssue with statements beeing quoted with " instead of ` using MySQL.
Looking into if this is ZF related or if it's in our codebase.

@CnApTaK

This comment has been minimized.

Copy link

CnApTaK commented Apr 19, 2013

why not just use db options?
'dbMsSql' => [
'platform' => 'SqlServer',
'driver' => 'Pdo',
'dsn' => 'sqlsrv:Database=...;Server=...',
'username' => '...',
'password' => '...',
],

@frank-mueller

This comment has been minimized.

Copy link
Author

frank-mueller commented Jun 26, 2013

@CnApTaK With your config i always get an "Connect Error: could not find driver".
This is so because the sqlsrv driver does not exist on linux platforms.

@CnApTaK

This comment has been minimized.

Copy link

CnApTaK commented Jun 26, 2013

for linux i use:
'db' => array(
'platform' => 'SqlServer',
'driver' => 'Pdo',
'dsn' => 'dblib:host=...;dbname=...;',
'username' => '...',
'password' => '...',
'charset' => 'UTF-8',
'pdotype' => 'dblib',
),
topic problem was with SQL syntax, which is solved with 'platform' => 'SqlServer'

@frank-mueller

This comment has been minimized.

Copy link
Author

frank-mueller commented Jun 27, 2013

Ok - thanks to @CnApTaK

The "platform" parameter is working right now in ZF 2.2.1. So this problem is solved for now.

I always have problems with my database operations on MS-SQL-Server. But for the new problem (with Paginator-Object #4712 ) another tickets exists - therefore I close this ticket.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment