MSSQL support?

[MattWilcox]

Part of the docs says that Connect should be able to talk to any database that Yii2 has support for, and links to a page where Yii2 says it supports MSSQL.

However the only viable options that seem to be taken by the REMOTE_DB_DRIVER config are 'mysql' and 'pgsql', attempting to use 'mssql' leaves a "Driver not found" error:

Database Exception – yii\db\Exception
could not find driver
↵
Caused by: PDOException
could not find driver
in /home/ubuntu/sites/gllm/7_website/craft/vendor/yiisoft/yii2/db/Connection.php at line 712

I've tried spelunking into the code but can't find anything obvious. I have found an old issue on Stack Overflow where it was recommended to run composer require yiisoft/yii2-mssql but that is not an installable package anymore. Further, it seems as though it's been retired (attempting to find that on GitHub or Packagist forwards to a renamed Yii3 variant now) and that support ought to be built-in since Yii 2.1?

[khalwat]

Pretty sure you'll need to use a DSN for this

https://stackoverflow.com/questions/52734835/how-to-connect-sql-server-with-yii2

[MattWilcox]

Thanks Andrew, unfortunately this terminology is a bit beyond me. Where would I be applying something like:

'db' => [
    'class' => 'yii\db\Connection',
    'driverName' => 'sqlsrv',
    'dsn' => 'sqlsrv:Server=xx.xx.xx.xx;Database=xxxx',
    'username' => '<username>',
    'password' => '<password>',
    'charset' => 'utf8'
],

in context of this plugin?

[khalwat]

I want to qualify this that I haven't done this myself, but I know some people have used the plugin to connect to a remote MS SQL db. For the driver name, here's what Yii2 has in their code:

        'sqlsrv' => 'yii\db\Command', // newer MSSQL driver on MS Windows hosts
        'mssql' => 'yii\db\Command', // older MSSQL driver on MS Windows hosts

You'd set it up something like this: the config.php:

https://nystudio107.com/docs/connect/#configuring-connect

return [
    'connections' => [
        'remote' => [
            'driver' => getenv('REMOTE_DB_DRIVER'),
            'server' => getenv('REMOTE_DB_SERVER'),
            'user' => getenv('REMOTE_DB_USER'),
            'password' => getenv('REMOTE_DB_PASSWORD'),
            'database' => getenv('REMOTE_DB_DATABASE'),
            'schema' => getenv('REMOTE_DB_SCHEMA'),
            'tablePrefix' => getenv('REMOTE_DB_TABLE_PREFIX'),
            'port' => getenv('REMOTE_DB_PORT')
        ],
    ],
];

...and you'd define the REMOTE_DB_DRIVER .env variable to be either mssql or sqlsrv -- whichever works. REMOTE_DB_PORT would be set to 1433. And then the rest would be similar to whatever you use for MySQL (user, password, server, etc).

So maybe you don't need a DSN? If you do, you can construct it from the above.

[MattWilcox]

So i have set up Connect and tested via a MySQL on my local machine (just connecting to a Nitro DB actually) and that worked; so I'm sure the plugin is installed and working fine.

If I then try as either mssql or sqlsrv as the values for REMOTE_DB_DRIVER in the .env file, I get thrown the same error of "could not find driver"

Seems like its tripping at any value in the driver constant other than mysql or pgsql, before it even gets to anything else.

connect.php

return [
    'connections' => [
        'remote' => [
            'driver'      => getenv('REMOTE_DB_DRIVER'),
            'server'      => getenv('REMOTE_DB_SERVER'),
            'user'        => getenv('REMOTE_DB_USER'),
            'password'    => getenv('REMOTE_DB_PASSWORD'),
            'database'    => getenv('REMOTE_DB_DATABASE'),
            'schema'      => getenv('REMOTE_DB_SCHEMA'),
            'tablePrefix' => getenv('REMOTE_DB_TABLE_PREFIX'),
            'port'        => getenv('REMOTE_DB_PORT')
        ],
    ],
];

.env

REMOTE_DB_DRIVER="mssql"
REMOTE_DB_SERVER="REDACTED"
REMOTE_DB_USER="REDACTED"
REMOTE_DB_PASSWORD="REDACTED"
REMOTE_DB_DATABASE="internal_web_data"
REMOTE_DB_TABLE_PREFIX=""
REMOTE_DB_PORT="1433"

test.twig

{% set db = craft.connect.open('remote') %}
{% set results = craft.connect.query(db)
    .select(['*'])
    .from('ebs_courses_to_craftcms')
    .limit(10)
    .all()
%}

{% for result in results %}
    <div>{{ result | json_encode }}</div>
{% endfor %}

Output

could not find driver
↵
Caused by: PDOException
could not find driver
in /home/ubuntu/sites/gllm/7_website/craft/vendor/yiisoft/yii2/db/Connection.php at line 712

I've also tried adjusting to what I think might be right for the DSN, but that also fails with the same error...

adjusted .env

REMOTE_DB_DRIVER="sqlsrv"
REMOTE_DB_SERVER="REDACTED"
REMOTE_DB_USER="craftcms"
REMOTE_DB_PASSWORD="REDACTED"
REMOTE_DB_DATABASE="internal_web_data"
REMOTE_DB_TABLE_PREFIX=""
REMOTE_DB_PORT="1433"
REMOTE_CLASS="yii\db\Connection"
REMOTE_DSN="sqlsrv:Server=REDACTED;Database=internal_web_data"

adjusted connect.php

return [
    'connections' => [
        'remote' => [
            'driver'      => getenv('REMOTE_DB_DRIVER'),
            'server'      => getenv('REMOTE_DB_SERVER'),
            'user'        => getenv('REMOTE_DB_USER'),
            'password'    => getenv('REMOTE_DB_PASSWORD'),
            'database'    => getenv('REMOTE_DB_DATABASE'),
            'schema'      => getenv('REMOTE_DB_SCHEMA'),
            'tablePrefix' => getenv('REMOTE_DB_TABLE_PREFIX'),
            'port'        => getenv('REMOTE_DB_PORT'),
            'dsn'         => getenv('REMOTE_DSN'),
            'class'       => getenv('REMOTE_CLASS'),
        ],
    ],
];

[khalwat]

Are we sure the driver it can't find isn't the PHP PDO driver? From the error message, that's what it looks like to me.

https://www.php.net/manual/en/ref.pdo-dblib.php

[MattWilcox]

Well, this is a Nitro site so everything that should be available ought to be available.

PHP Info is giving: API Extensions mysqli,pdo_mysql

PDO
PDO drivers mysql, pgsql
pdo_mysql
Client API version  mysqlnd 7.4.15
pdo_mysql.default_socket    /var/run/mysqld/mysqld.sock
pdo_pgsql
PDO Driver for PostgreSQL   enabled
PostgreSQL(libpq) Version   12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
pgsql
PostgreSQL(libpq) Version   12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
PostgreSQL(libpq)   PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
Multibyte character support enabled
SSL support enabled
Active Persistent Links 0
Active Links    0
pgsql.allow_persistent  On
pgsql.auto_reset_persistent Off
pgsql.ignore_notice Off
pgsql.log_notice    Off
pgsql.max_links Unlimited
pgsql.max_persistent    Unlimited

I don't like how the only PDO drivers listed are mysql and pgsql though...

[khalwat]

Nope, I don't think Nitro will install the mssql PDO driver by default. Might be best to ask over on the Nitro repo?

[MattWilcox]

Will do, thanks Andrew!