Trouble with getting cfmigrations tables to create on the correct database

Box Modules
1

Hello!

I’m setting up a new coldbox app and I’d like to use cfmigrations to do some development testing. I’m using Microsoft SQL Server, and I have a SQL server with quite a few databases on it. I have indicated the following in my .env file (private information like IPs and credentials omitted):

DB_CLASS=com.microsoft.sqlserver.jdbc.SQLServerDriver
DB_BUNDLENAME=mssqljdbc4
DB_BUNDLEVERSION=4.0.2206.100
DB_DRIVER=MSSQL
DB_HOST=loebsql2
DB_PORT=1433
DB_DATABASE=receiving_coldbox_app

I then run the command “migrate install” in commandbox and this happens:
image

I don’t plan to install the migrations in the master database. I’m curious why the command wants to install the tables there :frowning:

I understand that in the documentation there’s a step about schema that I may be forgetting? All of my database tables use .dbo, so I’m curious how changing it would do anything to help.

Any help is appreciated. Thanks for reading :slight_smile:

2

I’m pretty sure you’re missing your connection string, like so:

DB_CONNECTIONSTRING=jdbc:sqlserver://loebsql12:1433;DATABASENAME=yourdbname;sendStringParametersAsUnicode=true;SelectMethod=direct

I’ve never used that last one (db_database) because the database name is specified in the connection string. “Master” sounds like MSSQL’s default choice.

1 Like
3

Thanks for the reply!

I added the following to my .env file

DB_CONNECTIONSTRING="jdbc:sqlserver://{myIpHere};DATABASENAME=receiving_coldbox_app;sendStringParametersAsUnicode=true;SelectMethod=direct"

My app starts just fine now, but the “migrate install” command yields the same error message. Any ideas?

4

is “receiving_coldbox_app” the name of your database in MSSQL?

Are you providing a user and password? Typically:

ERP_DB_USER=SQLuser
ERP_DB_PASSWORD=encrypted:BIGLONGENCRYPTEDPASSWORD

Does that user have access to receiving_coldbox_app?

And I’m sure you saw this but you’ve got the cfmigrations section setup in your box.json right? (FORGEBOX: CFMigrations Commands)

5

Yes, “receiving_coldbox_app” is the name of the database in MSSQL.
Yes, I am providing credentials in my .env under DB_USER and DB_PASSWORD
That user does have access to the receiving_coldbox_app database.
The only thing I have not setup in the cfmigrations box.json section is the DB_SCHEMA , it seems. What would I put there, as each table in my database is a .dbo?

6

The root schema name. For MSSQL, it’s usually “dbo”.

7

Can you paste your migrations config from your box.json

8 
"cfmigrations":{
        "migrationsDirectory":"resources/database/migrations",
        "schema":"${DB_SCHEMA}",
        "connectionInfo":{
            "bundleName":"${DB_BUNDLENAME}",
            "bundleVersion":"${DB_BUNDLEVERSION}",
            "password":"${DB_PASSWORD}",
            "connectionString":"${DB_CONNECTIONSTRING}",
            "class":"${DB_CLASS}",
            "username":"${DB_USER}"
        },
        "defaultGrammar":"AutoDiscover@qb"
    }
9

It looks good to me Jeff - Did you reload your CommandBox after you made any changes to your .env or box.json files to make sure the changes get picked up?

10

That is something I did not know I needed to do!

image

So now I’m getting a different error. While it’s still an error, it’s promising!

1 Like
11

Update: It works now. I needed to remove the quotation marks around my connection string in my .env file. Thanks for the help guys :slight_smile:

1 Like