Yii support pdo_odbc for mysql and mssql.

[qiangxue]

I am using Yii framework 1.1.9.

I search for net and found that Yii cannot support pdo_odbc for mysql and mssql.

So, I have modified Yii Framework\db\CDBConnection.php Line 415;

if(($pos=strpos($this->connectionString,':'))!==false)
{
  $driver=strtolower(substr($this->connectionString,0,$pos));
  if($driver==='mssql' || $driver==='dblib')
  $pdoClass='CMssqlPdoAdapter';
}
return new $pdoClass($this->connectionString,$this->username, $this->password,$this->_attributes);

Which didn't reuse the function called getDriverName

So, I rewrite it to

$driver=$this->getDriverName();
if($driver==='mssql' || $driver==='dblib')
  $pdoClass='CMssqlPdoAdapter';
return new $pdoClass($this->connectionString,$this->username, $this->password,$this->_attributes);

For getDriverName function, I rewrote it when db object detect odbc the retrieve driver property.

public function getDriverName()
{
  if(($pos=strpos($this->connectionString, ':'))!==false)
  {
    $driverName = strtolower(substr($this->connectionString, 0, $pos));
    if ($driverName == 'odbc')
    {
      $connectionProperty = array();
      $connectionPropertyString = strtolower(substr($this->connectionString, $pos + 1));
      $connectionPropertyItems = explode(";", $connectionPropertyString);
      foreach($connectionPropertyItems as $connectionPropertyItem)
      {
        $items = explode("=", $connectionPropertyItem, 2);
        if (count($items) > 1)
          $connectionProperty[strtolower(trim($items[0]))] = strtolower(trim($items[1]));
        else
          $connectionProperty[strtolower(trim($items[0]))] = '';
      }
      $driverType = $connectionProperty['driver'];
      if ($driverType == '{sql server}' || $driverType == '{sql server native client 10.0}' || $driverType == '{sql native client}')
      {
        $driverName='mssql';
      }
      elseif ($driverType == '{mysql}' || $driverType == '{mysql odbc 3.51 driver}' || $driverType == '{mysql odbc 5.1 driver}')
      {
        $driverName='mysql';
      }
    }
    return $driverName;
  }
  // return $this->getAttribute(PDO::ATTR_DRIVER_NAME);
}

for windows php versio 5.2, we need download http://s3.paramorphicdesigns.com/random/php/php_pdo_odbc.dll patch which support to 5.2.17 (Because php has PDO ODBC: Long binary field in query result crashes PHP ("Out of memory" error)) Ref:http://www.mail-archive.com/php-bugs@lists.php.net/msg148533.html

Migrated from http://code.google.com/p/yii/issues/detail?id=3074

---

earlier comments

yhlaikk said, at 2012-01-07T02:36:44.000Z:

function createPdoInstance didn't reuse the function called getDriverName()

coderkk said, at 2012-01-09T09:26:47.000Z:

php_pdo_odbc Long binary field issue has fixed at PHP 5.4, I am testing yii at PHP 5.4 RC5 now forget to tell you my connection string is 'odbc:Driver={SQL Server Native Client 10.0};Server={host name};Database={database name};

[ellier]

So, is it possible to add ODBC to Yii? If so, please explain how to accomplish that. Thanks.

[nineinchnick]

I've switched to pdo_odbc using this:

<?php
class OdbcConnection extends CDbConnection
{
    public $driver;

    /**
     * Creates the PDO instance.
     * When some functionalities are missing in the pdo driver, we may use
     * an adapter class to provides them.
     * @return PDO the pdo instance
     */
    protected function createPdoInstance()
    {
        $pdoClass=$this->pdoClass;
        $driver=strtolower($this->driver);
        if($driver==='mssql' || $driver==='dblib')
            $pdoClass='CMssqlPdoAdapter';
        elseif($driver==='sqlsrv')
            $pdoClass='CMssqlSqlsrvPdoAdapter';
        return new $pdoClass($this->connectionString,$this->username,
                                    $this->password,$this->getAttributes());
    }

    /**
     * Returns the name of the DB driver
     * @return string name of the DB driver
     */
    public function getDriverName()
    {
        return strtolower($this->driver);
        // return $this->getAttribute(PDO::ATTR_DRIVER_NAME);
    }
}

This allows to use connectionString starting with 'odbc:' and specify the driver in the 'driver' property.

Let's hear some opinions from the core team about this.

[samdark]

Nice trick. I'm for taking it in but we need tests first.

[resurtm]

Uhm, here's my observations on this topic:

Software i use:

# dpkg -l | grep odbc
ii  iodbc                                 3.52.7-3.1                     amd64        GTK+ config frontend for the iODBC Driver Manager
ii  libiodbc2                             3.52.7-3.1                     amd64        iODBC Driver Manager
ii  libmyodbc:amd64                       5.1.10-3                       amd64        the MySQL ODBC driver
ii  libodbc1:amd64                        2.3.1-1                        amd64        ODBC library for Unix
ii  libodbc1:i386                         2.3.1-1                        i386         ODBC library for Unix
ii  odbcinst                              2.3.1-1                        amd64        Helper program for accessing odbc ini files
ii  odbcinst1debian2:amd64                2.3.1-1                        amd64        Support library for accessing odbc ini files
ii  php5-odbc                             5.5.10+dfsg-1                  amd64        ODBC module for php5
ii  python-pyodbc                         3.0.6-2                        amd64        Python module for ODBC database access

Operating system i use:

# uname -a
Linux resurtm-desktop 3.13-1-amd64 #1 SMP Debian 3.13.7-1 (2014-03-25) x86_64 GNU/Linux

# cat /proc/version
Linux version 3.13-1-amd64 (debian-kernel@lists.debian.org) (gcc version 4.8.2 (Debian 4.8.2-16) ) #1 SMP Debian 3.13.7-1 (2014-03-25)

# cat /etc/debian_version 
jessie/sid

Database configuration:

'components'=>array(
    'db'=>array(
        'connectionString' => 'odbc:Driver={MySQL};Server=127.0.0.1;Database=test',
        'username' => '',
        'password' => '',
    ),
),

Testing code:

$result = Yii::app()->getDb()->createCommand('SELECT * FROM tbl_test2')->queryAll();
var_dump($result);

Configuration files:

# cat /etc/odbcinst.ini 
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
FileUsage = 1

# cat /etc/odbc.ini 
[yii-connector]
Description           = MySQL connection for Yii
Driver                = MySQL
Database              = test
Server                = localhost
UserName              = root
Password              = ******
Port                  = 3306
Socket                = /var/run/mysqld/mysqld.sock

Everything works just fine. Please, correct me if i did something not related to this issue.

UPD: oh, really, have to test it with MSSQL yet...

[nineinchnick]

@resurtm if you use a 'odbc:...' connection string Yii will not map it to any CDbSchema and AR's might not work. You only posted an example with DAO. A simple solution would allow to override the driverName property to force one CDbSchema if it can't be detected from the connection string.

[resurtm]

@nineinchnick, thanks! Got it now. :)

[williamyiu]

does yii2 support odbc with mssql?

[samdark]

Yes but it's better to use forums to ask questions.

[rapttor]

I got this problem with Yii 1.1.x project and almost got crazy:

First make sure you have

    sudo apt install php-xml php-mbstring php-pdo php-mysql 

installed, and restart apache

    sudo apachectl restart
    or: 
    sudo service apache2 restart

Detailed error was that database library cannot be found.

My solution was related to caching:

    cache' => array(
        'class' => 'system.caching.CDbCache',
        //'class' => 'system.caching.CFileCache',
        'connectionID'=>'db', // <<< THIS IS THE ISSUE
    ),

if connectionID is not set, db caching defaults to mysqli database in /protected/data directory which cannot be accessed if mysqli driver is not installed on system (common issue with dedicated servers, DO droplets, xampp/wamp...)

or, you can disable db caching and enable fileCache instead.