CODEWITHSUNDEEP
grails
Bill J
Bill J

Reputation: 55

Grails Runtime Exception - Value too long for column "PHOTO BINARY(255)

I am getting the captioned exception when tying to load jpeg files (between 3 and 8 KB) into a column defined as byte[], using Grails 2.2.3 - here's the error:

org.h2.jdbc.JdbcSQLException: Value too long for column "PHOTO BINARY(255) ... SQL statement: update profile set col1=?, ..., photo=?, ..., coln=? where id=?

This was working in Grails 1.3.7, using the org.hsqldb.jdbcDriver, but is now always failing. Should I be defining the variable differently? If so as what?

I tried changing all the byte[] photo code to byte[] photo = new byte[10000], but that did not help at all.

I am adding on to the original question in response to the answers I've been given. First of all thank you all. This is the relevant code in DataSource.groovy:

environment {
  developement {
    dataSource {
      dbCreate = "update"
      url = "jdbc:h2:file:devDb:MVCC=TRUE;LOCK_TIMEOUT=10000
    }
  }
}

The following file and code is where I thought the image was being loaded from - ImageController.groovy:

class PhotoUploadCommand {
  byte[] photo
  //def photo      // tried, makes no diff here
  String userId
  static constraints = { photo(maxSize: 1024 * 1024) }  // added as per suggestions
}
class ImageController {
  def imageService
  def upload = { PhotoUploadCommand puc ->
    def user = User.findByUserId(puc.userId)
    user.profile.photo = puc.photo
    //user.profile.photo = request.getFile('photo')  // also tried - N/G
    redirect(controller: 'user', action: 'profile', id: puc.userId)
}

If I comment out all the code beginning with class ImageController { and ending with the closing } it makes no difference - therefore, I must be wrong and this is NOT where the images are being loaded from. However, there is no other code in this entire application that I can find that directly relates to loading the photo. So it must be scafolding from the Profile.groovy class:

class Profile {
  byte[] photo
  //def photo  // tried, but then the element is removed from the template
  String fullName
  String bio
  String homepage
  String email
  String timezone
  String country
  String jabberAddress
  String skin

  static mapping = {
    photo column: 'photo', sqlType: 'VARBINARY(10000)', nullable: 'true'
    //photo(type: 'image')  // also tried
  }

  static constraints = {
    fullName(nullable: true)
    // ... 
    //photo(nullable: true, maxSize: 1024 * 1024)  // also tried
    photo(nullable: true)
    // ... 
  }

There is also a UserContoller.class that contains all these same columns and additionally:

String userId
String password
String passwordRepeat

... and changing byte[] photo to def photo there does nothing - neither does adding the mappings. Very confusing for a Java programmer.

Upvotes: 1

Views: 3294

Answers (4)

Laker
Laker

Reputation: 1

I had this problem, too. I tried about a dozen suggestions from the web--none of them worked.

That is until I changed my dbCreate for my development environment in DataSource.groovy. I had it set to "update" because I wanted to keep my data. Then I guessed that the database columns couldn't be resized unless the database was recreated. So I changed it to "create", ran grails run-app, shut it down, then switched it back to "update". I had to re-enter all my data, but the image upload worked from that point on.

Upvotes: 0

matcauthon
matcauthon

Reputation: 2261

You can also use the type defintion:

static mapping = {
    photo(type:'image')
}

It will be automatically mapped to the correct type of the database...

Make sure the database schema is set/upgraded properly.

Upvotes: 0

codelark
codelark

Reputation: 12334

You can use either the constraints or mapping closures to affect the hibernate column type:

Using constraints

Grails will examine the maxSize or size constraints to inform the size of column necessary.

static constraints = {
    photo maxSize: 10000
}

Using column definitions

Grails allows you to specify the sql datatype in the mapping closure. These can be specific to your DB vendor, but covers cases where the constraint hints aren't flexible enough to match your DB schema. e.g. Using postgres bytea columns.

static mapping = {
    photo column: 'photo', sqlType: 'VARBINARY(10000)'
}

Upvotes: 4

emilan
emilan

Reputation: 13075

Use constraints to set field size .

class MyDomain {
    .....
    byte[] photo

    static constraints = {
        photo(maxSize: 1024 * 1024)
    }
}

Upvotes: 2

Related Questions