Coldbox and VueJS untangled

Category: Commandbox

List all foreign keys in SQL server

This post is not very cfml specific, but I am using this a lot with cfmigrations, so it might be worthwhile sharing. Recently I was doing some major restructuring of a database, and most of the time I try to script this using cfmigrations or commandbox-migrations. Recently I had to drop some tables and thought this would be easy with cfmigrations. So I fired up commandbox, and executed

migrate create dropOldTables

which creates a cfc named yyyy-mm-dd_dropOldtables.cfc in my resources/database/migrations map. If you didn’t use commandbox-migrations before, you have to install and init first, as described here. The migration command should be trivial, something like:

function up( schema, query ) {
   schema.drop("ipnumbers");
}
Continue reading

Cfcompile to the rescue (part 2)

A few days ago I blogged about some annoying lucee or coldbox behaviour. On syntax errors in components often I didn’t get feedback on the offending file or line numbers. This makes it very hard to debug your application if you made changes in several files at once. After my post I was contacted by Zac Spitzer who asked me if I could file a bug. I already did this a few months ago, but they couldn’t reproduce my case. So we talked about this bug and I digged a little deeper to find out what was wrong.

Continue reading

Cfcompile to the rescue

This is a story about sloppiness, dislexia, or maybe my touch typing skill are just lacking when coding. I also hear friends telling me their cat is sleeping on the keyboard. To add to this disaster, Lucee is not very helpful when trying to decipher my typo’s. I wonder if you ever saw a screen like this:

We all see these ugly screens sometimes, but usually they provide some information on an error. But not this time…

Continue reading

Commandbox-migrations and SQL server

Cfmigrations is a nice tool to describe database changes and version them with your application code. It comes in two flavours as a forgebox package: cfmigrations if you want to manage your database changes from within you application and commandbox-migrations if you want to do the same from within commandbox. Actually the commandbox version is just a wrapper around cfmigrations. The forgebox description gives a nice overview how to use this tool.

How to use: some MSSQL and java problems

So why this post, if the manual is already there? Because it doesn’t always work out of the box, and you have to know how to use this tool in commandbox. So let’s start.
First we are going to install commandbox-migrations. Start up commandbox in your homedir and execute

install commandbox-migrations

If you want to start using migrations, you have to initialize it. This will not do any database activity yet, it just creates the right keys in your box.json.

migrate init

The following entry will be created in your box.json

    "cfmigrations":{
        "schema":"${DB_SCHEMA}",
        "connectionInfo":{
            "password":"${DB_PASSWORD}",
            "connectionString":"${DB_CONNECTIONSTRING}",
            "class":"${DB_CLASS}",
            "username":"${DB_USER}"
        },
        "defaultGrammar":"AutoDiscover"
    }

Save yourself some trouble and change your default grammar immediately, because the Autodiscover probably doesn’t work within commandbox. Your options (as documented here) are: MSSQLGrammar, MySQLGrammar, OracleGrammar and PostgresGrammar. We choose MSSQLGrammar. As you can see from the box.json, most options are populated with environmental variables instead of real values (the values surrounded by ${} ). This is a good practice so your credentials don’t end up in you code, and you probably need different values for development and production. To use environmental variables in your commandbox environment you can install the commandbox-dotenv package. Now you can create your environmental variables in a file called .env in your root directory. Please make sure you exclude this file from version control!!!

Now you need to set your environmental variables DB_PASSWORD, DB_USER, DB_SCHEMA, DB_CONNECTIONSTRING and DB_CLASS. Values for password and user are obvious, but what about the other values? In my environment I can leave the DB_SCHEMA empty, but I need values for DB_CONNECTIONSTRING and DB_CLASS. If you don’t know these values yet, you can find these values by creating a a datasource in your Lucee Admin. If you edit this datasource, you will find some useful info at the bottom of this screen, like this

datasource properties

So I entered all values for the Microsoft driver in my .env file and tried to install the tables necessary for cfmigrations (by default called cfmigrations. I executed this:

migrate install

This will NOT work. You have to reload commandbox first, to retrieve your added (or changed) environmemtal variables. You can do this just by executing the command reload. If you forget to reload, you will probably get a java.lang.NullPointerException. But after reloading and trying to create a valid MSSQL connection we will enter Microsoft/Java problem territory

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “java.security.ProviderException: java.security.KeyException

For some reason the Microsoft driver is not very cooperative in a commandbox environment, although I have no issues when used in my Lucee server. The commandbox gitbook manual has some info on hitting databases from commandbox: https://commandbox.ortusbooks.com/task-runners/hitting-your-database and indeed:

Microsoft SQL Server Issues
If you are getting SSL related exceptions when trying connecting to an MS SQL database using the Microsoft SQL Server (JDBC4) driver (com.microsoft.sqlserver.jdbc.SQLServerDriver), try using the jTDS driver (net.sourceforge.jtds.jdbc.Driver) instead.

So, that’s really a waste of time if you try to use cfmigrations with SQLServer for the first time! Actually I ‘ve used it a zillion times with older versions of commandbox back in the Lucee 4.5 days without any problems. But hey, problem solved! I changed my settings in .env and reloaded commandbox

jtds instead of the microsoft driver

If I execute migrate install again, cfmigrations is ready for some real database modifications, which I will describe in a later post.

Cfmigrations really is a very handy tool. It is nice if you can reconfigure your database tables directly from commandbox, but you really need some extra info in advance, such as:

  • how to work with commandbox-dotenv
  • why is a SQL driver misbehaving in a commandbox environment, and more important: how to avoid this by using the jTDS driver.

I am sure the process is a lot easier with some other databases, but I hope this write-up helps. If you really want to avoid all the trouble in configuring your database, you can also use cfmigrations from within your application, where you have your datasource already available.

© 2020 ShiftInsert.nl

Theme by Anders NorenUp ↑