PDO ODBC Driver does not support auto detection of entity quote character

[numkem]

I'm currently using Idiorm with SQL Server 2008 on Linux through ODBC and I get this message by default :

SQLSTATE[42000]: Syntax error or access violation: 8180 [FreeTDS][SQL Server]Statement(s) could not be prepared. (SQLExecute[8180] at /build/src/php-5.4.13/ext/pdo_odbc/odbc_stmt.c:254)

I tracked down the problem to idiorm quoting table names (using '`') by default while SQL Server really hates it.

Fixing the issues only takes to configure idiorm to not use quotes like this:

ORM::configure('identifier_quote_character', '');

Would seem like the autodetection of that character is not functioning when using the pdo_odbc driver.

[treffynnon]

I seem to recollect that this was the case with ODBC in that it cannot detect what server is on the other side of the ODBC interface. I don't use it though so I cannot be sure. Would you like to have a go at a fix for auto detection over ODBC - that's assuming it is even possible of course?

[numkem]

I could take a look at it.

In the case of we can't detect correctly we could always (that could be a hack) do a switch/case with the driver's name. But again there is many ways of doing the DSN and it wouldn't work all the time.

The way I have mine configured is something like this :

ORM::configure('odbc:DRIVER=FreeTDS;SERVERNAME=host;DATABASE=database;APP=app_name');

If it was required to do it like that at all time it could work. But since this is Linux, choice is always the option so you could also configure the host/username/password straight into unixodbc so all you have to provide to the DSN is the odbc connection name. I did it this way because have dev/preprod/prod environments.

Even than the driver's name is at your own discretion. Howto's usually call it this way because we are indeed using FreeTDS with unixodbc but people might want to change it's name to reflect something else.

I'll start by looking at what internal properties of the PDO object has once it's created and maybe I could track down to the internals of the ODBC connection so I might get something in there that looks for sure like something from FreeTDS.

Best way could always be to add a section regarding ODBC/SQL Server.

[treffynnon]

I tend to agree after taking a quick look myself last night. This is probably better off being documented rather than coded around as it is too difficult to ascertain the underlying DB/system from an ODBC connection.

[samsong]

I am using numkems branch but it still needs one more change

in below code, mssql + dblib & possibly even sqlsrv should return empty string '' as quote identifier

        protected static function _detect_identifier_quote_character($connection_name) {
            switch(self::$_db[$connection_name]->getAttribute(PDO::ATTR_DRIVER_NAME)) {
                case 'pgsql':
                case 'sqlsrv':
                case 'dblib':
                case 'mssql':
                case 'sybase':
                case 'firebird':
                    return '"';
                case 'mysql':
                case 'sqlite':
                case 'sqlite2':
                default:
                    return '`';
            }
        }

[treffynnon]

@samsong I am not sure what makes you think that an empty string can possibly quote an identifier correctly. That would produce invalid SQL where column or table names are reserved words. According to their respective documentation they all use a " character.

Please see my answer to your other comments for more information: #116 (comment)

[treffynnon]

Closing as this is impractical within the Idiorm library.