Clone wiki

grails-datatables / DatabaseCompatibility

Database Compatibility

The GrailsDataTables plugin is designed to work with any type database that is compatible with Grails. It uses Hibernate criteria queries to query your domain objects, so Hibernate takes care of the variations in SQL support.

However, when a text search is performed on a date column, it is necessary to convert the date to a string and compare that to the search string. Since there is no specific Hibernate Criteria for that, a SqlRestriction is used to convert the date to a string and perform a like comparison. For example:

TO_CHAR(columnName, 'MM/DD/YYYY') like ?

Various databases use different SQL to convert dates to strings, therefore it is necessary to detect the type of database in use and then use the appropriate conversion SQL. This is performed by the getDateSearchSql method in DataTablesService:

protected def getDateSearchSql(def columnName, def dateFormat, def dateFormatLength)

Since the plugin is new, this has thus far only been tested with H2 and Oracle. If you test it with another type of database, please let me know whether it works. In order to test, you will have to put a date column in your table and then try to search within it.

If you discover a problem, you can probably resolve it yourself by extending DataTablesService and overriding the getDateSearchSql method.

See Also

column name, dateFormat, dateFormatLength

Back to Home.