Connecting to Sql Server from Ubuntu using ODBC

Yii 1.1 General Discussion
1

I am attempting to connect to Microsoft SQL Server from Ubuntu using Microsoft’s ODBC driver (RedHat6/msodbcsql-11.0.2270.0.tar.gz). I followed the procedure described here for installation:

I have successfully connected to my database from the command line using sqlcmd.

I updated /etc/odbc.ini with the following:


[ODBC Driver 11 for SQL Server]

Description=Microsoft ODBC Driver 11 for SQL Server

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

Threading=1

UsageCount=3

I upgraded to Yii 1.1.16 which is noted as supporting ODBC: www.yiiframework.com/news/83/yii-1-1-16-is-released/

I believe the problem is in my db configuration:


		'db' => array (

            'class'=>'CDbConnection',

            'driverName' => 'sqlsrv',

            'connectionString' => 'odbc:Driver={ODBC Driver 11 for SQL Server};Server=<server>;Database=<database>',

            'username' => '<username>',

			'password' => '<password>',

			'charset' => 'utf8',

		),

Does anyone have any recommendations?

Thanks, Steve

2

And the error I am seeing is:




 CDbConnection failed to open the DB connection: could not find driver


/var/www/yii-1.1.16.bca042/framework/db/CDbConnection.php(399)
3

I was able to get access to SQL Server via ODBC working.

The driverName parameter needed to be set to mssql and I updated my odbc.ini and odbcinst.ini files.

odbc.ini:


[SQLServer]

Driver=ODBC Driver 11 for SQL Server

Description=Microsoft ODBC Driver 11 for SQL Server

odbcinst.ini:


[ODBC]

Trace                   = yes

TraceFile               = /tmp/odbctracefile.log

# disable tracing in production!


[ODBC Driver 11 for SQL Server]

Description             = Microsoft ODBC Driver 11 for SQL Server

Driver                 = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

Threading               = 1

UsageCount              = 3

Change to the db field in config/main.php:


                'db' => array (

            'driverName' => 'mssql',

            'connectionString' => 'odbc:Driver=ODBC Driver 11 for SQL Server;Server=<server>;Database=<database>',

            'username' => '<username>',

                        'password' => '<password>',

                        'charset' => 'utf8',

			'enableParamLogging'=>true,

			'enableProfiling'=>true

                ),

These changes allowed the connection to SQL Server to complete, but I received a "SQLSTATE[22001]: String data, right truncated" Error when attempting to read the Message table. This I beleive is due to an issue with the php pdo_odbc extension on 64-bit systems: http://stackoverflow.com/questions/16532694/pdo-odbc-doesnt-work-whit-bind-values-nvarchar-and-text-are-incompatible-in-th

I resolved this problem by installing the pdo_odbc.so found here: http://onefinepub.com/2014/02/php-segfaults-pdo_odbc-64-bit-platforms/

This solution has the same issues I had using FreeTDS. No support for NVARCHAR(MAX). I am going to check out the easy soft driver, but will likely switch back to FreeTDS.

4

I was unable to get unicode to work with FreeTDS and I had a number of problems with the easy soft driver, so it looks like the Microsoft ODBC driver is the winner.

5

Make sure you enable schema caching when using Sql Server (schemaCachingDuration flag). The Sql Server schema queries are excruciatingly slow.

6

The product documentation on SQL Server ODBC Driver page of DataDirect can help you fix this.

7

For some reason I couldn’t add a link, but please feel free to access sql server ODBC page by datadirect