Using the New OrderedTestDecorator in MXUnit 2.1.1

One request that has come up frequently from users of the ColdFusion Koans was to have the ability to see the test results in order.  This weekend I created a new test decorator that will allow users to do just that, set the order of the test results via annotation.  Thinking it would be useful for others I opened a pull request on the MXUnit project and it was merged into the master branch on GitHub and added to the new 2.1.1 release.

In order to use the new decorator you must have MXUnit 2.1.1 or newer.  One way to check if you have the required decorator is to look in the mxunit/framework/decorators directory for the file OrderdTestDecorator.cfc.

In order to set the order of your tests two things have to happen.  The first is to add the custom decorator to you test component.

In a script based component the decorator is added like so:



In a tag based component you can add the decorator like this:



Once you have the annotation added to your test, you can start ordering them by adding the @order annotation to each test like so:

In a script based component the annotation looks like this:



In a tag based component it looks like this:



That's it, now your tests will run in the order you specified.

Use Oracle's FREE SQL Developer as a Microsoft SQL Server GUI on OSX

I'm on a Mac, my company uses a SQL Server, and since there is not native SQL Server Enterprise manager client I was forced to find a workaround. One common solution is to have aVirtual Machine running windows but to me that approach feels like using a sledgehammer to pound in a finishing nail.  Thanks to this post on stackoverflow I am able to use Oracle's FREE SQL Developer application to connect to Microsoft SQL Server Instances on my MacBook Pro running Lion.  I've used SQL Developer for Oracle development for a number of years now so the learning curve was pretty small for me but anybody who is familiar with SQL Server Enterprise Manager should be able to find their way around pretty easily, however there are a few things to be aware of which I'll discuss a bit later.


Installation


Before you can install SQL Developer you'll need to make sure that you have the JDK installed on your machine.  I have the Java for Mac Developer Preview installed on my machine from: https://developer.apple.com/downloads/.


Once the JDK is installed (you can check by opening a terminal and typing  javac -version ) the next step is to download and install SQL Developer from Oracle's website here.  Once SQL Developer is installed you'll need to get the jDTS plugin files from: http://sourceforge.net/projects/jtds/files/, at the time of this writing the most current version is 1.2.5.  Once the zip is download and extracted move the entire folder someplace where your account will have execute permissions on the jdsts-1.2.3.jar file.  I have a lib directory in my User folder where I keep all my third party .jar files.

You now have everything you need to get SQL Developer executing SQL queries, you just need to do some simple setup in SQL Developer.  Open SQL Developer and as of version 3.0 go to Tools -> Preferences  and expand the "Database" node.  Next click on Third Party JDBC Drivers click on "Add Entry...".  From this menu browse to the /jdts-1.2.5-dist folder you copied in the step above and select jdts-1.2.5.jar (or whichever version corresponds to the version you downloaded) and click "OK".

Configuration

SQL Server Connection Tab

Once you have the plugin installed exit out to the main SQL Developer IDE and add a new connection by either: clicking on File -> New -> Database Connection or by clicking on the green + icon in the "Connections" tab.   You should now see a "SQLServer" tab where you can enter the connection information for your SQL Server.

Using SQL Developer to Query Microsoft SQL Server Databases

Select Default Database

If you have not used SQL Developer before there are a few things to be aware of.  Since it is primarily an Oracle IDE you have to think in a somewhat "Oracle" way when running queries on your database. Since Oracle is a schema-centric database server you'll save yourself lots of typing if you set a default database for your current connection.   If you don't you'll have to type the fully qualified table name for each table you reference in a query (e.g. database.owner.tablename).  To do this right click on the database you'd like to use and click on "Select Default Database" in the context menu.  Once you do this you can do normal SELECT x FROM Table queries and leave out the fully qualified table name.  You'll have to do this each time you connect to SQL Server.   

SQL Developer will allow you to do many of the basic database tasks you can do in Enterprise Manager, however there are quite a few limitations. You can view your Table/View structure and data, you can also view most of the other objects in SQL Server like Stored Procedures and Functions.   SQL Developer will also allow you write DML (Data Manipulation Language) statements like Update, Delete, and Insert.   You can also write some DDL (Data Design Language) statements like ALTER TABLE.  However, the one huge shortcoming of SQL Developer is its inability to recognize the "BEGIN" keyword.  This means that any statement that uses BEGIN, like transactions, stored procedures, and functions will all generate a syntax error and not execute.

One nice "benefit" of using SQL Developer is the "Format" context menu.  When I'm writing code I often get SQL statements in debugging code, however, its usually one long string, debugging this code in Enterprise Manager usually meant having to manually update format the SQL, however in SQL Developer I can paste the code into the SQL Worksheet, right click, and click on "Format" and have a nicely formatted SQL Statement.  

Conclusion

While its not a complete replacement for Enterprise Manager I do find that SQL Developer will allow me to do many of the things I spend 90% of my time doing to my databases.  The best part is that this is a completely Free and somewhat mature solution.     

ColdFusion Quickie: Using the null attribute in cfqueryparam

In a database with foreign key constraints a "null" value is typically used to store a value that doesn't have a link to the referenced table. Lets look at a simple example:

In this example we have a contact table with a foreign key reference to the address table.  This means that every value in the addressId column must have a matching value in the addressId table.  There may be situations where a contact's address is not known so in order to satisfy the foreign key constraint the value "null" must be stored in the addressId field.  

Now lets look at the typical CFC's for these objects: 

Contact.cfc

/** * @accessors true */component Contact{ /** *@getters true *@setters true */ property numeric contactId; /** *@getters true *@setters true */ property String firstName; /** *@getters true *@setters true */ property String lastName; /** *@getters true *@setters true */ property address Address; public void function init(numeric contactId = 0,                                   String firstName = "",                                  String lastName = "",                                   Address address = new Address()) output=false hint="default constructor"{  variables.contactId = arguments.contactId;  variables.firstName = arguments.firstName;  variables.lastName = arguments.lastName;  variables.address = arguments.address; }}

Address.cfc

/*** @accessors true*/component{ /** *@getters true *@setters true */ property numeric addressId; /** *@getters true *@setters true */ property String line1;                //rest of implementation hidden public void function init(numeric addressId = 0,                                   String line1 = "",                                   String line2 = "",                                   String city = "") output=false hint=""{  variables.addressId = arguments.addressId;                ... }}

Most of this code is quite uninteresting, however, whats important to note is that the addressId field is instantiated to a value of 0 in the default constructor.   If we were to write our own persistance layer inserting this value into the database would throw a foreign key constraint error, since there is likely to be no matching Address record with an addressId of 0.

We all know by know that we should use cfqueryparam for all of our params.  As it turns out this function has an attribute called "null".  When the "null" attribute is set to true cfqueryparam ignores the "value" attribute and inserts a value into the database.  In our above example we would write our insert/update statements like so (assuming that the contactId field is auto-generated):

<cfquery name="insertQuery" datasource="#variables.datasource#">INSERT INTO    Contact(firstName           ,lastName           ,addressId)VALUES(      <cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getFirstName#" />     ,<cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getLastName#" />     ,<cfqueryparam cfsqltype="cf_sql_varchar"                        value="#Contact.getLastName#"                        null="#Contact.getAddressId() eq 0#" />)</cfquery>

Now if we insert a record with the default addressId value of 0 our database wont throw an error because ColdFusion will insert the value into the database, this is assuming that the column does allow the value of of course.