How do you invoke a database function call from grails?

Question

How do you invoke database functions from grails? Such as the CONVERT(decimal, timestamp) Without using groovy SQL like this:

def sql = new Sql(dataSource)

I would like to avoid any SQL statements and take full advantage of the grails magic.

score 5 · Accepted Answer · edited May 23 '17 at 10:34

5

My understanding is that grails does not have native support for database functions. You will need to write SQL. Gorm is an abstraction of Hibernate, you may want to refer to:

How can you call custom database functions with Hibernate?

edited May 23 '17 at 10:34

Community

1
1

answered Aug 02 '12 at 18:43

chrislovecnm

2,549
3
20
36

1

Yes, the route to take here is to have Hibernate execute the SQL, or use Groovy SQL. – cdeszaq Aug 02 '12 at 18:46
I thought this might be the case but I just wanted to make sure :/ Thanks! – PJT Aug 02 '12 at 19:15

Ritesh Maddala · Answer 2 · 2021-02-19T17:12:00.470

In your Domain class First you must -

import groovy.sql.Sql

Then you can create Sql instance and call a function like below -

    def date = "'21-Feb-19 14:10:10.123000'"
    def timestamp_format = "'DD-Mon-RR HH24:MI:SS.FF'"
    String query = 'select to_timestamp('+date+','+timestamp_format+') from dual'
    def sql = Sql.newInstance('url', 'username', 'password', 'driverClassName')
    def result= sql.rows(query);
    def rows = result.collect{it.values()}
    println rows

How do you invoke a database function call from grails?

2 Answers2