[cfmigrations 4.1.3] MSSQL Server: There is no `bundleName` key in your connectionInfo

[DaveL]

I’m giving cfmigrations another shot to see if I can use it in my workflow. Unfortunately, it doesn’t appear to be playing nicely with MSSQL Server on Lucee or Adobe. It’s been awhile, so maybe there’s an oversight on my end.

I created a new Coldbox 7 app using coldbox create app and configured the .env file as follows:

# ColdBox Name and Environment
APPNAME=MyApp
ENVIRONMENT=development

# Database Information
DB_CONNECTIONSTRING=jdbc:macromedia://127.0.0.1:1433/mydsn?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useLegacyDatetimeCode=true&allowPublicKeyRetrieval=true
DB_CLASS=macromedia.jdbc.MacromediaDriver
DB_BUNDLENAME=
DB_BUNDLEVERSION=
DB_DRIVER=MSSQL
DB_HOST=127.0.0.1
DB_PORT=1433
DB_DATABASE=mydsn
DB_USER=sa
DB_PASSWORD=thisisnotapassword

// ... etc

I can confirm that the DSN connection works in the Lucee and ACF admin

I installed CFmigrations via install cfmigrations and ran migrate init to create a default .cfmigrations.json file. I updated the defaultGrammar to MSSQL so the final file looked like this:

{
    "default": {
        "manager": "cfmigrations.models.QBMigrationManager",
        "migrationsDirectory": "resources/database/migrations/",
        "seedsDirectory": "resources/database/seeds/",
        "properties": {
            "defaultGrammar": "SqlServerGrammar@qb", <-- updated from default 'AutoDiscover@qb'
            "schema": "${DB_SCHEMA}",
            "migrationsTable": "cfmigrations",
            "connectionInfo": {
                "password": "${DB_PASSWORD}",
                "connectionString": "${DB_CONNECTIONSTRING}",
                "class": "${DB_CLASS}",
                "username": "${DB_USER}",
                "bundleName": "${DB_BUNDLENAME}",
                "bundleVersion": "${DB_BUNDLEVERSION}"
            }
        }
    }
}

When I ran migrate install I got the following error:
ERROR (6.1.0+00813)

There is no bundleName key in your connectionInfo. Please create one with the necessary values. See GitHub - commandbox-modules/commandbox-migrations

I looked at the Github URL in the error and noted a small point about how MySql and Commandbox5 need some specific values set in the bundleName and bundleVersion keys. However, I don’t see any notes for those using MSSQL.

Speaking of which, my ColdBox .env files have never needed DB_BUNDLENAME or DB_BUNDLEVERSION before today, so these are new to me. It’s not clear as someone who uses MSSQL Server, where to get these values from the Lucee Admin, or ACF Admin. Regardless, leaving them blank has never prohibited my apps from working, so I’ve always left them with empty values.

Here are some things I tried to resolve the problem:

1. Tried setting the .cfmigrations config defaultGrammer back to autodiscover.

2. Tried updating my .env file with the explicit values for bundleName and bundleVersion as specified in the commandbox-migrations readme.
   

3. Tried changing the engine in server.json to Adobe@2021

I ran server forget and server start before each attempt above and none of them got me any closer to running migrate install successfully.

Please let me know if you have any tips or suggestions on troubleshooting further.

[DaveL]

Update:

I learned a few things since yesterday. CFMigrations doesn’t use the server in your web app. Instead, it uses the version of Lucee built into Commandbox. So in other words, it doesn’t matter whether your app is Adobe, Lucee, etc. CFMigrations will always use Lucee (Or whatever Commandbox uses in the future).

I was able to fix the problem by doing two things:

1. Update my .env file with updated DB_CONNECTIONSTRING, DB_CLASS, DB_BUNDLENAME, and DB_BUNDLEVERSION for MSSQL Server on Lucee. As a side note, I think these settings should be added to the cfmigrations-commandbox repo’s documentation/readme. I had a hard time finding what values to use.

These are the changes I made to make it work with MSSQL Server:

DB_CONNECTIONSTRING=jdbc:sqlserver://127.0.0.1:1433;databaseName=mydsn;useSSL=false;useUnicode=true;characterEncoding=UTF-8;serverTimezone=UTC;useLegacyDatetimeCode=true;allowPublicKeyRetrieval=true
DB_CLASS=com.microsoft.sqlserver.jdbc.SQLServerDriver
DB_BUNDLENAME=org.lucee.mssql
DB_BUNDLEVERSION=7.2.2.jre8

Note: I had to update the connection string format from the default ColdBox uses now. CFMigrations needed the params to be separated by semicolons rather than using the URL format.

After doing that change, I restarted Commandbox and I was able to migrate install. Another thing I learned is that Commandbox must read the environment variables when it starts, so if you make a change to the .env file or the .cfmigrations.json file, you need to exit Commandbox and restart it to load the changed values.

I hope this helps someone else.