CODEWITHSUNDEEP
grailsgrails-services
Sherif
Sherif

Reputation: 431

Delete records in Grails service

In Grails Service, I have to delete records from the Db, but I get below error :

spi.SqlExceptionHelper, Connection is read-only. Queries leading to data modification are not allowed.

Although, there is @Transactional(readOnly = false) in my service, here is the delete part in my service code:

def adsDurationIs7 = null
adsDurationIs7 = Ads.findAllByDuration("7 days", [sort: "dateCreated", order: "asc"])
adsDurationIs7.each {
   Ads.get(it.id).delete(flush: true)
}

Upvotes: 0

Views: 2092

Answers (2)

Burt Beckwith
Burt Beckwith

Reputation: 75681

I'm not sure why that's not working, but even if it did you're doing the work about as expensively as you possibly can. You load all of the instances into memory (including all non-lazy properties and collections), then for each one you get its id and use that to load the instance again with a get call (although if you're lucky and/or you have caching configured correctly this might be a no-op), and then use that to delete each database record one at a time. And you're ordering in the database, which adds processing time but is completely unnecessary since you're deleting everything that the query returns.

What you really want is GORM code that ends up running SQL similar to

delete from ads where duration=?

where the PreparedStatement sets the ? parameter value to "7 days".

This "where" query will do exactly that:

Ads.where { duration == '7 days' }.deleteAll()

as will this HQL update:

Ads.executeUpdate 'delete Ads a where a.duration = :duration',
                  [duration: '7 days']

So your service should look like

import grails.transaction.Transactional

@Transactional
class MyService {

   void deleteAdsDurationIs7() {
      Ads.where { duration == '7 days' }.deleteAll()
   }
}

or

import grails.transaction.Transactional

@Transactional
class MyService {

   void deleteAdsDurationIs7() {
      Ads.executeUpdate 'delete Ads a where a.duration = :duration',
                        [duration: '7 days']
   }
}

Upvotes: 4

Michal_Szulc
Michal_Szulc

Reputation: 4177

You are executing this service function from controller's function, which is not transactional. Add @Transactional to controller's function.

Here is an example: https://stackoverflow.com/a/21998182/2166188

Upvotes: 0

Related Questions