Allow passing the DSN by using `doctine.dbal.options.dsn` for symfony projects

[mario-van-zadel]

The README.MD describes to pass the dns as part of the $params array:

$connection = \Doctrine\DBAL\DriverManager::getConnection(
    [
        'driverClass' => \ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver::class,
        'dsn' => 'name of the created dsn',
    ]
);

I tried to set the DSN by using the config/packages/doctrine.yaml of a symfony project:

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        dsn: '%env(resolve:ODBC_DSN)%'

The above configuration throws the following error because doctrine.dbal doesn't support custom attributes (like dsn):

Unrecognized option "dsn" under "doctrine.dbal.connections.default". Available options are "MultipleActiveResultSets", "application_name", "auto_commit", "charset", "connectstring", "dbname", "default_dbname", "default_table_options", "driver", "driver_class", "host", "instancename", "keep_slave", "logging", "mapping_types", "memory", "options", "password", "path", "persistent", "platform_service", "pooled", "port", "profiling", "profiling_collect_backtrace", "profiling_collect_schema_errors", "protocol", "schema_filter", "server", "server_version", "service", "servicename", "sessionMode", "shard_choser", "shard_choser_service", "shard_manager_class", "shards", "slaves", "sslcert", "sslcrl", "sslkey", "sslmode", "sslrootcert", "unix_socket", "url", "use_savepoints", "user", "wrapper_class".

Passing the dsn attribute as part of doctine.dbal.options is working:

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: '%env(resolve:ODBC_DSN)%'

.env

ODBC_DSN="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\xampp\\htdocs\\MyDatabase.mdb;"

This requires the ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver's constructor to also check the $driverOptions for a DSN. This change is done in this pull request.

[zoilomora]

This change would affect many more parts of the code. Including the way to instantiate the Connection (PDO and ODBC) and the README.md

Can you put an example of a doctrine.yml file where you include the configuration?

[zoilomora]

The problem is that for almost all the part of the MicrosoftAccessSchemaManager.php the PDO Connection does not give the necessary support in Microsoft Access. A pure ODBC Connection is required.

To simplify the configuration of both connections a DSN created in the system is necessary.

The files would be as follows:

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: '%env(resolve:MSACCESS_DSN)%'

.env

MSACCESS_DSN="MyDatabase"

@marioklump seems correct to you?

[mario-van-zadel]

I added an example of the doctrine.yml configuration.

After checking it, I recognised that the two configuration attributes odbc_driver and filename aren't part of your new repo:

https://github.com/mpk-software/doctrine-dbal-msaccess-deprecated/blob/master/src/Doctrine/DBAL/Driver/MsAccess/Driver.php#L41 vs. https://github.com/zoilomora/doctrine-dbal-msaccess/blob/master/src/Doctrine/DBAL/Driver/MicrosoftAccess/Driver.php#L74

It there any reason for it? Should I set the odbc_driver and filename in another way?

As far as I remember, I had to pass the values for odbc_driver and filename through the yaml config doctrine.dbal. options to make them available inside the ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver's constructor.

[mario-van-zadel]

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: '%env(resolve:MSACCESS_DSN)%'

This looks great! Of course, I should be able to concat my DSN string manually. But the problem is/was, that the attributes assigned to doctrine.dbal.options of the yaml weren't part of the \ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver constructor's parameter $params. They were part of the parameter $driverOptions. That's why I changed the parameter of $this->constructPdoDsn(...) from $params to $driverOptions.

.env

MSACCESS_DSN="MyDatabase"

@marioklump seems correct to you?

I'm going to try running my project with your new repo and keep you informed! ;-)

[zoilomora]

@marioklump I see fine to just leave the DSN in $driverOptions. After all, it's a driver option 😅. Thus the code becomes less complex by not having to check the same information in 2 different variables.

Remember to modify the README.md

[mario-van-zadel]

@zoilomora, please have a look at the updated commit and the example in the pull request description.

[zoilomora]

@marioklump check if this configuration example would work for you:

$connection = \Doctrine\DBAL\DriverManager::getConnection(
    [
        'driverClass' => \ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver::class,
        'driverOptions' => [
            'dsn' => 'name of the created dsn',
            'charset' => 'UTF-8',
        ],
    ]
);

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: 'name of the created dsn'

[mario-van-zadel]

Yes, it's working. I've just tested the following configuration in my real-world project (symfony project requesting a MS Access database):

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: '%env(resolve:ODBC_DSN)%'

.env

ODBC_DSN="DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\xampp\\htdocs\\MyDatabase.mdb;"

[zoilomora]

@marioklump but that would not be the correct way. You would have to create a DSN for the ODBC Connection to be correct and the MicrosoftAccessSchemaManager.php methods to work correctly.

[mario-van-zadel]

Yes, it's working with both the combination of DRIVER and DBQ and the usage of a named DSN:

config/packages/doctrine.yaml

doctrine:
    dbal:
        driver_class: '\ZoiloMora\Doctrine\DBAL\Driver\MicrosoftAccess\Driver'
        options:
            dsn: '%env(resolve:ODBC_DSN)%'

.env

ODBC_DSN="MyDatabase"

The configured DSN I'm sorry for the german screenshot.

[zoilomora]

@marioklump effectively, that would have to be the way to use it so that the ODBC Connection works correctly and we have the other functionalities of MicrosoftAccessSchemaManager.php