Tag Archives: SQL

How to get Grails to use a reserved word with Microsoft SQL

If you’re building a Grails application using a Microsoft SQL database, and you want to have a property on an object that’s a reserved word in MSSQL, like RULE, you’ll probably end up with a SQL syntax error because Grails, Gorm and Hibernate don’t know what words are reserved in MSSQL.

You can just use another name. Does the property need to be called MyObject.rule? You might want to call it something more specific anyway. Problem avoided!

But if you want to keep the name, or if you can’t change it in the DB (say, because you’re connecting to a legacy database), you can get it to work!

Use a custom mapping. Grails lets you map a domain object’s properties to a column that doesn’t fit the usual camelCase → snake_case pattern, and you can use the same mechanism to add the delimiters that MSSQL needs to use the reserved word as a column name. Problem solved!

static mapping = {
    rule column: '[rule]'
}

Now Gorm/Hibernate will use the right delimiters on the back-end, and you can keep accessing myObject.rule, MyObject.findByRule() and so on.

Dates and ColdFusion Query of Queries

Argh.

If you need to run any date comparisons on a ColdFusion Query of Queries, you need the following:

  • In the original database query, select the values as datetime. DO NOT cast them as date. (This is with MSSQL, anyway).
  • In the follow-up query, use CFQUERYPARAM and the cf_sql_date type.

I spent waaay too long trying to select subsets of the original query based on dates, and whether I selected them as strings (for implicit conversion) or using query parameters, I just couldn’t get any results out of them. It turned out that since the original query had cast the datetime value as a date, I couldn’t make any comparisons in the query of queries.

Once I removed that cast (originally there to simply group timestamps by day, but I’d since changed the query to group by month), it worked.

<cfquery name="realquery">
select mydate, mystuff from mytable
</cfquery>

<cfquery dbtype="query" name="followup">
select mystuff from mytable
where mydate = <cfsqlparam value="#somedate#"
                   cfsqltype="cf_sql_date" />
</cfquery>

Obviously this example is oversimplified, but you get the idea.

Links: Clouds, Scott Pilgrim, the Blue ‘e’, and Bobby Tables