englishteeth.co.uk

The usual grails approach is to create the database to reflect the model, but if there is an existing database there are a few things to bear in mind.

I got a good start on this from Grails on Oracle, but it’s a bit out of date now and is still from a having control over the database angle.

In the configuration
I was getting an “Unsupported feature” org.springframework.jdbc.support.MetaDataAccessException
Although this appeared to be down to the target database not supporting auto-detection, I was providing org.hibernate.dialect.OracleDialect in the hibernate properties. However, after checking the documentation on Grails Data Sources, this should have been in the data source.

dataSource {
  pooled = true
  driverClassName = "oracle.jdbc.OracleDriver"
  dialect = "org.hibernate.dialect.OracleDialect"
  username = "username"
  password = "password"
}
hibernate {
  show_sql = "true"
  cache.provider_class = "org.hibernate.cache.NoCacheProvider"
  current_session_context_class = "thread"
}

environments {
  development {
    dataSource {
      dbCreate = "update"
      url = "jdbc:oracle:thin:@path:port:sid"
    }
  }
}

After sorting that, the errors were SQL grammar related and needed to be dealt with at the domain object level; show_sql = “true” did work in the hibernate properties and helped identify where these were coming from.

In the domain objects
The differences are captured in mapping declaration… (duh!)

• I didn’t want my domain object to be the same name as the table.
• The id column in the table was not to the grails convention.
• There was no version column in the table.

(There was a lastUpdated column, but the first task was to get something working before I worried about optimistic locking!)

class MyDomainObject {

  static mapping = {
    table 'db_domain_object'
    id column:'domain_object_id'
    version false // no version column in table
  }

  String someProperty
  String someOtherProperty

}

Nothing earth shattering or difficult, but not exactly obvious from the server output.

Currently Oracle (10g and earlier) does not provide a function to test if a value is numeric. However, it is relatively simple to implement.

The following clause will return the number of non numeric characters found in a string:

LENGTH(TRANSLATE(TRIM(string1), ' +-.0123456789', ' '))

A non zero result means the string is not numeric.

SELECT string_column AS Numbers
FROM table_of_strings
WHERE
LENGTH(TRANSLATE(TRIM(string_column), ' +-.0123456789', ' ')) = 0

Depending on environmental constraints, it may be preferrable to implement a function:

create or replace function getNumberFromString(inputString in varchar2)
  return number
is
  numberValue number;
begin
  numberValue := to_number(inputString);
  return numberValue ;
exception
  when VALUE_ERROR then return null;
end;

This could have been implemented as an “isNumeric” function returning a boolean result. However, this currently would not be supported in SQL that could otherwise benefit.

SELECT string_column AS Numbers
FROM table_of_strings
WHERE getNumberFromString(string_column) IS NOT NULL