Clear out and restore fixture data using Grails/GORM

Question

I have a need to reset GORM fixture data (H2) on a controller request. So I created the following controller...

def dataSource
FixtureLoader fixtureLoader
def index() {
    Sql sql = new Sql(dataSource)
    sql.execute("DROP ALL OBJECTS DELETE FILES")
    sql.close()
    fixtureLoader.load('f1','f2','f3')
}

I would expect this to clear out the DB, however, I still get the following error

[org.codehaus.groovy.grails.web.errors.GrailsExceptionResolver] NonUniqueObjectException occurred when processing request: [GET] a different object with the same identifier value was already associated with the session: [*]. Stacktrace follows: org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [*]

another weird thing is if I go into the dbconsole and blow everything away using the 'DROP ALL OBJECTS DELETE FILES' command and when I go the the controller I get the same error. Even though I can confirm they are wiped out of the DB.

UPDATE

So I have tried a few things...

1.) 'DROP ALL OBJECTS DELETE FILES' seems to work as expected in dbconsole. but the code

    Sql sql = new Sql(dataSource)
    sql.execute("DROP ALL OBJECTS DELETE FILES")
    sql.close()

Seems to not have the same effect.

2.) I have added the following lines

def sessionFactory
...
sql.close()
sessionFactory.currentSession.flush() //This one  
fixtureLoader.load('f1','f2','f3')

Then I go and delete in DB console (since the SQL seems to be failing), then run. However, when I run the function again I still see the fixtures.

Answer 1

To do this I basically did the same as the accepted answer here...

In my Bootstrap I create the tmp file and then when the controller is called I run it as a script and everything gets reset. I did also have to make sure that caching was removed per the comment.